CBO & different execution plans
Matthias Hoys
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
Anurag Varma
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
Matthias Hoys
"Anurag Varma" <avor...@gmail.com> wrote in message
news:1173301649.1...@j27g2000cwj.googlegroups.com...
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.
Charles Hooper
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.
joel garry
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?
Also see http://www.ixora.com.au/q+a/library.htm#10_02_2000
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
Anurag Varma
> "Anurag Varma" <avora...@gmail.com> wrote in message
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
Anurag Varma
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')
Anurga
Jack
"joel garry" <joel-...@home.com> wrote in message
news:1173312651.3...@p10g2000cwp.googlegroups.com...
>
> Also see http://www.ixora.com.au/q+a/library.htm#10_02_2000
>
> 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.
>
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.
Matthias Hoys
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
Charles Hooper
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.
Matthias Hoys
"Charles Hooper" <hoope...@yahoo.com> wrote in message
news:1173392430....@h3g2000cwc.googlegroups.com...
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
DA Morgan
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
damo...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Matthias Hoys
"DA Morgan" <damo...@psoug.org> wrote in message
news:11736256...@bubbleator.drizzle.com...
Thanks for the tip, looks like this is what I need. I'll check it out
tomorrow.
Charles Hooper
> 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.
Matthias Hoys
Nice tip, thanks, will try this tomorrow.
Matthias Hoys
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
Charles Hooper
> "Charles Hooper" <hooperc2...@yahoo.com> wrote in message
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.
DA Morgan
> 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.
Charles Hooper
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',CASCADE=>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
Plan hash value: 3617692013
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 |
00:00:00.01 |37 |
------------------------------------------------------------------------------------
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;
C1 C2
---------- ----------
1856 6
1906 6
1956 6
2006 6
...
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
SELECT * FROM T1 WHERE C2=5
Plan hash value: 3617692013
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers |
------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 |
00:00:00.01 |37 |
------------------------------------------------------------------------------------
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.
DA Morgan
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
Matthias Hoys
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.
Matthias
joel garry
>
> Our character set is WE8MSWIN1252, but I couldn't find a list of supported
> character sets.
>
You can get some idea from the appendices in the Globalization Support
Guide.
Metalink Note:306411.1 is pretty interesting, explaining what the
future may hold and recommended character sets. Yours is one of them.
jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20070310/news_1b10ams.html
Charles Hooper
>
> > 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
>
> > Plan hash value: 3617692013
>
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time | Buffers |
> > |* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 |
> > 00:00:00.01 |37 |
> > | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> > Time | Buffers |
> > |* 1 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 |
> > 00:00:00.01 |37 |
>
> > 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
> (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.
Matthias Hoys
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.
Yes, I tried that hint with dbms_advanced_rewrite, but it gave the same
error as you have.
Thanks
Matthias
Matthias Hoys
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 :-)
Matthias
DA Morgan
> *
> 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.
I agree. Maybe you could fool the CBO by making some inconsequential
change so that it would see the statement as different. For example:
For example:
WHERE C2+0 = x+0;
Though, of course, that can cause unintended collateral damage.
--
Daniel A. Morgan
University of Washington
DA Morgan
My experience with character sets was that I originally selected
a character set that supported multibyte characters and had to
change to WE8MSWIN1252 to get it to work.
The page: http://www.psoug.org/reference/character_sets.html
is the result of that incident.
Charles Hooper
> Charles Hooper wrote:
> > *
> > 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.
>
> I agree. Maybe you could fool the CBO by making some inconsequential
> change so that it would see the statement as different. For example:
>
> For example:
> WHERE C2+0 = x+0;
>
> Though, of course, that can cause unintended collateral damage.
> --
> Daniel A. Morgan
> University of Washington
It looks like you might have a good work around, as long as the extra
predicate does not significantly affect the cost. Here is an example:
BEGIN
SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE
( NAME => 'T1_EQ2',
SOURCE_STMT => 'SELECT * FROM T1 WHERE C2=5',
DESTINATION_STMT => 'SELECT /*+ FULL(T1) */ * FROM T1 WHERE C2=5
AND SYSDATE=SYSDATE',
VALIDATE => FALSE,
REWRITE_MODE => 'TEXT_MATCH' );
END;
/
SELECT
*
FROM
T1
WHERE
C2=5;
SELECT
*
FROM
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS'));
SQL_ID 7q30ztw0h1w4t, child number 1
-------------------------------------
SELECT * FROM T1 WHERE C2=5
Plan hash value: 3332582666
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers |
-------------------------------------------------------------------------------------
|* 1 | FILTER | | 1 | | 200 |
00:00:00.01 | 37 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 200 | 200 |
00:00:00.01 | 37 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYSDATE@!=SYSDATE@!)
2 - filter("C2"=5)
And now to put the system back to normal:
EXEC SYS.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE('T1_EQ2');
SQL_ID 7q30ztw0h1w4t, child number 0
-------------------------------------
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)
Matthias Hoys
"Matthias Hoys" <an...@spam.com> wrote in message
news:45ef1abf$0$2940$ba62...@news.skynet.be...
By accident, I found the solution for this problem on MetaLink :
Bug 4652274 - Explain Plan Differs With Different Users
It has to do with the init parameter secure_view_merging, which is new since
10gR2. Setting it to FALSE in the spfile and boucing the instance resolved
all my problems ... Now queries on view from another schema have the same
exection plans when executed by the view owner compared to another user.
Matthias
Jonathan Lewis
"Matthias Hoys" <an...@spam.com> wrote in message
news:4617abb4$0$13866$ba62...@news.skynet.be...
>
>>
>
> By accident, I found the solution for this problem on MetaLink :
>
> Bug 4652274 - Explain Plan Differs With Different Users
>
> It has to do with the init parameter secure_view_merging, which is new
> since 10gR2. Setting it to FALSE in the spfile and boucing the instance
> resolved all my problems ... Now queries on view from another schema have
> the same exection plans when executed by the view owner compared to
> another user.
>
> Matthias
>
>
>
Thanks for the feedback.
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html