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

David,

Before looking at the specifics, some questions:

1. What version of Oracle?
2. What operating system and version?
3. What is your hardware configuration?
4. What is your SGA size and layout?
5. What are your physical disks and cache layout? (especially for the tablespaces involved in the SQL)
6. For these tables, what are the layout of the tablespaces - physical and logical?
7. Are you using partitioning, indexes (e.g. binary, btree, function, etc.), etc? If so, what? If no, why NOT?
8. How busy is the system during the two days?

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.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"David FitzGerald" <david_at_fitzg.com> wrote in message news:7f966bc5.0301210517.5d3b54ae_at_posting.google.com...
> Firstly, sorry for cross-posting -- I don't know the correct group to
> post to.
>
> I've written a PL/SQL script to create stock forecast data from
> another table.
>
> The ITEM_LOCATIONS table has approximately 65 million rows in it, with
> about 100,000 unique ITEM's and 650 LOC's.
>
> What I am trying to do is this:
>
> 1) Select all LOCATION's and ITEM's from ITEM_LOCATIONS table.
> 2) For each item from (1) create 7 records in new table (FORECAST),
> each of which has the same data except for a different date
> (sequential) for each one.
>
> The problem is that this is taking an awfully long time to process
> (more than 2 days). I am new to PL/SQL so don't know if I am
> approaching this the wrong way, or if this is the quickest I'll ever
> create over half a billion rows? Any pointers would be massively
> appreciated.
>
> Thanks!
> David
>
> ----------------
>
> DECLARE
> cursor c_items is
> select ITEM, LOCATION from ITEM_LOCATIONS;
>
> start_date DATE ;
>
> BEGIN
> start_date := '23-Jan-2003';
> for this_item in c_items LOOP
>
> for ctr in 0..6 LOOP
> insert into FORECAST
> values(this_item.item, item.location, start_date+ctr,
> '0');
> END LOOP;
> END LOOP;
>
> commit;
> END;
Received on Tue Jan 21 2003 - 07:48:50 CST