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

Last updated on AUGUST 31, 2017

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms