"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message
news:1173392430.990319.15890_at_h3g2000cwc.googlegroups.com...
> On Mar 8, 2:38 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
>> 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
>
> It looks to me like the public synonyms are pointing at different
> objects than what you believe to be the case - that is why the query
> runs quickly when the schema owner executes the SQL statements and
> slowly when other users execute the same SQL statement. This may be a
> security layer that is built into the system.
> Rows
> 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)
> ...
> 202 VIEW HTS_SECURE_ASSAY_RESULT_TYPE (cr=10 pr=0 pw=0
> time=6549 us)
> ...
> 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)
> ...
> 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)
>
> In the above, divide the time= values by 1,000,000 to determine the
> seconds involved in that part of the execution plan. For instance,
> the 2,377,609 rows retrieved from the HTS_ASSAY_RESULT table using
> the HTS_ASSAY_RESULT_IDX41 index consumed 47 seconds. Those rows are
> needed to satisfy the HTS_SECURE_ASSAY_RESULT view, which means that
> view added a total of 133 seconds to the query run time.
>
> There is a possibility that I am reading the execution plan
> incorrectly when calculating the relationship of time between parent
> and child operations in the plan. Are you using VPD (Virtual Private
> Database) functionality?
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
We are not using VPD. But you are right, the public synonyms point to
different objects, it seems like the application has some built-in security
layer. Still, those extra security tables are very small, so I'm still
searching why the query runs fast on Oracle 8i and slow on 10g (with roughly
the same CBO settings and statistics). I found out that, if I add the
HTS_ASSAY_RESULT table to the end of the table list in the FROM part of the
query, the query always executes fast. However, I can't modify the
application code. And no matter what CBO settings or statistics that I use,
Oracle 10g generates a non-performant execution plan. Only by rewriting the
query, it's executed fine ... So I'm a bit stuck here ... Maybe stored
outlines could help ?
Matthias
Received on Sat Mar 10 2007 - 16:20:38 CST
