Kbase P19649: How to optimize cursor usage for Oracle DataServer?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/06/2003 |
|
Status: Verified
GOAL:
How to optimize cursor usage for Oracle DataServer?
GOAL:
Tips for optimizing and/or fine tuning cursor usage for Oracle DataServer
FACT(s) (Environment):
Oracle DataServer
FIX:
If the ·c client connection parameter for cursor value is set to a low value, i.e., to say 50 to 100 range, and get an error stating increase the ·c value, most probably, the application is causing the cursor leak. If a higher cursor value is used in both oracle init file and the data server connection with -c, it may cause problem to oracle because of the resource issue and the oracle·s recursive error. If however the oracle generates a recursive error, the data server automatically decreases the ·c value and prints the message in the data server log file.
If the application has the cursor leak, the most important things to check if many FIND statement on many indexes are used, or never closed the open queries and the dynamic queries in the application. A FIND statement will keep the cursor open until the life of the applications since it does not know about if/when the find next or previous on the same query will be used in the future. A very good practice is to avoid using FIND statement in the application that uses the different data server products. Read through the following solution for more information on the oracle DataServer performance consideration:
P17193, " What are the performance considerations for Oracle dataserver?"
To determine the optimum cursor value, consider the following steps:
1. The first step is to start off with a lower number of cursors with ·c parameter with -Dsrv switch turned onto verbose logging level.
2. After running the application, analyze the dataserver log file for the count of sqlcrc values. The fgrep option on unix or MKS tool or similar other tools can be used to accomplish the purpose.
a. Count the total number of sqlcrc values in the log file.
b. Count the total unique sqlcrc values in the log file.
3. If you have a high sqlcrc count and low unique sqlcrc count, there may have some reparsing of the same sql are happening. In that case, look at the first sqlcrc value and trace for next all occurrences of the same sqlcrc value. Then compare the cursor number inside the ·<>· brackets of all the oparse call. Most probably the cursor number will be different for each oparse call. These cursor numbers are the most recently reused cursor that was marked for reused with ·omru·. The ratio (·b· divided by ·a· in the sub steps of step 2) of optimized reparsing value should not exceed more then 5 to 10 percent range.
If a cursor is reused for a same SQL (·a good thing·), an oparse will not be done again on the same SQL, instead an oexfet (for SELECT statement)/oexec (for insert or update or delete statement) /oexn (for bulk inserts) with the same cursor number as it was in the first oparse of the SQL will be seen in the data server log file.