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
Gints,
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
deployement.
It's great to have a multi- tasking job.
Ron
>>> "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
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=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.
Gints
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto: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
enough?
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
