Sunday, December 23, 2007

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here are ways to get both while you dont have statspack snapshot you can use them as alternate.



QUERY(cusomizable,according to your workload and) and Sample Output from SPOOL

1 SELECT ses.sid
2 , DECODE(ses.action,NULL,'online','batch') "User"
3 , MAX(DECODE(sta.statistic#,9,sta.value,0))
4 /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
5 , MAX(DECODE(sta.statistic#,40,sta.value,0))
6 /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
7 , 60*24*(sysdate-ses.logon_time) "Minutes"
8 FROM V$SESSION ses
9 , V$SESSTAT sta
10 WHERE ses.status = 'ACTIVE'
11 AND sta.sid = ses.sid
12 AND sta.statistic# IN (9,40)
13 GROUP BY ses.sid, ses.action, ses.logon_time
14 ORDER BY
15 SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
16* / greatest(3600*24*(sysdate-ses.logon_time),1) DESC
17
SQL> /

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
9 online 12913.0075 12912.9963 26.7166667
5 online 98.9491051 98.9362416 29.8
14 online 4578.26388 .012476606 26.7166667
19 online 3170.5866 .00270636 24.6333333
20 online 1328.76316 .035087719 1.9
18 online .111731844 .026536313 11.9333333
7 online .749860101 0 29.7833333
21 online .2 0 .5
6 online .016219239 0 29.8
1 online 0 0 29.8166667
2 online 0 0 29.8166667

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
3 online 0 0 29.8166667
4 online 0 0 29.8

13 rows selected.



1 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
2 FROM V$SQLAREA
3 WHERE buffer_gets > 1000
4 OR disk_reads > 100
5* ORDER BY buffer_gets + 100*disk_reads DESC
SQL>
SQL> /

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
---------- ---------- ----------- ---------- -----------
2626326413 1 572 384 1
690085868 1 2575 134 1
2963598673 349 1095 55 6
657604649 1 1172 18 1

1 comment:

Anonymous said...

The requisites for mortgage qualification are also
in accordance to these of students. What is the need for rebuilding bad
credit score? One particular kind of home enhancement mortgage is known as a
house enhancement equity mortgage.

My web page - lilithwallpapers.com