RE: Temp space in 10g and data insert speed

  • From: "Ron Rogers" <RROGERS@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>, <ineyman@xxxxxxxxxxxxxx>, <Gints.Plivna@xxxxxxxxx>
  • Date: Tue, 11 Jan 2005 11:35:10 -0500

 Thanks for the feedback, It gives me other avenues to venture down.
The load procedures are.
Log into server 1 and issue "insert into table1@databaselink select *
from table1;"
on the larger tables I would separate the inserts by the primary key
(saledate) and
load a month at a time then commit;

I will look into the WORKAREA_SIZE_POLICY parameter and it's impact
when I get a chance. Right now I'm tied up with new client pc
It's great to have a multi- tasking job.

>>> "Gints Plivna" <Gints.Plivna@xxxxxxxxx> 01/11/2005 9:47:43 AM >>>
According to docs default value for 10g for parameter
WORKAREA_SIZE_POLICY is AUTO. As you havent it explicitly set to
in init file which means you are using automatic sizing of PGA defined
by  pga_aggregate_target. As you have commented out
it may use its default value and once again from docs it is 10 MB or
of the size of the SGA, whichever is greater. Probably 20% (~130M I
think) in your case is big enough.

But if you are using WORKAREA_SIZE_POLICY=AUTO then Oracle doesn't use
sort_area_size and sort_area_retained_size.

And you haven't told anything about data loading process, so probably
problem isn't in db parameters.


> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx 
> On Behalf Of Igor Neyman
> Sent: Tuesday, January 11, 2005 4:24 PM
> To: RROGERS@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx 
> Subject: RE: Temp space in 10g and data insert speed
> Why do you think tempfile should be increasing? May be it's big
> To check usage of tempfile look into V$TEMPSTAT.
> Also, increasing sort_area_size and sort_area_retained size might
> with the performance.
> Igor Neyman, OCP DBA
> ineyman@xxxxxxxxxxxxxx

Other related posts: