CURSOR_SPACE_FOR_TIME is FALSE.
This is an Oracle Apps R11 install.
Hemant
At 05:29 AM 30-11-03 -0800, you wrote:
What's
the value for your cursor_space_for_time parameter?
Tanel.
- ----- Original Message -----
- From: Hemant K
Chitale
- To: Multiple recipients of
list ORACLE-L
- 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: 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
