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

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Thu, 24 Jan 2013 15:05:16 +0000 (UTC)
Message-ID: <pan.2013.01.24.15.05.11_at_gmail.com>



On Wed, 23 Jan 2013 07:03:57 -0800, dba cjb 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

Chris, you are using parallel query, as your trace is suggesting. You may need to combine the trace files from the other processes using trcsess. Also, you can get the plan directly from V$SQL_PLAN. If you have the proper licenses, as John has mentioned, you can monitor your SQL by using the /*+ MONITOR */ hint.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com
Received on Thu Jan 24 2013 - 16:05:16 CET

Original text of this message