From oracle-l-bounce@freelists.org Mon Mar 1 13:08:51 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i21J8oT21214 for ; Mon, 1 Mar 2004 13:08:50 -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 i21J8oo21209 for ; Mon, 1 Mar 2004 13:08:50 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5BED3394A5C; Mon, 1 Mar 2004 14:09:33 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 01 Mar 2004 14:08:23 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from einsteinium.btinternet.com (einsteinium.btinternet.com [194.73.73.147]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D0B16394FE8 for ; Mon, 1 Mar 2004 14:08:18 -0500 (EST) Received: from [81.133.23.218] (helo=Primary) by einsteinium.btinternet.com with smtp (Exim 3.22 #25) id 1AxspU-0005u9-00 for oracle-l@freelists.org; Mon, 01 Mar 2004 19:11:52 +0000 Message-ID: <006801c3ffc1$0f739570$6702a8c0@Primary> From: "Jonathan Lewis" To: References: Subject: Re: query slow in 9i, but not slow in 8i Date: Mon, 1 Mar 2004 19:11:53 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-archive-position: 147 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l As has already been pointed out, the estimated volume of data to sort is different. > > 1 0 SORT (UNIQUE) (Cost=118228 Card=8046044 Bytes=225289232) > > 2 1 MERGE JOIN (Cost=1102 Card=8046044 Bytes=225289232) > > 1 0 SORT (UNIQUE) (Cost=77324 Card=6490972 Bytes=188238188) > > 2 1 HASH JOIN (Cost=41347 Card=6490972 Bytes=188238188) 9.2 has estimated 225MB as its input and output, 8.1 has estimated 188MB as its input and output. Given the change in volume you should at least expect the incremental cost to go up by about 50%. In fact it has gone up by 117,000 in 9.2 and 33,000 in 8.1 a factor of 4: which may be explained by the 9.2 parameter: _NEW_SORT_COST_ESTIMATE = TRUE Are you also running cpu_costing ? This could add a significant amount to the estimated cost of a large sort. You could run a 10053 trace on the two statements, and look for the bit that is the sort (unique). It will look something like: SORT resource Sort statistics Sort width: 5 Area size: 131072 Max Area size: 1257472 Degree: 1 Blocks to Sort: 30 Row size: 49 Rows: 4882 Initial runs: 2 Merge passes: 1 IO Cost / pass: 16 Total IO sort cost: 46 Total CPU sort cost: 6933962 Total Temp space used: 484000 Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland June 2004 UK - Optimising Oracle Seminar ----- Original Message ----- From: "Guang Mei" To: Sent: Monday, March 01, 2004 5:22 PM Subject: RE: query slow in 9i, but not slow in 8i > Yes, The statistics is update to date on all the tables (in both 8i and 9i). > I ran > > execute DBMS_STATS.GATHER_TABLE_STATS(, ,cascade => > TRUE); > > on all the tables in the schema. > > > Guang > > -----Original Message----- > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org]On Behalf Of Juan Cachito Reyes > Pacheco > Sent: Monday, March 01, 2004 12:11 PM > To: oracle-l@freelists.org > Subject: Re: query slow in 9i, but not slow in 8i > > > If you have statictis up to date with dbms package. > > We had a problem with the new views cbo feature > try to use the /*+ NO_MERGE */ hint, > if this is the same problem we had , this will fix that. > Otherwise no idea. > > ----- Original Message ----- > From: "Guang Mei" > To: "Oracle-L-freelists" > Sent: Monday, March 01, 2004 1:09 PM > Subject: query slow in 9i, but not slow in 8i > > > > Hi: > > > > I have a query which gave two very different explain plan on 8173 and > 9204. > > The two instances (on two separate Sun Solaris boxes) both have the same > > db_file_multiblock_read_count (8), block_size (8k), sort_area_size > > (90000000) and sort_area_retained_size (9000000). I narrowed down the part > > which causeed this: > > ---------------------------------------------------------------- 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 -----------------------------------------------------------------