RE: Temp space in 10g and data insert speed

Guang,
Thanks for the input.
 That might be a good choice if the network was not reliable.( not saying that ours is the best)
but the table sizes are around 3 - 4 Gig in size and have 10s of millions of rows. Time to transfer the
data to the server temp table and then load from the temp table could be very prohibitive.
I have checked the reference manual for 10g that I downloaded yesterday and I am starting with
the parameters pga_aggregate_target while adjusting the sga_target to match the memory
in the server.
I will keep you posted.
Ron

>>> "Guang Mei" <gmei_at_incyte.com> 01/11/2005 11:56:57 AM >>>
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