RE: Temp space in 10g and data insert speed

I have never tested it, but it may worth trying by "copy" the remote table to a local table first (I think there is a copy command in sqlplus), then insert them into your destination table.

Guang

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ron Rogers Sent: Tuesday, January 11, 2005 11:35 AM To: oracle-l_at_freelists.org; ineyman_at_perceptron.com; Gints.Plivna_at_softex.lv
Subject: RE: Temp space in 10g and data insert speed

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_at_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_at_softex.lv> 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_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Igor Neyman
> Sent: Tuesday, January 11, 2005 4:24 PM
> To: RROGERS_at_galottery.org; oracle-l_at_freelists.org
> 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_at_perceptron.com

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

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