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