Re: why this sql's exection plan is so different on two servers
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: selectObservationlist.id, CurationDetails.Context, (to_number(CurationDetails.text) + 200), 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY PEINDEX' 125 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9 4 Card=10368 Bytes=860544) 134 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) 153 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Cost=7 5 Card=221033 Bytes=1326198) 162 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card= 841893 Bytes=15995967) 171 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) 191 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) 10 FILTER 21 HASH JOIN (Cost=1777 Card=9190 Bytes=1406070) 32 HASH JOIN (Cost=794 Card=8004 Bytes=1072536) 43 HASH JOIN (Cost=697 Card=8087 Bytes=986614) 54 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIST' (Cost=519 Card=4 Bytes=132) 65 BITMAP CONVERSION (TO ROWIDS) 76 BITMAP AND 87 BITMAP INDEX (SINGLE VALUE) OF
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_INDEX53544 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_INDEX53544 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: selectObservationlist.id, CurationDetails.Context, (to_number(CurationDetails.text) + 200), 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY PEINDEX' 125 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9 4 Card=10368 Bytes=860544) 134 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) 153 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Cost=7 5 Card=221033 Bytes=1326198) 162 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card= 841893 Bytes=15995967) 171 TABLE ACCESS
Re: RE: why this sql's exection plan is so different on two servers
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_INDEX53544 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_INDEX53544 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) + 200), Identifier.ID, Identifier.SpeciesID fromObservationlist, 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118
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 [EMAIL PROTECTED] select INDEX_NAME,CLUSTERING_FACTOR from user_indexes where TABLE_NAME = 'IDENTIFIER'; INDEX_NAME CLUSTERING_FACTOR -- - IDENTIFIER_GENEID_INDEX53544 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_INDEX53544 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: selectObservationlist.id, CurationDetails.Context, (to_number(CurationDetails.text) + 200), 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY PEINDEX' 125 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9 4 Card=10368 Bytes=860544) 134 TABLE ACCESS (BY INDEX ROWID) OF 'IDENTIFIER' (Cos t=2 Card=62405 Bytes=748860) 14 13 INDEX (RANGE SCAN) OF 'IDENTIFIER_GENEID_INDEX'
Re: why this sql's exection plan is so different on two servers
Hi! I see more hash joins in second query's execution plan. The first one uses lot's of nested loops (and index scan on identifier table). If your data, setup, parameters are exactly identical, take 10053 trace from both queries and check for statistics differences. Note that the fast running query has lower cost than the slow one - then (if your parameters are identical) either statistics are different or optimizer plan iteration is cut off before it gets to the optimal plan. I assume that your optimizer_max_permutations and _optimizer_search_limit are left default? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 25, 2003 10:04 PM 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) + 200), 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY PEINDEX' 125 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9 4 Card=10368 Bytes=860544) 134 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) 153 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Cost=7 5 Card=221033 Bytes=1326198) 162 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card= 841893 Bytes=15995967) 171 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) 191 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) 10 FILTER 21 HASH JOIN (Cost=1777 Card=9190 Bytes=1406070) 32 HASH JOIN (Cost=794 Card=8004 Bytes=1072536) 43 HASH JOIN (Cost=697 Card=8087 Bytes=986614) 54 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIST' (Cost=519 Card=4 Bytes=132) 65 BITMAP CONVERSION (TO ROWIDS) 76 BITMAP AND 87 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIONL ISTTARGETINDEX'
RE: why this sql's exection plan is so different on two servers
Hi Daniel:
Here they are. I am now try to see if 10053 trace would reveal something
different (suggested by Tanel).
Guang
[EMAIL PROTECTED] select table_name, blocks from dba_tables where owner='MT'
and table_name in ('OBSERVATIONLIST', 'CURATIONDETAILS',
'IDENTIFIER','CATEGORY2GENE','TERMOBS')
order by table_name;
TABLE_NAME BLOCKS
-- --
CATEGORY2GENE6389
CURATIONDETAILS 615
IDENTIFIER578
OBSERVATIONLIST 37038
TERMOBS 7798
[EMAIL PROTECTED] select table_name, blocks from dba_tables where owner='MT'
and table_name in ('OBSERVATIONLIST', 'CURATIONDETAILS',
'IDENTIFIER','CATEGORY2GENE','TERMOBS')
order by table_name;
TABLE_NAME BLOCKS
-- --
CATEGORY2GENE6389
CURATIONDETAILS 615
IDENTIFIER578
OBSERVATIONLIST 37038
TERMOBS 7798
[EMAIL PROTECTED] select name,value from v$parameter where
name='db_file_multiblock_read_count';
NAME
VALUE
db_file_multiblock_read_count
8
[EMAIL PROTECTED] select name,value from v$parameter where
name='db_file_multiblock_read_count';
NAME
VALUE
db_file_multiblock_read_count
8
-Original Message-
Daniel Fink
Sent: Monday, August 25, 2003 4:19 PM
To: Multiple recipients of list ORACLE-L
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
[EMAIL PROTECTED] select INDEX_NAME,CLUSTERING_FACTOR from user_indexes where
TABLE_NAME = 'IDENTIFIER';
INDEX_NAME CLUSTERING_FACTOR
-- -
IDENTIFIER_GENEID_INDEX53544
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_INDEX53544
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:
selectObservationlist.id, CurationDetails.Context,
(to_number(CurationDetails.text) + 200),
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
RE: why this sql's exection plan is so different on two servers
is db_file_multiblock_read_count and other init.ora parameters the same? Patrice. -Original Message- Sent: Monday, August 25, 2003 5:20 PM To: Multiple recipients of list ORACLE-L Hi! I see more hash joins in second query's execution plan. The first one uses lot's of nested loops (and index scan on identifier table). If your data, setup, parameters are exactly identical, take 10053 trace from both queries and check for statistics differences. Note that the fast running query has lower cost than the slow one - then (if your parameters are identical) either statistics are different or optimizer plan iteration is cut off before it gets to the optimal plan. I assume that your optimizer_max_permutations and _optimizer_search_limit are left default? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 25, 2003 10:04 PM 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) + 200), 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY PEINDEX' 125 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9 4 Card=10368 Bytes=860544) 134 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) 153 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Cost=7 5 Card=221033 Bytes=1326198) 162 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card= 841893 Bytes=15995967) 171 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) 191 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) 10 FILTER 21 HASH JOIN (Cost=1777 Card=9190 Bytes=1406070) 32 HASH JOIN (Cost=794 Card=8004 Bytes=1072536) 43 HASH JOIN (Cost=697 Card=8087 Bytes=986614) 54 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIST' (Cost=519 Card=4
RE: why this sql's exection plan is so different on two servers
I was wrong by saying that the init.ora are the same. By looking at the 10053 trace and comparing init.ora files more carefully, I found that hash_area_size is very different: [EMAIL PROTECTED] select name,value from v$parameter where name = 'hash_area_size'; NAME VALUE hash_area_size 6553600 [EMAIL PROTECTED] select name,value from v$parameter where name = 'hash_area_size'; NAME VALUE hash_area_size 18000 After I increased the hash_area_size on server (max), I got the query run fast again. So it looks like the smaller hash_area_size caused Oracle to choose NESTED LOOPS over HASH JOIN on this particular query. Anyway, thanks for all the help. Guang -Original Message- Tanel Poder Sent: Monday, August 25, 2003 4:20 PM To: Multiple recipients of list ORACLE-L Hi! I see more hash joins in second query's execution plan. The first one uses lot's of nested loops (and index scan on identifier table). If your data, setup, parameters are exactly identical, take 10053 trace from both queries and check for statistics differences. Note that the fast running query has lower cost than the slow one - then (if your parameters are identical) either statistics are different or optimizer plan iteration is cut off before it gets to the optimal plan. I assume that your optimizer_max_permutations and _optimizer_search_limit are left default? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, August 25, 2003 10:04 PM 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) + 200), 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) 10 FILTER 21 HASH JOIN (Cost=1893 Card=5 Bytes=765) 32 NESTED LOOPS (Cost=922 Card=4 Bytes=536) 43 NESTED LOOPS (Cost=622 Card=4 Bytes=512) 54 HASH JOIN (Cost=614 Card=4 Bytes=464) 65 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS T' (Cost=519 Card=4 Bytes=132) 76 BITMAP CONVERSION (TO ROWIDS) 87 BITMAP AND 98 BITMAP INDEX (SINGLE VALUE) OF 'OBSERVATIO NLISTTARGETINDEX' 108 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONST ATUSINDEX' 118 BITMAP INDEX (SINGLE VALUE) OF 'CURATIONTY PEINDEX' 125 TABLE ACCESS (FULL) OF 'CURATIONDETAILS' (Cost=9 4 Card=10368 Bytes=860544) 134 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) 153 INDEX (FAST FULL SCAN) OF 'PK_TERM' (UNIQUE) (Cost=7 5 Card=221033 Bytes=1326198) 162 TABLE ACCESS (FULL) OF 'CATEGORY2GENE' (Cost=970 Card= 841893 Bytes=15995967) 171 TABLE ACCESS (BY INDEX ROWID) OF
Re: why this sql's exection plan is so different on two servers
I was wrong by saying that the init.ora are the same. By looking at the 10053 trace and comparing init.ora files more carefully, I found that hash_area_size is very different: ... After I increased the hash_area_size on server (max), I got the query run fast again. Thank god for 10053 ;) Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
