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: Temp space in 10g and data insert speed

RE: Temp space in 10g and data insert speed

From: Jesper Haure Norrevang <jhn.aida_at_cbs.dk>
Date: Wed, 12 Jan 2005 11:26:26 +0100
Message-id: <000001c4f891$2c46a470$4a28e282@AIDA.local>


Hi,

Ron Rogers wrote:
<Quote>

I will look into the WORKAREA_SIZE_POLICY parameter and it's impact when I get a chance.
</Quote>

I have a datawarehouse at my site. After a few experiments I have left the idea of using WORKAREA_SIZE_POLICY = AUTO when loading the warehouse. The reason is: Every time you run the batch, Oracle will deceide values for SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE dependent on the load on your instance.

When *AREA_SIZE-parameters change, the optimizer might choose another plan, because smaller values make sorts more expensive due to more sort runs. Thus you get a very challenging job, when trying to make the optimizer choose the same good plan every day.

My solution is to change the parameters for the session doing the update, like this:

alter session set optimizer_mode          = ALL_ROWS;
alter session set workarea_size_policy    = 'MANUAL';
alter session set hash_area_size          = 209715200;
alter session set sort_area_retained_size = 104857600;
alter session set sort_area_size          = 104857600;


Besides V$TEMPSTAT, you might get information from V$SORT_SEGMENT and V$SORT_USAGE

Hope this helps.

Regards
Jesper Haure Norrevang

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 12 2005 - 04:27:15 CST

Original text of this message

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