Re: Library Cache Latch statistics from StatsPack -- more statistics

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 12 Feb 2004 22:49:13 +0800

 
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
-----------------------------------------------------------------

Other related posts: