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

  • From: "Guang Mei" <gmei@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 1 Mar 2004 13:44:24 -0500

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Monday, March 01, 2004 1:22 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004