Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1H9AML08895 for ; Tue, 17 Feb 2004 03:10:22 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i1H9A9o08861 for ; Tue, 17 Feb 2004 03:10:12 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0648B394CC0; Tue, 17 Feb 2004 04:09:57 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 17 Feb 2004 04:08:44 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from smtp.uninet.ee (smtp.uninet.ee [194.204.0.4]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E2383394BBB for ; Tue, 17 Feb 2004 04:06:47 -0500 (EST) Received: from poder (wifi-customer.uninet.ee [194.204.18.251]) by smtp.uninet.ee (Postfix) with SMTP id 65212616C6 for ; Tue, 17 Feb 2004 11:08:15 +0200 (EET) Message-ID: <03b101c3f5aa$e46b2b60$0c00640a@poder> From: =?iso-8859-1?Q?Tanel_P=F5der?= To: References: <5.1.1.6.0.20040211234144.02f72e80@pop.singnet.com.sg> <5.1.1.6.0.20040212224533.00aac7a8@pop.singnet.com.sg> Subject: Re: Library Cache Latch statistics from StatsPack -- more statistics Date: Tue, 17 Feb 2004 11:08:15 +0200 (EET) MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-archive-position: 876 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tanel.poder.003@mail.ee Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Orig-Date: Fri, 13 Feb 2004 15:42:41 -0700 One way for reducing latching & contention in heavy soft-parse scenarios would be setting cursor_space_for_time to true. This requires somewhat more memory in your UGAs, but can "make things go faster". You should check others recommendations first, but you could measure your statistics for an operation in single session with and without this parameter set and continue from there. Tanel. ----- Original Message ----- From: "Hemant K Chitale" To: Sent: Thursday, February 12, 2004 7:49 AM Subject: 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 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" > To: > 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@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@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@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 -----------------------------------------------------------------