display_cursor

Introduction

DBMS_XPLAN.DISPLAY_CURSOR can be used to get more insights into the actual resource consumption on execution plan operation level when using the GATHER_PLAN_STATISTICS hint (from 10g on), or increasing the STATISTICS_LEVEL parameter to ALL (on session level, on system level the overhead is probably prohibitive).As soon as a SQL execution is done (either successfully, cancelled or with error) the corresponding extended data in the child cursor gets populated/updated and the additional information about the actual runtime profile can be accessed via V$SQL_PLAN_STATISTICS resp.

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