| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: query slow in 9i, but not slow in 8i
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');
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.
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');
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.
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
-- 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 -----------------------------------------------------------------Received on Mon Mar 01 2004 - 12:41:29 CST
 |
 |