Database Reference
In-Depth Information
■
to use both the SQL tuning advisor and the
dbms_sqltune
package, the tuning pack and the Diagnostic pack
must be licensed. remember, these packs are available only for enterprise edition.
Note
To start a tuning task, you have to call the
create_tuning_task
function in the
dbms_sqltune
package and pass
as a parameter one of the following (the function is overloaded four times to accept different kinds of parameters):
•
The text of a SQL statement
sql_id
) to a SQL statement stored in the library cache
•
The reference (
sql_id
) to a SQL statement stored in the Automatic Workload Repository
•
The reference (
•
The name of a SQL tuning set
SQL tUNING SetS
Simply put,
SQL tuning sets
are objects that store a set of SQL statements along with their associated execution
environments, execution statistics, and, optionally, execution plans. SQL tuning sets are managed with the
dbms_sqltune
package.
to use SQL tuning sets, either the tuning pack or real application testing and, therefore, enterprise edition,
are required.
You can find more information about SQL tuning sets in the
Oracle Database Performance Tuning Guide
manual
(up to and including version 11.2), or in the
Oracle Database SQL Tuning Guide
manual (beginning with version 12.1).
To simplify the execution of the
create_tuning_task
function in the
dbms_sqltune
package by taking as a
parameter a single SQL statement, I wrote the
tune_last_statement.sql
script. The idea is that you execute the SQL
statement that you want to have analyzed in SQL*Plus and then call the script without parameters. The script gets the
reference (
sql_id
) of the last SQL statement executed by the current session from the
v$session
view and then creates
and executes a tuning task referencing it. The central part of the script is the following anonymous PL/SQL block:
DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
BEGIN
SELECT prev_sql_id INTO l_sql_id
FROM v$session
WHERE audsid = sys_context('userenv','sessionid');
:tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
dbms_sqltune.execute_tuning_task(:tuning_task);
END;
The tuning task externalizes the output of its analysis in several data dictionary views. Instead of querying the
views directly, which is a bit bothersome, you can use the
report_tuning_task
function in the
dbms_sqltune
package
to generate a detailed report about the analysis. The following query shows an example of its utilization. Note that to
reference the tuning task, the name of the tuning task returned by the previous PL/SQL block is used:
