One way for reducing latching & contention in heavy soft-parse scenarios
would be setting cursor_space_for_time to true. This requires somewhat more
memory in your UGAs, but can "make things go faster". You should check
others recommendations first, but you could measure your statistics for an
operation in single session with and without this parameter set and continue
from there.
Tanel.
----- Original Message -----
From: "Hemant K Chitale" <hkchital@(protected)>
To: <oracle-l@(protected)>
Sent: Thursday, February 12, 2004 7:49 AM
Subject: Re: Library Cache Latch statistics from StatsPack -- more
statistics
>
> Jonathan,
> The statistics from yesterday were for a 4-hour period.
> Some more statistics today :
>
> For the 5minute period :
>
> Snap Id Snap Time Sessions
> ------- ------------------ --------
> Begin Snap: 397 12-Feb-04 13:40:03 383
> End Snap: 398 12-Feb-04 13:45:02 383
> Elapsed: 4.98 (mins)
>
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait %
> Total
> Event Waits Time (cs) Wt
> Time
> -------------------------------------------- ------------ ------------
> -------
> latch free 189,338 163,247
> 90.36
> db file sequential read 74,791 11,116
> 6.15
> db file scattered read 48,575 2,522
> 1.40
> PL/SQL lock timer 17 1,601
> .89
> log file sync 1,215 1,249
> .69
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (cs) (ms)
> /txn
> ---------------------------- ------------ ---------- ----------- ------
> ------
> latch free 189,338 84,293 163,247 9
> 162.2
>
> Pct Avg
> Pct
> Get Get Slps NoWait
> NoWait
> Latch Name Requests Miss /Miss Requests
> Miss
> ----------------------------- -------------- ------ ------ ------------
> ------
> library cache 1,520,906 4.8 2.5 1,649
> 19.6
>
>
> Get Spin
&
> Latch Name Requests Misses Sleeps Sleeps
> 1->4
> -------------------------- -------------- ----------- -----------
> ------------
> library cache 1,520,906 73,478 183,992
> 5766/9250/27
>
> 633/30829/0
>
> NoWait
> Waiter
> Latch Name Where Misses Sleeps
> Sleeps
> ------------------------ -------------------------- ------- ----------
> --------
> library cache kgllkdl: child: cleanup 0 76,856
> 1,355
> library cache kgllkdl: child: free pin 0 39,738
> 9,843
> library cache kglpnal: child: before pro 0 36,866
> 20,389
> library cache kglpin 0 22,339
> 16,657
> library cache kgldti: 2child 0 3,720
> 2,719
> library cache kglhdgn: child: 0 887
> 13,807
> library cache kglic 0 653
> 12,511
> library cache kglpnc: child 0 507
> 24,884
> library cache kglget: child: KGLDSBRD 0 307
> 2,175
> library cache kglget: child: KGLDSBYD 0 284
> 34,904
> library cache kglupc: child 0 236
> 20,781
> library cache kglpnal: child: alloc spac 0 209
> 5,515
> library cache kglrtl 0 166
> 240
> library cache kglhdgc: child: 0 48
> 200
> library cache kgldtld: 2child 0 46
> 181
> library cache kglidp: parent 0 27
> 5
> library cache kglpndl: parent: purge 0 24
> 17
> library cache kglpnp: child 0 17
> 14,454
> library cache kgldrp: parent 0 11
> 7
> library cache kglobpn: child: 0 7
> 561
> library cache kglpnal: parent held, no p 0 6
> 0
>
>
>
>
> For the 20minute period :
>
> Snap Id Snap Time Sessions
> ------- ------------------ --------
> Begin Snap: 397 12-Feb-04 13:40:03 383
> End Snap: 399 12-Feb-04 14:00:02 383
> Elapsed: 19.98 (mins)
>
> Top 5 Wait Events
> ~~~~~~~~~~~~~~~~~ Wait %
> Total
> Event Waits Time (cs) Wt
> Time
> -------------------------------------------- ------------ ------------
> -------
> latch free 688,470 451,351
> 64.36
> db file sequential read 440,756 143,801
> 20.51
> PL/SQL lock timer 655 67,182
> 9.58
> db file scattered read 172,346 17,913
> 2.55
> buffer busy waits 4,067 6,225
> .89
>
> Avg
> Total Wait wait
> Waits
> Event Waits Timeouts Time (cs) (ms)
> /txn
> ---------------------------- ------------ ---------- ----------- ------
> ------
> latch free 688,470 299,440 451,351 7
> 117.8
>
> Pct Avg
> Pct
> Get Get Slps NoWait
> NoWait
> Latch Name Requests Miss /Miss Requests
> Miss
> ----------------------------- -------------- ------ ------ ------------
> ------
> latch wait list 405,001 0.1 0.1 407,674
> 0.0
>
>
> Get Spin
&
> Latch Name Requests Misses Sleeps Sleeps
> 1->4
> -------------------------- -------------- ----------- -----------
> ------------
> library cache 6,874,664 283,875 675,544
> 25337/33853/
>
> 114693/10999
> 2/0
>
> NoWait
> Waiter
> Latch Name Where Misses Sleeps
> Sleeps
> ------------------------ -------------------------- ------- ----------
> --------
> library cache kgllkdl: child: cleanup 0 288,943
> 5,137
> library cache kglpnal: child: before pro 0 165,674
> 66,261
> library cache kgllkdl: child: free pin 0 123,605
> 30,579
> library cache kglpin 0 74,778
> 67,511
> library cache kgldti: 2child 0 8,313
> 7,349
> library cache kglhdgn: child: 0 3,224
> 43,202
> library cache kglget: child: KGLDSBRD 0 1,241
> 6,596
> library cache kglpnc: child 0 1,234
> 110,621
> library cache kglget: child: KGLDSBYD 0 1,201
> 144,860
> library cache kglpnal: child: alloc spac 0 1,073
> 20,347
> library cache kglic 0 1,044
> 18,755
> library cache kglupc: child 0 848
> 92,531
> library cache kglrtl 0 501
> 611
> library cache kgldtld: 2child 0 271
> 613
> library cache kglhdgc: child: 0 153
> 660
> library cache kglpnp: child 0 98
> 48,909
> library cache kglidp: parent 0 87
> 6
> library cache kglpndl: parent: purge 0 43
> 26
> library cache kglobpn: child: 0 39
> 1,951
> library cache kgldrp: parent 0 14
> 8
> library cache kglpnal: parent held, no p 0 12
> 0
> library cache kglpsl: child 0 3
> 19
>
> Hemant
>
> At 04:16 PM 11-02-04 +0000, you wrote:
>
> You haven't given a time-period for the snapshot,
> so we don't have a clue about whether the problem
> is causing real hardship.
>
> However, your comment about 'executions are high'
> matches the statistics.
>
> If you have a cursor held open (x$kgllk - lock mode = null),
> and want to execute it, you have to create a pin (x$kglpn -
> lock mode = share, I think).
>
> If you are doing extreme amounts of very short executions,
> than I guess you will be busy pinning and unpinning - and
> that's the general hint we might get from looking at the
> locations where the laching is going on.
>
> Do you have a small number of very large packages which
> have very popular procedures - is there a package with a
> handful of very popular procedures that keeps getting hit ?
> Or perhaps a couple of SQL statements that are executed
> an extreme number of times ?
>
> And, as Mark says, you could be seeing a problem
> that is being exaggerated by a bug.
>
> You get some idea of the benefit of the session_cached_cursors
> by checking a couple of stats in v$sesstat . The exact names
> escape me, but they are something like:
> session cursors cached
> session cursor cache hits.
>
> I think caching would just consume CPU at the client
> end, though, rather than cause latching directly. (Though
> if the client is running on the server, the extra CPU usage
> might exacerbate a latching problem).
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk[1]
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> Next public appearances:
> March 2004 Hotsos Symposium - The Burden of Proof
> March 2004 Charlotte NC OUG - CBO Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html[2]
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html[3]
> ____UK___February
> ____UK___June
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html[4]
>
>
> ----- Original Message -----
> From: "Hemant K Chitale" <hkchital@(protected)>
> To: <oracle-l@(protected)>
> Sent: Wednesday, February 11, 2004 3:44 PM
> Subject: RE: Library Cache Latch statistics from StatsPack
>
>
>
> Thanks Mark. I know I haven't put much information in my email.
> I am hoping that someone can explain which of the "Where"s for the
> Library Cache Latch should I worry about and *why* [ie , what does
> "kgllkdl: child: cleanup" or "kgllkdl: child: free pin" mean !!]
>
> I do have SESSION_CACHED_CURSORS -- and I think it is too high at 400.
> {progressively increased from 0 to 100 to 400 over the past year}.
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com[5]
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/[6]
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html[7]
> -----------------------------------------------------------------
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> http://hkchital.tripod.com[8] {last updated 24-Jan-04}
>
>
>
> --- Links ---
> 1 http://www.jlcomp.demon.co.uk/
> 2 http://www.jlcomp.demon.co.uk/tutorial.html
> 3 http://www.jlcomp.demon.co.uk/seminar.html
> 4 http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> 5 http://www.orafaq.com/
> 6 http://www.freelists.org/archives/oracle-l/
> 7 http://www.freelists.org/help/fom-serve/cache/1.html
> 8 http://hkchital.tripod.com/
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@(protected)
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
