Home » RDBMS Server » Enterprise Manager » EM SQL & Reference Collection Problem (Oracle Enterprise Manager 11g)
|
|
|
|
|
|
| Re: EM SQL & Reference Collection Problem [message #563215 is a reply to message #562625] |
Thu, 09 August 2012 14:50   |
 |
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I have found it easier to query gv$sqlarea directly to get these types of reports.
To get the percent of elapsed time each query uses. We use it to see what is
taking the longest time on the server.
ENWEBP1P > @v$sqlarea_elapsed_percent.sql
EXECUTIONS ELAPSED_TIME %elapsed HASH_VALUE SQL_TEXT
---------- ------------ -------- ---------- ------------------------------
172396 209938122 1.1 1788881921 /* load one-to-many com.nfl.si
37326 238628206 1.2 762605245 select registrati0_.registrati
2995 241307131 1.3 1037543043 /* select video from VideoV2 v
4929 257792581 1.3 283425879 /* select gameId from EcmRelat
10 269692806 1.4 14127136 /* select a from Article a lef
61983 310909421 1.6 2454737792 /* load one-to-many com.nfl.si
5495 409254166 2.1 1076646535 /* select a from Article a joi
7 444648851 2.3 2857689347 SELECT SYSDATE, MAX(CREATED_DA
7 444690743 2.3 3600608202 DECLARE SYS_DATE DATE; LAS
3371126 456536241 2.4 2866845384 select 1 from dual
12727 461298654 2.4 2615360058 /* SELECT p from Program p wh
120270 666523656 3.5 2266470811 /* select distinct a from Arti
5092 876510998 4.6 4021166266 select * from ( /* criteria qu
2561516 1035450725 5.4 1726352092 /* load collection com.nfl.sit
242483 1469209300 7.7 2605224548 select inst_id, blk_num, blk_s
24397 2042109548 10.7 2120468138 /* select a from Article a whe
ENWEBP1P > list
1 select executions,elapsed_time,
2 elapsed_time/
3 (select sum(elapsed_time) from v$sqlarea where elapsed_time>0)*100 "%
4 hash_value,
5 sql_text
6 from v$sqlarea
7 where elapsed_time > (select sum(elapsed_time) from v$sqlarea where e
8* order by elapsed_time
To see the average run time per execution, we use this after code pushes.
ENWEBP1P > @v$sqlarea_elapsed
TOT_SEC_ELAPSED HASH_VALUE EXECUTIONS INSTANC AVG_SEC_PER_EXEC SQL_TEXT
--------------- ---------- ---------- ------- ---------------- -----------
375.720241 2645225576 254 NWEBP3 1.47920772 select * fr
460.387118 2645225576 234 NWEBP4 1.96745791 select * fr
256.426049 1681916649 120 NWEBP3 2.13686593 /* criteria
367.813305 1681916649 163 NWEBP2 2.2565095 /* criteria
198.905837 1681916649 79 NWEBP4 2.51776353 /* criteria
ENWEBP1P > list
1 select elapsed_time/1000000 tot_sec_elapsed,hash_value,executions,i.i
2 elapsed_time/(executions+.001)/1000000 Avg_sec_per_exec,
3 sql_text
4 from gv$sqlarea s,gv$instance i
5 where executions >50 and elapsed_time/(executions+.001)/1000000>1
6 and upper(sql_text) not like '%DBMS_STATS%'
7 and upper(sql_text) not like '%WRH$%'
8 and upper(sql_text) not like '%WRI$%'
9 and upper(sql_text) not like '%OEM%'
10 and upper(sql_text) not like '%DR$%'
11 and upper(sql_text) not like '%DBMS%'
12 and upper(sql_text) not like '%DBID%'
13 and upper(sql_text) not like '%OWNER%'
14 and upper(sql_text) not like '%JOB$%'
15 and upper(sql_text) not like '%V$%'
16 and upper(sql_text) not like '%SEQUENCE#%'
17 and upper(sql_text) not like '%DBSNMP%'
18 and upper(sql_text) not like '%CTXSYS%'
19 and upper(sql_text) not like '%BACKUP_TYPE%'
20 and upper(sql_text) not like '%MGMT%'
21 and upper(sql_text) not like '%OBJ#%'
22 and elapsed_time/1000000>1 and s.inst_id=i.inst_id
23* order by elapsed_time/(executions+.001)/1000000
To find CPU HOGS:
ENWEBP1P > @v$sqlarea_cpu_percent.sql
DISK_READS buffers/exec CPU_TIME INSTANCE EXECUTIONS %_cpu HASH_VALUE SQL_TEXT
---------- ------------ ---------- -------- ---------- ------ ---------- --------
1 13.9 579508010 NWEBP2 846243 .3 2241319740 /* load
6 2720.9 583042285 NWEBP2 41371 .3 110562523 /* named
2448 11138.9 596025469 NWEBP3 9382 .3 1076646535 /* selec
101 120.0 658723356 NWEBP4 334576 .3 2900180164 /* PollH
13 360.3 668602300 NWEBP4 143892 .3 2454737792 /* load
0 .0 669596123 NWEBP2 25924311 .3 2866845384 select 1
2 2.2 679757697 NWEBP3 820554 .3 3778645433 /* named
2 3.3 702824992 NWEBP3 1006796 .4 1788881921 /* load
0 .0 714617190 NWEBP1 254207 .4 2605224548 select i
136 18.3 724516607 NWEBP4 161822 .4 2266470811 /* selec
401 2228.1 727473331 NWEBP2 33388 .4 2563421601 /* Selec
4789 11838.9 732537640 NWEBP1 5191 .4 4021166266 select *
4 3.5 754020388 NWEBP2 1711585 .4 1910456037 /* load
231 17.9 758488709 NWEBP3 176698 .4 2266470811 /* selec
3 3.5 769556982 NWEBP3 1884077 .4 1910456037 /* load
34 841.9 785875597 NWEBP3 63800 .4 3614017811 /* selec
0 11123.8 819064516 NWEBP3 12302 .4 1037543043 /* selec
6 10656.0 860123238 NWEBP4 12288 .4 1037543043 /* selec
88 3562.2 920310124 NWEBP3 25560 .5 110810247 /* named
0 23610.6 922019755 NWEBP3 9649 .5 1088851952 /* selec
1 22448.3 929441748 NWEBP4 9251 .5 1088851952 /* selec
214 2617.6 939493015 NWEBP3 34448 .5 2615360058 /* SELE
0 .0 996173545 NWEBP4 163134 .5 3672961273 SELECT v
2 23708.8 1000423916 NWEBP2 9978 .5 1088851952 /* selec
37 929.1 1026065094 NWEBP3 18332 .5 3072835175 /* crite
0 .0 1041964538 NWEBP2 385322 .5 2605224548 select i
0 2302.7 1136353326 NWEBP3 88016 .6 2503097366 /* named
0 .0 1219239130 NWEBP3 57377522 .6 2866845384 select 1
270 2617.3 1229049997 NWEBP4 37938 .6 2615360058 /* SELE
4 3526.0 1284563740 NWEBP2 34758 .7 110810247 /* named
0 .0 1381464084 NWEBP4 199386 .7 4056908702 SELECT v
0 .0 1406564175 NWEBP2 215055 .7 3672961273 SELECT v
155 10587.2 1418870284 NWEBP2 10570 .7 4021166266 select *
0 .0 1469323962 NWEBP4 56822059 .8 2866845384 select 1
185 3358.3 1576757275 NWEBP2 27286 .8 2120468138 /* selec
0 .0 1667520679 NWEBP3 236698 .9 4056908702 SELECT v
1 10533.4 1758329730 NWEBP4 12199 .9 4021166266 select *
0 3290.0 1850141540 NWEBP4 32159 1.0 2120468138 /* selec
129 10593.2 1888335952 NWEBP3 14585 1.0 4021166266 select *
0 .0 2007382636 NWEBP2 262845 1.0 4056908702 SELECT v
5846 5271.2 2120522417 NWEBP1 27022 1.1 2120468138 /* selec
99 3281.4 2184590039 NWEBP3 39298 1.1 2120468138 /* selec
378 1981.4 2553030084 NWEBP2 218287 1.3 2503097366 /* named
0 .0 3769519143 NWEBP4 1218972 1.9 2605224548 select i
0 .0 5.0620E+10 NWEBP3 22968041 26.0 2605224548 select i
ENWEBP1P > list
1 select disk_reads,
2 buffer_gets/(executions+.01) "buffers/exec",cpu_time,instance_name instance,
3 executions,
4 cpu_time/((select sum(cpu_time) from gv$sqlarea)+.01)*100 "%_cpu",
5 hash_value,sql_text
6 from gv$sqlarea s,gv$instance i
7 where i.inst_id=s.inst_id
8 and cpu_time/((select sum(cpu_time) from gv$sqlarea)+.01)*100>.01
9* order by cpu_time
To Find Disk Read Hogs:
ENWEBP1P > @v$sqlarea_PHYSICAL_READ_BYTES.sql
LOCKED_TOTAL PHYSICAL_READ_BYTES SQL_ID SQL_TEXT
------------ ------------------- ------------- -------------
2964 316751872 0aqpj2p02snn7 /* select a f
198 325312512 fx6ddh2tcj4a6 SELECT TH
19 396181504 3r5sxgju29skw select * from
30 738680832 f613u280dg410 /* select a f
28 2968092672 7gth8qufupw38 select * from
79 7693238272 aus40a5k400r9 /* criteria q
42 1.1811E+10 4xsbu1up59s83 SELECT SYSDAT
42 1.3965E+10 426fc9rb9tuya DECLARE SYS
ENWEBP1P > list
1 select locked_total,physical_read_bytes,sql_id,sql_text
2 from v$sqlarea where physical_read_bytes>1000000
3* order by physical_read_bytes
To find Disk Write Hogs:
ENWEBP1P > @v$sqlarea_PHYSICAL_WRITE_BYTES.sql
LOCKED_TOTAL PHYSICAL_WRITE_BYTES SQL_ID SQL_TEXT
------------ -------------------- ------------- -------------
2 103104512 8wx1x4m1w4xqn select * from
2 113770496 99rt1sf6g0dmw /* criteria q
6 113770496 aq88327jdnzp6 /* criteria q
6 113770496 f8tjpwtzxqt2x select * from
2 125960192 87xx4sga28qw0 /* criteria q
2 125960192 35bfjms2gh6q0 select * from
7 154656768 4frh91xfdvm5p select * from
6 154656768 267s9zf0kcf4v select * from
2 154656768 652gv59zbmy05 select * from
19 396165120 3r5sxgju29skw select * from
ENWEBP1P > list
1 select locked_total,physical_write_bytes,sql_id,sql_text
2 from v$sqlarea where physical_write_bytes>1000000
3* order by physical_write_bytes
[Updated on: Thu, 09 August 2012 14:52] Report message to a moderator |
|
|
|
| Re: EM SQL & Reference Collection Problem [message #563216 is a reply to message #563215] |
Thu, 09 August 2012 14:59 |
|
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
|
Senior Member |
|
|
I display the full sql with the sql_id or hash_value with the following 2 queries.
ENWEBP1P > select sql_text
2 from v$sqltext
3 where hash_value=1948574300
4 order by piece;
SQL_TEXT
----------------------------------------------------------------
select * from ( /* criteria query */ select this_.esb_id as esb1
_113_1_, this_.season_id as season2_113_1_, this_.season_type as
season3_113_1_, this_.defensive_assists as defensive4_113_1_, t
his_.defensive_fg_blocked as defensive5_113_1_, this_.defensive_
forced_fumble as defensive6_113_1_, this_.defensive_interception
s as defensive7_113_1_, this_.defensive_interceptions_avgyds as
defensive8_113_1_, this_.defensive_interceptions_long as defensi
ve9_113_1_, this_.defensive_interceptions_tds as defensive10_113
_1_, this_.defensive_interceptions_yards as defensive11_113_1_,
this_.defensive_passes_defensed as defensive12_113_1_, this_.def
ensive_punt_blocked as defensive13_113_1_, this_.defensive_sacks
as defensive14_113_1_, this_.defensive_solo_tackles as defensiv
e15_113_1_, this_.defensive_total_tackles as defensive16_113_1_,
this_.defensive_xp_blocked as defensive17_113_1_, this_.games_p
layed as games18_113_1_, this_.games_started as games19_113_1_,
this_.games_sub as games20_113_1_, this_.passing_20plus_yards_ea
ch as passing21_113_1_, this_.passing_40plus_yards_each as passi
ng22_113_1_, this_.passing_attempts as passing23_113_1_, this_.p
assing_average_yards as passing24_113_1_, this_.passing_completi
on_percentage as passing25_113_1_, this_.passing_completions as
passing26_113_1_, this_.passing_first_down_percentage as passing
27_113_1_, this_.passing_first_downs as passing28_113_1_, this_.
passing_fumbles as passing29_113_1_, this_.passing_interception_
percent as passing30_113_1_, this_.passing_interceptions as pass
ing31_113_1_, this_.passing_long as passing32_113_1_, this_.pass
ing_net_yards as passing33_113_1_, this_.passing_passer_rating a
s passing34_113_1_, this_.passing_sacked as passing35_113_1_, th
is_.passing_sacked_yards_lost as passing36_113_1_, this_.passing
_touchdown_percentage as passing37_113_1_, this_.passing_touchdo
wns as passing38_113_1_, this_.passing_yards as passing39_113_1_
, this_.receiving_20plus_yards_each as receiving40_113_1_, this_
.receiving_40plus_yards_each as receiving41_113_1_, this_.receiv
ing_average_yards as receiving42_113_1_, this_.receiving_first_d
own_percent as receiving43_113_1_, this_.receiving_first_downs a
s receiving44_113_1_, this_.receiving_fumbles as receiving45_113
_1_, this_.receiving_long as receiving46_113_1_, this_.receiving
_receptions as receiving47_113_1_, this_.receiving_target as rec
eiving48_113_1_, this_.receiving_touchdowns as receiving49_113_1
_, this_.receiving_yards as receiving50_113_1_, this_.rushing_20
plus_yards_each as rushing51_113_1_, this_.rushing_40plus_yards_
each as rushing52_113_1_, this_.rushing_attempts as rushing53_11
3_1_, this_.rushing_average_yards as rushing54_113_1_, this_.rus
hing_first_down_percentage as rushing55_113_1_, this_.rushing_fi
rst_downs as rushing56_113_1_, this_.rushing_fumbles as rushing5
7_113_1_, this_.rushing_long as rushing58_113_1_, this_.rushing_
touchdowns as rushing59_113_1_, this_.rushing_yards as rushing60
_113_1_, p1_.esb_id as esb1_85_0_, p1_.birth_city as birth2_85_0
_, p1_.birth_country as birth3_85_0_, p1_.birth_day as birth4_85
_0_, p1_.birth_month as birth5_85_0_, p1_.birth_state_prov as bi
rth6_85_0_, p1_.birth_year as birth7_85_0_, p1_.current_status a
s current8_85_0_, p1_.death_day as death9_85_0_, p1_.death_month
as death10_85_0_, p1_.death_year as death11_85_0_, p1_.display_
first_name as display12_85_0_, p1_.display_last_name as display1
3_85_0_, p1_.display_name as display14_85_0_, p1_.draft_number_o
verall as draft15_85_0_, p1_.draft_position as draft16_85_0_, p1
_.draft_round as draft17_85_0_, p1_.draft_team_id as draft18_85_
0_, p1_.draft_type as draft19_85_0_, p1_.draft_year as draft20_8
5_0_, p1_.first_name as first21_85_0_, p1_.gsis_player_id as gsi
s22_85_0_, p1_.high_school as high23_85_0_, p1_.is_deceased as i
s24_85_0_, p1_.last_name as last25_85_0_, p1_.middle_name as mid
dle26_85_0_, p1_.name_pronunciation as name27_85_0_, p1_.nfl_id
as nfl28_85_0_, p1_.person_id_type as person29_85_0_, p1_.suffix
as suffix85_0_ from PERSON_SEASON_STATS this_ inner join PERSON
S_VIEW p1_ on this_.esb_id=p1_.esb_id where this_.passing_yards
is not null and this_.season_type=:1 order by this_.passing_yar
ds desc, p1_.last_name asc ) where rownum <= :2
66 rows selected.
ENWEBP1P > select sql_text
2 from v$sqltext
3 where sql_id='3r5sxgju29skw'
4 order by piece;
SQL_TEXT
----------------------------------------------------------------
select * from ( /* criteria query */ select this_.esb_id as esb1
_113_1_, this_.season_id as season2_113_1_, this_.season_type as
season3_113_1_, this_.defensive_assists as defensive4_113_1_, t
his_.defensive_fg_blocked as defensive5_113_1_, this_.defensive_
forced_fumble as defensive6_113_1_, this_.defensive_interception
s as defensive7_113_1_, this_.defensive_interceptions_avgyds as
defensive8_113_1_, this_.defensive_interceptions_long as defensi
ve9_113_1_, this_.defensive_interceptions_tds as defensive10_113
_1_, this_.defensive_interceptions_yards as defensive11_113_1_,
this_.defensive_passes_defensed as defensive12_113_1_, this_.def
ensive_punt_blocked as defensive13_113_1_, this_.defensive_sacks
as defensive14_113_1_, this_.defensive_solo_tackles as defensiv
e15_113_1_, this_.defensive_total_tackles as defensive16_113_1_,
this_.defensive_xp_blocked as defensive17_113_1_, this_.games_p
layed as games18_113_1_, this_.games_started as games19_113_1_,
this_.games_sub as games20_113_1_, this_.passing_20plus_yards_ea
ch as passing21_113_1_, this_.passing_40plus_yards_each as passi
ng22_113_1_, this_.passing_attempts as passing23_113_1_, this_.p
assing_average_yards as passing24_113_1_, this_.passing_completi
on_percentage as passing25_113_1_, this_.passing_completions as
passing26_113_1_, this_.passing_first_down_percentage as passing
27_113_1_, this_.passing_first_downs as passing28_113_1_, this_.
passing_fumbles as passing29_113_1_, this_.passing_interception_
percent as passing30_113_1_, this_.passing_interceptions as pass
ing31_113_1_, this_.passing_long as passing32_113_1_, this_.pass
ing_net_yards as passing33_113_1_, this_.passing_passer_rating a
s passing34_113_1_, this_.passing_sacked as passing35_113_1_, th
is_.passing_sacked_yards_lost as passing36_113_1_, this_.passing
_touchdown_percentage as passing37_113_1_, this_.passing_touchdo
wns as passing38_113_1_, this_.passing_yards as passing39_113_1_
, this_.receiving_20plus_yards_each as receiving40_113_1_, this_
.receiving_40plus_yards_each as receiving41_113_1_, this_.receiv
ing_average_yards as receiving42_113_1_, this_.receiving_first_d
own_percent as receiving43_113_1_, this_.receiving_first_downs a
s receiving44_113_1_, this_.receiving_fumbles as receiving45_113
_1_, this_.receiving_long as receiving46_113_1_, this_.receiving
_receptions as receiving47_113_1_, this_.receiving_target as rec
eiving48_113_1_, this_.receiving_touchdowns as receiving49_113_1
_, this_.receiving_yards as receiving50_113_1_, this_.rushing_20
plus_yards_each as rushing51_113_1_, this_.rushing_40plus_yards_
each as rushing52_113_1_, this_.rushing_attempts as rushing53_11
3_1_, this_.rushing_average_yards as rushing54_113_1_, this_.rus
hing_first_down_percentage as rushing55_113_1_, this_.rushing_fi
rst_downs as rushing56_113_1_, this_.rushing_fumbles as rushing5
7_113_1_, this_.rushing_long as rushing58_113_1_, this_.rushing_
touchdowns as rushing59_113_1_, this_.rushing_yards as rushing60
_113_1_, p1_.esb_id as esb1_85_0_, p1_.birth_city as birth2_85_0
_, p1_.birth_country as birth3_85_0_, p1_.birth_day as birth4_85
_0_, p1_.birth_month as birth5_85_0_, p1_.birth_state_prov as bi
rth6_85_0_, p1_.birth_year as birth7_85_0_, p1_.current_status a
s current8_85_0_, p1_.death_day as death9_85_0_, p1_.death_month
as death10_85_0_, p1_.death_year as death11_85_0_, p1_.display_
first_name as display12_85_0_, p1_.display_last_name as display1
3_85_0_, p1_.display_name as display14_85_0_, p1_.draft_number_o
verall as draft15_85_0_, p1_.draft_position as draft16_85_0_, p1
_.draft_round as draft17_85_0_, p1_.draft_team_id as draft18_85_
0_, p1_.draft_type as draft19_85_0_, p1_.draft_year as draft20_8
5_0_, p1_.first_name as first21_85_0_, p1_.gsis_player_id as gsi
s22_85_0_, p1_.high_school as high23_85_0_, p1_.is_deceased as i
s24_85_0_, p1_.last_name as last25_85_0_, p1_.middle_name as mid
dle26_85_0_, p1_.name_pronunciation as name27_85_0_, p1_.nfl_id
as nfl28_85_0_, p1_.person_id_type as person29_85_0_, p1_.suffix
as suffix85_0_ from PERSON_SEASON_STATS this_ inner join PERSON
S_VIEW p1_ on this_.esb_id=p1_.esb_id where this_.passing_yards
is not null and this_.season_type=:1 order by this_.passing_yar
ds desc, p1_.last_name asc ) where rownum <= :2
66 rows selected.
|
|
|
|
Goto Forum:
Current Time: Tue Nov 26 04:56:49 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
