Cleanup Taking too Long?

I have know a programmer that has recently come up against an interesting puzzle when dealing with stored procedures, and I wanted to know if anyone knew why Oracle was behaving the way it did.

Basically, there existed a main procedure that parsed a dynamic where clause to execute a query, and as part of its columns also called several stored functions that then called stored procedures. The result of this complex query was about 3000 rows. Because of the complexity, the programmer was performance tuning the query and issued some rdbms_output.put_line statements to indicate to him approximately the speed at which the rows where being fetched and the total lapse time. What happened after finally getting the entire process to run in 6 minutes was that the 3000 rows only took 2 and half minutes and then the process would just hang a while before returning control back to the terminal. This is between the time it took after the display of the message "Ending" and after the close cursor command one line before the physical end of the procedure.

So my queston is this, what was the additional 3 1/2 minutes of time used for? None of the procedures did any updates, inserts or deletes so there should'nt have been any issue with database triggers and what not. Can't imagine that the rollback segments would have extended so far to cause that much of a delay. The version of the database is 7.2.2 running on an IBM SP2 with AIX. Supposedly the programmer has never failed to repeat the delay.

Any ideas what Oracle was doing? Received on Tue Aug 06 1996 - 00:00:00 CEST