| 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
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}.
The statistics show that hard parsing isn't high but executions are high. I hope to gather more information in the next few days.
Hemant
At 10:29 AM 11-02-04 -0500, you wrote:
>Hmm....library cache, but no shared pool latch contention *usually* =
>indicates excessive soft parsing. Have you set session_cached_cursors? =
>If not, why not? Try setting it to 50 or 100, for starters. =20
>
>Better yet, redesign the app to parse only once, and execute many times. =
> Yes, that's probably more difficult than it sounds.
>
>Also, as you're on 8.1.7.2, this could be a bug, causing the library =
>cache contention. You could try upgrading to at least 8.1.7.4, if not =
>9i.
>
>
>Seeing just those statistics, in isolation, and not knowing anything =
>about your system, I'm afraid that's the best I can do. =20
>
>Hope it helps,
>
>-Mark
>
>-----Original Message-----
>From: Hemant K Chitale [mailto:hkchital_at_singnet.com.sg]
>Sent: Wednesday, February 11, 2004 3:03 AM
>To: oracle-l_at_freelists.org
>Cc: hkchital_at_singnet.com.sg
>Subject: Library Cache Latch statistics from StatsPack
>
>
>
>I need help understanding these statistics from
>StatsPack [8.1.7.2 on Tru64]
>
>
>Top 5 Wait Events
>~~~~~~~~~~~~~~~~~ Wait % =
>Total
>Event Waits Time (cs) =
>Wt Time
>-------------------------------------------- ------------ ------------ =
>-------
>latch free 5,282,074 1,015,994 =
>40.36
>
>
> Avg
> Total Wait wait =
>Waits
>Event Waits Timeouts Time (cs) (ms) =
> /txn
>---------------------------- ------------ ---------- ----------- ------ =
>------
>latch free 5,282,074 1,921,007 1,015,994 2 =
> 57.8
>
>
> Pct Avg =
> Pct
> Get Get Slps NoWait =
>NoWait
>Latch Name Requests Miss /Miss Requests =
> Miss
>----------------------------- -------------- ------ ------ ------------ =
>------
>library cache 57,179,454 4.1 2.2 50,432 =
> 20.1
>library cache load lock 15,979 0.0 0.5 0
>
>
> Get =
>Spin &
>Latch Name Requests Misses Sleeps Sleeps =
>1->4
>-------------------------- -------------- ----------- ----------- =
>------------
>library cache 57,179,454 2,326,630 5,183,363 =
>522188/13792
> =
>7/632462/103
> 4053/0
>
>
> NoWait =
>Waiter
>Latch Name Where Misses Sleeps =
>Sleeps
>------------------------ -------------------------- ------- ---------- =
>--------
>library cache kgllkdl: child: cleanup 0 2,362,451 =
>92,370
>library cache kgllkdl: child: free pin 0 1,814,315 =
>288,230
>library cache kglpnal: child: before pro 0 669,779 =
>623,539
>library cache kglpin 0 173,050 =
>575,120
>library cache kglhdgn: child: 0 44,924 =
>410,493
>library cache kgldti: 2child 0 29,395 =
>69,509
>library cache kglget: child: KGLDSBYD 0 21,580 =
>820,546
>library cache kglget: child: KGLDSBRD 0 14,122 =
>55,366
>library cache kglpnal: child: alloc spac 0 13,106 =
>299,928
>library cache kglpnc: child 0 9,557 =
>753,591
>library cache kglupc: child 0 8,283 =
>699,105
>library cache kglhdgc: child: 0 2,154 =
>22,377
>library cache kglpnp: child 0 1,165 =
>327,989
>library cache kglrtl 0 947 =
> 2,657
>library cache kglobpn: child: 0 589 =
>53,885
>library cache kgldtld: 2child 0 566 =
> 2,305
>library cache kglidp: parent 0 517 =
> 159
>library cache kglic 0 451 =
>17,901
>library cache kglpndl: parent: purge 0 179 =
> 321
>library cache kglpnal: parent held, no p 0 150 =
> 4
>library cache kgldrp: parent 0 130 =
> 137
>library cache kglpnal: child: check gran 0 17 =
> 1,310
>library cache kglati 0 8 =
> 62
>library cache kglpsl: child 0 7 =
> 425
>library cache kglnti 0 4 =
> 29
>library cache kgldnp: child 0 3 =
> 4,001
>library cache kgldte: child 0 0 3 =
>26,359
>library cache kglalt: parent 0 1 =
> 9
>library cache kglpin: child: KGLMX 0 1 =
> 10
>library cache kglobld: child: 0 1 =
> 5,634
>library cache load lock kglllal: llatch: in loop 0 1 =
> 1
>
>
>What should I be looking at ?
>
>
>Hemant K Chitale
>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
>-----------------------------------------------------------------
>----------------------------------------------------------------
>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}
-- 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 Wed Feb 11 2004 - 09:44:51 CST
 |
 |