My Oracle Support Banner

How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite and Fast Refresh Problems (Doc ID 149815.1)

Last updated on DECEMBER 18, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 9.0.1.0 to 11.2.0.3 [Release 9.0.1 to 11.2]
Oracle Database - Personal Edition - Version 9.0.1.0 to 11.2.0.3 [Release 9.0.1 to 11.2]
Oracle Database - Standard Edition - Version 9.0.1.0 to 11.2.0.3 [Release 9.0.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

Purpose

This document shows how to use the procedures EXPLAIN_REWRITE and EXPLAIN_MVIEW in the DBMS_MVIEW package to gather useful information when troubleshooting problems with query rewrite using materialized views (MV), and MV fast refresh.

This document applies to all RDBMS versions >= 9i.

Troubleshooting Steps

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
Purpose
Troubleshooting Steps
 A. DBMS_MVIEW.EXPLAIN_REWRITE
 Troubleshooting Value
 Output Options
 Option 1: Use the REWRITE_TABLE for Output (query input limited to 4000 characters)
 
Option 2: Use a VARRAY for Output 
  Usage
  Examples Using DBMS_MVIEW_EXPLAIN_REWRITE
 Example 1 -- Pass only the query1 variable and use the REWITE_TABLE table as output
  
Example 2 -- Pass the query1 variable and explicit MV name, and use the EXPLAIN_REWITE table as output
 
 Example 3 -- Pass the query without it being declared as a variable and use the EXPLAIN_REWITE table as output
 
Example 4 -- Use a VARRAY for output instead of the EXPLAIN_REWRITE table
 Example 5 -- Use a CLOB for input and a Use a VARRAY for output
 REWRITE_TABLE Output
  B. DBMS_MVIEW.EXPLAIN_MVIEW
 Troubleshooting Value
 
 Output Options
 Option 1: Use the MV_CAPABILITIES table for output
 Option 2: Use a VARRAY for Output
 Usage
  Examples Using DBMS_MVIEW.EXPLAIN_MVIEW
 Example 1 -- Create the MV, pass the MV name to the EXPLAIN_MVIEW procedure, and output to MV_CAPABILITIES_TABLE
 
Example 2 -- Pass the select portion of the MV DDL to the EXPLAIN_MVIEW procedure and output to MV_CAPABILITIES_TABLE
 Example 3 -- Pass the MV name to the EXPLAIN_MVIEW procedure and output to a VARRAY
 Example 4 -- Use a CLOB as input and output to a VARRAY (versions >= 10g)
 MV_CAPABILITIES_TABLE Output
 Literals Passed to EXPLAIN_REWRITE or EXPLAIN_MVIEW
 Other Tools
References

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