Friday, September 22, 2017

Saw a nice option in formatting Oracle explain plans for Oracle 12c for plans that use adaptive plans. Courtesy of Oracle Database 12c: Adaptive Execution Plans with Tom Kyte
 select * from table(dbms_xplan.display_cursor(format=>'+adaptive, allstats last'));
Those options show for adaptive plans exactly which steps were skipped by the optimizer based on row cardinality. Check out the YouTube video above for an example, at 3:41 https://youtu.be/9o9iuxNBciQ?t=221 You may need to do one of the following:
  • Add the hint /*+ gather_plan_statistics */ to the statement
  • Set the parameter 'statistics_level' is set to 'ALL', at session or system level. Caution: if you set it at the system level, overall performance will be degraded.

No comments:

Post a Comment