From oracle-l-bounce@freelists.org Sat May 21 04:18:11 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4L9IBnj012834 for ; Sat, 21 May 2005 04:18:11 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4L9I94Z012830 for ; Sat, 21 May 2005 04:18:09 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1C9DA195CA1; Sat, 21 May 2005 03:15:17 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 01595-02; Sat, 21 May 2005 03:15:17 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8DE62195C16; Sat, 21 May 2005 03:15:16 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:references; b=OnH3E1aiyqhhG6OGJZqZ6LQ/BMMi7hTNvDHMWKQVdxSVfFMxVTEjSpnREvEzSZ/iBrsns+x/mg5EckwpCKIUx4j//uD0lAjNB8VA4RdeUo/r0C5uQdKKPrFZ1BICM1fBgtfXVcKXgtx+hD7/14Xgj74Kssp8la1F3yHvWAm2VN0= Message-ID: <962cf44b050521011350604f7e@mail.gmail.com> Date: Sat, 21 May 2005 16:13:28 +0800 From: zhu chao To: "Oracle-L (oracle-l@freelists.org)" Subject: Re: buffer busy wait for insert session In-Reply-To: <962cf44b0505210110542e09bd@mail.gmail.com> Mime-Version: 1.0 Content-type: text/plain References: <20050519192940.8201.qmail@web30906.mail.mud.yahoo.com> <962cf44b05051918553b5534b3@mail.gmail.com> <3b0f44a105052014005c603411@mail.gmail.com> <962cf44b05052021092e1257e4@mail.gmail.com> <3b0f44a105052021164e29aad7@mail.gmail.com> <962cf44b0505210110542e09bd@mail.gmail.com> Content-Transfer-Encoding: 8bit X-archive-position: 20073 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: zhuchao@gmail.com Precedence: normal Reply-To: zhuchao@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 You are right! Even with freelists=23 for the table and indexes, we still see the contention for the segment header: 1* select p1,p2,p3,count(*) from temp_bbw group by p1,p2,p3 having count(*)>10 order by count(*) 2 / P1 P2 P3 COUNT(*) ---------- ---------- ---------- ---------- 14 2 220 388 p1=14,p2=2 is the segment header of that table. We don't have freelist groups set, as changing this need to rebuild the table,which is impossible for us, the 1.5TB table. But we will make the change if we got the chance to rebuild this table, in our new headroom project for this database. Thanks! The total waits for the session is 31720, the number of waits for it to visit the segment header is 388, seems not a very big part of the total wait time? And convert to ASSM is also impossible as this again need to rebuild the whole tablespace:(. And we are still on 8.1.7. You talked about increate pctfree/reduce pctused to reduce the contention at the segment header, this can possible make less use of the freelist block(in the segment header block right?), but will make space wastage more, is it correct? The table is already too huge and beyond our control. I am not sure whether we should change it. SQL> select count(*) from temp_bbw; COUNT(*) ---------- 31720 And it is because of the concurrent read/write. Gopal , thanks for your support. Can you give some more detail about :"merge freelist and record freespace operations"? So for the other BBW on the data blocks, for the insert SQL session, with reason code 120/220, what could be the reason for the contention? Several sessoins trying to read the same free blocks from disk into memory and insert rows to the blocks? From v$session, there are 23 sessions running insert statement, and we have freelists = 23. Could it because oracle failed to distribute the free blocks evenly to the different sessions? and several sessions get the same block thus caused the BBW contention? Long email. But is interesting. THanks All. > On 5/21/05, K Gopalakrishnan wrote: > > > Zhu: > > > > The waits are for freelist space management (ktsmfl,ktsgrp) which is > > merge freelist and record freespace operations. Adding freelist groups > > (so that the freelist structure is moved to a totally new block, thus > > reducing contention for segment header blocks) will help. Can you > > confirm whether the blocks in question are header blocks or data > > blocks? > > > > Regards, > > Gopal > > > > > > On 5/21/05, zhu chao wrote: > > > Hi, Gopal/Alex, > > > We have table/indexes with freelists 23, which is pretty high. > > > > > > I did some analyze today, and found the contention is mainly from the > > table > > > itself, not the index. > > > > > > > > > > > > I grep the trace file gall1_ora_10686.trc and sqlldr the file into > > temp_bbw, > > > p1 stand for trace file with buffer busy wait p1 and p2 stand for p2 > > the > > > block_id and p3 stand for p3 the reason code. > > > > > > > > > > > > And I run the SQL to find out which blocks suffers most from the > > buffer busy > > > wait: > > > > > > 1 select > > > segment_name,segment_type,decode(p3,120,'READ',220,'WRITE') > > > reasoncode ,count(*) > > > 2 from temp_bbw a,dba_extents b > > > 3 where a.p1=b.file_id > > > 4 and p2 between block_id and block_id+blocks > > > 5* group by > > > segment_name,segment_type,decode(p3,120,'READ',220,'WRITE') > > > SQL> / > > > > > > SEGMENT_NAME SEGMENT_TYPE REASONCODE > > > COUNT(*) > > > ------------------------------ ------------------ > > > -------------------------------- ---------- > > > GALLERY TABLE > > > READ > > > 27537 > > > GALLERY TABLE > > > WRITE > > > 4183 > > > > > > All the buffer busy wait contention is from the table itself, not the > > index. > > > So reverse key index will not help us in this case, I think. Freelists > > =23 > > > is arealy pretty high. We don't see contention for the insert session. > > > > > > Hash partition , maybe will help. But we are using long raw, so it is > > rare > > > that one block will contains more than several rows. > > > z > > > Still have puzzle. Will continue research it. > > > > > > Thanks > > > > > > -- http://www.freelists.org/webpage/oracle-l