Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: session_cached_cursors pros & cons

Re: session_cached_cursors pros & cons

From: Juan Carlos Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Mon, 27 Sep 2004 13:01:22 -0400
Message-Id: <41584762.000001.00764@DAZA-MGEJCA5J7T>


>From my paper, I pasted the whole topic, hope be useful.

1.A Soft Parses: session_cached_cursor parameter Two kinds of parse calls exist, hard and soft. "hard parse" occurs when the SQL or PL/SQL statement is not found in the shared SQL area (shared pool), so a complete parsing is required (data dictionary object descriptions user's privileges, generate the execution plan, etc). The most expensive kind of parsing, and should be minimized for repeated execution.
The "soft parse", is performed when the statement is already in the shared pool (user must be authenticated again, all name translations must be done once more, syntaxis and security chekings), but the session lost the "link" to the shared portion, because the cursor was closed, so that the private portion must be rebuilt and linked to its shared portion again. To eliminate soft parsing in COBOL, C, or other 3GL applications, the precompiler option HOLD_CURSOR=YES should be used. Other options, such as RELEASE_CURSOR and MAXOPENCURSORS, can be used in conjunction with this to achieve optimal results.
For non-3GL programs (when you do not have the same degree of control over cursors) such as Oracle Forms and other third-party tools, the cursors will automatically be closed when a new form is called and switching from one form to another closes all session cursors associated with the first form. So if you subsequently return to the caller, at least a soft parse will be performed for each cursor. In this case, you should enable this parameter that will keep a copy of the user's cursors even though they are closed.  

1.A.i.a SESSION_CACHED_CURSOR parameter
Lets you specify the number of session cursors to cache. After the first “soft parse”, subsequent “soft parse” calls will find the cursor in the cache and do not need to reopen the cursor. To get placed in the session cache the same statement has to be parsed 3 times within the same cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed. Session cached cursors is a great help in reducing latching that takes place due
to excessive soft parsing (where a program parses, executes, closes a statement
over and over)
Steven Adams says,
http://www.ixora.com.au/scripts/library.htm The session cursor cache is an important facility for reducing load on the library cache. In our opinion, the session_cached_cursors parameter should always be set to at least 2. However, a larger value is normally beneficial.

Tom comment, (if steve adams said it, it is more then likely "true". as they said -- a larger is normally beneficial. I am partial (opinion, no true science here) to 100.)
1.A.i.b Important
Be aware that this is done at the expense of increased memory allocation for every session in the this will increase UGA memory which is in the PGA in dedicated server mode and in the SGA in shared server mode. An application to run optimally, is necessary to analyze how parsing works  

1.A.ii Syntax
You can set this parameter with
ALTER SESSION SET SESSION_CACHED_CURSOR = value ALTER SYSTEM SET SESSION_CACHED_CURSOR = value [DEFERRED] In parameter file
set SESSION_CACHED_CURSOR = (number), default value 0 1.A.iii Evaluating the accuracy of the value Set to 50 the parameter SESSION_CACHED_CURSOR and evaluate if this is enough

1.A.iii.a Stat: session cursor cache count Total number of cursors cached. This statistic is incremented only if SESSION_CACHED_CURSORS > 0. This statistic is the most useful in V$SESSTAT. If the value for this statistic in V$SESSTAT is close to the setting of the SESSION_CACHED_CURSORS parameter, the value of the parameter should be increased.
1.A.iii.a.1 Query to evaluate
To evaluate this parameter you can save the information of every user every time he logs off in a table, after that you can analyze it in different ways  here is one example:
CREATE TABLE Stat_Session_Historic
(
UUSER VARCHAR2(100),
DDATE DATE,
SstatisticName VARCHAR2 (200),
VVALUE NUMBER (6)
)
/
CREATE OR REPLACE TRIGGER TGR_LOGOFF_STATS BEFORE
LOGOFF
ON DATABASE   INSERT INTO Stat_Session_Historic
SELECT USER, SYSDATE, 'session cursor cache count', VALUE FROM V$SESSTAT C
WHERE C.statistic# = (select STATISTIC# from v$statname where name = session
cursor cache count')
AND C.SID = (SELECT SID FROM V$SESSION WHERE USER#= UID ) /
-- And the select will be the first 10 of an average of their statics in a period of time for user SELECT UUSER, AVGVAL
FROM
( SELECT UUSER, AVG( VVALUE ) AVGVAL
FROM Stat_Session_Historic
where TRUNC(DDATE) = TRUNC(SYSDATE) – only for today GROUP BY UUSER
ORDER BY 2 DESC
)
WHERE ROWNUM < 10 -- First 10 cases  

UUSER AVGVAL



ADM 5
SAF 1
Then you compare this value with the value you had set to the parameter, and then decide to increase or decrease the parameter. 1.A.iii.b Parse vs. Execute in statistics call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ----------

Parse 3 0.80 1.72 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 3 0.02 0.05 2 665 0 14
------- ------ -------- ---------- ---------- ---------- ----------

total 9 0.82 1.77 2 665 0 14
Misses in library cache during parse: 0
In this statistics we can see there is too soft parsing, parse = execute. In a well tuned application
parse = 1, execute = some number greater than or equal to 1 The best way to speed something up is to NOT do it. Hence, don't parse 3 times,
just parse 1 time.
if you find yourself parsing the same statement more then 3 times and you really
cannot fix the code, session cached cursors can be of some assistance. if you
do not, it will not help nor hurt.
1.A.iii.c Note.-
The V$SESSION_CURSOR_CACHE view is not a measure of the effectiveness of the SESSION_CACHED_CURSORS initialization parameter. Soft parse % ratio, tells you if you have too many , hard parses, but they cannot be fixed with session cached cursors. 1.A.iv Examples and techniques
1.A.iv.a Demonstrating the effect of changes in the parameter session_cached_cursors
>From Tom Kyte:

ops$tkyte_at_ORA817.US.ORACLE.COM> alter session set session_cached_cursors =0; Session altered.
no cached cursors......
ops$tkyte_at_ORA817.US.ORACLE.COM> select a.name, b.value 2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 /
NAME VALUE
------------------------------ ----------
session cursor cache hits 5 thats from logging in session cursor cache count 0
ops$tkyte_at_ORA817.US.ORACLE.COM> declare
2 l_cnt number;
3 begin
4 for i in 1 .. 100
5 loop
6 execute immediate 'select count(*) from dual d1' into l_cnt; 7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed. ops$tkyte_at_ORA817.US.ORACLE.COM> select a.name, b.value 2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 /
NAME VALUE
------------------------------ ----------
session cursor cache hits 5 no change
session cursor cache count 0
now, lets cache upto 10 cursors
ops$tkyte_at_ORA817.US.ORACLE.COM> alter session set session_cached_cursors=10; Session altered.
ops$tkyte_at_ORA817.US.ORACLE.COM> declare
2 l_cnt number;
3 begin
4 for i in 1 .. 100
5 loop
6 execute immediate 'select count(*) from dual d2' into l_cnt; 7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed. ops$tkyte_at_ORA817.US.ORACLE.COM> select a.name, b.value 2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%cursor ca%'
5 /
NAME VALUE
------------------------------ ----------
session cursor cache hits 104 99 more hits! session cursor cache count 4
ops$tkyte_at_ORA817.US.ORACLE.COM>  

Our first query in that loop didn't get a hit (we hadn't cached it yet), the

subsequent 99 did. It has to go through the mechanics of a pretending to do a
softparse (making sure things haven't been invalidated and such) but the code path is much smaller.
1.A.iv.b Another script to evaluate this parameter
>From Steven Adams

http://www.ixora.com.au/scripts/sql/session_cursor_cache.sql Tom comment: the script looked reasonable to me.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 27 2004 - 11:56:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US