Oracle 性能维护方法

-- oracle 查询最近执行过的 SQL语句
SELECT BUFFER_GETS,EXECUTIONS,SQL_TEXT FROM V$SQLAREA; 

-- 查找出被锁的表  
select b.owner,b.object_name,a.session_id,a.locked_mode  
from v$locked_object a,dba_objects b  
where b.object_id = a.object_id;  

select b.username,b.sid,b.serial#,logon_time  
from v$locked_object a,v$session b  
where a.session_id = b.sid order by b.logon_time; 

select sess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_mode
   from v$locked_object lo,dba_objects ao,v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

-- 杀进程中的会话  
alter system kill session 'sid,serial#';  

-- oracle 查询最近执行过的 SQL语句
SELECT sql_fulltext,last_load_time FROM v$sql 
WHERE last_load_time IS NOT NULL ORDER BY last_load_time DESC;

-- 正在执行的
select a.username,a.sid,b.SQL_FULLTEXT
from v$session a,v$sqlarea b 
where a.sql_address = b.address ;

-- 执行过的
select b.SQL_TEXT,b.FIRST_LOAD_TIME,b.SQL_FULLTEXT
  from v$sqlarea b
where b.FIRST_LOAD_TIME between '2016-10-15/09:24:47' and
       '2016-12-16/09:24:47' order by b.FIRST_LOAD_TIME ;
       
select OSUSER,PROGRAM,USERNAME,SCHEMANAME,B.Cpu_Time,STATUS,B.SQL_FULLTEXT
from V$SESSION A LEFT JOIN V$SQL B 
ON A.SQL_ADDRESS=B.ADDRESS AND A.SQL_HASH_VALUE=B.HASH_VALUE 
order by b.cpu_time desc ;
       
SELECT * FROM (select PARSING_USER_ID,SORTS,COMMAND_TYPE,DISK_READS,sql_fulltext FROM v$sqlarea 
order BY disk_reads DESC )where ROWNUM < 10 ;

-- 查看占io较大的正在运行的session
SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes 
FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND 
st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 
AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' 
ORDER BY physical_reads DESC ;

-- 捕捉运行很久的SQL 
select username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text 
 from v$session_longops,v$sql 
 where time_remaining <> 0 
 and sql_address = address and sql_hash_value = hash_value;
 
 -- 查看数据表的参数信息 
 SELECT partition_name,high_value,high_value_length,tablespace_name,pct_free,pct_used,ini_trans,max_trans,initial_extent,next_extent,min_extent,max_extent,pct_increase,FREELISTS,freelist_groups,LOGGING,BUFFER_POOL,num_rows,blocks,empty_blocks,chain_cnt,sample_size,last_analyzed 
    FROM dba_tab_partitions 
   --WHERE table_name = :tname AND table_owner = :towner 
 ORDER BY partition_position ;
 
-- 查看有哪些用户连接 
select s.osuser os_user_name,decode(sign(48 - command),1,to_char(command),'Action Code #' || to_char(command) ) action,p.program oracle_process,status session_status,s.terminal terminal,s.program program,s.username user_name,s.fixed_table_sequence activity_meter,'' query,0 memory,0 max_memory,0 cpu_usage,s.sid,s.serial# serial_num   
 from v$session s,v$process p where s.paddr=p.addr and s.type = 'USER' 
 order by s.username,s.osuser;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!