Master Note for Query Rewrite

++++++++++++++++++++++++++++++++++++++++++++++++++++
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: 9.2.0.1 to 11.2.0.2.0 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Purpose

This article gives you a single reference point from which to quickly find answers to common questions and issues for Query Rewrite. 

Scope and Application

This 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 Information

If 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:

Troubleshooting

1./ Query did not rewrite to ordinary materialized view

A./ The issue is reproducible at will
If 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 will
If 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.

  • You can use the REWRITE_OR_ERROR hint in the query, which causes the following error if the query failed to rewrite:
    ORA-30393: a query block in the statement did not rewrite
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 View

If 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 issues

To browse the most recent known issues in the Knowledge Base, search for "query rewrite" under the Oracle Database product.


What is next

If 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:

Minimum requirements:
- 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.

References

NOTE: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.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

News and Troubleshooting tips for Oracle Database and Enterprise Manager

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today