|
What's the value for your cursor_space_for_time
parameter?
Tanel.
----- Original Message -----
Sent: Sunday, November 30, 2003 8:54
AM
Subject: SESSION_CACHED_CURSORS -- RE:
Parse Vs Execute
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: [EMAIL PROTECTED]
>
>
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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:
[EMAIL PROTECTED] (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:
[EMAIL PROTECTED] 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:
[EMAIL PROTECTED] (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).
|
