Databases Reference
In-Depth Information
test_tuning_set
is tuned ordered by buffer gets in the previous example.
We can query the view
USER_ADVISOR_TASK
to check the status of the task while it's
running, or the view
V$ADVISOR_PROGRESS
to inspect the execution progress.
We can also interrupt a tuning task using the following procedure:
DBMS_SQLTUNE.INTERRUPT_TUNING_TASK( -
task_name => 'test_tuning_set');
To resume an interrupted task, we have the following corresponding procedure:
DBMS_SQLTUNE.RESUME_TUNING_TASK( -
task_name => 'test_tuning_set',
basic_filter => NULL);
See also
F
The
Creating
SQL
Tuning
Sets
recipe in this chapter
Configuring and using SQL Baselines
We have seen the importance of execution plan stability in
Managing
stored
outlines
.
The disadvantage (by design) of using stored outlines is in the rigidity—we are sure that
our execution plans don't change—so the performance doesn't deteriorate. However, due
to schema or data changes, there could be a better execution plan. We are bound to our
execution plan that is stored in the outlines, and we cannot benefit from the improvements.
SQL Plan Management with SQL Plan Baselines, a feature new to Oracle Database 11
g
, helps
us in obtaining planning stability without losing the opportunity for performance improvements.
Getting ready
To create SQL Baselines we need the
ADMINISTER
SQL
MANAGEMENT
OBJECT
privilege.
Connect as
SYSDBA
and grant permission to the user
SH
:
CONNECT / AS SYSDBA
GRANT ADMINISTER SQL MANAGEMENT OBJECT TO SH;
