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