From oracle-l-bounce@freelists.org Tue Mar 2 07:56:31 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i22DuVh29310 for ; Tue, 2 Mar 2004 07:56:31 -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 i22DuSo29294 for ; Tue, 2 Mar 2004 07:56:28 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C4EF4394E8E; Tue, 2 Mar 2004 08:56:53 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Mar 2004 08:55:47 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mlnyb904er.ml.com (mlnyb904er.ml.com [199.43.54.102]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8F118394D41 for ; Tue, 2 Mar 2004 08:55:42 -0500 (EST) Received: from ewstwt01.exchange.ml.com (unknown [146.125.249.151]) by mlnyb904er.ml.com (Postfix) with SMTP id EEDC12D03 for ; Tue, 2 Mar 2004 08:59:23 -0500 (EST) Received: from 146.125.97.83 by ewstwt01.exchange.ml.com with ESMTP ( Tumbleweed MMS SMTP Relay (MMS v4.7);); Tue, 02 Mar 2004 08:59:22 -0500 X-Server-Uuid: 3789b954-9c4e-11d3-af68-0008c73b0911 Received: from mlnyb706mb.amrs.win.ml.com ([146.125.97.45]) by mlnyb803bh.amrs.win.ml.com with Microsoft SMTPSVC(5.0.2195.5329); Tue, 2 Mar 2004 08:59:22 -0500 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 content-class: urn:content-classes:message MIME-Version: 1.0 Subject: RE: Latch Contention Date: Tue, 2 Mar 2004 08:59:22 -0500 Message-ID: <4ECF25179468EA4BB1BE66E8636BB2CF049B3B6F@mlnyb706mb-m.msg.ml.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Latch Contention thread-index: AcP/44vplEr3hHRIStycQJLr4sSmFAAesYgw From: "Potluri, Venu (CT Appl Suppt)" Importance: normal Priority: normal To: oracle-l@freelists.org X-OriginalArrivalTime: 02 Mar 2004 13:59:22.0906 (UTC) FILETIME=[90C657A0:01C4005E] X-WSS-ID: 6C5A4CB0804260-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis X-archive-position: 257 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: venu_potluri@ml.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l How do I find the latch holder? I have the same latch free wait problem today. Looked in v$latchholder but there was nothing in there. So the question is when I see major latch contention (library cache, cache buffer chains and shared pool) how to find sessions holding the latches. What do I do to attack this problem? Thanks. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of John Kanagaraj Sent: Monday, March 01, 2004 6:20 PM To: 'oracle-l@freelists.org' Subject: RE: Latch Contention Venu, Were you able to determine which and how many Concurrent requests were running at that time, and what changes if any were done for these programs? (the changes may be have been in the past, but manifest themseleves now as some programs run only during the monthend). I had a very interesting and similar situation where a scheduled cron job came in occassionally to generate 'GRANT SELECT' on all objects in all schemas to a read-only account and then run the resulting SQLs several times a day. This generates thousands of tiny (in comparison) SQLs, each of which had to be parsed, and modify objects and thus invalidating lib cache/dd cache and cluttered up shared pool. Shared pool and Lib cache latches were through the roof. I used V$SESSION_WAIT to determine which sessions were waiting and thus determined what was happening... The fix was to stop running this during peak hours and change it to GRANT SELECT on only new objects... John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Potluri, Venu (CT Appl Suppt) Sent: Monday, March 01, 2004 2:55 PM To: oracle-l@freelists.org Subject: Latch Contention Today we had problems with latch contention in our production Oracle Apps database. I saw lots of latch free waits, of the cache buffer chains and library cache variety. I looked for the most resource intensive sessions. But nothing stood out. Finally I flushed the shared pool (Cary Millsap is going to cringe at this time........). That cleared all the latch free waits and users reported better performance. Didn't want to flush the shared pool but had to do it due to month end close processing that was getting severly bogged down. I still haven't found the root cause. My question is where to look for the root cause next time. Here are some stats I gathered while the problem occurred. NAME GETS Miss % Spin % IGETS IMISSES ------------------------- ------------ ------- ------- ------------ --------- intra txn parallel recove 0 .00 .00 0 0 ry parallel txn reco latch 0 .00 .00 0 0 address list 1 .00 .00 0 0 mostly latch-free SCN 2 .00 .00 0 0 NLS data objects 2 .00 .00 0 0 Direct I/O Adaptor 2 .00 .00 0 0 message pool operations p 3 .00 .00 0 0 arent latch begin backup scn array 37 .00 .00 0 0 X$KSFQP 39 .00 .00 0 0 archive process latch 167 .00 .00 0 0 file number translation t 285 .00 .00 0 0 able ktm global data 300 .00 .00 0 0 archive control 927 .00 .00 0 0 longop free list 1,160 .00 .00 0 0 NAME GETS Miss % Spin % IGETS IMISSES ------------------------- ------------ ------- ------- ------------ --------- device information 1,710 .00 .00 0 0 kwqit: protect wakeup tim 2,811 .00 .00 0 0 dictionary lookup 3,250 .00 .00 0 0 global tx free list 3,763 .00 .00 0 0 sort extent pool 4,086 .00 .00 0 0 loader state object freel 6,103 .00 .00 0 0 ist ncodef allocation latch 9,642 .00 .00 0 0 job_queue_processes param 9,642 .00 .00 0 0 eter latch hash table modification l 0 .00 .00 22,991 0 atch session switching 24,272 .00 .00 0 0 i/o slave adaptor 0 .00 .00 36,912 0 vecio buf des 0 .00 .00 36,912 0 library cache load lock 38,888 .04 100.00 0 0 NAME GETS Miss % Spin % IGETS IMISSES ------------------------- ------------ ------- ------- ------------ --------- event group latch 40,868 .00 .00 0 0 transaction branch alloca 53,467 .00 .00 0 0 tion process group creation 80,972 .00 .00 0 0 channel handle pool latch 80,981 .00 .00 0 0 process allocation 40,868 .04 .00 40,863 .01223601 channel operations parent 121,890 .00 .00 0 0 latch global tx hash mapping 158,890 .00 .00 0 0 user lock 180,484 .01 68.42 0 0 Token Manager 181,487 .00 100.00 36,912 0 sequence cache 341,190 .01 89.66 0 0 active checkpoint queue l 695,343 .00 .00 0 0 atch list of block allocation 1,130,452 .00 100.00 0 0 dml lock allocation 1,322,672 .00 100.00 0 0 redo writing 1,598,340 .08 99.10 0 0 global transaction 2,039,041 .00 .00 0 0 NAME GETS Miss % Spin % IGETS IMISSES ------------------------- ------------ ------- ------- ------------ --------- transaction allocation 2,532,521 .00 100.00 0 0 undo global data 2,744,812 .00 100.00 0 0 enqueue hash chains 3,303,961 .00 94.44 0 0 enqueues 3,814,510 .01 99.50 0 0 session allocation 4,803,687 .04 92.49 0 0 messages 5,014,273 .06 99.27 0 0 multiblock read objects 10,086,892 .02 99.42 2 0 session idle bit 13,586,255 .00 100.00 0 0 row cache objects 16,937,164 .08 99.75 89 1.1235955 checkpoint queue latch 23,051,771 .00 67.45 0 0 redo copy 13,680 .00 .00 36,266,260 .00756626 redo allocation 36,522,674 .01 99.87 0 0 cache buffer handles 82,954,716 .32 99.93 0 0 cache buffers lru chain 36,022,870 .14 96.82 79,027,986 .16287268 shared pool 379,947,867 .16 98.51 0 0 latch wait list 317,618,315 5.94 99.76 297,982,053 5.0251922 library cache ############ 293.06 94.87 819,140 3.7733477 cache buffers chains ############ .16 99.78 107,818,804 .02991315 spin sl01 sl02 sl03 sl04 sl05 NAME GETS Miss % sl06 sl07 sl08 sl09 sl10 sl11 ------------------ ------------ ------ ------ ----- ----- ----- ----- ----- cache buffers chai ############ 0.2 99.8 0.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 library cache ############ ##### 94.9 4.0 0.7 0.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 shared pool 379,948,308 0.2 98.5 0.3 0.7 0.4 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 latch wait list 317,630,086 5.9 99.8 0.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 cache buffer handl 82,954,716 0.3 99.9 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 redo allocation 36,522,674 0.0 99.9 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 cache buffers lru 36,022,877 0.1 96.8 3.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 checkpoint queue l 23,051,900 0.0 67.4 32.6 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 row cache objects 16,937,179 0.1 99.8 0.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 session idle bit 13,586,307 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 spin sl01 sl02 sl03 sl04 sl05 NAME GETS Miss % sl06 sl07 sl08 sl09 sl10 sl11 ------------------ ------------ ------ ------ ----- ----- ----- ----- ----- multiblock read ob 10,086,892 0.0 99.4 0.6 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 messages 5,014,290 0.1 99.3 0.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 session allocation 4,803,691 0.0 92.5 7.3 0.1 0.1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 enqueues 3,814,532 0.0 99.5 0.5 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 enqueue hash chain 3,303,974 0.0 94.4 5.6 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 undo global data 2,744,812 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 transaction alloca 2,532,523 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 redo writing 1,598,345 0.1 99.1 0.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 dml lock allocatio 1,322,672 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 list of block allo 1,130,452 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 spin sl01 sl02 sl03 sl04 sl05 NAME GETS Miss % sl06 sl07 sl08 sl09 sl10 sl11 ------------------ ------------ ------ ------ ----- ----- ----- ----- ----- sequence cache 341,190 0.0 89.7 10.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 Token Manager 181,487 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 user lock 180,484 0.0 68.4 26.3 5.3 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 process allocation 40,868 0.0 0.0 93.3 6.7 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 library cache load 38,888 0.0 100.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 CHILD# ROUND(SLEEPS/GETS*100,2) RATIO --------- ------------------------ --------- 12 0 100 11 0 100 10 0 100 9 0 100 8 0 100 7 0 100 6 0 100 5 0 100 4 0 100 3 0 100 2 0 100 1 0 100 If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. http://www.ml.com/email_terms/ ======================================================================== ==== == If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. ======================================================================== ==== == ---------------------------------------------------------------- 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 ----------------------------------------------------------------- -------------------------------------------------------- If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. http://www.ml.com/email_terms/ -------------------------------------------------------- ============================================================================== If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail. ============================================================================== ---------------------------------------------------------------- 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 -----------------------------------------------------------------