From oracle-l-bounce@freelists.org Mon Mar 1 13:11:23 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i21JBNE21570 for ; Mon, 1 Mar 2004 13:11:23 -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 i21JBNo21565 for ; Mon, 1 Mar 2004 13:11:23 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 15DCD3950F0; Mon, 1 Mar 2004 14:11:56 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 01 Mar 2004 14:10:40 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from VIGO.trimac.com (mail.trimac.com [207.228.86.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id C1A3C394992 for ; Mon, 1 Mar 2004 14:10:37 -0500 (EST) Received: from gw-pdt.trimac.com(172.20.220.85) by VIGO.trimac.com via csmap id 20542; Mon, 01 Mar 2004 12:13:46 -0700 (MST) Received: from altair.centrexcc.com ([172.20.232.55]) by gw-pdt.trimac.com; Mon, 01 Mar 2004 11:14:01 -0800 Message-Id: <6.0.3.0.2.20040301120547.02b562d8@pop.centrexcc.com> X-Sender: centrex@hosting.telus.net@pop.centrexcc.com (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.0.3.0 Date: Mon, 01 Mar 2004 12:13:50 -0700 To: oracle-l@freelists.org From: Wolfgang Breitling Subject: RE: query slow in 9i, but not slow in 8i In-Reply-To: References: <6.0.3.0.2.20040301112121.02b574c8@pop.centrexcc.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-archive-position: 148 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l No, there are no histograms on any of the columns, just the min (LO) and max (HI) values for each column. However, in the Oracle 8i database, max(external_accession.SEQTABLEID) (=1770465) is equal to max(IDENTIFIER.SEQTABID) (=1770463) whereas in the Oracle 9i database it is not (1101043 < 1770463). That could explain the different cardinality estimates and subsequently the conclusion that a merge join would be cheaper than a hash join. I would import the 8i statistics for external_accession into the 9i database and see what that does to the plan and execution times. At 11:44 AM 3/1/2004, you wrote: >external_accession.SEQTABLEID does seem to have different histogram data >(1101043 vs 1770465). I am not familair with histograms, should I be doing >something like > >analyze table external_accession compute statistics for columns SEQTABLEID >size 20; > >in 9i? > >Guang > > >select lpad(TABLE_NAME,10), lpad(COLUMN_NAME, 10), >ENDPOINT_NUMBER, ENDPOINT_VALUE >from user_tab_histograms >where table_name= upper('identifier'); > > >-- from 9i: > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >IDENTIFIER ID 0 -399575 >IDENTIFIER GENEID 0 1 >IDENTIFIER SEQTABID 0 62 >IDENTIFIER TYPE 0 3.3750E+35 >IDENTIFIER SPECIESID 0 1 >IDENTIFIER IDSTR 0 2.3465E+35 >IDENTIFIER CLASSIFICA 0 1.6681E+35 >IDENTIFIER ID 1 1770463 >IDENTIFIER GENEID 1 10333859 >IDENTIFIER SEQTABID 1 1770463 >IDENTIFIER TYPE 1 4.1538E+35 > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >IDENTIFIER SPECIESID 1 102 >IDENTIFIER IDSTR 1 2.9712E+35 >IDENTIFIER CLASSIFICA 1 1.6681E+35 > >14 rows selected. > >-- from 8i: > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >IDENTIFIER ID 0 -399733 >IDENTIFIER GENEID 0 1 >IDENTIFIER SEQTABID 0 62 >IDENTIFIER TYPE 0 3.3750E+35 >IDENTIFIER SPECIESID 0 1 >IDENTIFIER IDSTR 0 2.3465E+35 >IDENTIFIER CLASSIFICA 0 1.6681E+35 >IDENTIFIER ID 1 1770463 >IDENTIFIER GENEID 1 10333859 >IDENTIFIER SEQTABID 1 1770463 >IDENTIFIER TYPE 1 4.1538E+35 > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >IDENTIFIER SPECIESID 1 102 >IDENTIFIER IDSTR 1 2.9712E+35 >IDENTIFIER CLASSIFICA 1 1.6681E+35 > >14 rows selected. > > >select lpad(TABLE_NAME,10), lpad(COLUMN_NAME, 10), >ENDPOINT_NUMBER, ENDPOINT_VALUE >from user_tab_histograms >where table_name= upper('external_accession'); > >-- from 9i: > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >EXTERNAL_A SEQTABLEID 0 298261 >EXTERNAL_A ACCESSION 0 3.3882E+35 >EXTERNAL_A ACCESSION2 0 2.5540E+35 >EXTERNAL_A DATABASE 0 5.3680E+35 >EXTERNAL_A PCT_IDENT 0 98 >EXTERNAL_A LENGTH 0 100 >EXTERNAL_A QUERYLEN 0 100 >EXTERNAL_A SUBJLEN 0 100 >EXTERNAL_A SEQTABLEID 1 1101043 >EXTERNAL_A ACCESSION 1 4.6847E+35 >EXTERNAL_A ACCESSION2 1 2.9712E+35 > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >EXTERNAL_A DATABASE 1 5.9398E+35 >EXTERNAL_A PCT_IDENT 1 100 >EXTERNAL_A LENGTH 1 94605 >EXTERNAL_A QUERYLEN 1 99999 >EXTERNAL_A SUBJLEN 1 270752 > >16 rows selected. > >-- from 8i: > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >EXTERNAL_A SEQTABLEID 0 298261 >EXTERNAL_A ACCESSION 0 3.3882E+35 >EXTERNAL_A ACCESSION2 0 2.5540E+35 >EXTERNAL_A DATABASE 0 5.3680E+35 >EXTERNAL_A PCT_IDENT 0 98 >EXTERNAL_A LENGTH 0 100 >EXTERNAL_A QUERYLEN 0 100 >EXTERNAL_A SUBJLEN 0 100 >EXTERNAL_A SEQTABLEID 1 1770465 >EXTERNAL_A ACCESSION 1 4.6847E+35 >EXTERNAL_A ACCESSION2 1 2.9712E+35 > >LPAD(TABLE LPAD(COLUM ENDPOINT_NUMBER ENDPOINT_VALUE >---------- ---------- --------------- -------------- >EXTERNAL_A DATABASE 1 5.9398E+35 >EXTERNAL_A PCT_IDENT 1 100 >EXTERNAL_A LENGTH 1 94605 >EXTERNAL_A QUERYLEN 1 99999 >EXTERNAL_A SUBJLEN 1 270752 > >16 rows selected. > >-----Original Message----- >From: oracle-l-bounce@freelists.org >[mailto:oracle-l-bounce@freelists.org]On Behalf Of Wolfgang Breitling >Sent: Monday, March 01, 2004 1:22 PM >To: oracle-l@freelists.org >Subject: RE: query slow in 9i, but not slow in 8i > > >Given those counts, is there a histogram on external_accession.SEQTABLEID ? > >At 10:44 AM 3/1/2004, you wrote: > >The rowcount are not exact the same, but close. The 9i dataset is loaded > >using the dump that was a few weeks old. And I ran > >DBMS_STATS.GATHER_TABLE_STATS after the loading. > > > >Guang > > > > > >-- from 8173: > >MT@atlas-SQL> select count(*) from mt.External_accession; > > > > COUNT(*) > >---------- > > 38102138 > > > >MT@atlas-SQL> select count(*) from mt.identifier; > > > > COUNT(*) > >---------- > > 127836 > > > > > >-- from 9204: > > > >SQL> select count(*) from mt.External_accession; > > > > COUNT(*) > >---------- > > 36907691 > > > >SQL> select count(*) from mt.identifier; > > > > COUNT(*) > >---------- > > 127612 > >Wolfgang Breitling >Oracle7, 8, 8i, 9i OCP DBA >Centrex Consulting Corporation >http://www.centrexcc.com > > >---------------------------------------------------------------- >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 >----------------------------------------------------------------- > >---------------------------------------------------------------- >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 >----------------------------------------------------------------- Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com ---------------------------------------------------------------- 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 -----------------------------------------------------------------