Databases Reference
In-Depth Information
Note
Starting with Oracle Database 11
g
Release 2, the DBMS_AUTO_SQLTUNE package should be used
(instead of DBMS_SQLTUNE) for administrating automatic SQL tuning features. If you are using an older release of
Oracle, use DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK to view automated SQL tuning advice.
Depending on the activity in your database, there may be a great deal of output. Here's a small
sample of output from a very active database:
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 99
Number of SQLs in the Report : 3
Number of SQLs with Findings : 3
Number of SQLs with SQL profiles recommended : 2
Number of SQLs with Index Findings : 1
Looking further down in the output, here is the specific advice in regards to creating an index:
Recommendation (estimated benefit: 99.98%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index STAR2.IDX$$_17F5F0001 on
STAR2.D_PRODUCT_INSTANCES("CREATE_DTT","D_PRODUCT_INSTANCE_ID");
Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow you to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
This output provides a strong recommendation to create an index. Before following the advice, we
recommend that you create the index in a test or development environment and verify that the benefit is
worth the cost. Additionally, you should adjust the index creation script so that you follow your naming,
storage, and tablespace placement standards.
