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).
