Oracle 10.2.0.3.0 64-bit on RHEL 4 optimizer_index_caching = 80 optimizer_index_cost_adj = 20 optimizer_mode = FIRST_ROWS, statistics have been gathered (with histograms).
Today I noticed the following strange CBO behaviour :
User A is the owner of a number of objects of a third-party application. User B has access to the objects of user A through role grants and public synonyms.
The problem is that certain queries run fast with user A (the schema owner), while they run slow with user B (and all other users with access to the application). However, this only happens when the system optimizer_mode = FIRST_ROWS ! When I change it to CHOOSE, there are no performance differences. It looks like, with an optimizer_mode of FIRST_ROWS, the CBO chooses different execution plans based on the user who is executing the queries ?? There are no logon triggers, different user profiles or objects with the same name as the public synonyms ...
Has anyone else seen this behaviour ? I haven't found the time yet to do a 10053 trace, the application is rather complex and generates a large amount of queries. For now, I changed the optimizer_mode to CHOOSE, but I want to keep FIRST_ROWS for a number of other applications in the same database ...
> Oracle 10.2.0.3.0 64-bit on RHEL 4 > optimizer_index_caching = 80 > optimizer_index_cost_adj = 20 > optimizer_mode = FIRST_ROWS, statistics have been gathered (with > histograms).
> Today I noticed the following strange CBO behaviour :
> User A is the owner of a number of objects of a third-party application. > User B has access to the objects of user A through role grants and public > synonyms.
> The problem is that certain queries run fast with user A (the schema owner), > while they run slow with user B (and all other users with access to the > application). However, this only happens when the system optimizer_mode = > FIRST_ROWS ! When I change it to CHOOSE, there are no performance > differences. It looks like, with an optimizer_mode of FIRST_ROWS, the CBO > chooses different execution plans based on the user who is executing the > queries ?? > There are no logon triggers, different user profiles or objects with the > same name as the public synonyms ...
> Has anyone else seen this behaviour ? I haven't found the time yet to do a > 10053 trace, the application is rather complex and generates a large amount > of queries. For now, I changed the optimizer_mode to CHOOSE, but I want to > keep FIRST_ROWS for a number of other applications in the same database ...
> Matthias
Considering you have gathered histograms ... Are you using bind variables or cursor_sharing=SIMILAR/FORCE? My first impression is that the behavior you are seeing is related to bind variable peeking.
> On Mar 7, 3:04 pm, "Matthias Hoys" <a...@spam.com> wrote: >> Hello,
>> Oracle 10.2.0.3.0 64-bit on RHEL 4 >> optimizer_index_caching = 80 >> optimizer_index_cost_adj = 20 >> optimizer_mode = FIRST_ROWS, statistics have been gathered (with >> histograms).
>> Today I noticed the following strange CBO behaviour :
>> User A is the owner of a number of objects of a third-party application. >> User B has access to the objects of user A through role grants and public >> synonyms.
>> The problem is that certain queries run fast with user A (the schema >> owner), >> while they run slow with user B (and all other users with access to the >> application). However, this only happens when the system optimizer_mode = >> FIRST_ROWS ! When I change it to CHOOSE, there are no performance >> differences. It looks like, with an optimizer_mode of FIRST_ROWS, the >> CBO >> chooses different execution plans based on the user who is executing the >> queries ?? >> There are no logon triggers, different user profiles or objects with the >> same name as the public synonyms ...
>> Has anyone else seen this behaviour ? I haven't found the time yet to do >> a >> 10053 trace, the application is rather complex and generates a large >> amount >> of queries. For now, I changed the optimizer_mode to CHOOSE, but I want >> to >> keep FIRST_ROWS for a number of other applications in the same database >> ...
>> Matthias
> Considering you have gathered histograms ... > Are you using bind variables or cursor_sharing=SIMILAR/FORCE? > My first impression is that the behavior you are seeing is > related to bind variable peeking.
> Anurag
Yes, the application uses bind variables, but cursor_sharing is not set through alter session or after-logon trigger statements. So I can't explain why the same query runs fast with user A and slow with user B.
> Oracle 10.2.0.3.0 64-bit on RHEL 4 > optimizer_index_caching = 80 > optimizer_index_cost_adj = 20 > optimizer_mode = FIRST_ROWS, statistics have been gathered (with > histograms).
> Today I noticed the following strange CBO behaviour :
> User A is the owner of a number of objects of a third-party application. > User B has access to the objects of user A through role grants and public > synonyms.
> The problem is that certain queries run fast with user A (the schema owner), > while they run slow with user B (and all other users with access to the > application). However, this only happens when the system optimizer_mode = > FIRST_ROWS ! When I change it to CHOOSE, there are no performance > differences. It looks like, with an optimizer_mode of FIRST_ROWS, the CBO > chooses different execution plans based on the user who is executing the > queries ?? > There are no logon triggers, different user profiles or objects with the > same name as the public synonyms ...
> Has anyone else seen this behaviour ? I haven't found the time yet to do a > 10053 trace, the application is rather complex and generates a large amount > of queries. For now, I changed the optimizer_mode to CHOOSE, but I want to > keep FIRST_ROWS for a number of other applications in the same database ...
> Matthias
I recall reading something that I believe Tom Kyte authored (may have been in one of his books) that using synonyms will cause a query to execute more slowly than it would using the actual object's schema and name (the schema owner would not need to perform a synonym lookup). The data dictionary must be checked a couple times to look up the actual object name and permissions. A lot of the SQL statements that check the data dictionary will contain a RULE hint, but I don't believe that has any effect on Oracle 10.2. FIRST_ROWS can make the optimizer use a less than ideal index to retrieve the first row quickly - I wonder if this has an effect on the dictionary lookups?
Have executed one of these statements? EXEC DBMS_STATS.GATHER_FIXED_OBJECT_STATS EXEC DBMS_STATS.GATHER_DICTIONARY_STATS
You can try a 10046 trace at level 8, and look at the wait events where dep is 1 or greater to see if it is the additional data dictionary lookups that are hurting performance.
Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
> > Today I noticed the following strange CBO behaviour :
> > User A is the owner of a number of objects of a third-party application. > > User B has access to the objects of user A through role grants and public > > synonyms.
> > The problem is that certain queries run fast with user A (the schema owner), > > while they run slow with user B (and all other users with access to the > > application). However, this only happens when the system optimizer_mode = > > FIRST_ROWS ! When I change it to CHOOSE, there are no performance > > differences. It looks like, with an optimizer_mode of FIRST_ROWS, the CBO > > chooses different execution plans based on the user who is executing the > > queries ?? > > There are no logon triggers, different user profiles or objects with the > > same name as the public synonyms ...
> > Has anyone else seen this behaviour ? I haven't found the time yet to do a > > 10053 trace, the application is rather complex and generates a large amount > > of queries. For now, I changed the optimizer_mode to CHOOSE, but I want to > > keep FIRST_ROWS for a number of other applications in the same database ...
You can't do an alter session? (If so, I would sympathize, as it is difficult in the app I use.)
> > Matthias
> I recall reading something that I believe Tom Kyte authored (may have > been in one of his books) that using synonyms will cause a query to > execute more slowly than it would using the actual object's schema and > name (the schema owner would not need to perform a synonym lookup). > The data dictionary must be checked a couple times to look up the > actual object name and permissions. A lot of the SQL statements that > check the data dictionary will contain a RULE hint, but I don't > believe that has any effect on Oracle 10.2. FIRST_ROWS can make the > optimizer use a less than ideal index to retrieve the first row > quickly - I wonder if this has an effect on the dictionary lookups?
That one made my face red years ago as I realized it was the suddenly obvious reason cpu's were burning on a particular app. Don't know if the details changed for more recent Oracle versions, but at least the concept should hold.
> Have executed one of these statements? > EXEC DBMS_STATS.GATHER_FIXED_OBJECT_STATS > EXEC DBMS_STATS.GATHER_DICTIONARY_STATS
> You can try a 10046 trace at level 8, and look at the wait events > where dep is 1 or greater to see if it is the additional data > dictionary lookups that are hurting performance.
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc
jg -- @home.com is bogus. "Sometimes when working onsite at the client, you need to do the stuff a little faster and not go into reading some docs or something like that." - SL
> >> Today I noticed the following strange CBO behaviour :
> >> User A is the owner of a number of objects of a third-party application. > >> User B has access to the objects of user A through role grants and public > >> synonyms.
> >> The problem is that certain queries run fast with user A (the schema > >> owner), > >> while they run slow with user B (and all other users with access to the > >> application). However, this only happens when the system optimizer_mode = > >> FIRST_ROWS ! When I change it to CHOOSE, there are no performance > >> differences. It looks like, with an optimizer_mode of FIRST_ROWS, the > >> CBO > >> chooses different execution plans based on the user who is executing the > >> queries ?? > >> There are no logon triggers, different user profiles or objects with the > >> same name as the public synonyms ...
> >> Has anyone else seen this behaviour ? I haven't found the time yet to do > >> a > >> 10053 trace, the application is rather complex and generates a large > >> amount > >> of queries. For now, I changed the optimizer_mode to CHOOSE, but I want > >> to > >> keep FIRST_ROWS for a number of other applications in the same database > >> ...
> >> Matthias
> > Considering you have gathered histograms ... > > Are you using bind variables or cursor_sharing=SIMILAR/FORCE? > > My first impression is that the behavior you are seeing is > > related to bind variable peeking.
> > Anurag
> Yes, the application uses bind variables, but cursor_sharing is not set > through alter session or after-logon trigger statements. > So I can't explain why the same query runs fast with user A and slow with > user B.
Same reason Bind Variable Peeking. Bind Variable Peeking happens when you use binds. So one user parsed the sql using a value which occurs frequently and its explain plan changed to something like a full table scan for ALL subsequent variations of the query.
Try this test yourself:
create table peekt (a number, b number); create index peekt_idx on peekt (a); insert into peekt select rownum, rownum from dba_objects where rownum < 100; insert into peekt select 100, rownum from dba_objects where rownum < 1000;
So we have a value "100" which occurs very frequently.
Now run the following test in sqlplus (Note; You have to use sql_trace or 10046 event to find out the real execution plan):
alter session set sql_trace=true; exec :v := 1 select /* PLAN_1 */ * from peekt where a = :v; exec :v := 100 select /* PLAN_1 */ * from peekt where a = :v; exec :v := 100 select /* PLAN_2 */ * from peekt where a = :v; exec :v := 1 select /* PLAN_2 */ * from peekt where a = :v;
Now grep the trace file to fid what the execution plans were: In my test database (Oracle 9.2.0.8, optimizer_method choose, cursor_sharing exact):
-> egrep '(BEGIN :v|select |STAT )' mysid_ora_26754.trc BEGIN :v := 1; END; select /* PLAN_1 */ * from peekt where a = :v STAT #1 id=1 cnt=1 pid=0 pos=1 obj=1034252 op='TABLE ACCESS BY INDEX ROWID PEEKT ' STAT #1 id=2 cnt=1 pid=1 pos=1 obj=1034253 op='INDEX RANGE SCAN PEEKT_IDX ' BEGIN :v := 100; END; select /* PLAN_1 */ * from peekt where a = :v STAT #1 id=1 cnt=1098 pid=0 pos=1 obj=1034252 op='TABLE ACCESS BY INDEX ROWID PEEKT ' STAT #1 id=2 cnt=1098 pid=1 pos=1 obj=1034253 op='INDEX RANGE SCAN PEEKT_IDX '
BEGIN :v := 100; END; select /* PLAN_2 */ * from peekt where a = :v STAT #1 id=1 cnt=1098 pid=0 pos=1 obj=1034252 op='TABLE ACCESS FULL PEEKT ' BEGIN :v := 1; END; select /* PLAN_2 */ * from peekt where a = :v STAT #1 id=1 cnt=1 pid=0 pos=1 obj=1034252 op='TABLE ACCESS FULL PEEKT '
As you see, the peeking happened on the first hard parse and execution plan did not change subsequently. The plan itself varied upon the first value used when the peeking happened.
So in your database if you do not gather histograms, then the queries should perform consistently from user to user. If your app uses binds exclusively, then gathering histograms should be done in rare case when fully justified. Or .. you might want to use literals for query which operate on skewed columns and require histograms. ... there might be other options too..
> > >> Today I noticed the following strange CBO behaviour :
> > >> User A is the owner of a number of objects of a third-party application. > > >> User B has access to the objects of user A through role grants and public > > >> synonyms.
> > >> The problem is that certain queries run fast with user A (the schema > > >> owner), > > >> while they run slow with user B (and all other users with access to the > > >> application). However, this only happens when the system optimizer_mode = > > >> FIRST_ROWS ! When I change it to CHOOSE, there are no performance > > >> differences. It looks like, with an optimizer_mode of FIRST_ROWS, the > > >> CBO > > >> chooses different execution plans based on the user who is executing the > > >> queries ?? > > >> There are no logon triggers, different user profiles or objects with the > > >> same name as the public synonyms ...
> > >> Has anyone else seen this behaviour ? I haven't found the time yet to do > > >> a > > >> 10053 trace, the application is rather complex and generates a large > > >> amount > > >> of queries. For now, I changed the optimizer_mode to CHOOSE, but I want > > >> to > > >> keep FIRST_ROWS for a number of other applications in the same database > > >> ...
> > >> Matthias
> > > Considering you have gathered histograms ... > > > Are you using bind variables or cursor_sharing=SIMILAR/FORCE? > > > My first impression is that the behavior you are seeing is > > > related to bind variable peeking.
> > > Anurag
> > Yes, the application uses bind variables, but cursor_sharing is not set > > through alter session or after-logon trigger statements. > > So I can't explain why the same query runs fast with user A and slow with > > user B.
> Same reason Bind Variable Peeking. Bind Variable Peeking happens > when you use binds. So one user parsed the sql using a value > which occurs frequently and its explain plan changed to something like > a full table scan > for ALL subsequent variations of the query.
> Try this test yourself:
> create table peekt (a number, b number); > create index peekt_idx on peekt (a); > insert into peekt select rownum, rownum from dba_objects where rownum > < 100; > insert into peekt select 100, rownum from dba_objects where rownum < > 1000;
> So we have a value "100" which occurs very frequently.
> Now run the following test in sqlplus (Note; You have to use sql_trace > or 10046 event to find out > the real execution plan):
> alter session set sql_trace=true; > exec :v := 1 > select /* PLAN_1 */ * from peekt where a = :v; > exec :v := 100 > select /* PLAN_1 */ * from peekt where a = :v; > exec :v := 100 > select /* PLAN_2 */ * from peekt where a = :v; > exec :v := 1 > select /* PLAN_2 */ * from peekt where a = :v;
> Now grep the trace file to fid what the execution plans were: > In my test database (Oracle 9.2.0.8, optimizer_method choose, > cursor_sharing exact):
> -> egrep '(BEGIN :v|select |STAT )' mysid_ora_26754.trc > BEGIN :v := 1; END; > select /* PLAN_1 */ * from peekt where a = :v > STAT #1 id=1 cnt=1 pid=0 pos=1 obj=1034252 op='TABLE ACCESS BY INDEX > ROWID PEEKT ' > STAT #1 id=2 cnt=1 pid=1 pos=1 obj=1034253 op='INDEX RANGE SCAN > PEEKT_IDX ' > BEGIN :v := 100; END; > select /* PLAN_1 */ * from peekt where a = :v > STAT #1 id=1 cnt=1098 pid=0 pos=1 obj=1034252 op='TABLE ACCESS BY > INDEX ROWID PEEKT ' > STAT #1 id=2 cnt=1098 pid=1 pos=1 obj=1034253 op='INDEX RANGE SCAN > PEEKT_IDX '
> BEGIN :v := 100; END; > select /* PLAN_2 */ * from peekt where a = :v > STAT #1 id=1 cnt=1098 pid=0 pos=1 obj=1034252 op='TABLE ACCESS FULL > PEEKT ' > BEGIN :v := 1; END; > select /* PLAN_2 */ * from peekt where a = :v > STAT #1 id=1 cnt=1 pid=0 pos=1 obj=1034252 op='TABLE ACCESS FULL PEEKT > '
> As you see, the peeking happened on the first hard parse and execution > plan did not change subsequently. The plan itself varied upon the > first value used > when the peeking happened.
> So in your database if you do not gather histograms, then the queries > should > perform consistently from user to user. If your app uses binds > exclusively, then > gathering histograms should be done in rare case when fully justified. > Or .. you might want to use literals for query which operate on skewed > columns and require histograms. > ... there might be other options too..
> Anurag
A key thing I forgot to mention: I had gathered stats on the table like this (before running the test): exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'PEEKT',cascade=>true, method_opt => 'FOR ALL COLUMNS SIZE AUTO')
> That one made my face red years ago as I realized it was the suddenly > obvious reason cpu's were burning on a particular app. Don't know if > the details changed for more recent Oracle versions, but at least the > concept should hold.
That is quite marginal. reduced dictionary cache load with modern computer is not so important. There are many far more important things to consider than that.
End users will not see any difference, so do not bother about this.
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 ?
> 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.
> On Mar 8, 2:38 pm, "Matthias Hoys" <a...@spam.com> 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 ?
>>> 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 damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
>>>> 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 > damor...@x.washington.edu > (replace x with u to respond) > Puget Sound Oracle Users Group
On Mar 10, 6:20 pm, "Matthias Hoys" <a...@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.
> On Mar 10, 6:20 pm, "Matthias Hoys" <a...@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.
> On Mar 10, 6:20 pm, "Matthias Hoys" <a...@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 :-)
> "Charles Hooper" <hooperc2...@yahoo.com> wrote in message > news:1173626542.709090.31890@8g2000cwh.googlegroups.com... > > On Mar 10, 6:20 pm, "Matthias Hoys" <a...@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.
Charles Hooper wrote: > 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.
Every time I have seen this behaviour it came down to one of two issues:
1. query_rewrite not set to trusted 2. a database character set not supported
If anyone has an example of this not working I would appreciate receiving it off-line.
Thank you. -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> Charles Hooper wrote: > > 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.
> Every time I have seen this behaviour it came down to one of two > issues:
> 1. query_rewrite not set to trusted > 2. a database character set not supported
> If anyone has an example of this not working I would appreciate > receiving it off-line.
> Thank you. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu > (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org
I threw together a quick demonstration of the problem that I was encountering, just adding a hint and leaving the rest of the SQL statement alone.
The set up: CREATE TABLE T1 ( C1 NUMBER(12), C2 NUMBER(12));
CREATE INDEX IND_JT1 ON T1(C1); CREATE INDEX IND_JT2 ON T1(C2,C1);
Insert 10,000 rows, with the second column cycling between 0 and 49: INSERT INTO T1 SELECT ROWNUM, MOD(ROWNUM,50) FROM DBA_OBJECTS WHERE ROWNUM<=10000;
Gather the statistics for the table and indexes: EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T1',CAS CADE=>TRUE);
Make certain that the environment is set up to handle rewrite equivalence: ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
Let DBMS Xplan report additional information: ALTER SESSION SET STATISTICS_LEVEL=ALL;
The first test, just a plain query: SELECT * FROM T1 WHERE C2=5;
Let's see the plan: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
SELECT * FROM T1 WHERE C2=5
Plan hash value: 3586113557
--------------------------------------------------------------------------- ----------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | --------------------------------------------------------------------------- ----------- |* 1 | INDEX RANGE SCAN| IND_JT2 | 1 | 200 | 200 | 00:00:00.01 | 16 | --------------------------------------------------------------------------- -----------
Predicate Information (identified by operation id): 1 - access("C2"=5)
Oracle used the IND_JT2 index to satisfy the query, which was expected.
Now, let's try a hint just to make certain that the hint syntax is correct: SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
SQL_ID 3hatv8v3nfu05, child number 0 ------------------------------------- SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=5)
Oracle now performed a full table scan to satisfy the query, as requested.
Now, let's try to create an equivalence to automatically insert the hint into the SQL statement: BEGIN SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( NAME => 'T1_EQ', SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', VALIDATE => FALSE, REWRITE_MODE => 'TEXT_MATCH' ); END; /
ERROR at line 1: ORA-30394: source statement identical to the destination statement ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 ORA-06512: at line 2
According to Oracle, the two SQL statements are already the same, one just has a comment, according to the above. Maybe there is something wrong with the syntax that I used.
Let's try again, this time make certain that the two SQL statements do not differ by just a comment (or white space): BEGIN SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( NAME => 'T1_EQ', SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=6', VALIDATE => FALSE, REWRITE_MODE => 'TEXT_MATCH' ); END; /
PL/SQL procedure successfully completed.
The above executed, so I just had to change the meaning of the SQL statement. Now, let's see if it works: SELECT * FROM T1 WHERE C2=5;
Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("C2"=6)
It looks like Oracle will recognize the hint if we change the meaning of the SQL statement.
If you see something that I did wrong, please let me know. This would be a very useful feature for me if I did not have to change the meaning of the SQL statement in order for query rewrite to accept a hint.
Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
Charles Hooper wrote: > On Mar 11, 4:45 pm, DA Morgan <damor...@psoug.org> wrote: >> Charles Hooper wrote: >>> 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. >> Every time I have seen this behaviour it came down to one of two >> issues:
>> 1. query_rewrite not set to trusted >> 2. a database character set not supported
>> If anyone has an example of this not working I would appreciate >> receiving it off-line.
>> Thank you. >> -- >> Daniel A. Morgan >> University of Washington >> damor...@x.washington.edu >> (replace x with u to respond) >> Puget Sound Oracle Users Groupwww.psoug.org
> I threw together a quick demonstration of the problem that I was > encountering, just adding a hint and leaving the rest of the SQL > statement alone.
> The set up: > CREATE TABLE T1 ( > C1 NUMBER(12), > C2 NUMBER(12));
> CREATE INDEX IND_JT1 ON T1(C1); > CREATE INDEX IND_JT2 ON T1(C2,C1);
> Insert 10,000 rows, with the second column cycling between 0 and 49: > INSERT INTO > T1 > SELECT > ROWNUM, > MOD(ROWNUM,50) > FROM > DBA_OBJECTS > WHERE > ROWNUM<=10000;
> Gather the statistics for the table and indexes: > EXEC > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T1',CAS CADE=>TRUE);
> Make certain that the environment is set up to handle rewrite > equivalence: > ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; > ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
> Let DBMS Xplan report additional information: > ALTER SESSION SET STATISTICS_LEVEL=ALL;
> The first test, just a plain query: > SELECT > * > FROM > T1 > WHERE > C2=5;
> Let's see the plan: > SELECT > * > FROM > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
> SELECT * FROM T1 WHERE C2=5
> Plan hash value: 3586113557
> --------------------------------------------------------------------------- ----------- > | Id | Operation | Name | Starts | E-Rows | A-Rows | A- > Time | Buffers | > --------------------------------------------------------------------------- ----------- > |* 1 | INDEX RANGE SCAN| IND_JT2 | 1 | 200 | 200 | > 00:00:00.01 | 16 | > --------------------------------------------------------------------------- -----------
> Predicate Information (identified by operation id): > 1 - access("C2"=5)
> Oracle used the IND_JT2 index to satisfy the query, which was > expected.
> Now, let's try a hint just to make certain that the hint syntax is > correct: > SELECT /*+ FULL(T1) */ > * > FROM > T1 > WHERE > C2=5;
> SELECT > * > FROM > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
> SQL_ID 3hatv8v3nfu05, child number 0 > ------------------------------------- > SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5
> Predicate Information (identified by operation id): > --------------------------------------------------- > 1 - filter("C2"=5)
> Oracle now performed a full table scan to satisfy the query, as > requested.
> Now, let's try to create an equivalence to automatically insert the > hint into the SQL statement: > BEGIN > SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE > ( NAME => 'T1_EQ', > SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', > DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', > VALIDATE => FALSE, > REWRITE_MODE => 'TEXT_MATCH' ); > END; > /
> ERROR at line 1: > ORA-30394: source statement identical to the destination statement > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 > ORA-06512: at line 2
> According to Oracle, the two SQL statements are already the same, one > just has a comment, according to the above. Maybe there is something > wrong with the syntax that I used.
> Let's try again, this time make certain that the two SQL statements do > not differ by just a comment (or white space): > BEGIN > SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE > ( NAME => 'T1_EQ', > SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', > DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=6', > VALIDATE => FALSE, > REWRITE_MODE => 'TEXT_MATCH' ); > END; > /
> PL/SQL procedure successfully completed.
> The above executed, so I just had to change the meaning of the SQL > statement. Now, let's see if it works: > SELECT > * > FROM > T1 > WHERE > C2=5;
> Predicate Information (identified by operation id): > --------------------------------------------------- > 1 - filter("C2"=6)
> It looks like Oracle will recognize the hint if we change the meaning > of the SQL statement.
> If you see something that I did wrong, please let me know. This would > be a very useful feature for me if I did not have to change the > meaning of the SQL statement in order for query rewrite to accept a > hint.
> Charles Hooper > PC Support Specialist > K&M Machine-Fabricating, Inc.
Retry your statement with VALIDATE=>TRUE and you will see why.
Oracle will likely send you the following personal note.
ERROR at line 1: ORA-30394: source statement identical to the destination statement ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 ORA-06512: at line 2 -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
>> 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.
> Every time I have seen this behaviour it came down to one of two > issues:
> 1. query_rewrite not set to trusted > 2. a database character set not supported
> If anyone has an example of this not working I would appreciate > receiving it off-line.
> Thank you. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu > (replace x with u to respond) > Puget Sound Oracle Users Group > www.psoug.org
I tried this:
begin SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE ( 'rs3 equivalence', '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' , '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_TYPE ART,HTS_ASSAY_PROTOCOL AP ,HTS_ASSAY A ,HTS_COMPOUND_LOT CL, HTS_ASSAY_RESULT R 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', FALSE, 'TEXT_MATCH'); end;
So basically, I'm rewriting the query with HTS_ASSAY_RESULT as the last table in the FROM part of the query, because this gives me a good execution plan.
But I received the following error:
ORA-30389: the source statement is not compatible with the destination statement ORA-00907: missing right parenthesis ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 ORA-06512: at line 2
My question : why ? Can't you switch the order of tables in the destination query ?
I also did: alter session set query_rewrite_integrity = 'TRUSTED'; grant execute on DBMS_ADVANCED_REWRITE to matthiash; GRANT CREATE MATERIALIZED VIEW TO matthiash;
Our character set is WE8MSWIN1252, but I couldn't find a list of supported character sets.
> Charles Hooper wrote: > > On Mar 11, 4:45 pm, DA Morgan <damor...@psoug.org> wrote: > >> Charles Hooper wrote: > >>> 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. > >> Every time I have seen this behaviour it came down to one of two > >> issues:
> >> 1. query_rewrite not set to trusted > >> 2. a database character set not supported
> >> If anyone has an example of this not working I would appreciate > >> receiving it off-line.
> >> Thank you. > >> -- > >> Daniel A. Morgan > >> University of Washington > >> damor...@x.washington.edu > >> (replace x with u to respond) > >> Puget Sound Oracle Users Groupwww.psoug.org
> > I threw together a quick demonstration of the problem that I was > > encountering, just adding a hint and leaving the rest of the SQL > > statement alone.
> > The set up: > > CREATE TABLE T1 ( > > C1 NUMBER(12), > > C2 NUMBER(12));
> > CREATE INDEX IND_JT1 ON T1(C1); > > CREATE INDEX IND_JT2 ON T1(C2,C1);
> > Insert 10,000 rows, with the second column cycling between 0 and 49: > > INSERT INTO > > T1 > > SELECT > > ROWNUM, > > MOD(ROWNUM,50) > > FROM > > DBA_OBJECTS > > WHERE > > ROWNUM<=10000;
> > Gather the statistics for the table and indexes: > > EXEC > > DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'TABLE_OWNER_HERE',TABNAME=>'T1',CAS CADE=>TRUE);
> > Make certain that the environment is set up to handle rewrite > > equivalence: > > ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE; > > ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;
> > Let DBMS Xplan report additional information: > > ALTER SESSION SET STATISTICS_LEVEL=ALL;
> > The first test, just a plain query: > > SELECT > > * > > FROM > > T1 > > WHERE > > C2=5;
> > Let's see the plan: > > SELECT > > * > > FROM > > TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
> > Predicate Information (identified by operation id): > > 1 - access("C2"=5)
> > Oracle used the IND_JT2 index to satisfy the query, which was > > expected.
> > Now, let's try a hint just to make certain that the hint syntax is > > correct: > > SELECT /*+ FULL(T1) */ > > * > > FROM > > T1 > > WHERE > > C2=5;
> > Predicate Information (identified by operation id): > > --------------------------------------------------- > > 1 - filter("C2"=5)
> > Oracle now performed a full table scan to satisfy the query, as > > requested.
> > Now, let's try to create an equivalence to automatically insert the > > hint into the SQL statement: > > BEGIN > > SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE > > ( NAME => 'T1_EQ', > > SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', > > DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', > > VALIDATE => FALSE, > > REWRITE_MODE => 'TEXT_MATCH' ); > > END; > > /
> > ERROR at line 1: > > ORA-30394: source statement identical to the destination statement > > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 > > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 > > ORA-06512: at line 2
> > According to Oracle, the two SQL statements are already the same, one > > just has a comment, according to the above. Maybe there is something > > wrong with the syntax that I used.
> > Let's try again, this time make certain that the two SQL statements do > > not differ by just a comment (or white space): > > BEGIN > > SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE > > ( NAME => 'T1_EQ', > > SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', > > DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=6', > > VALIDATE => FALSE, > > REWRITE_MODE => 'TEXT_MATCH' ); > > END; > > /
> > PL/SQL procedure successfully completed.
> > The above executed, so I just had to change the meaning of the SQL > > statement. Now, let's see if it works: > > SELECT > > * > > FROM > > T1 > > WHERE > > C2=5;
> > Predicate Information (identified by operation id): > > --------------------------------------------------- > > 1 - filter("C2"=6)
> > It looks like Oracle will recognize the hint if we change the meaning > > of the SQL statement.
> > If you see something that I did wrong, please let me know. This would > > be a very useful feature for me if I did not have to change the > > meaning of the SQL statement in order for query rewrite to accept a > > hint.
> > Charles Hooper > > PC Support Specialist > > K&M Machine-Fabricating, Inc.
> Retry your statement with VALIDATE=>TRUE and you will see why.
> Oracle will likely send you the following personal note.
> ERROR at line 1: > ORA-30394: source statement identical to the destination statement > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 > ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 > ORA-06512: at line 2 > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu > (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org
You result was identical to the result that I originally posted - the two statements only differ by a comment - the comment just happens to be a hint:
With validate at FALSE: SQL> BEGIN 2 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE 3 ( NAME => 'T1_EQ', 4 SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', 5 DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', 6 VALIDATE => FALSE, 7 REWRITE_MODE => 'TEXT_MATCH' ); 8 END; 9 / BEGIN * ERROR at line 1: ORA-30394: source statement identical to the destination statement ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 ORA-06512: at line 2
With validate at TRUE: SQL> BEGIN 2 SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE 3 ( NAME => 'T1_EQ', 4 SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5', 5 DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5', 6 VALIDATE => TRUE, 7 REWRITE_MODE => 'TEXT_MATCH' ); 8 END; 9 / BEGIN * ERROR at line 1: ORA-30394: source statement identical to the destination statement ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 29 ORA-06512: at "SYS.DBMS_ADVANCED_REWRITE", line 185 ORA-06512: at line 2
It would be nice to use DBMS_ADVANCED_REWRITE to add a hint, such as (for the OP): /*+ LEADING (HTS_ASSAY HTS_COMPOUND_LOT HTS_ASSAY_RESULT) */
And leave the rest of the SQL statement alone.
Charles Hooper PC Support Specialist K&M Machine-Fabricating, Inc.
Sorry, can't see what this Metalink note has to do with DBMS_ADVANCED_REWRITE ? I meant that I was looking for a list of character sets that dbms_advanced_rewrite supports, since Daniel mentioned in another post that was one of the causes of problems with the package. But maybe I should create a SR and ask the Oracle guys :-)
