"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1173625642.245224_at_bubbleator.drizzle.com...
> 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
Thanks for the tip, looks like this is what I need. I'll check it out
tomorrow.
Received on Sun Mar 11 2007 - 10:18:35 CDT
