Tuning OPEN_CURSORS

Oracle recommend that “The best advice for tuning OPEN_CURSORS is not to tune it. Set it high enough that you won’t have to worry about it. If your sessions are running close to the limit you’ve set for OPEN_CURSORS, raise it. Your goal in tuning this parameter is to set it high enough that you never get an ora-1000 during normal operations. If you set OPEN_CURSORS to a high value, this doesn’t mean that every session will have that number of cursors open. Cursors are opened on an as-needed basis. And if one of your applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high. To see if you’ve set OPEN_CURSORS high enough, monitor the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS.”

/* Formatted on 7/30/12 1:59:19 PM (QP5 v5.163.1008.3004) */
–Query v$open_cursor to check cached cursors…
–To find out actual cursors open, you may want to use the following:
–total cursors open, by session

SELECT a.VALUE,
s.username,
s.sid,
s.serial#
FROM v$sesstat a, v$statname b, v$session s
WHERE     a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = ‘opened cursors current’;

–To find out cursors that are actually open, you may want to use the following:
–total cursors open, by session

SELECT a.VALUE,
s.username,
s.sid,
s.serial#
FROM v$sesstat a, v$statname b, v$session s
WHERE     a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = ‘opened cursors current’;

SELECT COUNT (*) FROM v$open_cursor;
COUNT(*)
———-
13644

–This lists the number of distinct open cursors per session.
–Typically, a session will have 1, maybe 2, cursor handles for the same cursor. Any more.. and it is likely an application bug. Not an Oracle bug.

SELECT c.sid AS “OraSID”,
c.address || ‘:’ || c.hash_value AS “SQL Address”,
COUNT (*) AS “Cursor Copies”
FROM v$open_cursor c
GROUP BY c.sid, c.address || ‘:’ || c.hash_value
HAVING COUNT (*) > 2
ORDER BY 3 DESC;

Ảnh

–You can look at the SQL that has 09 cursor handles opened by session 841 using the following SQL:

select

s.sql_text as  “SQL Statement”

from    v$sqltext s

where s.address = ‘07000001F464F998’

and     s.hash_value = ‘1811755084’

order by

s.piece;

>> SQL Statement

—————————————————————-

SELECT “A1”.”CUSTOMER_NAME1″ FROM “STTM_CUSTOMER” “A1” WHERE “A1

“.”CUSTOMER_NO”=SUBSTR(:B1,0,8);

 

Conclusion:
This is hard evidence that the client application using session 841 is leaking cursor handles, and leaking badly. It is opening a cursor handle (very likely a ref cursor). It never closes that ref cursor after use. It simply opens another one.. and another one.. and another one.

Solution:
THIS IS AN APPLICATION BUG. Fix the application. (it has nothing to do with an Oracle error or bug, but everything to do with HOW the client app is ABUSING Oracle).

Use the above SQL to find the SQL statement that has 219 handles. Determine (query V$SESSION) which application is using SID (Session ID) 841. Then fix the application code. Make sure that it closes REF CURSORS after use. And close them properly!

One cannot simply free the client variable for that ref cursor. That does not close the ref cursor on the Oracle side. Oracle must be called saying “close refcursor <cursorhandle>”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s