RE: Alternative to V$PQ_TQSTAT for troubleshooting unbalanced HJs?

While I am struggling to get SQL Monitor to show me my query and *keep* showing my query (see elsewhere in this thread), I ran your XPLAN_ASH and am getting specific information that I think is ultimately what I am really interested in. In "Parallel Distribution ASH", the correlation between skew in parallel slave process samples lined right up with the execution plan lines... brilliant.

I am having a bit of difficulty unpacking / interpreting the "Parallel Distribution Graph ASH".

Almost too busy now to jot off this reply. Thank you.

-john

-----Original Message-----
From: Randolf Geist [mailto:info_at_www.sqltools-plusplus.org] Sent: Saturday, August 11, 2012 2:34 PM
To: Tornblad, John; oracle-l_at_freelists.org Subject: Re: Alternative to V$PQ_TQSTAT for troubleshooting unbalanced HJs?

John,

this is a nice coincidence - I might have exactly what you need in such cases where Real-Time SQL Monitoring and V$PQ_TQSTAT are of no real help.

I've just published the first public release of a SQL*Plus script called
"XPLAN_ASH" that allows SQL statement execution analysis using 11g ASH
data, and its primary purpose is analysing Parallel Execution and providing some details that are hard to obtain otherwise.

You can find the details how to use the tool and the download link on my blog:
http://oracle-randolf.blogspot.de/2012/08/parallel-execution-analysis-us ing-ash.html

If you simply want to analyse the last execution of the statement, just specify the SQL_ID as parameter. If the information is no longer available from your current ASH (GV$ACTIVE_SESSION_HISTORY) you can tell the script to pull the information from DBA_HIST_ACTIVE_SESS_HISTORY.

If you are looking for a particular execution you can specify the SQL_EXEC_START and SQL_EXEC_ID that identifies that statement execution.

In your particular case you might need to play a bit with the ASH options, instead of "ASH,DISTRIB_REL,TIMELINE" you might need to use
"ASH,DISTRIB,TIMELINE" instead to make a potential skew more visible -
see the blog post for more details about that.

And: This is a 1.0 version - although I've used it myself a lot already, I would expect some glitches. Feedback welcome!

Hope this helps,
Randolf

</pre>This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.

--
http://www.freelists.org/webpage/oracle-l