Re: CBO & different execution plans

On Mar 11, 2:53 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
> "Charles Hooper" <hooperc2..._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

I experimented a bit some time ago with dbms_advanced_rewrite. I had a SQL statement in a packaged application that was taking excessively long to execute - roughly 3.4 seconds per execution and the packaged application was trying to execute the statement thousands of times. The report that should have required 15 seconds to complete required 12+ minutes. The problem in this case had to do with Oracle picking the wrong index for a table access. Providing a hint to Oracle to use the correct index dropped the execution time down to roughly 0.04 seconds per execution, allowing the report to again complete in 15 seconds (bad news is that, if the application were specifically coded for Oracle, it could have built the report in less than 2 seconds). During my experimentation, I could not make dbms_advanced_rewrite recognize the presence of the hint and to have Oracle act on the hint that was embedded. I had to resort to a logon trigger that was specific to the application to change a session specific parameter related to bind variable peeking.

Considering that the problem is related to a view, you might be able to modify the view definition so that it executes more efficiently - possibly by using hints that artifically scale down the expected cardinality from the HTS_SECURE_ASSAY_RESULT view so that it is joined earlier, and to determine why that view is returning 2,377,609 rows when only 48 make it through the next join operation. Based on somewhat limited testing when I have encountered performance problems with SQL statements, Oracle 10.2.0.2 seems to prefer joining tables listed in the SQL statement first, and then joining those results to row results from views that are referenced in the SQL statement, even if it is _obvious_ that the view results need to drive the tables.

It might be interesting to take a look at a 10053 trace at level 1 for the SQL statement to see what is happening. It takes a while to determine how to read such a trace, so you will not find an answer immediately.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Mar 11 2007 - 15:16:46 CDT