Re: Options for large transactions

From: Adrian Carlson-Hedges <adrian_at_warcraft.demon.co.uk>
Date: Thu, 29 Nov 2001 20:19:48 +0000
Message-ID: <Rj9RUDAkhpB8Ewxh@warcraft.demon.co.uk>

Tom,

Thanks for the links, very helpful.

I re-ran my procedure to try and get the exact error, and what would you know. 18 hours later it completed successfully. However Your comments made me think, and it is possible that the error was caused by one of the other cursors inside the main loop. I had perhaps mistakenly assumed that the problem would be with the main outer cursor.

For your information, the process involved SQL*Loading the data into a 'legacy' schema. The data is Company Financial Report data. Each company can issue many reports, each report contains many facts, each fact may contain subfacts. Each report May have to modify some of the company level information. Each company has address(es), code(s), relation(s) to individuals, or other organisations.

Since the source data was from 3 different databases, I decided to sqlload it into my target database first with the tables defined as they were in the legacy hosts. (1 Oracle, 1 Teradata, 1 IBM Mainframe)

A commit was done after each company. Typically this was between 5-30 reports, each report had about 15-50 facts, and possibly subfacts.

The procedure goes something like

for rec_company in cur_company loop

        for rec_report in cur_report(rec_company.company_id) loop
                for rec_fact in cur_fact(rec_report.report_id) loop
                        stuff;
                end loop;
        end loop;
        commit;

end loop;

I suspect that the problem is running out of rollback within a company loop, where I may want to update the same block more than once.

Thanks again

Adrian

In article <9u0srg0pon_at_drn.newsguy.com>, Thomas Kyte <tkyte_at_us.oracle.com> writes
>In article <0EsoNDAnE+A8EwvN_at_warcraft.demon.co.uk>, Adrian says...
>>
>>No modifications are made to table1. The task is a migration of data
>>from legacy databases to a new system. Table1, 2,3,4,5 etc. are tables
>>of data as they were in the legacy database. It would be possible to
>>make all of my source tables read-only.
>>
>
>Well, I think if you are getting the 1555 and the only cursor that stays open is
>on table1 -- and you are getting the 1555 on table1 -- putting it in a read only
>tablespace *won't help*
>
>See
>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:895410916429
>for an example of why.
>
>see
>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:275215756923
>and read case 2 -- that example above is an example of case 2.
>
>Do you load up table1 with lots of data -- and then process it?
>
>Tell us more about HOW this process works. If you load up table1 with
>conventional inserts (say a big bulk load) we might have to "clean out" that
>table.
>
>What cursor is getting the error exactly?
>Against what table?
>
>
>
>
>
>
>>In article <9tuc0r05eu_at_drn.newsguy.com>, Thomas Kyte
>><tkyte_at_us.oracle.com> writes
>>>In article <9tu1sf$n9d$1_at_plutonium.btinternet.com>, "Adrian says...
>>>>
>>>>Oracle 8.1.6
>>>>
>>>>I have a procedure that contains something like :
>>>>
>>>>[...]
>>>>-- about 50000 records in table1
>>>>CURSOR my_cursor IS Select * from table1;
>>>>
>>>>FOR my_record in my_cursor loop
>>>> /*
>>>> * insert about 40-100 records in tables
>>>> * R,S,T,X,Y and Z based upon data
>>>> * from the cursor, and some other static tables (tables 2,3,4,5...)
>>>> */
>>>> do_plenty_of_stuff;
>>>>-- Commit;
>>>>END LOOP;
>>>>[...]
>>>>
>>>>If I have the commit commented out, then I avoid the snapshot too old error,
>>>>however I run out of rollback. If I uncomment the Commit, then it is likely
>>>>that I will get the snapshot too old error.
>>>>
>>>>If I wanted to make a rollback segment big enough for the entire
>>>>transaction, then it would need to be > 2Gb. I can do this, but am a little
>>>>reluctant to. Especially since I am only testing a load, and when my
>>>>procedure is complete, it will not be my database that the data will be
>>>>loaded into.
>>>>
>>>>What other options do I have?
>>>
>>>do you modify table1?
>>>
>>>>
>>>>It could be feasible to put tables 1,2,3,4, 5... into their own tablespace,
>>>>and have this tablespace as read-only. Would this be a possible solution, or
>>>>does the fact that the cursor is based upon a read-only tablespace make no
>>>>difference?
>>>>
>>>>I guess running the procedure in smaller chunks is an option, but I'm hoping
>>>>some bright spark has a better solution.
>>>>
>>>>Thanks
>>>>
>>>>Adrian
>>>>
>>>>
>>>
>>>--
>>>Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>>>Expert one on one Oracle, programming techniques and solutions for Oracle.
>>>http://www.amazon.com/exec/obidos/ASIN/1861004826/
>>>Opinions are mine and do not necessarily reflect those of Oracle Corp
>>>
>>
>>--
>>Adrian Carlson-Hedges
>
>--
>Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>Expert one on one Oracle, programming techniques and solutions for Oracle.
>http://www.amazon.com/exec/obidos/ASIN/1861004826/
>Opinions are mine and do not necessarily reflect those of Oracle Corp
>

-- 
Adrian Carlson-Hedges

Received on Thu Nov 29 2001 - 14:19:48 CST