then i dont know. CBO can be finicky. Ive seen the CBO draw two totally different plans when i have identically data in two different tablespaces. One ran in 10 minutes, one ran for 18 hours and i killed it.
what version of oracle are you on? also try analyze index <index_name> validate structure. in both databases. then do a minus. see if any of that is different. > > From: "Guang Mei" <[EMAIL PROTECTED]> > Date: 2003/08/25 Mon PM 03:39:37 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: why this sql's exection plan is so different on two servers > > 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 > > [EMAIL PROTECTED]> 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 > > > [EMAIL PROTECTED]> 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----- > [EMAIL PROTECTED] > 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" <[EMAIL PROTECTED]> > > Date: 2003/08/25 Mon PM 03:04:30 EDT > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
