Databases Reference
In-Depth Information
Tip
By default, the AWR contains only high resource-consuming queries. You can modify this behavior and
ensure that a specific SQL statement is included in every snapshot (regardless of its resource consumption) by
adding it to the AWR via the following code:
SQL> exec dbms_workload_repository.add_colored_sql('98u3gf0xzq03f');
SQL Tuning Set Name
If you have the requirement of running the SQL Tuning Advisor against multiple SQL queries, then a SQL
tuning set is required. To create a tuning task using a SQL tuning set as input, do so as follows:
SQL> variable mytt varchar2(30);
SQL> exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'IO_STS');
SQL> print :mytt
Execute DBMS_SQLTUNE and View the Advice
After you've created a tuning task, you can generate and view advice by executing the
EXECUTE_TUNING_TASK
procedure and provide to it the name of your tuning task, like so:
SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test');
Next, a report is generated that displays the tuning advice.
SQL> set long 10000 longchunksize 10000 linesize 132 pagesize 200
SQL> select dbms_sqltune.report_tuning_task('tune_test') from dual;
Here is a small snippet of the output:
2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 97.98%)
------------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index MV_MAINT.IDX$$_21E10001 on MV_MAINT.EMP("DEPT_ID");
Note that this output has a specific recommendation for adding an index. You'll need to test the
recommendations to ensure that performance does improve before implementing them in a production
environment.
