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

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

selectevent"WaitEvent",sum(seconds_in_wait)"WaitedSoFar(sec)",count(sid)"NumSessWaiting"fromv$session_waitgroupbyeventorderby3desc;

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

setlinesize200

col"WaitEvent"fora45

selectEVENT"WaitEvent",TIME_WAITED"TimeWaited",round(TIME_WAITED/(SELECTSUM(TIME_WAITED)FROMv$system_event),2)"%Timewaited",TOTAL_WAITS"Waits",round(TOTAL_WAITS/(SELECTSUM(TOTAL_WAITS)FROMV$system_event),2)"%Waited"fromv$system_eventorderby3desc;

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

selectsid,event"WaitEvent",state"WaitStat",

wait_time"W'dSoFar(secs)",seconds_in_wait"TimeW'd(secs)"

fromv$session_wait

whereeventlike'&event_name'

orderby5desc;

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,c.value value from v$sessiona,v$sesstat c

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

(select a.sid,c.value valuefrom v$session a,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语句

selectsql_text"sqlStatementText"

fromv$sqlarea

whereADDRESS='&sql_address';

6. 通过SID找出sql语句

SELECTsql_text

FROMv$sqltexta

WHEREa.hash_value=(SELECTsql_hash_value

FROMv$sessionb

WHEREb.sid='&SID')

ORDERBYpieceASC

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

select*from

(selectaddress"StmtAddr",

disk_reads"DiskRDS",

buffer_gets"BuffGets",

sorts"Sorts",

executions"Runs",

loads"BodyLoads"

fromv$sqlareawheredisk_reads>&A

orderbydisk_reads)

whererownum<&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%';