Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> why this sql's exection plan is so different on two servers

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

From: Guang Mei <gmei_at_incyte.com>
Date: Mon, 25 Aug 2003 11:04:30 -0800
Message-ID: <F001.005CD107.20030825110430@fatcity.com> 






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








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)








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


Received on Mon Aug 25 2003 - 14:04:30 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US