Re: CBO & different execution plans

Matthias Hoys wrote:
"Charles Hooper" <hooperc2000@xxxxxxxxx> wrote in message news:1173392430.990319.15890@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mar 8, 2:38 pm, "Matthias Hoys" <a...@xxxxxxxx> 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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: CBO & different execution plans
    ... This is the query: ... The explain plan when the schema owner executes the query ... It looks to me like the public synonyms are pointing at different ...
    (comp.databases.oracle.server)
  • Re: CBO & different execution plans
    ... This is the query: ... The explain plan when the schema owner executes the query (good ... It looks to me like the public synonyms are pointing at different ... There is a possibility that I am reading the execution plan ...
    (comp.databases.oracle.server)
  • Re: How to get a variant returned from a c++ function in vb.net?
    ... > This function executes a SQL query and returns a ... > single value - whatever the query asks for: ... Dim cn As New SqlConnection ... not a pointer to a string as the function expects. ...
    (microsoft.public.dotnet.framework.interop)
  • Re: When do selects execute for multiple resultsets
    ... SQL Server executes each query and stops when its ... When the first resultset is ... The CommandTimeout is measured from the time you execute the query until the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: CBO & different execution plans
    ... This is the query: ... The explain plan when the schema owner executes the query (good ...
    (comp.databases.oracle.server)