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" <ORACLE-L_at_fatcity.com> 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) + 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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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