Plan Directives used for Execution Plan
Date: Fri, 7 Oct 2016 00:08:23 +0200 (CEST)
Message-ID: <1204537701.5805.1475791703197.JavaMail.zimbra_at_performing-db.com>
Hi Listers,
when extracting an execution plan from the cache with SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('xyz',null,'COST,IOSTATS,LAST,ADVANCED,ADAPTIVE'))
I can see that we are using Plan Directives:
Note
- dynamic statistics used: dynamic sampling (level=2)
- statistics feedback used for this statement
- this is an adaptive plan (rows marked '-' are inactive)
- 9 Sql Plan Directives used for this statement
But for various reasons, the seven widely used objects joined by my select have more than 500 directives each. Is there a repeatable way to find out
a) what one directive does in detail?
b) which one was used for this very child cursor's xplan? (most important part of my question!)
The "joined force" of dba_sql_plan_directives and dba_sql_plan_dir_objects isn't very helpful for that... at least not for my weary eyes tonight. Ah, it would be great to avoid tracing for obvious reasons, but if I have to, I will.
Thank you very much in advance!
-- Martin Klier | Performing Databases GmbH Managing Partner | Senior DB Consultant Oracle ACEmartin.klier_at_performing-db.com | http://www.performing-databases.com -- http://www.freelists.org/webpage/oracle-l Received on Fri Oct 07 2016 - 00:08:23 CEST