| Inefficient cursor [message #372703] |
Sat, 03 March 2001 16:24  |
Kay
Messages: 3
Registered: March 2001
|
Junior Member |
|
|
I have written a plsql program that will be executed weekly and accesses approximately 500k records for reporting.
This program tends to be very slow, and i am wondering if a cursor is limited in the amount of record it can handle and what is the work around.
|
|
|
|
| Re: Inefficient cursor [message #372706 is a reply to message #372703] |
Sun, 04 March 2001 14:50  |
Joachim Lindner
Messages: 30
Registered: February 2001
|
Member |
|
|
Hi Kay,
I don't think there is something like a maxrows limit for
cursors. If there were such a limit your programs wouldn't
be performing badly but would be aborted instead.
I suggest to do the following without knowing any details
about the characteristics of your program.
1. Try to find out if the cursor really consumes the time
or if it is the (imperative) PL/SQL part of the program.
Execute the cursor's statement from within SQL*Plus and measure
the time elapsed.
2. If the cursor consumes most of the runtime then review the
execution plan thoroughly and try to optimize using PQO (might
help on single-processor machines, too) and/or other techniques
depending on the characteristics of the query and the amount of
data being accessed compared to the amount of data to be fetched.
Also verify that there is no concurrent OLTP that forces your
batch query to heavily access rollback segs in order to ensure
read consistency.
3. If the pl/sql part uses up most of the runtime then try to
identify the processing hotspot using trace info
(DBMS_APPLICATION_INFO package etc.).
For any further advise more details are required.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
