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 AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 and later
Information in this document applies to any platform.

Goal

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.

Solution

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
Goal
Solution
 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
References


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