| 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: 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 9162.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,64919.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 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 60
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 7117.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,6740.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 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 319
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]
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}.
-- 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]Received on Thu Feb 12 2004 - 08:49:13 CST
-----------------------------------------------------------------
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
-----------------------------------------------------------------
 |
 |