Re: why this sql's exection plan is so different on two servers

Guang,

What is the value of dba_tables.blocks for each of the tables? What is the value of db_file_multiblock_read_count for each of the dbs?

Daniel Fink

Guang Mei wrote:

> CLUSTERING_FACTOR values of the indexes for table IDENTIFIER are identical
> on both servers. Also all table and indexes are analyzed (as I said in my
> original email).
>
> Guang
>
> MT_at_max-SQL> select INDEX_NAME,CLUSTERING_FACTOR from user_indexes where
> TABLE_NAME = 'IDENTIFIER';
>
> INDEX_NAME CLUSTERING_FACTOR
> ------------------------------ -----------------
> IDENTIFIER_GENEID_INDEX 53544
> IDENTIFIER_ID_INDEX 4674
> IDENTIFIER_SEQTABID_INDEX 3376
> IDENTIFIER_SPECIESID_INDEX 1725
>
> MT_at_atlas-SQL> select INDEX_NAME,CLUSTERING_FACTOR from user_indexes where
> TABLE_NAME = 'IDENTIFIER';
>
> INDEX_NAME CLUSTERING_FACTOR
> ------------------------------ -----------------
> IDENTIFIER_GENEID_INDEX 53544
> IDENTIFIER_ID_INDEX 4674
> IDENTIFIER_SEQTABID_INDEX 3376
> IDENTIFIER_SPECIESID_INDEX 1725
>
> -----Original Message-----
> rgaffuri_at_cox.net
> Sent: Monday, August 25, 2003 3:15 PM
> To: Multiple recipients of list ORACLE-L
>
> go to dba_indexes and check CLUSTERING_FACTOR of the index you are using
> that is slow. Oracle is more likely to use an index if that value is close
> to the number of blocks. Less likely if its close to the number of rows.
>
> it basically says how well ordered the data is. Then make sure you analyze
> your indexes.
> >
> > From: "Guang Mei" <gmei_at_incyte.com>
> > Date: 2003/08/25 Mon PM 03:04:30 EDT
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Subject: why this sql's exection plan is so different on two servers
> >
> >
> > Hi:
> >
> > I have a question about what could be the reason(s) that an identical sql
> > have a dramatics execution time on two different servers.
> >
> > Both servers OS are the same (Solaris 2.8), Oracle version are the same
> > (8173). init.ora are the same, db data are identical (loaded from the same
> > dump file). All tables are indexes are analyzed right after schema import.
> > We actually run same application code on both dbs. One is used for the
> > weekly production, the other is used for backup in case we have production
> > problem. The query involved always run fast on both servers until this
> week.
> >
> > The sql statement in question is:
> >
> > select Observationlist.id, CurationDetails.Context,
> > (to_number(CurationDetails.text) + 2000000),
> > Identifier.ID, Identifier.SpeciesID
> > from Observationlist, CurationDetails, mt.Identifier, mt.Category2gene,
> > termlist
> > where Observationlist.SourceID = CurationDetails.ID
> > and CurationDetails.Context in (3001044, 3001064, 3001073)
> > and CurationDetails.Text = to_char(termlist.TermID)
> > and Observationlist.NextObs is null
> > and Observationlist.CurationStatus = 'E'
> > and Observationlist.CurationType in (3000722)
> > and Observationlist.Target = 'GeneID'
> > and Identifier.SpeciesID not in (6, 33, 100, 101, 103, 104, 105)
> > and Identifier.Type != 'A'
> > and Observationlist.GeneID = Identifier.GeneID
> > and Category2gene.CComment = 'PhenotypeLoader'||Observationlist.ID
> > and exists (
> > select 1 from termobs
> > where termobs.obsid=observationlist.id and
> > TermObs.TermID = 3000313)
> > and exists (
> > select 1 from termobs
> > where termobs.obsid=observationlist.id and
> > TermObs.TermID = 3000921);
> >
> >
> > --- on server 1, it is taking more than 10 hours to finish!:
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1893 Card=5 Bytes=76
> > 5)
> >
> > 1 0 FILTER
> > 2 1 HASH JOIN (Cost=1893 Card=5 Bytes=765)
> > 3 2 NESTED LOOPS (Cost=922 Card=4 Bytes=536)
> > 4 3 NESTED LOOPS (Cost=622 Card=4 Bytes=512)
> > 5 4 HASH JOIN (Cost=614 Card=4 Bytes=464)
> > 6 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS
> > T' (Cost=519 Card=4 Bytes=132)
> >
> > 7 6 BITMAP CONVERSION (TO ROWIDS)
> > 8 7 BITMAP AND
> > 9 8 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO
> > NLISTTARGETINDEX'
> >
> > 10 8 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST
> > ATUSINDEX'
> >
> > 11 8 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY
> > PEINDEX'
> >
> > 12 5 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9
> > 4 Card=10368 Bytes=860544)
> >
> > 13 4 TABLE ACCESS (BY INDEX ROWID) OF 'IDENTIFIER' (Cos
> > t=2 Card=62405 Bytes=748860)
> >
> > 14 13 INDEX (RANGE SCAN) OF 'IDENTIFIER_GENEID_INDEX'
> > (NON-UNIQUE) (Cost=1 Card=62405)
> >
> > 15 3 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Cost=7
> > 5 Card=221033 Bytes=1326198)
> >
> > 16 2 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card=
> > 841893 Bytes=15995967)
> >
> > 17 1 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=4 Card=
> > 1 Bytes=11)
> >
> > 18 17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQUE) (C
> > ost=3 Card=1)
> >
> > 19 1 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=4 Card=
> > 1 Bytes=11)
> >
> > 20 19 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQUE) (C
> > ost=3 Card=1)
> >
> >
> > ------ on server 2, it is taking less than a minute to finish :
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1777 Card=9190 Bytes
> > =1406070)
> >
> > 1 0 FILTER
> > 2 1 HASH JOIN (Cost=1777 Card=9190 Bytes=1406070)
> > 3 2 HASH JOIN (Cost=794 Card=8004 Bytes=1072536)
> > 4 3 HASH JOIN (Cost=697 Card=8087 Bytes=986614)
> > 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIST'
> > (Cost=519 Card=4 Bytes=132)
> >
> > 6 5 BITMAP CONVERSION (TO ROWIDS)
> > 7 6 BITMAP AND
> > 8 7 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIONL
> > ISTTARGETINDEX'
> >
> > 9 7 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONSTAT
> > USINDEX'
> >
> > 10 7 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTYPE
> > INDEX'
> >
> > 11 4 HASH JOIN (Cost=177 Card=10368 Bytes=922752)
> > 12 11 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9
> > 4 Card=10368 Bytes=860544)
> >
> > 13 11 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Co
> > st=75 Card=221033 Bytes=1326198)
> >
> > 14 3 TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=88 Card=62
> > 405 Bytes=748860)
> >
> > 15 2 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card=
> > 841893 Bytes=15995967)
> >
> > 16 1 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=4 Card=
> > 1 Bytes=11)
> >
> > 17 16 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQUE) (C
> > ost=3 Card=1)
> >
> > 18 1 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=4 Card=
> > 1 Bytes=11)
> >
> > 19 18 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQUE) (C
> > ost=3 Card=1)
> >
> >
> > From the execution plan I can see that when Oracle is doing FULL TABLE
> SCAN
> > on table IDENTIFIER, the query runs fast. So I added hint "FULL
> > (Identifier)" to the sql:
> >
> > select /*+ FULL (Identifier) */
> > Observationlist.id, CurationDetails.Context,
> > (to_number(CurationDetails.text) + 2000000),
> > Identifier.ID, Identifier.SpeciesID
> > from Observationlist, CurationDetails, mt.Identifier, mt.Category2gene,
> > termlist
> > where Observationlist.SourceID = CurationDetails.ID
> > and CurationDetails.Context in (3001044, 3001064, 3001073)
> > and CurationDetails.Text = to_char(termlist.TermID)
> > and Observationlist.NextObs is null
> > and Observationlist.CurationStatus = 'E'
> > and Observationlist.CurationType in (3000722)
> > and Observationlist.Target = 'GeneID'
> > and Identifier.SpeciesID not in (6, 33, 100, 101, 103, 104, 105)
> > and Identifier.Type != 'A'
> > and Observationlist.GeneID = Identifier.GeneID
> > and Category2gene.CComment = 'PhenotypeLoader'||Observationlist.ID
> > and exists (
> > select 1 from termobs
> > where termobs.obsid=observationlist.id and
> > TermObs.TermID = 3000313)
> > and exists (
> > select 1 from termobs
> > where termobs.obsid=observationlist.id and
> > TermObs.TermID = 3000921);
> >
> > and it ran just as fast on server 1.
> >
> > Could anyone explain to me why?
> >
> > TIA.
> >
> > Guang
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Guang Mei
> > INET: gmei_at_incyte.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guang Mei
> INET: gmei_at_incyte.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: Daniel.Fink_at_Sun.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/x-vcard attachment: Card for Daniel Fink