Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute

Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 01 Dec 2003 07:14:28 -0800
Message-ID: <F001.005D855F.20031201071428@fatcity.com>



CURSOR_SPACE_FOR_TIME is FALSE.

This is an Oracle Apps R11 install.

Hemant
At 05:29 AM 30-11-03 -0800, you wrote:
What's the value for your cursor_space_for_time parameter?
 
Tanel.
 
----- Original Message -----
From: Hemant K Chitale
To: Multiple recipients of list ORACLE-L
Sent: Sunday, November 30, 2003 8:54 AM
Subject: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute



I have taken SESSION_CACHED_CURSORS from 0 to 100 to 400.  On occassion I still see
very high LIBRARY CACHE LATCH contention and am considering upping the value again.
Currently, I set it at the Instance level.  Since I am running Oracle Apps, I have suggested
to the application team to put a custom ALTER SESSION trigger into the specific first
responsibility form for users who do navigate between forms a lot and where we see
high contention.
Running Steve Adams's query, I get
SQL> @Session_Cursor_Cache.sql

PARAMETER                     VALUE USAGE
----------------------------- ----- -----
session_cached_cursors          400   50%
open_cursors                   1024   36%



CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
----------------- ----------- -----------
           35.10%      63.09%       1.81%



MAX_CACHEABLE_CURSORS
---------------------
                 5227

Running StatsPack during a PEAK period and then analyzing the output at oraperf.com, I get :
33409 parses (673 hard parses), 498516 executions of SQL statements happened. Normally the number of parses should be low and executions should be high. Each cursor was parsed an average of 1.31 times. A value greater than 1, means that the same cursor is parsed more than once. A value lower than 1 means that not all opened cursors have been parsed yet. Parsing the same cursor again and again will consume CPU and other resources. There is no need to parse the same cursor again for each execute. The re-parsing normally happens becomes some applications have an build in cursor cache which is configured too small. Making the cursor cache in the application larger will reduce the reparsing. During this interval 508 sessions logged on and at the end of the timing interval 0 more sessions where active.

The init.ora parameter SESSION_CACHED_CURSORS has been set. This resulted in reducing the parse count from 32736 to 22550
 
During parsing 276280 msec of CPU were used and 1134430 msec was spent waiting on resources. This will most likely will be latch contention on 'library cache' latch
 
96% of the latch wait time is on the Library Cache Latch [85% of the Response Time was Wait Time, 71% of the Wait Time
was Latch Wait time and 96% of the Latch Wait Time was Library Cache Latch,  ..... this Wait Time analysis really
does make sense !]

Hemant
 
At 10:14 PM 29-11-03 -0800, you 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@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@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@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@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@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@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).
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :  http://hkchital.tripod.com

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: hkchital@singnet.com.sg 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@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).

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is :  http://hkchital.tripod.com

-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale INET: hkchital@singnet.com.sg 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@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). Received on Mon Dec 01 2003 - 09:14:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US