RE: Parse Vs Execute

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: saminathans_at_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_at_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_at_cybcon.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_at_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: Richard Ji
> INET: Richard.Ji_at_ztango.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_at_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_at_cybcon.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_at_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).