RE: Parse Vs Execute

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