Re: Plan Directives used for Execution Plan
Date: Fri, 7 Oct 2016 00:29:54 +0200 (CEST)
Message-ID: <1498848444.181209.1475792995037.JavaMail.open-xchange_at_app10.ox.hosteurope.de>
Hey Martin,
> 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
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
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
> Martin Klier - Performing Databases GmbH <martin.klier_at_performing-db.com> hat am 7. Oktober 2016 um 00:08 geschrieben:
> important part of my question!)
Stefan Koehler
Twitter: _at_OracleSK
>
> 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 ACE
>
> martin.klier_at_performing-db.com | http://www.performing-databases.com
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 07 2016 - 00:29:54 CEST