Hi Martin, If you can EXPLAIN PLAN the query you will get the list of
directive ID used with dbms_xplan.display with +metrics in the format.
Cheers, Franck.
Le ven. 7 oct. 2016 à 00:30, Stefan Koehler <contact@xxxxxxxx> a écrit :
Hey Martin,
Is there a repeatable way to find out a) what one directive does indetail? b) which one was used for this very child cursor's xplan? (most
important part of my question!)
Yes and Yes, but only as long as this SQL is still in cursor cache. Please
check out my blog post for the details: http://tinyurl.com/pc7oanp
Please be aware that "dbms_sqldiag.dump_trace" has some (general) issues
as well, but this is the best we can get so far.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Martin Klier - Performing Databases GmbH <martin.klier@xxxxxxxxxxxxxxxxx>hat am 7. Oktober 2016 um 00:08 geschrieben:
table(DBMS_XPLAN.DISPLAY_CURSOR('xyz',null,'COST,IOSTATS,LAST,ADVANCED,ADAPTIVE'))
Hi Listers,
when extracting an execution plan from the cache with
SELECT * FROM
select have more than 500 directives each. Is there a repeatable way to find
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
outimportant part of my question!)
a) what one directive does in detail?
b) which one was used for this very child cursor's xplan? (most
dba_sql_plan_dir_objects isn't very helpful for that... at least not for my
The "joined force" of dba_sql_plan_directives and
weary eyes tonight.
Ah, it would be great to avoid tracing for obvious reasons, but if Ihave to, I will.
--
Thank you very much in advance!
--
Martin Klier | Performing Databases GmbH
Managing Partner | Senior DB Consultant
Oracle ACE
martin.klier@xxxxxxxxxxxxxxxxx | http://www.performing-databases.com
//www.freelists.org/webpage/oracle-l