The complete and the most recent version of this article can be viewed
from My Oracle Support Knowledge Section.
Master Note for Query Rewrite (Doc ID 1215173.1)
Applies to:Oracle Server - Enterprise Edition - Version: 126.96.36.199 to 188.8.131.52.0 - Release: 9.2 to 11.2
Information in this document applies to any platform.
PurposeThis article gives you a single reference point from which to quickly find answers to common questions and issues for Query Rewrite.
Scope and ApplicationThis Master Note helps to understand Query Rewrite and provides assistance in using diagnostics effectively to debug/troubleshoot and resolve issues encountered.
Master Note for Query Rewrite
General InformationIf you are looking for general information about query rewrite, how it works, how it can improve performance, please have a look at the following documents:
1./ Query did not rewrite to ordinary materialized view
A./ The issue is reproducible at willIf the issue is reproducible at will, then you can use the DBMS_MVIEW.EXPLAIN_REWRITE and DBMS_MVIEW.EXPLAIN_MVIEW procedures to find the cause of the problem
For further details please look at the following article:
NOTE 149815.1 How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite Problems.
The description of the messages that the EXPLAIN_REWRITE produces (the QSM messages) can be found in the Oracle Database Error Messages document for the corresponding database version.
To interpret the EXPLAIN_REWRITE messages, and to take corrective actions, please consult the following article:
NOTE 236486.1 Manual Diagnosis & Troubleshooting for Query Rewrite
B./ The issue is not reproducible at willIf the issue occurs intermittently, and it is not reproducible at will to run the DBMS_MVIEW.EXPLAIN_REWRITE procedure on the non rewriting query, then on 10g and above the following can be done.
This way one can avoid non rewritten long running queries, as well as better capture the moment when the above mentioned diagnostics could be used to find the cause of the non rewrite.
- You can look up the cause of not sharing the rewritten plan in the V$SQL_SHARED_CURSOR. This view can be searched using the SQL_ID of the query. For further information about V$SQL_SHARED_CURSOR view can be found in Oracle Database Reference.
2./ The query did not rewrite to Cube Organized Materialized ViewIf the query does not rewrite to Cube Organized Materialized View (a new type of materialized view in 11g, where the data is stored in Oracle OLAP object called Analytic Workspace), then please look at the
Note 577293.1 Oracle OLAP 11g: How to ensure use of Cube Materialized Views/Query Rewrite
Known issuesTo browse the most recent known issues in the Knowledge Base, search for "query rewrite" under the Oracle Database product.
What is nextIf you have not found the answer to your question, or solution to your problem, then you may want to log a a service request with Oracle Support. It is a good idea to provide a testcase, where the question can be demonstrated, or the problem can be reproduced. This usually reduces the resolution time of the service request significantly.
A testcase for query rewrite questions/issues should contain the following:
- query that does not rewrite
- DDL of the objects involved in the query including indexes, constraints
- DDL of the mview that the query is expected to rewrite, and DDL of the objects referenced in the mview definition query
- the messages that the EXPLAIN_REWRITE and EXPLAIN_MVIEW procedures produced.
Depending on the nature of the problem, Oracle Support may also need
- statistics on the involved objects
- values of the session parameters where the query is executed
Any of the following could help you to collect testcase for a query rewrite problem:
- section about how to create testcase for query rewrite problem in NOTE 236486.1
- Note 727863.1 How to create a SQL-testcase using the DBMS_SQLDIAG package
- DDL of the involved objects and statistics on those, as well as session parameter values can be easily collected when running the query via the Xecute method of SQLT (SQLT is a support tool and can be downloaded from NOTE 215187.1)
- Testcase Builder available via EM DB Control - Support Workbench can also be used to collect and upload testcase.
ReferencesNOTE:149815.1 - How To Use DBMS_MVIEW.EXPLAIN_REWRITE and EXPLAIN_MVIEW To Diagnose Query Rewrite Problems
NOTE:215187.1 - SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly
NOTE:236486.1 - Manual Diagnosis & Troubleshooting for Query Rewrite Problems
NOTE:311777.1 - Query Rewrite using Dimensions explained
NOTE:577293.1 - Oracle OLAP 11g: How to ensure use of Cube Materialized Views/Query Rewrite
NOTE:727863.1 - How to create a SQL-testcase using the DBMS_SQLDIAG package.