RE: How to find directive used in execution plan

I read through your post. It's neat. However, your test case considers a simple eg. In my case, table had 315 directives. While, I went through 10053 trace. I found that it evaluates overs 70 dirid. Yet, it didn’t list final 7 directives that it used for the execution plan. That's exactly where I got stuck.

Although, upon further investigation, I found that reason for regression was "optimizer_dynamic_sampling" in this db was set to 4. Once I changed it to 2, query ran fine.

But, yeah, my question remained unanswered ☹ from 10053 trace.

Maybe I m still missing something?

Thanks,

Yogi | Technical Specialist – Databases | Fidelity International

FIL India Business Services Private Limited

5th Floor, Tower – D, Unitech Cyber Park,

Sector 39, Gurgaon 122 002, Haryana, India.

T: +91 124 412 3656 | I: 8 779 3656 | E: yogesh.tiwari_at_fil.com

Disclaimer: The information transmitted is intended for the person or entity to which it is addressed and may contain confidential, privileged or copyrighted material or attorney work product. If you receive this in error, please contact the sender and delete the material from any computer. Any comments or statements made are not necessarily those of FIL India Business Services Private Limited or any other Fidelity entity. All e-mails may be monitored or recorded.

-----Original Message-----
From: Stefan Koehler [mailto:contact_at_soocs.de] Sent: 14 March 2016 12:55
To: ORACLE-L; Tiwari, Yogesh
Subject: Re: How to find directive used in execution plan

Hi Yogi,

> Any cues?

Yes, good old EXPLAIN PLAN FOR plus METRICS option reveals the secret. The cursor itself does not provide this information (as it is not stored). You can check my Twitter post for an example: https://twitter.com/OracleSK/status/604567977208487936

In addition you can also use good old CBO trace to get this information - for more details please check my blog post: http://tinyurl.com/pc7oanp

Best Regards

Stefan Koehler

Freelance Oracle performance consultant and researcher

Homepage: http://www.soocs.de

Twitter: _at_OracleSK

> "Tiwari, Yogesh" <Yogesh.Tiwari_at_fidelity.co.in<mailto:Yogesh.Tiwari_at_fidelity.co.in>> hat am 14. März 2016 um 08:01 geschrieben:

>

> Oracle-l,

>

> Recently, I came across a query that has different plans in 2 different databases. Although, stats are same in both databases.

>

> I see executions plans in both DBs report that they use 5 and 7 sql

> plan directives respectively. I wonder, if there is a way to find which those directives are…?

>

> I tried SELECT * FROM

> TABLE(dbms_xplan.display_cursor('29x29ps2zsjcm', '0','advanced’)); but it doesn’t help. I remember seeing directives IDs in execution plan, but cant recall which option was used to display sql plan directives.

>

> Any cues?

>

> Thanks,

>

> Yogi

>

> Disclaimer: The information transmitted is intended for the person or

> entity to which it is addressed and may contain confidential, privileged or copyrighted material or attorney work product. If you receive this in error, please contact the sender and delete the material from any computer.

> Any comments or statements made are not necessarily those of FIL India

> Business Services Private Limited or any other Fidelity entity. All e-mails may be monitored or recorded.

--
http://www.freelists.org/webpage/oracle-l