Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Materialized View Problem, Part 2

RE: Materialized View Problem, Part 2

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Thu, 11 May 2000 09:17:05 -0400
Message-Id: <10494.105373@fatcity.com>


I never saw this show up, so I'm reposting:

-----Original Message-----
From: Miller, Jay
Sent: Wednesday, May 10, 2000 1:24 PM
To: 'ORACLE-L_at_fatcity.com'
Subject: RE: Materialized View Problem, Part 2

Okay, I figured out how to finesse the issue by removing the column from the where clause of the mview and adding to the SELECT and GROUP BY (this makes the mview a bit larger and therefore slower but still a huge improvement) so that it no longer requires an exact text match (it's not a complex mview anymore).

Then another issue came up today.
More data was loaded today, I did a complete refresh on both mviews. They were listed as fresh and valid.

So now I had my_mview1 and my_mview2.
But neither one was used by the query anymore (it worked perfectly yesterday).

Finally in frustration I created my_mview3 which was an exact copy of my_mview2. I then issued the query again and... my_mview2 was used. I then dropped my_mview1 and my_mview3. my_mview2 was still used. Huh?

I'm waiting to see if this recurs after tonight's data load but does anyone have an idea as to why creating a third materialized view would suddenly cause Oracle to recognize the second one?

-----Original Message-----
From: A. Bardeen [mailto:abardeen_at_hotmail.com] Sent: Tuesday, May 09, 2000 8:55 PM
To: Multiple recipients of list ORACLE-L Subject: Re: Materialized View Problem

Jay,

I'm assuming that the explain plan you've generated was from running the statement manually from SQL*Plus. I'm also assuming you're trying to use the query rewrite feature with mv's.

Hmm, I'm wondering if it's a permissions issue (is the query actually executed from within a stored procedure for example), but I can't recall any

special permissions needed by the user performing the query other than that the session needs to have query rewrite enabled (presumably you have QUERY_REWRITE=TRUE in the init.ora) and the mv has to be created with query rewrite (which it obviously is since it's used by the query in SQL*Plus).

I'd either turn on SQL_TRACE from within the application or use oradebug to attach to the user process connected via the VB app and trace it that way (see note 1058210.6 "HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG on MetaLink for instructions on getting a 10046 trace). Then run the resulting trace file through tkprof to generate an explain plan and compare it with the one from SQL*Plus. This might not show any control characters, but it might point you in the right direction.

Corrections and comments, are of course, always welcome.

HTH,

>From: "Miller, Jay" <JayMiller_at_TDWaterhouse.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Materialized View Problem
>Date: Tue, 09 May 2000 14:04:17 -0800
>
>We've just started using Materialized Views on our data warehouse and have
>run into a weird bottleneck.
>
>We created the mview with no problem (it's a complex view so it requires an
>exact text match). When the sql is sent to the instance by a Visual Basic
>program the materialized view is not used. However if I capture the text
>of
>the SQL in topsessions and run it in SQL Plus (or do an explain plan) the
>mview *is* used.
>
>My working hypothesis is that there is an invisible control character in
>the
>SQL being sent from VB. Does anyone know how I can check for this or have
>any other suggestions?
>
>
>Thanks loads!
>
>Jay
>
>--
>Author: Miller, Jay
> INET: JayMiller_at_TDWaterhouse.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).



Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
-- 
Author: A. Bardeen
  INET: abardeen_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
Received on Thu May 11 2000 - 08:17:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US