Matthias Hoys wrote:
> "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
If you can not change the application code take a serious look at
using Advanced Rewrite.
Morgan's Library at www.psoug.org
click on DBMS_ADVANCED_REWRITE
Just because you can't change the code ... doesn't mean you can't
rewrite the query. What is required is setting query_rewrite_integrity
to 'TRUSTED'.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sun Mar 11 2007 - 10:07:22 CDT
