I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400. On
occassion I still see
very high LIBRARY CACHE LATCH contention and am considering upping the
value again.
Currently, I set it at the Instance level. Since I am running
Oracle Apps, I have suggested
to the application team to put a custom ALTER SESSION trigger into the
specific first
responsibility form for users who do navigate between forms a lot and
where we see
high contention.
Running Steve Adams's query, I get
SQL> @Session_Cursor_Cache.sql
PARAMETER
VALUE USAGE
----------------------------- ----- -----
session_cached_cursors
400 50%
open_cursors
1024 36%
CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
----------------- ----------- -----------
35.10%
63.09% 1.81%
MAX_CACHEABLE_CURSORS
---------------------
5227
Running StatsPack during a PEAK period and then analyzing the
output at oraperf.com, I get :
33409 parses (673 hard parses),
498516 executions of SQL statements happened. Normally the number of
parses should be low and executions should be high. Each cursor was
parsed an average of 1.31 times. A value greater than 1, means that the
same cursor is parsed more than once. A value lower than 1 means that not
all opened cursors have been parsed yet. Parsing the same cursor again
and again will consume CPU and other resources. There is no need to parse
the same cursor again for each execute. The re-parsing normally happens
becomes some applications have an build in cursor cache which is
configured too small. Making the cursor cache in the application larger
will reduce the reparsing. During this interval 508 sessions logged on
and at the end of the timing interval 0 more sessions where active.
The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted
in reducing the parse count from 32736 to 22550
During parsing 276280 msec
of CPU were used and 1134430 msec was spent waiting on resources. This
will most likely will be latch contention on 'library cache' latch
96% of the latch wait time is on the Library Cache Latch [85% of
the Response Time was Wait Time, 71% of the Wait Time
was Latch Wait time and 96% of the Latch Wait Time was Library Cache
Latch, ..... this Wait Time analysis really
does make sense !]
Hemant
At 10:14 PM 29-11-03 -0800, you wrote:
I thought the
session_cached_cursors is dynamic and scope is
session? This is on 8.1.7. I have used:
alter session set session_cached_cursors=500;
-----Original Message-----
Sent: Sunday, November 30, 2003 12:24 AM
To: Multiple recipients of list ORACLE-L
Sami,
'cached_cursors' is not a valid hint, at least not in 9i.
Or at least, I can find no reference to it.
And 'cached cursors' as it appears in the SQL is not a
valid hint syntax.
You need to set the session_cached_cursors value in the
init.ora, and bounce the database. This parameter cannot
be set dynamically, at least as of 9i.
Jared
On Sat, 2003-11-29 at 14:44, Sami wrote:
> Dear Jonathan Lewis,
>
> Many thanks for your response.
>
> Using session_cached_cursor parameter I am not getting better
response time.
> I did run this testcases multiple times but always
session_cached_cursor=0
> gives better response time.
> But the same time w.r.t latch, session_cached_cursor=100 is giving
positive
> impact.
>
> 1) session_cached_cursor=0 -> more latches but good response
time(2.60)
> 2) session_cached_cursor=100 -> less # of latches but higher
response
> time(2.87)
>
> Version :8.1.7.3
> OS: Sun Solaris
>
> tkprof output
> =============
> SELECT /*+ cached cursors 0
> */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV
> FROM T1 P,T2 E,T3 C WHERE P.T1ID =
E.T1ID AND P.BUSINESS_COUNTRY_ID =
> C.COUNTRYABBREV
>
>
> call
count cpu
elapsed
disk query current
> rows
> ------- ------ -------- ---------- ---------- ----------
----------
> ----------
> Parse
2000
1.76
1.77
0
0 0
> 0
> Execute 2000
0.84
0.74
0
0 0
> 0
> Fetch
0 0.00
0.00
0
0 0
> 0
> ------- ------ -------- ---------- ---------- ----------
----------
> ----------
> total
4000
2.60
2.51
0
0 0
> 0
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 165 (recursive depth:
1)
>
> Rows Row Source Operation
> -------
---------------------------------------------------
> 0 HASH JOIN
> 0 INDEX FAST
FULL SCAN (object id 76648)
> 0 HASH
JOIN
> 0 TABLE
ACCESS FULL T2
> 0 TABLE
ACCESS FULL T1
>
>
>
>
> SELECT /*+ cached cursors 100
*/FIRST_NAME,LAST_NAME,CUSTOMERID,
> COUNTRYABBREV
> FROM T1 P,T2 E,T3 C WHERE P.T1ID =
E.T1ID AND P.BUSINESS_COUNTRY_ID =
> C.COUNTRYABBREV
>
>
> call
count cpu
elapsed
disk query current
> rows
> ------- ------ -------- ---------- ---------- ----------
----------
> ----------
> Parse
2000
2.05
1.99
0
0 0
> 0
> Execute 2000
0.82
0.74
0
0 0
> 0
> Fetch
0 0.00
0.00
0
0 0
> 0
> ------- ------ -------- ---------- ---------- ----------
----------
> ----------
> total
4000
2.87
2.73
0
0 0
> 0
>
> Misses in library cache during parse: 0
> Optimizer goal: CHOOSE
> Parsing user id: 165 (recursive depth:
1)
>
> Rows Row Source Operation
> -------
---------------------------------------------------
> 0 HASH JOIN
> 0 INDEX FAST
FULL SCAN (object id 76648)
> 0 HASH
JOIN
> 0 TABLE
ACCESS FULL T2
> 0 TABLE
ACCESS FULL T1
>
>
>
****************************************************************************
> ****
>
> Program used to generate the above trace file.
> ==============================================
>
> alter session set SQL_TRACE=true;
> alter session set session_cached_cursors=0;
> declare
> type rc is ref cursor;
> C rc;
> n number :=0;
> begin
> n := dbms_utility.get_time;
> for i in 1 .. 2000 loop
> open C for select
/*+ cached cursors 0 */
> first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c
where
> p.t1id=e.t1id and p.business_country_id=c.countryabbrev;
> close C;
> end loop;
> dbms_output.put_line( dbms_utility.get_time - n );
> end;
> /
> alter session set session_cached_cursors=100;
> declare
> type rc is ref cursor;
> C rc;
> n number :=0;
> begin
> n := dbms_utility.get_time;
> for i in 1 .. 2000 loop
> --open C for select
/*+ cached_cursors 100 */ * from dual;
> open C for select
/*+ cached cursors 100 */
> first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c
where
> p.t1id=e.t1id and p.business_country_id=c.countryabbrev;
> close C;
> end loop;
> dbms_output.put_line( dbms_utility.get_time - n );
> end;
> /
>
>
> SQL> @x
>
> Session altered.
> Session altered.
>
> 394
>
> PL/SQL procedure successfully completed.
> Session altered.
>
> 413
>
> PL/SQL procedure successfully completed.
> SQL>
>
>
>
Name
Run1 Run2
Diff
> LATCH.KCL lock element
parent
1
2 1
> LATCH.KCL name table
latch
1
2 1
> LATCH.cache buffers lru
chain
1
2 1
> STAT...calls to
kcmgas
2
1 -1
> STAT...redo ordering
marks
2
1 -1
> STAT...free buffer
requested
2
1 -1
> LATCH.checkpoint queue
latch
113
114 1
> LATCH.list of block
allocation
0
1 1
> LATCH.dlm domain lock table
la
0
2 2
> LATCH.name-service namespace
b
17
19 2
> LATCH.name-service request
que
17
19 2
> LATCH.redo
writing
4
6 2
> STAT...redo
entries
26
28 2
> LATCH.dlm group lock table
lat
0
2 2
> STAT...calls to
kcmgcs
17
20 3
> LATCH.dlm lock table freelist
12,000
12,004 4
> LATCH.session
allocation
15
19 4
> LATCH.enqueue hash
chains
0
4 4
>
LATCH.enqueues
0
4 4
> LATCH.dlm resource hash list
24,000
24,005 5
> LATCH.process parent latch
30,000
30,005 5
> STAT...consistent
gets
34
39 5
> LATCH.redo
allocation
30
25 -5
> STAT...db block
gets
64
70 6
> STAT...consistent
changes
60
68 8
> LATCH.undo global
data
23
14 -9
> STAT...db block
changes
88
97 9
> LATCH.dlm resource table freel
6,026
6,037 11
> STAT...session logical
reads
98
109 11
> STAT...parse time
cpu
57
83 26
> STAT...parse time
elapsed
58
85 27
>
LATCH.messages
200
236 36
> STAT...recursive cpu
usage
220
256 36
> LATCH.cache buffers
chains
404
327 -77
> STAT...redo
size
4,304 4,500
196
> STAT...session cursor cache co
-99
100 199
> LATCH.shared
pool
14,002 8,002 -6,000
> LATCH.library
cache
94,232 79,824 -14,408
>
> Run1 latches total versus runs -- difference and pct
> Run1
Run2 Diff Pct
> 181,088 160,677 -20,411 112.70%
>
>
> --
> Please see the official ORACLE-L FAQ:
http://www.orafaq.net
> --
> Author: Sami
> INET: saminathans@myrealbox.com
>
> Fat City Network Services -- 858-538-5051
http://www.fatcity.com
> San Diego, California --
Mailing list and web hosting services
>
---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail
message
> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and
in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You
may
> also send the HELP command for other information (like
subscribing).
>
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Jared Still
INET: jkstill@cybcon.com
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California --
Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like
subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Richard Ji
INET: Richard.Ji@ztango.com
Fat City Network Services -- 858-538-5051
http://www.fatcity.com
San Diego, California --
Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You
may
also send the HELP command for other information (like subscribing).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :
http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
INET: hkchital@singnet.com.sg
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sun Nov 30 2003 - 00:54:24 CST
