DBA SQL AWR

DIKKAT :

Asagidaki tum SQL komutlari, ORACLE AWR raporlarindan bilgiyi alir. Bu yuzden oncelikle sistem uzerinde AWR raporlarinin calismis oldugundan emin olmalisiniz. Ayrica AWR raporlarinin calisma zamanlari en az yarimsaat olmali ki daha dogru sonuclar alinabilsin.

Oracle 10g uzerinde RAC sistemleri mevcut ise insantce degerlerinden bu gorulebilir. Eger yoksa instance_number sadece 1 olacaktir.

AWR raporlarinin baslangic tarih ve saat ile bitis tarih ve saat degerlerini istediginiz gibi girebilirsiniz. Ozellikle is saatleri arasinda alacaginiz raporlamalar size daha dogru sonuclar verecektir.

/* ###
/* ### Oracle CPU Kullanim Oranlari (CPU yogunlugunun gorulmesi)
/* ###

select smr.INSTANCE_NUMBER,

min(smr.MINVAL) min, max(smr.MAXVAL) max,

round(avg(smr.MINVAL)) minavg, round(avg(smr.MAXVAL)) maxavg,

round(avg(smr.AVERAGE)) avg

from DBA_HIST_SYSMETRIC_SUMMARY smr,

v$database d

where

smr.BEGIN_TIME >= to_date('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

smr.END_TIME <= to_date('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

smr.DBID=d.DBID and

smr.METRIC_NAME='Database CPU Time Ratio'

group by smr.INSTANCE_NUMBER;



INSTANCE_NUMBER MIN MAX MINAVG MAXAVG AVG

--------------- ---------- ---------- ---------- ---------- ----------

2 0 41.5811655 0 26 18

1 0 34.9719813 0 20 12

=============================================================================================

/* ###
/* ### Oracle Bekleme Oranlari (Oracle sisteminin kullanicilardan is bekleme zaman degerleri)
/* ###

select smr.INSTANCE_NUMBER,

min(smr.MINVAL) min, max(smr.MAXVAL) max,

round(avg(smr.MINVAL)) minavg, round(avg(smr.MAXVAL)) maxavg,

round(avg(smr.AVERAGE)) avg

from DBA_HIST_SYSMETRIC_SUMMARY smr,

v$database d

where

smr.BEGIN_TIME >= to_date('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

smr.END_TIME <= to_date('YYYY-AA-GG S2','YYYY-MM-DD HH24') and

smr.DBID=d.DBID and

smr.METRIC_NAME='Database Wait Time Ratio'

group by smr.INSTANCE_NUMBER;



INSTANCE_NUMBER MIN MAX MINAVG MAXAVG AVG

--------------- ---------- ---------- ---------- ---------- ----------

2 0 98.7966488 0 92 82

1 0 99.4121159 0 95 88



=============================================================================================

/* ###
/* ### En çok CPU kullanan -yoran- ilk 10 SQL (CPU TIME Degerine göre)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by CPU_TIME desc

)

where rownum<=10;





INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

--------------- ------------- ---------- ------------ ---------- -----------

2 b5hzmkcpbmn23 29148 14241 7619 211526710

1 6ahddxp5un7a3 7 4333 3760 79255773

1 gcfyqusdngbv4 207011 5213 3746 701526739

1 53wbuxzx13ndw 313 67766 2941 92357833

1 b5hzmkcpbmn23 9073 4675 2774 64520393

2 g5jnvm7kgtyah 1586 4345 2210 235469477

2 gxknp4j7gd8g0 6331013 2809 2040 186057685

1 829355xf7xjj4 3513 2592 1356 42605109

1 1z6rfm3gfbzmv 125 27509 1127 29389172

1 gqau08wu3zrgp 286155 13099 981 6029967

=============================================================================================

/* ###
/* ### En uzun süren ilk 10 SQL (Elepsad_time degerine gore):
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2','YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by ELAPSED_TIME desc

)

where rownum<=10;



INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

--------------- ------------- ---------- ------------ ---------- -----------

1 53wbuxzx13ndw 313 67766 2941 92357833

1 1z6rfm3gfbzmv 125 27509 1127 29389172

2 gt1sjbrcc75y7 26497 25423 57 1467588

2 cm273h7pxw8k9 52906 25214 127 1641933

2 af582nx1dpb7y 74522 24179 118 973147

1 17hz8amdfpkth 305 21702 406 4864716

2 4xvr2wj20qjna 5228 20716 357 16711832

1 dbcc5nqf4dyxf 104817 18855 244 6366708

1 d0k0dj5v6yc6p 287381 17005 960 5148445

1 20wbqjfrqmfdx 85300 17001 236 7369135

=============================================================================================

/* ###
/* ### En çok mantiksal okuma yapan ilk 10 SQL (Buffer Gets degerine gore)
/* ###



select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1’,'YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by BUFFER_GETS desc

)

where rownum<=10;



INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS

--------------- ------------- ---------- ------------ ---------- -----------

1 gcfyqusdngbv4 207011 5213 3746 701526739

2 g5jnvm7kgtyah 1586 4345 2210 235469477

2 b5hzmkcpbmn23 29148 14241 7619 211526710

2 gxknp4j7gd8g0 6331013 2809 2040 186057685

1 53wbuxzx13ndw 313 67766 2941 92357833

2 gcc76mbkfzbrg 19962 1347 909 87347052

1 6ahddxp5un7a3 7 4333 3760 79255773

1 b5hzmkcpbmn23 9073 4675 2774 64520393

1 79aunmuktf6sf 8613 1811 956 47733280

2 a859cj7gtwggw 3909566 535 326 43179158

=============================================================================================

/* ###
/* ### En çok DISK okuma yapan ilk 10 SQL (DISK_READS degeri gore)
/* ###

select * from

(

select sqs.INSTANCE_NUMBER, sqs.SQL_ID,

sum(EXECUTIONS_DELTA) EXECUTIONS,

round(sum(ELAPSED_TIME_DELTA/1000/1000)) ELAPSED_TIME,

round(sum(CPU_TIME_DELTA/1000/1000)) CPU_TIME,

sum(BUFFER_GETS_DELTA) BUFFER_GETS,

sum(DISK_READS_DELTA) DISK_READS

from DBA_HIST_SQLSTAT sqs,

DBA_HIST_SNAPSHOT snp,

v$database d

where

snp.BEGIN_INTERVAL_TIME >= to_timestamp('YYYY-AA-GG S1','YYYY-MM-DD HH24') and

snp.END_INTERVAL_TIME <= to_timestamp('YYYY-AA-GG S2’,'YYYY-MM-DD HH24') and

sqs.DBID=d.DBID and

sqs.DBID=snp.DBID and

sqs.INSTANCE_NUMBER=snp.INSTANCE_NUMBER and

sqs.SNAP_ID=snp.SNAP_ID

group by sqs.INSTANCE_NUMBER,sqs.SQL_ID

order by DISK_READS desc

)

where rownum<=10;



INSTANCE_NUMBER SQL_ID EXECUTIONS ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS

--------------- ------------- ---------- ------------ ---------- ----------- ----------

1 53wbuxzx13ndw 313 67766 2941 92357833 4428838

2 33ug56m93d3za 0 12325 285 7445226 3156943

2 3n4p7gcjah2tb 1 11248 228 7907331 2527719

2 fwygkuyv8csvk 1 10730 200 7075954 2191880

1 1z6rfm3gfbzmv 125 27509 1127 29389172 1637314

1 17hz8amdfpkth 305 21702 406 4864716 1245073

2 4xvr2wj20qjna 5228 20716 357 16711832 1176634

1 gdna3tyy8ntu8 379 16587 419 9084651 1144990

1 8acwusvy4jx71 102 11412 497 19688222 894536

2 c0njscvt37djh 1 2404 75 1347772 788968

=============================================================================================