Re: Working out which part of an explain plan is being currently executed

From: TheBoss <TheBoss_at_invalid.nl>
Date: 24 Jan 2013 20:41:21 GMT
Message-ID: <XnsA152DCA4A4490TheBossUsenet_at_194.109.133.133>

dba cjb <chris.brown_at_providentinsurance.co.uk> wrote in news:623f05c0-3e4f-4e10-8168-c97041d3af24_at_u7g2000yqg.googlegroups.com:

> On Jan 23, 3:45 pm, John Hurley <johnthehur..._at_gmail.com> wrote:

>> On Jan 23, 10:03 am, dba cjb <chris.br..._at_providentinsurance.co.uk>
>> wrote:
>>
>>
>>
>>
>>
>> > I have a big query running / as a starting point i would like to
>> > drill down and work out which execution steps are currently being
>> > executed...
>> > ...or which objects are being accessed
>> > this may give me a starting point to analyzing potential for
>> > performance improvement. My query has been running for a day
>>
>> > I have tried tracing but just get a load of wait events ( see below
>> > ) ..my only general gut feeling is that nested loops may be
>> > wronglly selected
>>
>> > Any pointers or suggestions for further education gratefully
>> > received
>>
>> > regards
>> > Chris B
>>
>> > AIT #1188826856: nam='PX Deq: Execute Reply' ela= 6555 sleeptime/
>> > senderid=200 passes=1 p3=0 obj#=-1 tim=1729507286272
>> > WAIT #1188826856: nam='PX Deq: Execute Reply' ela= 13407 sleeptime/
>> > senderid=200 passes=1 p3=0 obj#=-1 tim=1729507299754
>> > WAIT #1188826856: nam='PX Deq: Execute Reply' ela= 19983 sleeptime/
>> > senderid=200 passes=1 p3=0 obj#=-1 tim=1729507319871
>> > WAIT #1188826856: nam='PX Deq: Execute Reply' ela= 6461 sleeptime/
>> > senderid=200 passes=1 p3=0 obj#=-1 tim=1729507326459
>> > WAIT #1188826856: nam='PX Deq: Execute Reply' ela= 25443 sleeptime/
>> > senderid=200 passes=1 p3=0 obj#=-1 tim=1729507352001
>> > WAIT #1188826856: nam='PX Deq: Execute Reply' ela= 12178 sleeptime/
>> > senderid=200 passes=1 p3=0 obj#=-1 tim=1729507364343
>>
>> Do you have licenses for OEM / diagnostics pack / tuning pack?
>>
>> The SQL Monitoring GUI shows where your query is executing at ...-
>> Hide q

> uoted text -

>>
>> - Show quoted text -

> 
> thanks John ...I am pretty sure we are so I will research how we may
> get this implemented
> 
> regards
> Chris B

What you probably are looking for is called "Real-Time SQL Monitoring". Oracle has described this 11R2 feature in this whitepaper: http://www.oracle.com/technetwork/database/manageability/owp-sql- monitoring-128746.pdf

and also in a tutorial:
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod /manage/sqlmonitor/sqlmonitor.htm

If you prefer independent information, there are quite a lot well-known Oracle bloggers that have written about it:

Greg Rahn:
http://structureddata.org/2008/01/06/oracle-11g-real-time-sql- monitoring-using-dbms_sqltunereport_sql_monitor/

Tim Hall:
http://www.oracle-base.com/articles/11g/real-time-sql-monitoring- 11gr1.php

Tanel Poder:
http://tech.e2sn.com/oracle/troubleshooting/oracle-s-real-time-sql- monitoring-feature-v-sql_monitor

Doug Burns:
http://oracledoug.com/serendipity/index.php?/archives/1506-Real-Time- SQL-Monitoring-in-SQL-Developer.html

If you have time to wait a month, you can also register for the free webinar Doug will be presenting:
http://www.red-gate.com/products/oracle-development/schema-compare-for- oracle/education/webinars/webinar-oracle-sql-monitoring-doug-burns

Cheers!

-- 
Jeroen

Received on Thu Jan 24 2013 - 21:41:21 CET