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
Oracle Database - Enterprise Edition - Version 220.127.116.11 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!