Re: Optimising PL/SQL Script. Takes 2 days to run.

Thanks for the help everyone! Sorry I didn't give any details of my environment...

> 1) What version of Oracle?

9.2.0.1.0

> 2) What operating system and version?

Solaris 8, Sparc

> 3) What is your hardware configuration?

4x 750MHz UltraSparc III's with 4096MB RAM (1 domain of a SunFire V3800)

> 4) What is your SGA size and layout?

400MB connected to EMC Symmetrix via fibre-channel.

> 5) What are your physical disks and cache layout? (especially for the
> tablespaces involved in the SQL)

It's all on an EMC Symmetrix -- So I can't really tell the exact layouts.

> 6) For these tables, what are the layout of the tablespaces - physical and
> logical?

Sorry, I feel like a fool... but I don't know how to answer this!

> 7) Are you using partitioning, indexes (e.g. binary, btree, function,
> etc.), etc? If so, what? If no, why NOT?

I'm so new to this, that doing this would never have entered into my mind. Also, I don't know how to find it out.

> 8) How busy is the system during the two days?

Not very.

> One thing I immediately see is doing an insert into a table takes a long
> time (especially with logging on, etc.) I would suggest looking at having
> an external program build the forcast table for you externally and the using
> SQL loader (or alike) to load the data quickly.

I will try this approach... I'm much more proficient with Perl than with SQL :-)

Thanks for your help!!

David. Received on Wed Jan 22 2003 - 04:13:22 CST