Question: What
does the dbms_sqldiag_internal package do? Can you
show an example of using dbms_sqldiag_internal to change a
SQL execution plan?
Answer: To use the
dbms_sqldiag_internal package allows you to create your own
SQL patch to direct the SQL optimizer to use a set of hints
when it optimizes a query.
You can use
dbms_sqldiag_internal.i_create_patch to insert hints
into a query whose text you cannot edit. This function lets
you specify a query text and a set of hints to apply to the
query.
Oracle provide an undocumented package called
dbms_sqldiag_internal., which gives you the ability
to manually create a SQL patch for the purpose of adding
hints to SQL from vendor applications, in cases where you
are not able to modify the SQL. This has the same
functionality as optimizer plan stability.
In this example, we intercept the SQL for the query
"select * from bigtable;" in the library cache and apply a
parallel hint to the query:
begin
sys.dbms_sqldiag_internal.i_create_patch(
sql_text => 'select *
from bigtable,
hint_text =>
'parallel(bigtable,31)',
name => 'big_table_parallel_sql_patch');
end;
/
Also see
dbms_sqldiag.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
