Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

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 🙂


5 Responses to “DOUG presentation on dbms_xplan”

  1. Srinivas Varala said


    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.


  2. 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.

  3. Venky said

    Great paper Riyaj. I am looking for something like this right now.

  4. 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?

    • 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:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: