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).
