Re: Event to show Query Rewrite

[Arup Nanda]

Title: Message

Thanks for the link. The docs specify how to check if query rewrite occured and if so on _which_ MV, not the _exact_ rewritten query. I already deduced from the dbms_mview.explain_rewrite and 10046 trace that the MV_base_table is being selected; what I wanted to find out was what was the query on that MV - was it "select sum(totrows) from MV_base_table" or "select count(*) from MV_base_table". I suspect the latter but can't prove it without a trace. ----- Original Message ----- From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Tuesday, June 24, 2003 5:19 PM Subject: RE: Event to show Query Rewrite Its not an event, but maybe this will help.   http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/qr.htm#39292     -----Original Message----- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: Event to show Query Rewrite Listers,   Does anyone know how to set an event, or some other method, to see what query Oracle rewrites for a Materialized View and how it arrives at that decision? I am seeing a problem here in my DW database where the query "select count(*) from base_table" is rewritten as "select count(*) from MV_on_the _base_table", a clearly wrong rewrite of the query.   Oracle 9.2.0.3 on Tru64 Unix.   The base table is (col1 date, col2 varchar2(20), col3, col4, col5...) range partitioned on col1 and list subpartitioned on col2. the MV is "select col1, col2, count(*) totrows from base group by col1, col2", also partitioned in the same manner as the base table. The MV is first as a table and then the MV is built with "on prebuilt table" clause.   Query_rewrite_enabled = true and query_rewrite_integrity = stale_tolerated.   A query "select count(*) from base" is probably rewritten as "select count(*) from MV_base", and of course, the results are wrong. Oracle Support asks to provide a test case and I am unable to reproduce it. Upon examination, the STALENESS column in user_mviews is "UNKNOWN" for this MV; STALE for all others. Oracle Support can't explain what could be the cause of this status except that "it happens when an MV is created on a prebuilt table".   My only recourse at this time is to identify the decision making process Oracle used to arrive at the query it rewrites to and perhaps explain that somehow. Any help will be greatly appreciated.   Thanks a lot in advance.   Arup Nanda