OraFAQ Forum: Performance Tuning » MVIEW huge temp space

Home » RDBMS Server » Performance Tuning » MVIEW huge temp space (10.2.0.3 linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MVIEW huge temp space [message #532537]

Wed, 23 November 2011 04:47

dee_bee_eh
Messages: 6
Registered: November 2011

Junior Member

I run a query, takes 20 minutes or so, I traced it and can see no more then 20-30 mb of temp space required in the plan. Happy with this.

I developed it for use in a materialized view, however when I create the mview with the sql, the temp space required grows until it maxxes out. I increased the existing 10gb to 50gb but still maxxed out. Took the SQL out, reran it, ran in 20 minutes barely scratching the temp, I ran a "create table as <select>" and same behaviour as the SQL, barely touched the temp as per the plan. So the temp space blolwing is unique to the mview create.

Im working with mviews years on several sites and have never seen this.

What could be causing it.

Report message to a moderator

Re: MVIEW huge temp space [message #532566 is a reply to message #532537]

Wed, 23 November 2011 08:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: MVIEW huge temp space [message #532571 is a reply to message #532566]

Wed, 23 November 2011 09:13

dee_bee_eh
Messages: 6
Registered: November 2011

Junior Member

I do understand. In this case however, the tuning of the SQL isnt the problem, its the mview create statement. The SQL runs fine outside of the mview create, and if I cut the "create mview " statement out and replace it with "create table as <select SQL>" it works fine. maybe I should rephrase then to as whats going on under the hood that would cause an mview create to use so much temp that a table create on the same SQL wouldnt?

heres the mview statement (minus the SQL as its only the mview statement itself Im concerned about)after a couple of attempts I set the temp tablespace to autoextend from 10gb and it blew out at 50gb

CREATE MATERIALIZED VIEW my_big_mv 
TABLESPACE MY_TABLESPACE
PCTUSED    0
PCTFREE    0
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE MY_TABLESPACE
            PCTFREE    10
            INITRANS   2
            MAXTRANS   255
            STORAGE    (
                        INITIAL          64K
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        BUFFER_POOL      DEFAULT
                       )
REFRESH COMPLETE
START WITH TO_DATE('22-Nov-2011 01:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT (round(sysdate) + 1/24) + 1 
WITH PRIMARY KEY
AS 
<my big SQL goes here>

and the create table statement that didnt use any more then 70-80mb of temp as per the execution plan during creation is..

create table my_big_table
as 
<my big SQL goes here>

Report message to a moderator

Re: MVIEW huge temp space [message #532572 is a reply to message #532571]

Wed, 23 November 2011 09:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I should rephrase then to as whats going on under the hood
ALTER SESSION SET SQL_TRACE=TRUE

Report message to a moderator

Re: MVIEW huge temp space [message #532588 is a reply to message #532571]

Wed, 23 November 2011 09:37

John Watson
Messages: 8930
Registered: January 2010
Location: Global Village

Senior Member

As a workaround, you could create the table the quick way, and then the materialized view with
CREATE MATERIALIZED VIEW....ON PREBUILT TABLE....
and then write your own job to drop and re-create it every day. That would get you going while you investigate the execution plan you are getting when you use the usual syntax.

Report message to a moderator

Re: MVIEW huge temp space [message #532590 is a reply to message #532572]

Wed, 23 November 2011 09:39

dee_bee_eh
Messages: 6
Registered: November 2011

Junior Member

and yes, the session was traced and profiled
however..... (more woe) The mview create statement has nothing after it in the trace (it was just the create statement itself) which was quite puzzling as I hadnt seen that before and Im tracing a long time. I guessed it was because the statement bombed with the out of temp error and it didnt get to complete. I get a full trace out alright but that could be explainable as the session itself did complete, albeit with the max temp error.

As an aside, I traced the session with the create table as statement and that had the same plan and timings as an explain plan on the raw SQL itself.

Im thinking bug but ct isnt in a position to upgrade and oracle wont look at it due to version.

Im thinking Im going to give them a flat table instead as that works. needs must.

I guess I was hoping someone was going to be to say "ah yes, ye old mview create temp space usage issue in 10203, add the eye_of_newt parameter and youll be right as rain".

Report message to a moderator

Re: MVIEW huge temp space [message #532595 is a reply to message #532588]

Wed, 23 November 2011 09:43

dee_bee_eh
Messages: 6
Registered: November 2011

Junior Member

John Watson wrote on Wed, 23 November 2011 09:37

As a workaround, you could create the table the quick way, and then the materialized view with
CREATE MATERIALIZED VIEW....ON PREBUILT TABLE....
and then write your own job to drop and re-create it every day. That would get you going while you investigate the execution plan you are getting when you use the usual syntax.

And yes, thats what Im currently doing, and I thought the same as you as well when I had a D'OH moment and realised I dont need an mvew once I have the table refreshed at night (Im doing a trunc and insert into).
I created a synonym for the ct's app and theyre querying that, if the mview thing gets sorted, I'll point it at the mview instead of the table.

ct is upgrading in the new year. hopefully its a bug and will be resolved but if not support should look at it in a call then.

Report message to a moderator

Previous Topic:	Elapsed Time in Trace file output (2 Merged)
Next Topic:	Reports Tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 08:26:22 CDT 2024