My Oracle Support Banner

How to Get Execution Plans and Execution Elapsed Times for a Refresh Generated Recursive SQL (Doc ID 1315632.1)

Last updated on FEBRUARY 21, 2024

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


With mview refreshes it could be useful to know the current and history execution plans used for a particular recursive SQL generated by the mview refresh.

For example complete refresh generated recursive SQL - execution of which is a significant part of the complete refresh - is the following
  INSERT INTO mview SELECT <mview definition query>
This SQL may need to be investigated in regard what is currently used execution plan, and whether there
are any other execution plans for this SQL in the cursor cache or in AWR, and possibly elapsed time of the executions of the SQL using those plans.

This could come handy if execution plan change is suspected as the cause of the refresh performance degradation.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Tools to achieve the goal:
 Locate the SQL of interest
 Average execution elapsed time for execution plans used for the SQL
 Get the execution plans involved

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.