RE: session_cached_cursors pros & cons
- From: SJHussain@xxxxxxxxxxxxxxx
- To: anjo.kolk@xxxxxxxxxxx
- Date: Tue, 28 Sep 2004 10:42:25 +0300
Anjo,
You gave me the exact answer what I was looking for. When I put the
proposal of setting 100 as value, my management raised a question that do
we need to increase the shared_pool_size, does lead to any shared_pool
fragmentation and this kind of questions and I was not uptot the mark to
conveince them and now I can confidently tell them.
I have analyzed my statsreport with oraperf analyzer and it also recommend
to put 100. Do you think setting 100 will cause the problems?
Best Regards,
Syed Jaffar Hussain
"Anjo Kolk"
<anjo.kolk@oraper
f.com> To
Sent by: <jreyes@xxxxxxxxxxxxxxxx>,
oracle-l-bounce@f <SJHussain@xxxxxxxxxxxxxxx>,
reelists.org <oracle-l@xxxxxxxxxxxxx>
cc
09/28/2004 10:33 Subject
AM RE: session_cached_cursors pros &
cons
Please respond to
anjo.kolk@oraperf
.com
I haven't read all the replies but one thing to keep in mind with
session_cached_cursors is that cursors that are normally closed are kept
open and that memory is kept pinned. That means that a larger shared pool
is
needed to keep all the 'open' cursors in the shared pool. It is also
responsible for a larger fragmentation of the shared pool.
In fact caching SQL statements increases performance, but could cause a
fragmentation and other shared pool problems.
So make sure that your shared pool is big enough.
Anjo.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Juan Carlos Reyes Pacheco
Sent: Monday, September 27, 2004 7:01 PM
To: SJHussain@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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
--------------
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@xxxxxxxxxxxxxxxxxxxx> alter session set session_cached_cursors
=0;
Session altered.
no cached cursors......
ops$tkyte@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx> alter session set
session_cached_cursors=10;
Session altered.
ops$tkyte@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Visit us on www.alfransi.com.sa
====== Banque Saudi Fransi - Privacy Notice ======
This message is intended only for the person or entity to which it is
addressed and may contain confidential and/or privileged material. Any
use of this information by persons or entities other than the intended
recipient is prohibited. If you have received this in error, please
contact the sender and delete the material from your computer. Any
opinions and other information contained in this message that do not
relate to the official business of Banque Saudi Fransi shall be
understood as neither given or endorsed by it. Although precautions
have been taken to ensure no viruses are present in this email, BSF
cannot accept responsibility for any loss or damage arising from the
use of this email or attachments.
====== Banque Saudi Fransi - Privacy Notice ======
--
//www.freelists.org/webpage/oracle-l
Other related posts:
