RE: Temp space in 10g and data insert speed

  • From: "Ron Rogers" <RROGERS@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 12 Jan 2005 08:33:47 -0500

Informed responders.
Thanks you for all of you suggestions.
I have set the pga_aggregate parameter and that option helped some.
I have checked the various V$ views and nothing showed as a problem.
The majority of the
views had little or no information in them.
checking the wait events and the SQL_NET was extra high. There was a
network compatability
problem with the ethernet card. It currently must be set at half duplex
or the packets crawlllll.
Network people are looking intofinding the reason we can't use full
duplex.
I also increased the number of redo logs from 3 to 4 and that helped
some. 
Thanks for all of your ideas.
I will keep monitoring the load speed and try to get it increased.
Ron

>>> Jesper Haure Norrevang <jhn.aida@xxxxxx> 01/12/2005 5:26:26 AM >>>
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

--
//www.freelists.org/webpage/oracle-l

Other related posts: