DOUG presentation on dbms_xplan
Posted by Riyaj Shamsudeen on October 22, 2012
Please join us at the DOUG (DALLAS ORACLE USERS GROUP) Oracle Database Forum meeting on Thursday, October 25, 2012 from 5 pm – 7 pm.
Presented by Riyaj Shamsudeen, OraInternals, & Sahil Thapar:
“Out with the old way, Enter dbms_xplan: A Swiss army knife for performance engineers”
Rough outline:
(i) Ability to query access path from memory, AWR repository
(ii) Ability to use cardinality feedback method to understand access plan issues. Few tips from a real world experience will be provided too.
(iii) Ability to understand issues with database links etc.
(iv) Options such as ADVANCED, ALLSTATS etc
(v) Why should you choose dbmx_xplan over tkprof+sql_trace combination?
(vi) Disadvantages of dbms_xplan and a quick introduction to dbms_monitor.
Refreshments sponsored by me 🙂
Update: Uploading the presentation pdf files. Enjoy 🙂
Oracle database internals by Riyaj 
Srinivas Varala said
Riyaj,
You are doing great service to oracle community. I have learnt so much from your training and blog. please post your presentation on dbms_xplan in your blog. Unfortunately I am far away from Dallas.
Thanks
Srinivas
krish said
The paper listed autotrace shows the actual plan. That is incorrect. autotrace uses EXPLAIN PLAN and so cannot show the explain plan when things like bind peeking takes place.
Venky said
Great paper Riyaj. I am looking for something like this right now.
Yasir said
How to fetch historical execution plan? Lets say a query is slow today but was working well yesterday. Change in execution plan is evident from change in hash value. How to find the yesterday execution plan?
Riyaj Shamsudeen said
Yasir
If you have access to AWR, you can use dbms_xplan.display_awr to understand the execution plan of a SQL statement from AWR (Assumption here is that SQL elapsed time of the SQL statement exceeded the threshold and so,captured in AWR tables. )
Tim Gorman has written an excellent script and it is very useful to identify the sql history quickly. Search for Sqlhistory.sql in the page below: