Thanks for the question, RaviRaj.

Asked: November 03, 2015 - 10:56 am UTC

Last updated: November 24, 2016 - 1:15 pm UTC

Version: 11g Enterprise Edition Release 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am using DBMS_XPLAN.DISPLAY_CURSOR(format ==> 'Advanced') to generate the explain plan.

This is generating for me 2 sections of information 'Query Block Name / Object Alias (identified by operation id):' and 'Outline Data'.

Could you please help me out on how to read these 2 sections and make sense of the explain plan?

I browsed to see if I can find more info about it but I am not able to find single response that helps me understand these 2 sections.

I would really appreciate your help with this.

Apologies for any typoes :) (I read somewhere in your blog that you hate them)

PS1: I really love this site and like many others I am really grateful for all your help.
PS2: In case if this question is already explained, please share the link

Thanks,
Ravi.

and Chris said...

Each select/insert/update/delete/merge in a SQL statement is a query block. Oracle assigns names to these query blocks. If you have multiple subqueries it's not always obvious which is which. Fortunately you can give your own names using the QB_NAME hint. Connor explains in this video:

https://www.youtube.com/watch?v=bfaFT9doqCg

The outline data is the complete set of hints you need to reproduce the execution plan.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Ravi, November 03, 2015 - 7:51 pm UTC

Very useful, thanks.

Follow up questions:

Ravi, November 03, 2015 - 7:52 pm UTC

The video is really helpful (probably I should have checked youtube before posting here, my bad :) )

I understand that QB_NAME hint allows me to give a custom name to each query.

I have a few follow-up questions:

Will it be feasible for you to explain me different tags in 'outline data' and how could I make this additional information more readable?

I am running a query that has nearly 100 steps & I would like to know, using this 'outline data' and the 'alias information' will I be able to tell what part of query executes first? Like 'Leading' keyword indicates any precedence?

November 04, 2015 - 12:42 am UTC

"It depends".

One of the things the optimizer will often try to do is re-arrange your query to make in run better.

Hence you might use (say) query block names of Q1 and Q2, for two queries that both access the EMP table, but when you check the execution plan, Q2 has disappeared, because Oracle has merged Q1 and Q2 into one because it can do it with one probe of EMP.

But in the main, the QB_NAME will assist in the understanding of big plans.

The outline section is the list of hints that would be needed to replicate this execution plan, even if the statistics changed. (Hence its the list of hints that would be stored if you created a stored outline).

Hope this helps.

Thanks Chris Saxon for the explanation. Need suggestion for any resource to learn using Outline Data

Praveen, November 23, 2016 - 7:08 am UTC

I have a query which is running very slow and we have been asked to extract the explain plan in advanced mode and then use the OUTLINE data as a hint to tune query.

I have never used "OUTLINE Data" to tune the query and the details are very confusing to convert into hints. Please find below some details.

/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$15" "DB_SRVR_PK"@"SEL$15")
FULL(@"SEL$16" "DB_SRVR_TP"@"SEL$16")
USE_HASH_AGGREGATION(@"SEL$20")
FULL(@"SEL$20" "BSPD"@"SEL$20")
USE_HASH(@"SEL$A876118C" "BJOIN"@"SEL$13")
LEADING(@"SEL$A876118C" "AJOIN"@"SEL$14" "BJOIN"@"SEL$13")
FULL(@"SEL$A876118C" "BJOIN"@"SEL$13")
.
.
.
.
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

Please help me with some reference document which explain how to read Outline Data and use the same in Hints.

I have found this method to use Outline Hints, but by using this cost has increased,

http://www.bobbydurrettdba.com/2013/08/30/outline-hint-for-query-tuning/

November 24, 2016 - 1:15 pm UTC

The outline data are the hints!

You shouldn't really use these in your queries though. If you want to "lock" a particular execution plan, look at SQL plan management:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-management-11gr2-133099.pdf

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

Follow up questions:

Thanks Chris Saxon for the explanation. Need suggestion for any resource to learn using Outline Data

More to Explore

DBMS_XPLAN

Understanding 'Outline Data' in Oracle Explain Plan

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks

Follow up questions:

Thanks Chris Saxon for the explanation. Need suggestion for any resource to learn using Outline Data

More to Explore

DBMS_XPLAN