58.Oracle杂记——Oracle诊断常用命令

58.Oracle杂记——Oracle诊断常用命令

1.  正在等事件的等待次数

select event "Wait Event",  sum(seconds_in_wait) "Waited So Far(sec)",   count(sid) "Num Sess Waiting"  from v$session_wait  group by event order by 3 desc; 

 

2.  系统最主要的等待事件

set linesize 200  

col "Wait Event" for a45  

select EVENT "Wait Event",TIME_WAITED "Time Waited",  round(TIME_WAITED/(SELECT SUM(TIME_WAITED) FROM v$system_event),2) "%Time waited",  TOTAL_WAITS "Waits",   round(TOTAL_WAITS/(SELECT SUM(TOTAL_WAITS) FROM V$system_event),2) "%Waited"  from v$system_event order by 3 desc;  

 

3.  查询产生该等待事件最多的哪些会话

select sid,event "Wait Event",state "Wait Stat",  

       wait_time "W'd So Far(secs)", seconds_in_wait "Time W'd (secs)"  

from v$session_wait  

where event like '&event_name'  

order by 5 desc;  

4.  资源消耗最大的会话ID和SQL address

select cpu.sid "SID",cpu.username"USER Name",cpu.value "CPU(sec)", reads.value "IORead(k)",writes.value "IO Write(k)",cpu.sql_address from

  (select a.sid sid,a.sql_address,a.usernameusername,b.name,c.value value,a.serial# serial# 

from v$session a,v$statname b,v$sesstat c wherea.sid=c.sid and b.statistic#=c.statistic# and b.name='CPU used by thissession') cpu,(select a.sid,a.username,b.name,c.value value from v$sessiona,v$statname b ,v$sesstat c

where a.sid=c.sid andb.statistic#=c.statistic# and b.name='physical reads') reads,

 (select a.sid,a.username,b.name,c.value valuefrom v$session a,v$statname b ,v$sesstat c

where a.sid=c.sid andb.statistic#=c.statistic# and b.name='physical writes') writes

where cpu.sid=reads.sid and reads.sid=writes.sidand cpu.username is not null order by cpu.value desc; 

5.  通过Sql Address找SQL语句

select sql_text "SQL Statement Text"  

from v$sqlarea  

where ADDRESS='&sql_address'; 

6.  通过SID找出SQL语句

 SELECT sql_text  

    FROM v$sqltext a  

   WHERE a.hash_value = (SELECT sql_hash_value  

                           FROM v$session b  

                          WHERE b.sid = '&SID')  

ORDER BY piece ASC  

7.  查找哪些语句引起的等待，按照最消耗资源的顺序排列显示

select * from   

        (select address "Stmt Addr",  

                disk_reads "Disk RDS",  

                buffer_gets "Buff Gets",  

                sorts "Sorts",  

                executions "Runs",  

                loads "Body Loads"  

          from v$sqlarea where disk_reads > &A  

          order by disk_reads  )  

where rownum < &B;  

8.  查看隐藏参数

select x.ksppinm name,y.ksppstvlvalue,x.ksppdesc pdesc from sys.x$ksppi x,sys.x$ksppcv y where x.indx=y.indxand x.ksppinm like '%&par%';