From oracle-l-bounce@freelists.org Fri Oct 14 04:24:32 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9E9OVn9029456 for ; Fri, 14 Oct 2005 04:24:31 -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 j9E9OGvX029407 for ; Fri, 14 Oct 2005 04:24:17 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 567661FEF7E; Fri, 14 Oct 2005 04:24:08 -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 12294-05; Fri, 14 Oct 2005 04:24:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CAAAD1FEB1C; Fri, 14 Oct 2005 04:24:07 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=BcucWDVb91jaxiEYqzDTfs7HartmLD31SPAAyQSRqPFjOB4A+gFBZ+PVB99OtCmD4WbJxOvFlsCjJHtihwTgOw+dvSEtdLAM1s2QF9HQOZ4WJXc+lqcyIUJM+Af+Myd+nLinLrbbKqArnkwzy1l0XxK816hO4X+Wrhmw1VomeyI= Message-ID: <962cf44b0510140222r2c7f3a55n61a626471b27825d@mail.gmail.com> Date: Fri, 14 Oct 2005 17:22:12 +0800 From: zhu chao To: "oracle-l@timothyhopkins.net" Subject: Re: Keep CBO plan stable(plan stability) Cc: oracle-l@freelists.org In-Reply-To: <19730.192.165.213.18.1129217099.squirrel@192.165.213.18> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Content-Disposition: inline References: <962cf44b0510130608j6da1b84fp604590805b82f6be@mail.gmail.com> <3831.193.32.3.82.1129211714.squirrel@webmail.tiscali-business.nl> <962cf44b0510130734r7a79b3f2jec93976c34e3426d@mail.gmail.com> <22179.193.32.3.82.1129214728.squirrel@webmail.tiscali-business.nl> <962cf44b0510130801m4b97ba8cn173dc6d68e598e1a@mail.gmail.com> <19730.192.165.213.18.1129217099.squirrel@192.165.213.18> X-archive-position: 26965 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-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.5 required=5.0 tests=AWL,BAYES_00, TO_ADDRESS_EQ_REAL autolearn=ham version=2.63 Hi, Tim, The segment header dump is like: (for the table with wrong plan) *** 2005-10-14 02:16:50.796 *** SESSION ID:(4612.10519) 2005-10-14 02:16:50.794 Start dump data blocks tsn: 12 file#: 30 minblk 38409 maxblk 38409 buffer tsn: 12 rdba: 0x07809609 (30/38409) scn: 0x0315.8fb11a2a seq: 0x01 flg: 0x00 tail: 0x1a2a1001 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 10 #blocks: 127999 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x06c06003 ext#: 5 blk#: 11770 ext size: 12800 #blocks in seg. hdr's freelists: 66 #blocks below: 75769 mapblk 0x00000000 offset: 5 Unlocked Map Header:: next 0x00000000 #extents: 10 obj#: 6749 flag: 0x40000000 Extent Map ----------------------------------------------------------------- As for the plan stability, while running CBO, ifI do have statistics, Is there any other case, that CBO is still able to change the plan? Can you give me a test case? Thanks very much. On 10/13/05, oracle-l@timothyhopkins.net wrote: > Hi Zhu, > > If you have the time, could we please just check everything is as expected > with the segment header for that table: > > Grab the file and block id from the following query: > > SELECT header_file, > header_block > FROM > dba_segments > WHERE segment_name = 'USER_INFO'; > > And substitute the returned values into the following query: > > ALTER SYSTEM DUMP DATAFILE &header_file BLOCK &header_block; > > This should produce a file in your user dump directory, which contains a > line like the following: > > #blocks below: 19 > > It's this value the CBO uses for the NBLK stat. We just need to check that > it's correctly recorded as a large number for your table. > > In relation to your other question; no you can't guarantee plan stability > without stored outlines. As discussed, the segment header will change over > time and if someone collects system statistics these could also alter your > execution plan. > > Cheers, > Tim > > -- Regards Zhu Chao www.cnoug.org -- http://www.freelists.org/webpage/oracle-l