Automating SQL Tuning

Use the following query to determine if any Automatic SQL Tuning jobs are enabled:

SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client
ORDER BY client_name;

Run the following query to view the last several times the Automatic SQL Tuning Advisor job
has run:

SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')
FROM dba_advisor_executions
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'
ORDER BY execution_end desc;

When you create a database in Oracle Database 11g or higher, Oracle automatically implements three automatic maintenance jobs:

• Automatic SQL Tuning Advisor
• Automatic Segment Advisor
• Automatic Optimizer Statistics Collection
These tasks are automatically configured to run in maintenance windows. A maintenance window is
a specified time and duration for the task to run. You can view the maintenance window details with this
query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor, optimizer_stats, segment_advisor
FROM dba_autotask_window_clients;

--------------------------------------------------------------------------
DBA_AUTOTASK_CLIENT Statistical information about automatic jobs
DBA_AUTOTASK_CLIENT_HISTORY Window history of job execution
DBA_AUTOTASK_CLIENT_JOB Currently running automatic scheduled jobs
DBA_AUTOTASK_JOB_HISTORY History of automatic scheduled job runs
DBA_AUTOTASK_SCHEDULE Schedule of automated tasks for next 32 days
DBA_AUTOTASK_TASK Information regarding current and past tasks
DBA_AUTOTASK_OPERATION Operations for automated tasks
DBA_AUTOTASK_WINDOW_CLIENTS Displays windows that belong to the MAINTENANCE_WINDOW_GROUP


You’re aware that Oracle automatically runs a daily job that generates SQL tuning advice. You want to view the advice.

If you’re using Oracle Database 11g Release 2 or higher, here’s the quickest way to display automatically generated SQL tuning advice:

SET LINESIZE 80 PAGESIZE 0 LONG 100000
SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

################################################################
Generating a SQL Script to Implement Automatic Tuning Advice
################################################################

You’ve reported on the automatic tuning advice. Now you want to generate a SQL script that can be used to implement tuning advice.

SET LINES 132 PAGESIZE 0 LONG 10000
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK') FROM dual;

Here is a small snippet of the output for this example:

execute dbms_stats.gather_index_stats(ownname => 'STAR2', indname => 'F_CONFIG_P
ROD_INST_FK1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
create index NSESTAR.IDX$$_17F5F0004 on NSESTAR.D_DATES("FISCAL_YEAR","FISCAL_WE
EK_NUMBER_IN_YEAR","DATE_DTT");

--------------------------------------------------------------

The SCRIPT_TUNING_TASK function generates the SQL to implement the advice recommended by the Automatic SQL Tuning job. If the tuning task doesn’t have any advice to give, then there won’t be any SQL statements generated in the output. SYS_AUTO_SQL_TUNING_TASK is the default name of the Automatic SQL Tuning task. If you’re unsure of the details regarding this task, then query the DBA_ADVISOR_LOG view:

select task_name, execution_start from dba_advisor_log
where task_name='SYS_AUTO_SQL_TUNING_TASK'
order by 2;

###############################################################
Modifying Automatic SQL Tuning Features
###############################################################

Use the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure to modify the default behavior of Automatic SQL Tuning. For example, if you want SQL profiles to be automatically accepted, you can do so as follows:

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

You can verify that auto SQL profile accepting is enabled via this query:

SELECT parameter_name, parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name ='ACCEPT_SQL_PROFILES';

To disable automatic acceptance of SQL profiles, pass a FALSE value to the procedure:

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');
END;
/

The DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure allows you to modify the default behavior of the Automatic SQL Tuning job. You can view all of the current settings for Automatic SQL Tuning via this query:

SELECT parameter_name ,parameter_value
FROM dba_advisor_parameters
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND parameter_name IN ('ACCEPT_SQL_PROFILES',
'MAX_SQL_PROFILES_PER_EXEC',
'MAX_AUTO_SQL_PROFILES',
'EXECUTION_DAYS_TO_EXPIRE');

--------------------------------------------------------
ACCEPT_SQL_PROFILE Determines if SQL profiles are automatically accepted
EXECUTION_DAYS_TO_EXPIRE Number of days to save task history
MAX_SQL_PROFILES_PER_EXEC Limit of SQL profiles accepted per execution of tuning task
MAX_AUTO_SQL_PROFILES Maximum limit of SQL profiles automatically accepted

##########################################################
You can also use Enterprise Manager to manage the features regarding Automatic SQL Tuning.
From the main database page, navigate to the Advisor Central page. Next, click the SQL Advisors link.
Now click the Automatic SQL Tuning Results page.

###########################################
Disabling and Enabling Automatic SQL Tuning
###########################################

You want to completely disable and later re-enable the Automatic SQL Tuning job.

Use the DBMS_AUTO_TASK_ADMIN.DISABLE procedure to disable the Automatic SQL Tuning job. This
example disables the Automatic SQL Tuning Advisor job.

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
To re-enable the job, use the ENABLE procedure as shown:

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

BEGIN
dbms_auto_task_admin.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'TUESDAY_WINDOW');
END;
/
You can verify that the window has been disabled via this query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor
FROM dba_autotask_window_clients;

Parameter Description
CLIENT_NAME Name of client; query DBA_AUTOTASK_CLIENT for details.
OPERATION Name of operation; query DBA_AUTOTASK_OPERATION for details.
WINDOW_NAME Operation name of the window

###############################################
Modifying Maintenance Window Attributes
###############################################

You realize that the automatic tasks (such as the Automatic SQL Tuning job) run during regularly scheduled maintenance windows. You want to modify the length of time associated with a maintenance window.

Here’s an example that changes the duration of the Sunday maintenance window to two hours:

BEGIN
dbms_scheduler.set_attribute(
name => 'SUNDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(2, 'hour'));
END;
/

SELECT window_name, next_start_date, duration
FROM dba_scheduler_windows;

The key to understanding how to modify a maintenance window is that it is an attribute of the database job scheduler and therefore must be maintained via the DBMS_SCHEDULER package. When you install Oracle Database 11g, by default three automatic maintenance jobs are configured:
• Automatic SQL Tuning
• Statistics gathering
• Segment advice

These jobs automatically execute in preconfigured daily maintenance windows. A maintenance window consists of a day of the week and the length of time the job runs.
You can view the future one month’s worth of scheduled jobs via this query:

SELECT window_name, to_char(start_time,'dd-mon-yy hh24:mi'), duration
FROM dba_autotask_schedule
ORDER BY start_time;

###############################################################
Creating a SQL Tuning Set Object
###############################################################

You’re working on a performance issue that requires that you analyze a group of SQL statements. Before you process the SQL statements as a set, you need to create a SQL tuning set object.

Use the DBMS_SQLTUNE.CREATE_SQLSET procedure to create a SQL tuning set object—for example:

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'HIGH_IO',
description => 'High disk read tuning set');
END;
/


The prior code creates a tuning set with the name of HIGH_IO. At this point, you have created a named tuning set object. The tuning set does not contain any SQL statements.

A SQL tuning set object must be created before populating a tuning set with SQL statements You can view any defined SQL tuning sets in the database by querying the DBA_SQLSET view:

select id, name, created, statement_count from dba_sqlset;

EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET' );

################################################################
Viewing Resource-Intensive SQL in the AWR
################################################################

The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function can be used to extract SQL stored in the AWR. This particular query selects queries in the AWR between snapshots 8200 and 8201 ordered by the top 10 in the disk reads usage category:

SELECT
sql_id
,substr(sql_text,1,20)
,disk_reads
,cpu_time
,elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(8200,8201,
null, null, 'disk_reads',null, null, null, 10))
ORDER BY disk_reads DESC;

Before you work with SQL tuning sets, it’s critical to understand you can use the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function to retrieve high resource-usage SQL from the AWR. The result sets retrieved by this PL/SQL function can be used as input for populating SQL tuning sets. See Table 11-5 for a description of the SELECT_WORKLOAD_REPOSITORY function parameters. You have a great deal of flexibility in how you use this function. A few examples will help illustrate this. Say you want to retrieve SQL from the AWR that was not parsed by the SYS user. Here is the SQL to do that:

SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(8200,8201,
'parsing_schema_name <> ''SYS''',
NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));

The following example retrieves the top ten queries ranked by buffer gets for non-SYS users:

sql_id
,substr(sql_text,1,20)
,disk_reads
,cpu_time
,elapsed_time
,buffer_gets
,parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
begin_snap => 21730
,end_snap => 22900
,basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'buffer_gets'
,result_limit => 10
));

BEGIN_SNAP Non-inclusive beginning snapshot ID
END_SNAP Inclusive ending snapshot ID
BASELINE_NAME Name of AWR baseline
BASIC_FILTER SQL predicate to filter SQL statements from workload; if not set, then only
SELECT, INSERT, UPDATE, DELETE, MERGE, and CREATE TABLE                                           statements are captured. OBJECT_FILTER Not currently used
                                        RANKING_MEASURE(n) Order by clause on selected SQL statement(s),                                       such as elapsed_time, cpu_time, buffer_gets, disk_reads, and so on; N can be                                          1, 2, or 3.
RESULT_PERCENTAGE Filter for choosing top N% for ranking measure
RESULT_LIMIT Limit of the number of SQL statements returned in the result set.
ATTRIBUTE_LIST List of SQL statement attributes (TYPICAL, BASIC, ALL, and so on)
RECURSIVE_SQL Include/exclude recursive SQL (HAS_RECURSIVE_SQL or NO_RECURSIVE_SQL)

#########################################################
Viewing Resource-Intensive SQL in Memory
#########################################################


Before populating a SQL tuning set, you want to view high-load SQL statements in the cursor cache in memory. You want to eventually use SQL contained in memory as input for populating a SQL tuning set.

Use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to view current high resource-consuming SQL
statements in memory. This query selects SQL statements in memory that have required more than a
million disk reads:

SELECT
sql_id
,substr(sql_text,1,20)
,disk_reads
,cpu_time
,elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000000'))
ORDER BY sql_id;

Before you work with SQL tuning sets, it’s critical to understand you can use the  DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to retrieve high resource-usage SQL from memory. The result set retrieved by this PL/SQL function can be used as input for populating SQL tuning sets. See Table 11-6 for a description of the SELECT_CURSOR_CACHE function parameters.
You have a great deal of flexibility in how you use this function. Here’s an example that selects SQL in memory, but excludes statements parsed by the SYS user and also returns statements with an elapsed time greater than 100,000:

SELECT sql_id, substr(sql_text,1,20)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('parsing_schema_name <> ''SYS''
AND elapsed_time > 100000'))
ORDER BY sql_id;

In the prior query, the SYS keyword is enclosed by two single quotes (in other words, those aren’t
double quotes around SYS). The SQL_TEXT column is truncated to 20 characters so that the output can be
displayed on the page more easily. Here is some sample output:

SELECT *
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''byzwu34haqmh4'''));

Note that the SQL_ID in the prior statement is enclosed by two single quotes (not double quotes). This next example selects the top ten queries in memory in terms of CPU time for non-SYS users:

SELECT
sql_id
,substr(sql_text,1,20)
,disk_reads
,cpu_time
,elapsed_time
,buffer_gets
,parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
basic_filter => 'parsing_schema_name <> ''SYS'''
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));

########################################################
Populating SQL Tuning Set from High-Resource SQL in AWR
########################################################

Use the following steps to populate a SQL tuning set from high resource-consuming statements in the
AWR:
1. Create a SQL tuning set object.
2. Determine begin and end AWR snapshot IDs.
3. Populate the SQL tuning set with high-resource SQL found in AWR.

The prior steps are detailed in the following subsections.

Step 1 Create a SQL Tuning Set Object

Create a SQL tuning set. This next bit of code creates a tuning set named IO_STS:

BEGIN
dbms_sqltune.create_sqlset(
sqlset_name => 'IO_STS'
description => 'STS from AWR');
END;
/

Step 2 Determine Begin and End AWR Snapshot IDs
If you’re unsure of the available snapshots in your database, you can run an AWR report or select the SNAP_ID from DBA_HIST_SNAPSHOTS:

select snap_id, begin_interval_time
from dba_hist_snapshot order by 1;

Step 3: Populate the SQL Tuning Set with High-Resource SQL Found in AWR

DECLARE
base_cur dbms_sqltune.sqlset_cursor;
BEGIN
OPEN base_cur FOR
SELECT value(x)
FROM table(dbms_sqltune.select_workload_repository(
26800,26900, null, null,'disk_reads',
null, null, null, 15)) x;
--
dbms_sqltune.load_sqlset(
sqlset_name => 'IO_STS',
populate_cursor => base_cur);
END;
/

The prior code populates the top 15 SQL statements contained in the AWR ordered by disk reads. The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function is used to populate a PL/SQL cursor with AWR information based on a ranking criterion. Next the DBMS_SQLTUNE.LOAD_SQLSET procedure is used to populate the SQL tuning set using the cursor as input.

How It Works
The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function can be used in a variety of ways to populate a SQL tuning set using queries in the AWR. You can instruct it to load SQL statements by criteria such as disk reads, elapsed time, CPU time, buffer gets, and so on. See Table 11-5 for descriptions for parameters to this function. When designating the AWR as input, you can use either of the following:
• Begin and end AWR snapshot IDs
• An AWR baseline that you’ve previously created

You can view the details of the SQL tuning set (created in the “Solution” section) via this query:

SELECT
sqlset_name
,elapsed_time
,cpu_time
,buffer_gets
,disk_reads
,sql_text
FROM dba_sqlset_statements
WHERE sqlset_name = 'IO_STS';

############################################################
Populating a SQL Tuning Set from Resource Consuming SQL in Memory
############################################################

You want to populate a tuning set from high resource-consuming SQL statements that are currently in the memory.

Use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to populate a SQL tuning set with statements
currently in memory. This example creates a tuning set and populates it with high-load resourceconsuming
statements not belonging to the SYS schema and having disk reads greater than 1,000,000:
-- Create the tuning set

EXEC DBMS_SQLTUNE.CREATE_SQLSET('HIGH_DISK_READS');
-- populate the tuning set from the cursor cache

DECLARE
cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN cur FOR
SELECT VALUE(x)
FROM table(
DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
'parsing_schema_name <> ''SYS'' AND disk_reads > 1000000',
NULL, NULL, NULL, NULL, 1, NULL,'ALL')) x;
--
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'HIGH_DISK_READS',
populate_cursor => cur);
END;
/

The DBMS_SQLTUNE.SELECT_CURSOR_CACHE function (see Table 11-6 for function parameter descriptions) allows you to extract from memory SQL statements and associated statistics into a SQL tuning set. The procedure allows you to filter SQL statements by various resource-consuming criteria, such as the following:
• ELAPSED_TIME
• CPU_TIME
• BUFFER_GETS
• DISK_READS
• DIRECT_WRITES
• ROWS_PROCESSED
This allows you a great deal of flexibility on how to filter and populate the SQL tuning set.

Populating SQL Tuning Set with All SQL in Memory

You want to create a SQL tuning set and populate it with all SQL statements currently in memory.

Use the DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure to efficiently capture all of the SQL
currently stored in the cursor cache (in memory). This example creates a SQL tuning set named
PROD_WORKLOAD and then populates by sampling memory for 3,600 seconds (waiting 20 seconds between
each polling event):

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,description => 'Prod workload sample');
--
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 3600
,repeat_interval => 20);
END;
/

The DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure allows you to poll for queries and memory and use any queries found to populate a SQL tuning set. This is a powerful technique that you can use when it’s required to capture a sample set of all SQL statements executing. You have a great deal of flexibility on instructing DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET to capture SQL statements in memory (see Table 11-7 for details on all parameters). For example, you can instruct the procedure to capture a cumulative set of statistics for each SQL statement by specifying a CAPTURE_MODE of DBMS_SQLTUNE.MODE_ACCUMULATE_STATS.

BEGIN
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'PROD_WORKLOAD'
,time_limit => 60
,repeat_interval => 10
,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/

This is more resource-intensive than the default settings, but produces more accurate statistics for each SQL statement.

SQLSET_NAME SQL tuning set name none
TIME_LIMIT Total time in seconds to spend sampling 1800
REPEAT_INTERVAL While sampling, amount of time to pause in seconds before polling memory again 300
CAPTURE_OPTION Either INSERT, UPDATE, or MERGE statements when new statements are detected MERGE
CAPTURE_MODE When capture option is UPDATE or MERGE, either replace statistics or accumulate statistics. Possible values are
MODE_REPLACE_OLD_STATS or MODE_ACCUMULATE_STATS.
MODE_REPLACE_OLD_STATS
BASIC_FILTER Filter type of statements captured NULL
SQLSET_OWNER SQL tuning set owner; NULL indicates the current user. NULL
RECURSIVE_SQL Include (or not) recursive SQL; possible values are HAS_RECURSIVE_SQL, NO_RECURSIVE_SQL. HAS_RECURSIVE_SQL

Displaying the Contents of a SQL Tuning Set

You can determine the name and number of SQL statements for SQL tuning sets in your database via
this query:
SELECT name, created, statement_count
FROM dba_sqlset;

Use the following query to display the SQL text and associated statistical information for each query
within the SQL tuning set:

SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text
FROM dba_sqlset_statements;

You can also use the DBMS_SQLTUNE.SELECT_SQLSET function to retrieve information about SQL tuning sets—for example:

SELECT
sql_id
,elapsed_time
,cpu_time
,buffer_gets
,disk_reads
,sql_text
FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('&&sqlset_name'));

Whether you use the DBMS_SQLTUNE.SELECT_SQLSET function or directly query the data dictionary views depends entirely on your personal preference or business requirement

Selectively Deleting Statements from a SQL Tuning Set

Problem
You want to prune SQL statements from an STS that don’t meet a performance measure, such as queries
that have less than 2,000,000 disk reads.

Solution
First view the existing SQL information associated with an STS:

select sqlset_name, disk_reads, cpu_time, elapsed_time, buffer_gets
from dba_sqlset_statements;

Now use the DBMS_SQLTUNE.DELETE_SQLSET procedure to remove SQL statements from the STS based on the specified criterion. This example removes SQL statements that have less than 2,000,000 disk reads from the SQL tuning set named IO_STS:

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'IO_STS'
,basic_filter => 'disk_reads < 2000000');
END;
/

If you want to delete all SQL statements from a SQL tuning set, then don’t specify a filter—for
example:
SQL> exec DBMS_SQLTUNE.DELETE_SQLSET(sqlset_name => 'IO_STS');