RE: Temp space in 10g and data insert speed

  • From: "Gints Plivna" <Gints.Plivna@xxxxxxxxx>
  • To: <ineyman@xxxxxxxxxxxxxx>, <RROGERS@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 11 Jan 2005 16:47:43 +0200

According to docs default value for 10g for parameter
WORKAREA_SIZE_POLICY is AUTO. As you havent it explicitly set to MANUAL
in init file which means you are using automatic sizing of PGA defined
by  pga_aggregate_target. As you have commented out pga_aggregate_target
it may use its default value and once again from docs it is 10 MB or 20%
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=3DAUTO 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 help
> with the performance.
> Igor Neyman, OCP DBA
> ineyman@xxxxxxxxxxxxxx

Other related posts: