RE: query slow in 9i, but not slow in 8i

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 01 Mar 2004 12:13:50 -0700
Message-Id: <6.0.3.0.2.20040301120547.02b562d8@pop.centrexcc.com>

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_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
>Sent: Monday, March 01, 2004 1:22 PM
>To: oracle-l_at_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_at_atlas-SQL> select count(*) from mt.External_accession;
> >
> > COUNT(*)
> >----------
> > 38102138
> >
> >MT_at_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_at_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_at_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_at_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
-----------------------------------------------------------------

Received on Mon Mar 01 2004 - 13:11:23 CST