Oracle Scratchpad

January 27, 2010

Plan Notes

Filed under: dbms_xplan — Jonathan Lewis @ 7:07 pm BST Jan 27,2010

Nothing terribly important or exciting in this post, but you’ve probably seen the “Notes” section at the end of an execution plan from time to time. The notes are extracted from the “other_xml” column of (usually) the first line of the execution plan data using calls to the extractvalue() XML function.

It’s interesting to see from a trace of a call to dbms_xplan.display_cursor() how the number of things that can appear as notes is slowly growing over recent versions of Oracle. The statements below are extracted from the trace files for 10.2.0.3, 11.1.0.6, and 11.2.0.1 in that order:

--------
10.2.0.5
--------
SELECT
	/*+ opt_param('parallel_execution_enabled', 'false') */
	extractvalue(xmlval, '/*/info[@type = "sql_profile"]'),
	extractvalue(xmlval, '/*/info[@type = "outline"]'),
	extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]'),
	extractvalue(xmlval, '/*/info[@type = "row_shipping"]'),
	extractvalue(xmlval, '/*/info[@type = "index_size"]'),
	extractvalue(xmlval, '/*/info[@type = "plan_hash"]')
from
	(select xmltype(:v_other_xml) xmlval from dual)
;

--------
11.1.0.7
--------
SELECT
	/*+ opt_param('parallel_execution_enabled', 'false') */
	extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), 
	extractvalue(xmlval, '/*/info[@type = "sql_patch"]'), 
	extractvalue(xmlval, '/*/info[@type = "baseline"]'), 
	extractvalue(xmlval, '/*/info[@type = "outline"]'), 
	extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]'), 
	extractvalue(xmlval, '/*/info[@type = "dop"]'), 	
	extractvalue(xmlval, '/*/info[@type = "row_shipping"]'), 
	extractvalue(xmlval, '/*/info[@type = "index_size"]'), 
	extractvalue(xmlval, '/*/info[@type = "result_checksum"]'), 
	extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]'), 
	extractvalue(xmlval, '/*/info[@type = "plan_hash"]') 
from
	(select xmltype(:v_other_xml) xmlval from dual)
;

--------
11.2.0.4
--------
SELECT 
	/*+ opt_param('parallel_execution_enabled', 'false') */ 
	extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), 
	extractvalue(xmlval, '/*/info[@type = "sql_patch"]'), 
	extractvalue(xmlval, '/*/info[@type = "baseline"]'), 
	extractvalue(xmlval, '/*/info[@type = "outline"]'), 
	extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]'), 
	extractvalue(xmlval, '/*/info[@type = "dop"]'), 
	extractvalue(xmlval, '/*/info[@type = "dop_reason"]'), 
	extractvalue(xmlval, '/*/info[@type = "queuing_reason"]'), 
	extractvalue(xmlval, '/*/info[@type = "row_shipping"]'), 
	extractvalue(xmlval, '/*/info[@type = "index_size"]'), 
	extractvalue(xmlval, '/*/info[@type = "result_checksum"]'), 
	extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]'), 
	extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]'), 
	extractvalue(xmlval, '/*/info[@type = "plan_hash"]') 
from 
	(select xmltype(:v_other_xml) xmlval from dual)
;

It’s simple little checks like this that often lead me to discover interesting little changes in newer versions of Oracle.

Update Jan 2011: Note, for example, the appearance of “dop” (degree of parallelism) in 11.1 and “dop_reason” in 11.2; I haven’t checked these carefully yet, but it seems likely that they’re intended to¬†supply the answer to the common question “How parallel did my query go, and why ?”

4 Comments »

  1. [...] by Plan Notes « Oracle Scratchpad — January 5, 2011 @ 8:46 am UTC Jan 5,2011 | [...]

    Pingback by How parallel « Oracle Scratchpad — January 5, 2011 @ 8:51 am BST Jan 5,2011 | Reply

  2. I have not checked, but wouldnt this infer, that the statement is reparsed when the required dop is changed on subsequent executions of the same statement?

    or ist just other xml changed, but not the plan?

    Comment by stefan — January 5, 2011 @ 6:02 pm BST Jan 5,2011 | Reply

  3. Stefan,

    That’s a question that should be pursued. I haven’t checked the details but I would be a little surprised if the other_xml is modified on every execution, and equally surprised if the statement was re-optimized on every execution. So it’s possible that this will only tell you what happened on the first execution – or maybe it tells you what the expected DOP is, rather than actual DOP, where the expected DOP is not necessarily the degree of any table, or the degree of any hint.

    Comment by Jonathan Lewis — January 5, 2011 @ 9:37 pm BST Jan 5,2011 | Reply

  4. Is there a way to use opt_param hint to affect the query similarly to

    alter session force parallel DML;

    Comment by Josh C — August 5, 2013 @ 7:52 pm BST Aug 5,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,990 other followers