Re: CBO & different execution plans

"Charles Hooper" <hooperc2000_at_yahoo.com> wrote in message news:1173626542.709090.31890_at_8g2000cwh.googlegroups.com...
> On Mar 10, 6:20 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
>> 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
>
> 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
>
> In the fast running execution, tables are joined in the following
> order:
> HTS_ASSAY to HTS_COMPOUND_LOT, which results in 5 rows. Previous
> results are then joined to 536 rows from HTS_ASSAY_RESULT, which
> results in 536 rows. The results of the previous are then joined with
> 48 rows from HTS_ASSAY_PROTOCOL, which produces 48 rows. The previous
> results are then joined with 48 rows from HTS_ASSAY_RESULT_TYPE, which
> results in 48 rows.
>
> In the slow running execution:
> The public synonymn for HTS_ASSAY_RESULT points to
> HTS_SECURE_ASSAY_RESULT view
> HTS_SECURE_ASSAY_RESULT view contains a reference to:
> HTS_ASSAY
> HTS_ASSAY_PROTOCOL
> HTS_ASSAY_RESULT
> The HTS_ASSAY to HTS_ASSAY_PROTOCOL join results in 95 rows. The
> results of this join is then apparently outer joined with the
> 2,377,609 rows returned from the HTS_ASSAY_RESULT table. In the last
> join operation for the SQL statement, the results of combining the
> other tables (50 rows) is hash joined with the results from
> HTS_SECURE_ASSAY_RESULT view (2,377,609 rows), which results in 48
> rows being returned by the SQL statement.
>
> When the tables are joined in the order indicated in the slow running
> execution, Oracle does not have any way to constrain the 2,377,609
> rows in the HTS_ASSAY_RESULT table until the view is finally combined
> with the row set results of the other tables. My guess is that Oracle
> is predicting that the cardinality coming out of the
> HTS_SECURE_ASSAY_RESULT view will be very high, so it selects to join
> this view last, rather than third as in the fast running execution.
>
> What you might try to do is execute a 10053 trace at level 1 when
> executing the above SQL statement as a user who is not the schema
> owner. Then, alter the optimizer_features_enabled parameter for the
> session to 8.1.7 and eexcute another 10053 trace at level 1 when
> executing the above SQL statement. If the above SQL statement
> executes quickly with optimizer_features_enabled set to 8.1.7, examine
> the two trace files. The trace files will list all parameters,
> including hidden parameters, that were in effect during the execution
> of the SQL statement. Compare the parameters between the two runs -
> where differences exist, you are seeing the parameters that changed
> automatically when optimizer_features_enabled was changed. By
> starting a new session and altering that session one parameter at a
> time before each execution of the SQL statement, you may be able to
> find the parameter that corrects the problem. If you find the
> problematic parameter, you could create a logon trigger that sets the
> necessary session level parameters when that application module runs.
>
> If you post a dump of the optimizer parameters, someone may be able to
> identify the parameter that is affecting the join order.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>

Update : I executed the query on 10g with optimizer_features_enable = '8.1.7'.
Result : same bad execution plan, same shitty performance :-) This problem is really driving me crazy ...

Next episode in this never-ending story : dbms_advanced_rewrite :-)

Matthias Received on Sun Mar 11 2007 - 13:53:46 CDT