Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cursor Performance Questions

958558Aug 28 2012 — edited Aug 29 2012
I have a cursor on a complex SQL that goes against seven large tables (10M rows). When I run the SQL it returns rows quickly even when the final result set is a million rows. (So first_rows does not seem to have any impact).

When I run it via the cursor, it can take 10-15 minutes before the cursor get the first 20K rows. I am doing a bulk collect on the cursor and inserting the rows, commiting them. Looking at the session via Toad I see its on the select statment most of the time. Why does it take 10-15 minutes to get the first 20K rows??

As the the cursor runs, each block of 20K rows is obtained faster and faster till when the last five batches of 20K come in, the batches take only 5 - 10 secs each.

So I have two questions:
Why is the cursor so slow to get started, while the SQL shows results within seconds.
Once started why does it appear to speed up towards the end.

I have seen this happen many times ( > 20) so its not a one off occurrence.

I am on Oracle 10.2.0.2

R
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 26 2012
Added on Aug 28 2012
8 comments
982 views