How to Get Execution Plans and Execution Elapsed Times for a Refresh Generated Recursive SQL
(Doc ID 1315632.1)
Last updated on FEBRUARY 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 and laterOracle 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.
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 |