| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: session_cached_cursors pros & cons
>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
------------------------------ ----------session cursor cache hits 5 thats from logging in session cursor cache count 0
------------------------------ ----------session cursor cache hits 5 no change
------------------------------ ----------session cursor cache hits 104 99 more hits! session cursor cache count 4
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-lReceived on Mon Sep 27 2004 - 11:56:59 CDT
 |
 |