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

6 comments:

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

oakleyses said...

tory burch outlet, polo ralph lauren, ugg boots, chanel handbags, tiffany jewelry, longchamp outlet, longchamp pas cher, gucci handbags, sac longchamp pas cher, uggs on sale, jordan pas cher, michael kors pas cher, ray ban sunglasses, oakley sunglasses, polo outlet, louboutin pas cher, longchamp outlet, burberry pas cher, louis vuitton, cheap oakley sunglasses, replica watches, replica watches, longchamp outlet, christian louboutin outlet, prada handbags, oakley sunglasses wholesale, louis vuitton outlet, oakley sunglasses, ray ban sunglasses, louis vuitton, prada outlet, nike outlet, louis vuitton outlet, christian louboutin, nike free run, nike roshe, ugg boots, nike air max, polo ralph lauren outlet online, kate spade outlet, christian louboutin uk, tiffany and co, nike free, oakley sunglasses, jordan shoes, louis vuitton outlet, nike air max, ray ban sunglasses

oakleyses said...

nike tn, ralph lauren uk, true religion outlet, nike air max uk, burberry handbags, michael kors, abercrombie and fitch uk, nike blazer pas cher, oakley pas cher, timberland pas cher, hogan outlet, uggs outlet, michael kors outlet online, replica handbags, sac vanessa bruno, true religion outlet, converse pas cher, guess pas cher, ray ban pas cher, ray ban uk, true religion outlet, michael kors outlet, mulberry uk, nike roshe run uk, hollister uk, coach outlet, kate spade, nike air max, new balance, true religion jeans, north face, coach outlet store online, uggs outlet, michael kors outlet online, polo lacoste, michael kors outlet, lululemon canada, nike air force, vans pas cher, michael kors outlet, hollister pas cher, michael kors outlet online, sac hermes, michael kors, burberry outlet, michael kors outlet online, north face uk, nike air max uk

oakleyses said...

converse, louboutin, soccer jerseys, ferragamo shoes, hermes belt, longchamp uk, p90x workout, north face outlet, baseball bats, instyler, nike trainers uk, babyliss, mont blanc pens, soccer shoes, nfl jerseys, valentino shoes, oakley, hollister clothing, abercrombie and fitch, chi flat iron, ralph lauren, nike air max, north face outlet, gucci, ray ban, vans, beats by dre, herve leger, jimmy choo outlet, giuseppe zanotti outlet, mac cosmetics, ghd hair, reebok outlet, nike roshe run, hollister, mcm handbags, timberland boots, nike air max, lululemon, bottega veneta, insanity workout, nike huaraches, vans outlet, hollister, iphone cases, wedding dresses, new balance shoes, converse outlet, asics running shoes, celine handbags

oakleyses said...

marc jacobs, ugg,uggs,uggs canada, louis vuitton, swarovski, barbour uk, montre pas cher, moncler outlet, louis vuitton, canada goose outlet, louis vuitton, pandora charms, wedding dresses, juicy couture outlet, moncler, canada goose outlet, louis vuitton, canada goose uk, ugg uk, canada goose, ugg pas cher, coach outlet, hollister, moncler, canada goose outlet, ugg, pandora jewelry, moncler outlet, karen millen uk, pandora jewelry, links of london, louis vuitton, canada goose, canada goose, swarovski crystal, moncler uk, moncler, moncler, doudoune moncler, juicy couture outlet, canada goose jackets, thomas sabo, replica watches, ugg,ugg australia,ugg italia, barbour, pandora uk, supra shoes, lancel, toms shoes

dong dong23 said...

retro jordans
cheap jordans
polo ralph lauren
longchamp outlet
gucci handbags
oakley sunglasses
ralph lauren
louis vuitton bags
louis vuitton handbags
toms outlet
cheap jerseys
adidas stan smith
supra shoes
louis vuitton outlet
tods outlet
hollister uk
michael kors outlet
louis vuitton outlet
oakley sunglasses
replica watches
tory burch sale
kate spade handbags
michael kors outlet online
nike uk
louis vuitton
giuseppe zanotti
fitflop sandals
michael kors outlet online
gucci handbags
kate spade handbags
hollister clothing
ray bans
coach factory outlet
nike roshe run
celine
jordans for sale
celine
hollister clothing
marc jacobs
christian louboutin shoes
20166.4wengdongdong