| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SESSION_CACHED_CURSORS -- RE: Parse Vs Execute
What's the value for your cursor_space_for_time parameter?
Tanel.
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
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_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).
--
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).
Hemant K Chitale
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: tanel.poder.003_at_mail.ee 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).Received on Sun Nov 30 2003 - 07:29:25 CST
 |
 |