From oracle-l-bounce@freelists.org Thu Oct 13 08:10:58 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9DDAwKY002053 for ; Thu, 13 Oct 2005 08:10:58 -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 j9DDAsvX002033 for ; Thu, 13 Oct 2005 08:10:54 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 55AF31FD76D; Thu, 13 Oct 2005 08:10:47 -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 28982-10; Thu, 13 Oct 2005 08:10:47 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CBB771FD780; Thu, 13 Oct 2005 08:10:46 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=kfIR74MVaU6GjUnqQ3lzTlRR6/9Ke7K0xB2+bfpC/nZuZHYqIZgBTIkjnlKoQ0Fd/Kt+syqiU0AGu6PQC1GYi0fGICO4RcnmjdGYEQV+AaYi9zHg00DVv33htqL+zUZX7uYOj/toZ4mGPMpNeDEtFF954GX6azqyUzvuUL+0u3E= Message-ID: <962cf44b0510130608j6da1b84fp604590805b82f6be@mail.gmail.com> Date: Thu, 13 Oct 2005 21:08:28 +0800 From: zhu chao To: oracle list Subject: Keep CBO plan stable(plan stability) 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 X-archive-position: 26905 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, 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