Re: CBO & different execution plans

From: Matthias Hoys <anti_at_spam.com>
Date: Thu, 8 Mar 2007 20:38:08 +0100
Message-ID: <45f06628$0$2943$ba620e4c@news.skynet.be>

Update :

Even with optimizer_mode = CHOOSE, the query is executed differently between user A (schema owner) and user B (user with access through views and public synonyms).

This is the query (without bind variables):

select htsdecode.assayName(r.alt_assay_id) c1,   htsdecode.resulttype(r.result_type) c2,   operator||nvl(to_char(r.result_value),r.result_val_char) || ' ' ||   htsdecode.resultunit(r.result_unit) result_value, ' ', r.concentration ||   ' ' || htsdecode.concUnit(r.conc_unit) cu, '' ,   htsdecode.assayVersion(r.alt_assay_id) c4, to_char(r.experiment_date,   'DD-MON-RRRR') c5, r.covariance,r.std_devn, r.result_id,   nvl(r.parent_result_id,0),art.drill_order,1,1, 1,' ',   htsdecode.sampleLot(r.sample_id) c10,' ','N' c12, r.sample_id from
 HTS_ASSAY_RESULT R,HTS_ASSAY_RESULT_TYPE ART,HTS_ASSAY_PROTOCOL AP ,   HTS_ASSAY A ,HTS_COMPOUND_LOT CL where r.alt_assay_id=ap.alt_assay_id and   art.assay_id=ap.assay_id and art.result_type=r.result_type and r.sample_id =

   cl.sample_id and cl.compound_id = 2866242 and a.assay_id = ap.assay_id and
  a.assay_name in ('PKCZ_IE') order by r.experiment_id,r.result_id,   r.parent_result_id,art.drill_order

The explain plan when the schema owner (user A) executes the query (good performance) :

Rows Row Source Operation
------- ---------------------------------------------------

     48  SORT ORDER BY (cr=1398 pr=4 pw=0 time=124886 us)
     48   NESTED LOOPS  (cr=654 pr=4 pw=0 time=17142 us)
     48    NESTED LOOPS  (cr=604 pr=4 pw=0 time=12769 us)
    536     NESTED LOOPS  (cr=66 pr=4 pw=0 time=22330 us)
      5      NESTED LOOPS  (cr=10 pr=0 pw=0 time=439 us)
      1       TABLE ACCESS BY INDEX ROWID HTS_ASSAY (cr=2 pr=0 pw=0 time=139 
us)
      1        INDEX UNIQUE SCAN HTS_ASSAY_UNQ (cr=1 pr=0 pw=0 time=72 
us)(object id 65132)
      5       TABLE ACCESS BY INDEX ROWID HTS_COMPOUND_LOT (cr=8 pr=0 pw=0 
time=247 us)
      5        INDEX RANGE SCAN HTS_COMPOUND_LOT_UNQ (cr=3 pr=0 pw=0 
time=108 us)(object id 65195)
    536      TABLE ACCESS BY INDEX ROWID HTS_ASSAY_RESULT (cr=56 pr=4 pw=0 
time=31011 us)
    536       INDEX RANGE SCAN HTS_ASSAY_RESULT_IDX7 (cr=13 pr=4 pw=0 
time=23917 us)(object id 65154)
     48     TABLE ACCESS BY INDEX ROWID HTS_ASSAY_PROTOCOL (cr=538 pr=0 pw=0 
time=30562 us)
    536      INDEX UNIQUE SCAN HTS_ASSAY_PROTOCOL_UNQ (cr=2 pr=0 pw=0 
time=13639 us)(object id 65146)
     48    TABLE ACCESS BY INDEX ROWID HTS_ASSAY_RESULT_TYPE (cr=50 pr=0 
pw=0 time=3264 us)
     48     INDEX UNIQUE SCAN HTS_ASSAY_RESULT_TYPE_PK (cr=2 pr=0 pw=0 

time=1341 us)(object id 65171)

The explain plan when user B executes the query (slow response) :

Rows Row Source Operation
------- ---------------------------------------------------

     48  SORT ORDER BY (cr=50513 pr=3 pw=0 time=6519765 us)
     48   HASH JOIN  (cr=49749 pr=0 pw=0 time=2482091 us)
     50    HASH JOIN  (cr=27 pr=0 pw=0 time=8612 us)
     95     VIEW  HTS_SECURE_ASSAY_PROTOCOL (cr=7 pr=0 pw=0 time=5969 us)
     95      MERGE JOIN  (cr=7 pr=0 pw=0 time=4818 us)
     43       TABLE ACCESS BY INDEX ROWID HTS_ASSAY (cr=3 pr=0 pw=0 
time=1213 us)
     43        INDEX FULL SCAN HTS_ASSAY_PK (cr=1 pr=0 pw=0 time=400 
us)(object id 65131)
     95       SORT JOIN (cr=4 pr=0 pw=0 time=4129 us)
     95        VIEW  index$_join$_008 (cr=4 pr=0 pw=0 time=6619 us)
     95         HASH JOIN  (cr=4 pr=0 pw=0 time=5634 us)
     95          INDEX RANGE SCAN HTS_ASSAY_PROTOCOL_PK (cr=1 pr=0 pw=0 
time=808 us)(object id 65145)
     95          INDEX FAST FULL SCAN HTS_ASSAY_PROTOCOL_UNQ (cr=3 pr=0 pw=0 
time=878 us)(object id 65146)
   1010     MERGE JOIN CARTESIAN (cr=20 pr=0 pw=0 time=22959 us)
      5      MERGE JOIN CARTESIAN (cr=10 pr=0 pw=0 time=559 us)
      1       VIEW  HTS_SECURE_ASSAY (cr=2 pr=0 pw=0 time=123 us)
      1        TABLE ACCESS BY INDEX ROWID HTS_ASSAY (cr=2 pr=0 pw=0 time=87 
us)
      1         INDEX UNIQUE SCAN HTS_ASSAY_UNQ (cr=1 pr=0 pw=0 time=46 
us)(object id 65132)
      5       TABLE ACCESS BY INDEX ROWID HTS_COMPOUND_LOT (cr=8 pr=0 pw=0 
time=296 us)
      5        INDEX RANGE SCAN HTS_COMPOUND_LOT_UNQ (cr=3 pr=0 pw=0 
time=124 us)(object id 65195)
   1010      BUFFER SORT (cr=10 pr=0 pw=0 time=11544 us)
    202       VIEW  HTS_SECURE_ASSAY_RESULT_TYPE (cr=10 pr=0 pw=0 time=6549 
us)
    202        MERGE JOIN  (cr=10 pr=0 pw=0 time=4516 us)
     43         TABLE ACCESS BY INDEX ROWID HTS_ASSAY (cr=3 pr=0 pw=0 
time=932 us)
     43          INDEX FULL SCAN HTS_ASSAY_PK (cr=1 pr=0 pw=0 time=317 
us)(object id 65131)
    202         SORT JOIN (cr=7 pr=0 pw=0 time=2206 us)
    202          TABLE ACCESS BY INDEX ROWID HTS_ASSAY_RESULT_TYPE (cr=7 
pr=0 pw=0 time=4084 us)
    202           INDEX RANGE SCAN HTS_ASSAY_RESULT_TYPE_PK (cr=1 pr=0 pw=0 

time=1639 us)(object id 65171)
2377609 VIEW HTS_SECURE_ASSAY_RESULT (cr=49722 pr=0 pw=0 time=133150391 us)

2377609     HASH JOIN  (cr=49722 pr=0 pw=0 time=104619068 us)
     95      MERGE JOIN  (cr=7 pr=0 pw=0 time=3810 us)
     43       TABLE ACCESS BY INDEX ROWID HTS_ASSAY (cr=3 pr=0 pw=0 
time=1157 us)
     43        INDEX FULL SCAN HTS_ASSAY_PK (cr=1 pr=0 pw=0 time=452 
us)(object id 65131)
     95       SORT JOIN (cr=4 pr=0 pw=0 time=3037 us)
     95        VIEW  index$_join$_016 (cr=4 pr=0 pw=0 time=5615 us)
     95         HASH JOIN  (cr=4 pr=0 pw=0 time=4460 us)
     95          INDEX RANGE SCAN HTS_ASSAY_PROTOCOL_PK (cr=1 pr=0 pw=0 
time=775 us)(object id 65145)
     95          INDEX FAST FULL SCAN HTS_ASSAY_PROTOCOL_UNQ (cr=3 pr=0 pw=0 
time=706 us)(object id 65146)
2377609      TABLE ACCESS BY INDEX ROWID HTS_ASSAY_RESULT (cr=49715 pr=0 
pw=0 time=47552259 us)
2377609       INDEX RANGE SCAN HTS_ASSAY_RESULT_IDX41 (cr=4389 pr=0 pw=0 

time=19020922 us)(object id 65153)

So : why the difference in explain plan between the 2 users ??? Could this be a CBO bug ?

Matthias Received on Thu Mar 08 2007 - 13:38:08 CST