| 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: More Latch Stats : was re Fwd: Re: Library Cache Latch statis tics
Hemant,
If this was due to some process(es) executing a _very_ large number of small, quickly-completing queries within a very short period of time, then you might be able to trace them down from V$SESSTAT (joined to V$STATNAME) - look for 'user calls' (which is the count of completed SQLs). Further investigation of V$SESSION/V$PROCESS might provide additional clues.
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale
>Sent: Friday, February 13, 2004 7:45 AM
>To: oracle-l_at_freelists.org
>Subject: More Latch Stats : was re Fwd: Re: Library Cache
>Latch statistics
>
>
>
>Jonathan,
>More information on Latch statistics :
>SQL> select child#, gets, misses, sleeps
> 2 from v$latch_children
> 3 where name = 'library cache'
> 4 order by latch#, child#
> 5 /
>
> CHILD# GETS MISSES SLEEPS
>--------------- --------------- --------------- ---------------
> 1 423,328,364 16,726,978 41,653,237
> 2 277,975,674 8,689,048 20,557,142
> 3 363,865,543 18,702,438 45,141,452
> 4 337,035,589 17,647,934 43,325,116
> 5 286,648,335 7,916,652 19,205,209
>
>SQL> @Latch_Where_Now
>SQL>
>---------------------------------------------------------------
>------------
>----
>SQL> --
>SQL> -- Script: latch_where_now.sql
>SQL> -- Purpose: shows a snapshot of latch sleeps by
>code locations
>SQL> -- For: 8.0 and higher
>SQL> --
>SQL> -- Copyright: (c) Ixora Pty Ltd
>SQL> -- Author: Steve Adams
>SQL> --
>SQL>
>---------------------------------------------------------------
>------------
>----
>SQL>
>SQL> spool Latch_Where_Now
>SQL> set recsep off
>SQL> column name format a30 heading "LATCH TYPE"
>SQL> column location format a40 heading "CODE LOCATION and [LABEL]"
>SQL> column sleeps format 999999 heading "SLEEPS"
>SQL>
>SQL> select /*+ ordered use_merge(b) */
> 2 b.name,
> 3 b.location,
> 4 b.sleeps - a.sleeps sleeps
> 5 from
> 6 (
> 7 select /*+ no_merge */
> 8 wsc.ksllasnam name,
> 9 rpad(lw.ksllwnam, 40) ||
> 10 decode(lw.ksllwlbl, null, null, '[' || lw.ksllwlbl ||
>']') location,
> 11 wsc.kslsleep sleeps
> 12 from
> 13 sys.x$kslwsc wsc,
> 14 sys.x$ksllw lw
> 15 where
> 16 wsc.inst_id = userenv('Instance') and
> 17 lw.inst_id = userenv('Instance') and
> 18 lw.indx = wsc.indx
> 19 ) a,
> 20 (
> 21 select /*+ no_merge */
> 22 wsc.ksllasnam name,
> 23 rpad(lw.ksllwnam, 40) ||
> 24 decode(lw.ksllwlbl, null, null, '[' || lw.ksllwlbl ||
>']') location,
> 25 wsc.kslsleep sleeps
> 26 from
> 27 ( select min(indx) zero from sys.x$ksmmem where rownum <
>1000000 ) de
>lay,
> 28 sys.x$kslwsc wsc,
> 29 sys.x$ksllw lw
> 30 where
> 31 wsc.inst_id = userenv('Instance') and
> 32 lw.inst_id = userenv('Instance') and
> 33 wsc.kslsleep > delay.zero and
> 34 lw.indx = wsc.indx
> 35 ) b
> 36 where
> 37 b.name = a.name and
> 38 b.location = a.location and
> 39 b.sleeps > a.sleeps
> 40 order by
> 41 3 desc
> 42 /
>
>LATCH TYPE CODE LOCATION and [LABEL]
> SLEEPS
>------------------------------
>---------------------------------------- -------
>library cache kgllkdl: child: cleanup
> 325
> [latch]
>library cache kglpnal: child: before
>processing 144
> [latch]
>library cache kgllkdl: child: free pin
> 139
> [latch]
>library cache kgldti: 2child
> 76
>library cache kglpin
> 67
>shared pool kghfrunp: clatch: nowait
> 22
>library cache kglic
> 19
> [child]
>
>LATCH TYPE CODE LOCATION and [LABEL]
> SLEEPS
>------------------------------
>---------------------------------------- -------
>library cache kglhdgn: child:
> 8
> [latch]
>shared pool kghfrunp: alloc: clatch nowait
> 4
>library cache kglpnal: child: alloc space
> 3
> [latch]
>cache buffers chains kcbgtcr: kslbegin
> 2
> [buffer DBA]
>library cache kglpnc: child
> 1
> [child]
>library cache kglupc: child
> 1
> [child]
>
>13 rows selected.
>
>SQL>
>SQL> clear columns
>SQL> @Latch_Where_Now
>SQL>
>---------------------------------------------------------------
>------------
>----
>SQL> --
>SQL> -- Script: latch_where_now.sql
>SQL> -- Purpose: shows a snapshot of latch sleeps by
>code locations
>SQL> -- For: 8.0 and higher
>SQL> --
>SQL> -- Copyright: (c) Ixora Pty Ltd
>SQL> -- Author: Steve Adams
>SQL> --
>SQL>
>---------------------------------------------------------------
>------------
>----
>SQL>
>SQL> spool Latch_Where_Now
>SQL> set recsep off
>SQL> column name format a30 heading "LATCH TYPE"
>SQL> column location format a40 heading "CODE LOCATION and [LABEL]"
>SQL> column sleeps format 999999 heading "SLEEPS"
>SQL>
>SQL> select /*+ ordered use_merge(b) */
> 2 b.name,
> 3 b.location,
> 4 b.sleeps - a.sleeps sleeps
> 5 from
> 6 (
> 7 select /*+ no_merge */
> 8 wsc.ksllasnam name,
> 9 rpad(lw.ksllwnam, 40) ||
> 10 decode(lw.ksllwlbl, null, null, '[' || lw.ksllwlbl ||
>']') location,
> 11 wsc.kslsleep sleeps
> 12 from
> 13 sys.x$kslwsc wsc,
> 14 sys.x$ksllw lw
> 15 where
> 16 wsc.inst_id = userenv('Instance') and
> 17 lw.inst_id = userenv('Instance') and
> 18 lw.indx = wsc.indx
> 19 ) a,
> 20 (
> 21 select /*+ no_merge */
> 22 wsc.ksllasnam name,
> 23 rpad(lw.ksllwnam, 40) ||
> 24 decode(lw.ksllwlbl, null, null, '[' || lw.ksllwlbl ||
>']') location,
> 25 wsc.kslsleep sleeps
> 26 from
> 27 ( select min(indx) zero from sys.x$ksmmem where rownum <
>1000000 ) de
>lay,
> 28 sys.x$kslwsc wsc,
> 29 sys.x$ksllw lw
> 30 where
> 31 wsc.inst_id = userenv('Instance') and
> 32 lw.inst_id = userenv('Instance') and
> 33 wsc.kslsleep > delay.zero and
> 34 lw.indx = wsc.indx
> 35 ) b
> 36 where
> 37 b.name = a.name and
> 38 b.location = a.location and
> 39 b.sleeps > a.sleeps
> 40 order by
> 41 3 desc
> 42 /
>
>LATCH TYPE CODE LOCATION and [LABEL]
> SLEEPS
>------------------------------
>---------------------------------------- -------
>library cache kgllkdl: child: cleanup
> 151
> [latch]
>library cache kglpin
> 134
>library cache kgllkdl: child: free pin
> 118
> [latch]
>library cache kglpnal: child: before
>processing 57
> [latch]
>cache buffers chains kcbgtcr: kslbegin
> 27
> [buffer DBA]
>multiblock read objects kcbzib: MBRGET
> 4
>cache buffers chains kcbgcur: kslbegin
> 1
>
>LATCH TYPE CODE LOCATION and [LABEL]
> SLEEPS
>------------------------------
>---------------------------------------- -------
> [buffer DBA]
>library cache kglhdgn: child:
> 1
> [latch]
>
>8 rows selected.
>
>SQL>
>SQL> clear columns
>SQL>
>
>
>>X-Original-To: oracle-l_at_freelists.org
>>Delivered-To: oracle-l_at_freelists.org
>>X-Sender: hkchital_at_pop.singnet.com.sg
>>X-Mailer: QUALCOMM Windows Eudora Version 5.1.1
>>Date: Thu, 12 Feb 2004 22:49:13 +0800
>>To: oracle-l_at_freelists.org
>>From: Hemant K Chitale <hkchital_at_singnet.com.sg>
>>Subject: Re: Library Cache Latch statistics from StatsPack -- more
>> statistics
>>X-archive-position: 505
>>X-ecartis-version: Ecartis v1.0.0
>>Sender: oracle-l-bounce_at_freelists.org
>>X-original-sender: hkchital_at_singnet.com.sg
>>Reply-To: oracle-l_at_freelists.org
>>
>>
>>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_at_singnet.com.sg>
>>To: <oracle-l_at_freelists.org>
>>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_at_freelists.org
>>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_at_freelists.org
>>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
>>-----------------------------------------------------------------
>
>Hemant K Chitale
>Oracle 9i Database Administrator Certified Professional
>http://hkchital.tripod.com {last updated 24-Jan-04}
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>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
>-----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Feb 13 2004 - 13:25:08 CST
 |
 |