From oracle-l-bounce@freelists.org Thu Oct 13 09:36:53 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9DEar63020935 for ; Thu, 13 Oct 2005 09:36:53 -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 j9DEakvX020911 for ; Thu, 13 Oct 2005 09:36:47 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 90CA21FDA0A; Thu, 13 Oct 2005 09:36:36 -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 09476-07; Thu, 13 Oct 2005 09:36:36 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1934C1FD9DA; Thu, 13 Oct 2005 09:36:36 -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=DchmTA7AvvtI5yMAUl4WyvRg2jkgsVkAZi6C8QZ663UOXznTE1mvvPpVEE/TURsUcCbh/LtrQqtSCXDIDJCrmUg5au0s5V8yGGgg/l1p7lpigEGkqcdXGaJhikl4QnewexEm2HGJOx8lkWUMkvZvo0xHUoJPgea7072Fx/jGu5c= Message-ID: <962cf44b0510130734r7a79b3f2jec93976c34e3426d@mail.gmail.com> Date: Thu, 13 Oct 2005 22:34:26 +0800 From: zhu chao To: lex.de.haan@naturaljoin.nl Subject: Re: Keep CBO plan stable(plan stability) Cc: oracle list In-Reply-To: <3831.193.32.3.82.1129211714.squirrel@webmail.tiscali-business.nl> 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> X-archive-position: 26917 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.8 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 Hi, Lex, Real life application is sometimes more complicated, for some historical reasons. In this case, I did test with dynamic sampling, it used bitmap access for btree index plan, which seems very bad. I believe dynamic sampling is not enabled by default for not analyzed tables, unless we specify the dynamic_sampling hint in SQL, right? Thanks On 10/13/05, Lex de Haan wrote: > let me answer with a counter question: > why do you ask the CBO to optimize your SQL statements without statistics? > that's like forcing someone without legs to run the marathon ... > you might at least allow the CBO to perform dynamic sampling. > > to answer your question, in the absence of statistics, the CBO uses a mix > of hard-coded built-in values, information from the segment header, and > information from the row cache. > > cheers, > > Lex. > > > hi, all, > > Recently we have a SQL changed the plan, without any init > > parameter change, and table analyze. > > Oracle version is 9.2.0.5, and optimizer_mode=rule. Tables are not > > analyzed, but we have a no_expand hint, so actually SQL is using CBO. > > We have 3 database running the identical application, and 1 > > database's plan is good, but two database have their pan > > changed(previously it should be fine, as we see no timeout in > > application, and no heavy SQL in statspack). Plan seems changes as > > data volume changed. > > I tried to understand why the plan is different on two different > > database with exact setting (the data volume is a bit didfferent, say, > > one db the table is 5gb, and another db the table is 2gb). I tried to > > trace with 10053, and I found Oracle saw different "number of blocks" > > for the two tables in two database. Table is not analyzed. > > Table with correct plan: > > TOTAL :: (NOT ANALYZED) CDN: 2000 NBLKS: 2530 AVG_ROW_LEN: 100 > > table with wrong plan: > > TOTAL :: (NOT ANALYZED) CDN: 2000 NBLKS: 60 AVG_ROW_LEN: 100 > > > > Both data for NBLKS are wrong. > > > > So I have two questions: > > 1. How does CBO get this information? > > 2. When we do not analyze tables, when will CBO change the plan? > > > > Thansk > > > > > > -- > > Regards > > Zhu Chao > > www.cnoug.org > > -- > > http://www.freelists.org/webpage/oracle-l > > > > > -- Regards Zhu Chao www.cnoug.org -- http://www.freelists.org/webpage/oracle-l