More Latch Stats : was re Fwd: Re: Library Cache Latch statistics
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Feb 2004 23:45:21 +0800
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@xxxxxxxxxxxxx
>Delivered-To: oracle-l@xxxxxxxxxxxxx
>X-Sender: hkchital@xxxxxxxxxxxxxxxxxx
>X-Mailer: QUALCOMM Windows Eudora Version 5.1.1
>Date: Thu, 12 Feb 2004 22:49:13 +0800
>To: oracle-l@xxxxxxxxxxxxx
>From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
>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@xxxxxxxxxxxxx
>X-original-sender: hkchital@xxxxxxxxxxxxxx
>Reply-To: oracle-l@xxxxxxxxxxxxx
>
>
>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@xxxxxxxxxxxxxx>
>To: <oracle-l@xxxxxxxxxxxxx>
>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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/[6]
>FAQ is at //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 //www.freelists.org/archives/oracle-l/
> 7 //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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
