Re: CBO & different execution plans

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Mar 2007 08:22:22 -0700
Message-ID: <1173626542.709090.31890@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. Received on Sun Mar 11 2007 - 10:22:22 CDT