Cursor Performance Questions
958558Aug 28 2012 — edited Aug 29 2012I 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