Yes, you are correct. I was thinking of another cursor parm; I should have checked first.
Jared On Sat, 2003-11-29 at 22:14, Richard Ji 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). > -- 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).
