RSS Feed

Method R Discussion Group

I encountered an interesting trace of a parallel query process. Event 10046 was enabled after a call (probably a fetch) began and disabled before it ended. The trace represents just under two hours of activity.

Without any other information I have to assume that event 10046 was enabled to help figure out (1) why some task was not yet complete and (2) when will it probably complete?

Does it make sense to use this event to answer questions #1 and #2?

My opinion is that there are more effective ways to answer questions #1 and #2.

I'd agree that there are probably more effective ways to answer both questions.  Not to be flippant, but the answer to #1 would seem to be answered with "because it's still doing the work".  While that answer is not what might be desired, it is accurate. 

What did the trace file show?  Just a few time stamps?  What about the child process trace files?  Did they contain anything?  Just curious...

But, the bottom-line of your post was the question: "Does it make sense to use this event to answer questions #1 and #2?".  I don't think so.  If the situation is as you indicate - a single call that is running inordinately long and the trace is started after it begins and is ended before it completes - then the data that would be needed to answer either question would likely not be found in the trace file.  Perhaps this is a case when information exposed through the various v$ views might lend a hand.

I don't have the trace file for the "coordinator", just one parallel query slave trace file. Here are just a few lines to demonstrate what that trace file contains:

*** a timestamp
WAIT #1: nam='blah' ...
many more blah's and humbugs
*** a timestamp
WAIT #1: nam='blah' ...
many more blah's and humbugs

...and the cycle repeats for a total elapsed time of about 2 hours.

If you were interested in the specific events, here is an approximate aggregation:
e=160s count=66000 maxe=2s name=PX Deq Credit: need buffer
e=3044s count=40000 maxe=2s name=PX Deq Credit: send blkd
e=6s count=40 maxe=2s name=PX Deq: Execution Msg
e=0 count=2300 maxe=0s name=PX qref latch
e=20s count=15000 maxe=.15s name=direct path read

The person who presented me the data implied this file is a complete trace of a slave's contribution. That made me think again about the problem.

A parallel query slave's contribution to a coordinator might not show a PARSE, EXEC, or FETCH if its role is a sorter. I think that someone could look at the read events in that trace file and determine if the blocks being read were coming from a temporary or permanent segment. Knowing that the reads were coming from a temporary segment might convince me that this slave's role was a sorter.