Re: 01555 and select statement

why is the statement committing inside the loop?

I've seen Tom Kyte (http://asktom.oracle.com) use such statements when he is TRYING to cause an ORA-01555.

The code above is a "poster child" for poor coding design. In 10g R1, depending upon some init.ora settings, it would likely run behind the scenes with FORALL and BULK COLLECT with no commit until the end of processing.

"10 days to finish"
Are other sessions attempting to perform DML against this table also? You may be experiencing a condition of "yo-yo updating", whereby a row that is to be modified has changed since the cursor of interest was opened, and the statement is rolled back and restarted. If so, you may want to have this routine run in a maintenance window or in a more pessimistic fashion and select for update nowait prior to actually updating the rows of interest (or be a bdbafh and lock the table exclusive nowait).

You will want to make sure that the select and update statements are tuned, and you might want to hike the sort_area_size, hash_area_size and db_file_multiblock_read_count for this session (aka _go_faster) using a workarea_policy of manual.

Have you considered running this in parallel, possibly leveraging dbms_job to parallelize this if parallel_servers are not available?

Tom Kyte has examples of this on his site. Here is a good one for you to start with: http://asktom.oracle.com/pls/ask/f?p=4950:8:8648822369547731155::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330

hth.

-bdbafh Received on Wed May 10 2006 - 12:46:13 CDT