Re: CBO & different execution plans

On Mar 10, 6:20 pm, "Matthias Hoys" <a...@xxxxxxxx> 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.

.

Relevant Pages

  • Re: Finally which ORM tool?
    ... the session' method. ... they use the same mechanism as Linq to Sql does: ... Also, if you pass a variable to the query, the value the variable ... q is affected if I change foo AFTER this query and BEFORE execution. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Finally which ORM tool?
    ... the session' method. ... able to execute the query by itself. ... has at EXECUTION time is used, ... That SHOULDN'T be important, simply because q LOOKS like a declaration, ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: Touble With Dates
    ... Session altered. ... Every function adds execution cost to your script. ... But who would want to have a query only match a down-to-the-second ... TRUNC will trucate the value to the date only so ...
    (perl.dbi.users)
  • Re: Finally which ORM tool?
    ... things for the framework, the framework can decide what to do by itself. ... the client would let you effectively have a "fake" session that you ... attaching graphs) and together with deferred execution of linq ... able to execute the query by itself. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Oracle 9i database index
    ... then use the same DBMS XPlan approach to determine if the execution ... performance is better or worse for the particular SQL statement. ... MY_DATE BETWEEN TRUNC(SYSDATE-100) AND ... to 20% of the rows to be returned by a query, ...
    (comp.databases.oracle.server)