Home » RDBMS Server » Enterprise Manager » EM SQL & Reference Collection Problem (Oracle Enterprise Manager 11g)
EM SQL & Reference Collection Problem [message #562622] Thu, 02 August 2012 22:01 Go to next message
RedFux
Messages: 5
Registered: August 2012
Junior Member
Hello, I'm a newbie in Oracle world so I have several questions about EM.
1. I use EM to see the SQL stats (Execution time, buffer gets, etc) in the reference collection section but why everytime I run the same query, the EM returns the multiplied rows. To be more clear I'll give an example : First run it displayed 1000 rows and 1 execution, second run it displayed 2000 rows and 2 executions, third run it displayed 3000 rows and 3 executions and so on. I want the EM to always display stats for 1 execution. How to deal with this?
2. Why all of my sql statements that I put into SQL worksheet display and executed didn't register in the SQL Reference Collection list? If that's worked, I don't have to reinput the SQL manually.

Thank You so much for the attention and cooperation, guys. Razz
Re: EM SQL & Reference Collection Problem [message #562624 is a reply to message #562622] Thu, 02 August 2012 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 21949
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: EM SQL & Reference Collection Problem [message #562625 is a reply to message #562624] Thu, 02 August 2012 22:23 Go to previous messageGo to next message
RedFux
Messages: 5
Registered: August 2012
Junior Member
BlackSwan wrote on Thu, 02 August 2012 20:08
Please read and follow the forum guidelines, to enable us to help you:


Thank you, I just read the rules and I can't find any topic that suits my problem but here's an additional information I can give :
> OS : Windows Server 2003 R2
> Oracle Version : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Oracle Enterprise Manager 11g
PL/SQL Release 11.2.0.1.0 - Production
any help would be highly appreciated. Thanks! Smile
Re: EM SQL & Reference Collection Problem [message #563215 is a reply to message #562625] Thu, 09 August 2012 14:50 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Enterprise Manager recreation
Next Topic: Monitoring standby database using grid control
Goto Forum:
  


Current Time: Sun Apr 20 01:43:41 CDT 2014

Total time taken to generate the page: 0.07844 seconds