Problem

You want to determine if the Automatic SQL Tuning job is enabled and regularly running. If the job is enabled, you want to display other aspects, such as when it starts and how long it executes.

Solution

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;

The following output shows that there are three enabled automatic jobs running, one of which is the SQL Tuning Advisor:

CLIENT_NAME                     STATUS     CONSUMER_GROUP  WINDOW_GROUP
------------------------------- ---------- --------------- ---------------
auto optimizer stats collection ENABLED    ORA$AUTOTASK    ORA$AT_WGRP_OS
auto space advisor              ENABLED    ORA$AUTOTASK    ORA$AT_WGRP_SA
sql tuning advisor              ENABLED    ORA$AUTOTASK    ORA$AT_WGRP_SQ

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;

Here is some sample output:

TASK_NAME                STATUS       TO_CHAR(EXECUTION_END,'D
------------------------ ------------ ------------------------
SYS_AUTO_SQL_TUNING_TASK COMPLETED    19-MAY-13 06:00
SYS_AUTO_SQL_TUNING_TASK COMPLETED    20-MAY-13 22:00
SYS_AUTO_SQL_TUNING_TASK COMPLETED    21-MAY-13 22:00

How It Works

When you create a 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;

Here’s a snippet of the output for this example:

WINDOW_NAME          TO_CHAR(WINDOW_NEXT_TIME,'D SQL_TUNE OPTIMIZE SEGMENT_
-------------------- --------------------------- -------- -------- --------
SUNDAY_WINDOW        26-MAY-13 06:00:00          ENABLED  ENABLED  ENABLED
THURSDAY_WINDOW      23-MAY-13 22:00:00          ENABLED  ENABLED  ENABLED
TUESDAY_WINDOW       28-MAY-13 22:00:00          ENABLED  ENABLED  ENABLED

There are several data dictionary views related to the automatically scheduled jobs. See Table 11-1 for descriptions of these views.

Problem

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

Solution

If you’re using Oracle Database 11g Release 2 or higher, here’s the quickest way to display automatically generated SQL tuning advice (run this from SQL*Plus):

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

Depending on the activity in your database, there may be a great deal of output. Here’s a small sample of the advice:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 37
Current Execution                       : EXEC_3032
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200
Completion Status                       : COMPLETED
Started at                              : 05/22/2013 22:00:03
Completed at                            : 05/22/2013 22:00:53
Number of Candidate SQLs                : 2
Cumulative Elapsed Time of SQL (s)      : 3
-------------------------------------------------------------------------------

How It Works

The “Solution” section describes a simple method to display in-depth automatic tuning advice for high-load queries in your database. Depending on the activity and load on your database, the report may contain no suggestions or may provide a great deal of advice. The advice can be of the following types:

• New/fresh statistics
• SQL profiles
• Addition of indexes
• Modifying the SQL statement

The Automatic SQL Tuning Advisor uses the high-workload SQL statements identified in the AWR as the target SQL statements to report on. The advice report consists of one or more of the following general subsections:

• General information
• Summary
• Details
• Findings
• Explain plans
• Alternate plans
• Errors

The general information section contains high-level information regarding the start and end time, number of SQL statements considered, cumulative elapsed time of the SQL statements, and so on.

The summary section contains information regarding the SQL statements analyzed—for example:

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
                      Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed                      : 2
Number of SQLs in the Report                 : 1
Number of SQLs with Findings                 : 1
Number of SQLs with SQL profiles recommended : 1
 
-------------------------------------------------------------------------------
    SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
       196 0g55kd7p78gnb                      31.74%

The details section contains information describing specific SQL statements, such as the owner and SQL text. Here is a small sample:

-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
 Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID  : 196
Schema Name: SYS
SQL ID     : 0g55kd7p78gnb
SQL Text   : SELECT registration_id FROM registrations ...

The findings section contains recommendations such as accepting a SQL profile or creating an index—for example:

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
  The SQL profile was not automatically created because the verified benefit
  was too low.
 
  Recommendation (estimated benefit: 31.74%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'SYS_AUTO_SQL_TUNING_TASK', object_id => 196, replace => TRUE);

Where appropriate, the original execution plan for a query is displayed along with a suggested fix and new execution plan. This allows you to see the before and after plan differences. This is very useful when determining if the findings (such as adding an index) would improve performance.

Lastly, there is an error section of the report. For most scenarios, there typically will not be an error section in the report.

The “Solution” section showed how to execute the REPORT_AUTO_TUNING_TASK function from a SQL statement. This function can also be called from an anonymous block of PL/SQL. Here’s an example:

VARIABLE tune_report CLOB;
BEGIN
  :tune_report := DBMS_AUTO_SQLTUNE.report_auto_tuning_task(
    begin_exec   => NULL
   ,end_exec     => NULL
   ,type         => DBMS_AUTO_SQLTUNE.type_text
   ,level        => DBMS_AUTO_SQLTUNE.level_typical
   ,section      => DBMS_AUTO_SQLTUNE.section_all
   ,object_id    => NULL
   ,result_limit => NULL);
END;
/
--
SET LONG 1000000
PRINT :tune_report

The parameters for the REPORT_AUTO_TUNING_TASK function are described in detail in Table 11-2. These parameters allow you a great deal of flexibility to customize the advice output.

Problem

You’ve reviewed the automatic SQL tuning advice (see Recipe 11-2 for details). Now you want to generate a SQL script that can be used to implement the tuning advice.

Solution

Use the DBMS_SQLTUNE.SCRIPT_TUNING_TASK function to generate the SQL statements to implement the advice of a tuning task. You need to provide as input the name of the automatic tuning task. The default task name generated by the automatic SQL tuning advice is SYS_AUTO_SQL_TUNING_TASK (run this from SQL*Plus):

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:

-----------------------------------------------------------------
-- Script generated by DBMS_SQLTUNE package, advisor framework --
-- Use this script to implement some of the recommendations    --
-- made by the SQL tuning advisor.                             --
--                                                             --
-- NOTE: this script may need to be edited for your system     --
--       (index names, privileges, etc.) before it is executed. --
-----------------------------------------------------------------
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_AUTO_SQL_TUNING_TASK',
 object_id => 196, replace => TRUE);

You can edit the script as required and then execute it directly from SQL*Plus to implement the automatic tuning advice.

How It Works

The DBMS_SQLTUNE.SCRIPT_TUNING_TASK function generates the SQL required to implement the advice recommended by the Automatic SQL Tuning Advisor (such as new statistics, creating profiles, indexes, or re-writing the SQL statement). 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;

Here’s some sample output for this example:

TASK_NAME                      EXECUTION
------------------------------ ---------
SYS_AUTO_SQL_TUNING_TASK       22-MAY-13

Problem

You’ve noticed that sometimes the Automatic SQL Tuning Advisor job recommends that a SQL profile be applied to a SQL statement (see Chapter 12 for details on SQL profiles). The default behavior of the automatic tuning advice job is to not automatically accept SQL profile recommendations. You want to modify this behavior and have the job place any SQL profiles that it recommends into an accepted state.

Solution

Use the DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure to modify the default behavior of the Automatic SQL Tuning Advisor. 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';

Here is some sample output:

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ------------------------------
ACCEPT_SQL_PROFILES            TRUE

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;
/

How It Works

The DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER procedure allows you to modify the default behavior of the Automatic SQL Tuning Advisor job. You can view all of the current settings for the automated job 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'),

Here’s some sample output:

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ------------------------------
ACCEPT_SQL_PROFILES            FALSE
EXECUTION_DAYS_TO_EXPIRE       30
MAX_SQL_PROFILES_PER_EXEC      20
MAX_AUTO_SQL_PROFILES          10000

The prior parameters are described in Table 11-3.

You can also use Enterprise Manager to modify the behavior of the Automatic SQL Tuning Advisor. Click on the “Performance” tab, then “Advisors home”, then “SQL Advisors”, then “Automatic SQL Tuning Advisor Results.” You should be presented with a screen similar to Figure 11-2.

From this screen, you can configure, view results, disable, and enable various aspects of Automatic SQL Tuning job.

Problem

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

Solution

Use the DBMS_AUTO_TASK_ADMIN.DISABLE procedure to disable the Automatic SQL Tuning Advisor job. For example:

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

You can report on the status of the automatic tuning job via this query:

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

Here’s some sample output:

CLIENT_NAME                              STATUS   CONSUMER_GROUP
---------------------------------------- -------- --------------------
auto optimizer stats collection          ENABLED  ORA$AUTOTASK
auto space advisor                       ENABLED  ORA$AUTOTASK
sql tuning advisor                       DISABLED ORA$AUTOTASK

To re-enable the job, use the DBMS_AUTO_TASK_ADMIN.ENABLE procedure as shown:

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

How It Works

You might want to disable the Automatic SQL Tuning Advisor job because you have a very active database and want to ensure that this job doesn’t impact the overall performance of the database. The DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE procedures allow you to turn on and off this automated job. These procedures take three parameters (see Table 11-4 for details).

The behavior of the procedures varies depending on which parameters you pass in:

• If CLIENT_NAME is provided and both OPERATION and WINDOW_NAME are NULL, then the client is disabled.
• If OPERATION is provided, then the operation is disabled.
• If WINDOW_NAME is provided, and OPERATION is NULL, then the client is disabled in the provided window name.

The prior parameters allow you to control at a granular detail the schedule of the automatic task. Given the prior rules, you would disable the Automatic SQL Tuning Advisor job during the Tuesday maintenance window as follows:

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;

Here is a snippet of the output:

WINDOW_NAME        TO_CHAR(WINDOW_NEXT_TIME,'D SQL_TUNE
------------------ --------------------------- --------
SUNDAY_WINDOW      26-MAY-13 06:00:00          ENABLED
THURSDAY_WINDOW    30-MAY-13 22:00:00          ENABLED
TUESDAY_WINDOW     28-MAY-13 22:00:00          DISABLED

Problem

You realize that the automatic tasks (such as the Automatic SQL Tuning Advisor job) run during regularly scheduled maintenance windows. You want to limit the time the job runs and so lessen the impact the job has on overall database performance.

Solution

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

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

You can confirm the changes to the maintenance window with this query:

SELECT window_name, next_start_date, duration
FROM dba_scheduler_windows;

Here is a snippet of the output:

WINDOW_NAME        NEXT_START_DATE                          DURATION
------------------ ---------------------------------------- ---------------
SUNDAY_WINDOW      26-MAY-13 06.00.00.000000 AM MST7MDT     +000 02:00:00
SATURDAY_WINDOW    25-MAY-13 06.00.00.000000 AM MST7MDT     +000 20:00:00

How It Works

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 modified via the DBMS_SCHEDULER package. Starting with Oracle 11g and higher, when you create a database, by default three automatic maintenance jobs are configured:

• Automatic SQL Tuning
• Statistics gathering
• Segment advice

You can view the high-level details of these jobs via this query:

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

Here is some sample output:

CLIENT_NAME                         STATUS   CONSUMER_GROUP       WINDOW_GROUP
----------------------------------- -------- -------------------- --------------------
auto optimizer stats collection     ENABLED  ORA$AUTOTASK         ORA$AT_WGRP_OS
auto space advisor                  ENABLED  ORA$AUTOTASK         ORA$AT_WGRP_SA
sql tuning advisor                  ENABLED  ORA$AUTOTASK         ORA$AT_WGRP_SQ

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 1 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;

Here is a small sample of the output:

WINDOW_NAME        TO_CHAR(START_TIME,'DD-M DURATION
------------------ ------------------------ ---------------
FRIDAY_WINDOW      24-may-13 22:00          +000 04:00:00
SATURDAY_WINDOW    25-may-13 06:00          +000 20:00:00
SUNDAY_WINDOW      26-may-13 06:00          +000 02:00:00

Problem

You’re working on improving a business process comprised of multiple SQL statements. This requires that you create a SQL tuning set object that will be used to associate a set of SQL statements (and corresponding statistics).

Solution

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 an STS named HIGH_IO. The tuning set does not yet contain any SQL statements (see Recipes 11-9, 11-11, and 11-12 for details on adding SQL statements to an STS).

How It Works

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:

SQL> select owner, name, id, created, statement_count from dba_sqlset;

Here is some sample output:

OWNER      NAME                         ID CREATED   STATEMENT_COUNT
---------- -------------------- ---------- --------- ---------------
MV_MAINT   HIGH_IO                       1 23-AUG-13               0

If you need to drop a SQL tuning set object, then use the DBMS_SQLTUNE.DROP_SQLSET procedure to drop a tuning set. The following example drops the previously created SQL tuning set:

SQL> EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'HIGH_IO'),

Problem

You want to view SQL statements in the AWR that are consuming the most resources. This will lay the foundation for understanding how you can populate a SQL tuning set with high resource-consuming SQL statements recorded in the AWR.

Solution

First determine what snapshots are available and which range you would like to report on:

SELECT snap_id, instance_number, end_interval_time
FROM dba_hist_snapshot
ORDER BY snap_id;

Next use the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function to extract SQL stored in the AWR. This particular query selects queries in the AWR between snapshots 1669 and 1671 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(1669,1671,
            null, null, 'disk_reads',null, null, null, 10))
ORDER BY disk_reads DESC;

Here is a small snippet of the output:

SQL_ID         SUBSTR(SQL_TEXT,1,20 DISK_READS      CPU_TIME  ELAPSED_TIME
-------------- -------------------- ---------- ------------- -------------
achffburdff9j   delete from "MVS"."     101145     814310000     991574249
5vku5ap6g6zh8  INSERT /*+ BYPASS_RE      98172      75350000      91527239

How It Works

The DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function is used to retrieve high resource-usage SQL from the AWR. Table 11-5 provides details regarding 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(1669,1671,
'parsing_schema_name <> "SYS"',
NULL, NULL,NULL,NULL, 1, NULL, 'ALL'));

The following example retrieves the top 10 queries ranked by buffer gets 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_WORKLOAD_REPOSITORY(
 begin_snap => 1669
,end_snap => 1671
,basic_filter => 'parsing_schema_name <> "SYS"'
,ranking_measure1 => 'buffer_gets'
,result_limit => 10
));

In the prior queries, the SYS keyword is enclosed by two single quotes (in other words, those aren’t double quotes around SYS).

You can also automatically calculate and pass in a pair of AWR snapshot IDs to the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY function. For example, say you wanted to view the highest CPU-consuming SQL in the last 7 days for non-SYS users:

COL bsnap NEW_VALUE begin_snap
COL esnap NEW_VALUE end_snap
--
SELECT MAX(snap_id) bsnap
FROM dba_hist_snapshot
WHERE begin_interval_time < sysdate-7;
--
SELECT MAX(snap_id) esnap
FROM dba_hist_snapshot;
--
COL sql_text            FORMAT A40
COL sql_id              FORMAT A15
COL parsing_schema_name FORMAT A15
COL cpu_seconds         FORMAT 999,999,999,999,999
SET LONG 10000 LINES 132 PAGES 100 TRIMSPOOL ON
--
SELECT sql_id, sql_text
,disk_reads, cpu_time cpu_seconds, elapsed_time, buffer_gets, parsing_schema_name
FROM table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
 begin_snap => &begin_snap
,end_snap => &end_snap
,basic_filter => 'parsing_schema_name <> "SYS"'
,ranking_measure1 => 'cpu_time'
,result_limit => 10
));

The prior examples give a small sampling of the power of the DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSTIORY function. This lays the foundation for using this tool to automatically populate a SQL tuning set with high-resource usage SQL in the AWR.

Problem

You want to create a SQL tuning set and populate it with the top resource-consuming SQL statements found in the AWR.

Solution

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 using the DBMS_SQLTUNE package.

The prior steps are detailed in the following subsections.

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

select snap_id, begin_interval_time
from dba_hist_snapshot order by 1;

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

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 (see Chapter 7 for details)

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';

Problem

You want to view resource-consuming SQL statements currently in memory. This will lay the foundation for understanding how you can populate a SQL tuning set with high resource-consuming SQL statements stored in memory.

Solution

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 read more than a million blocks from disk:

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;

Here is some sample output:

SQL_ID        SUBSTR(SQL_TEXT,1,20 DISK_READS   CPU_TIME ELAPSED_TIME
------------- -------------------- ---------- ---------- ------------
0s6gq1c890p4s  delete from "MVS"."    3325320 8756130000   1.0416E+10
b63h4skwvpshj BEGIN dbms_mview.ref    9496353 1.4864E+10   3.3006E+10

How It Works

The DBMS_SQLTUNE.SELECT_CURSOR_CACHE function is used to display high resource-usage SQL currently in the shared pool memory area. 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 the SELECT_CURSOR_CACHE function. For example, this next query selects the top 10 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
));

Here’s another example that selects SQL in memory but excludes statements parsed by the SYS user and also returns statements with an elapsed time greater than 1 second (1,000,000 microseconds):

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 > 1000000'))
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 a LOB data type and is truncated to 20 characters so that the output can be displayed on the page more easily. Here is some sample output:

SQL_ID        SUBSTR(SQL_TEXT,1,20 DISK_READS   CPU_TIME ELAPSED_TIME
------------- -------------------- ---------- ---------- ------------
byzwu34haqmh4 SELECT /* DS_SVC */           0     140000      1159828

Once you have identified a SQL_ID for a resource-intensive SQL statement, you can view all of its execution details via this query:

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).

Problem

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

Solution

Use the DBMS_SQLTUNE.SELECT_CURSOR_CACHE function to populate a SQL tuning set with statements currently in memory. This example creates a SQL tuning set named HIGH_DISK_READS and populates it with high-load resource-consuming 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;
/

In the prior code, notice that the SYS user is bookended by sets of two single quotes (not double quotes). The SELECT_CURSOR_CACHE function loads the SQL statements into a PL/SQL cursor, and the LOAD_SQLSET procedure populates the SQL tuning set with the SQL statements.

How It Works

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.

If you want to view the SQL statements contained within the STS, then query the DBA_SQLSET_STATEMENTS view. This query shows the details of the STS created previously in the “Solution” section:

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

Problem

You’re experiencing performance problems with a production database. You want to populate a SQL tuning set with all SQL statements currently in memory. The idea being that you can use the SQL Tuning Advisor to analyze the SQL statements as a set to better determine which statements should be tuned.

Solution

Use the following steps to populate a SQL tuning set from high resource-consuming statements in memory:

1. Create a SQL tuning set object.
2. Populate the SQL tuning set with high-resource SQL found in memory via the DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure.

The prior steps are detailed in the following subsections.

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

BEGIN
  DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
    sqlset_name     => 'PROD_WORKLOAD'
   ,time_limit      => 3600
   ,repeat_interval => 20);
END;
/

How It Works

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 currently in memory.

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.

Problem

You have populated a SQL tuning set and want to verify its characteristics such as the SQL statements and corresponding statistics.

Solution

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;

Here is some sample output:

NAME                 CREATED   STATEMENT_COUNT
-------------------- --------- ---------------
IO_STS               26-MAY-13              15

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;

Here is a small snippet of the output. The SQL_TEXT column has been truncated in order to fit the output on the page:

SQLSET_NAME     ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS SQL_TEXT
--------------- ------------ ---------- ----------- ---------- ----------------------------
IO_STS              235285363   45310000      112777       3050 INSERT ......
IO_STS               52220149   22700000      328035      18826 delete from.....

How It Works

Recall that a SQL tuning set consists of one or more SQL statements and the corresponding execution statistics. This information is viewable from the DBA_SQLSET_* views. Table 11-8 describes the type of SQL tuning set information contained within each of these views.

You can also use the DBMS_SQLTUNE.SELECT_SQLSET function to retrieve information about SQL tuning set. 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.

Problem

You want to prune SQL statements from an SQL tuning set 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;

Here is some sample output:

SQLSET_NAME                    DISK_READS   CPU_TIME ELAPSED_TIME BUFFER_GETS
------------------------------ ---------- ---------- ------------ -----------
IO_STS                            3112941 3264960000   7805935285     2202432
IO_STS                            2943527 3356460000   8930436466     1913415
IO_STS                            2539642 2310610000   5869237421     1658465
IO_STS                            1999373 2291230000   6143543429     1278601
IO_STS                            1993973 2243180000   5461607976     1272271
IO_STS                            1759096 1930320000   4855618689     1654252

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;
/

How It Works

The key to understanding that you can modify SQL tuning set is understanding that a SQL tuning set consists of the following:

• One or more SQL statements
• Associated metrics/statistics for each SQL statement

Because the metrics/statistics are part of the SQL tuning set, you can remove SQL statements from a SQL tuning set based on characteristics of the associated metrics/statistics. You can use the DBMS_SQLTUNE.DELETE_SQLSET procedure to remove statements from the STS based on statistics such as the following:

• ELAPSED_TIME
• CPU_TIME
• BUFFER_GETS
• DISK_READS
• DIRECT_WRITES
• ROWS_PROCESSED

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'),

Problem

You’ve identified some resource-intensive SQL statements in a production environment. You want to transport these statements and associated statistics to a test environment, where you can tune the statements without impacting production.

Solution

The following steps are used to copy a SQL tuning set from one database to another:

1. Create a staging table in source database.
2. Populate the staging table with SQL tuning set data.
3. Copy the staging table to the destination database.
4. Unpack the staging table in the destination database.

The prior steps are elaborated on in the following subsections.

BEGIN
  dbms_sqltune.create_stgtab_sqlset(
  table_name => 'STS_TABLE'
 ,schema_name => 'MV_MAINT'),
END;
/

BEGIN
  dbms_sqltune.pack_stgtab_sqlset(
  sqlset_name          => 'PROD_WORKLOAD'
 ,sqlset_owner         => 'SYS'
 ,staging_table_name   => 'STS_TABLE'
 ,staging_schema_owner => 'MV_MAINT'),
END;
/

SELECT name, owner, created, statement_count
FROM dba_sqlset;

NAME                 OWNER      CREATED   STATEMENT_COUNT
-------------------- ---------- --------- ---------------
HIGH_IO              MV_MAINT   23-AUG-13               0
PROD_WORKLOAD        SYS        23-AUG-13             148
IO_STS               MV_MAINT   23-AUG-13              15

create database link source_db
connect to mv_maint
identified by foo
using 'source_db';

SQL> create table STS_TABLE as select * from STS_TABLE@source_db;

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
  sqlset_name        => '%'
 ,replace            => TRUE
 ,staging_table_name => 'STS_TABLE'
 ,staging_schema_owner=> 'MV_MAINT'),
END;
/

How It Works

A SQL tuning set consists of one or more queries and corresponding execution statistics. You will occasionally have a need to copy a SQL tuning set from one database to another. For example, you might be having performance problems with a production database but want to capture and move the top resource-consuming statements to a test database where you can diagnose the SQL (within the SQL tuning set) without impacting production.

Keep in mind that an STS can be used as input for any of the following tools:

• SQL Tuning Advisor
• SQL Access Advisor
• SQL Plan Management
• SQL Performance Analyzer

The prior tools are used extensively to troubleshoot and test SQL performance. Transporting a SQL tuning set from one environment to another allows you to use these tools in a testing or development environment.

Problem

You realize that as part of manually running the SQL Tuning Advisor, you need to first create a tuning task.

Solution

Use the DBMS_SQLTUNE.CREATE_TUNING_TASK procedure to create a SQL tuning task. You can use the following as inputs when creating a SQL tuning task:

• Text for a specific SQL statement
• SQL identifier for a specific SQL statement from the cursor cache in memory
• Single SQL statement from the AWR given a range of snapshot IDs
• SQL tuning set name (see Recipes 11-7 through 11-12 for details on how to create and populate a SQL tuning set)

Examples of the prior techniques for creating a SQL tuning task are described in the following subsections.

DECLARE
  tune_task VARCHAR2(30);
  tune_sql  CLOB;
BEGIN
  tune_sql := 'select count(*) from emp';
  tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text    => tune_sql
   ,user_name   => 'MV_MAINT'
   ,scope       => 'COMPREHENSIVE'
   ,time_limit  => 60
   ,task_name   => 'tune_test1'
   ,description => 'Provide SQL text'
);
END;
/

SELECT sql_id, plan_hash_value , sql_text
FROM v$sql where sql_text like '%&mytext%'
and sql_text not like '%FROM v$sql%';

DECLARE
  tune_task VARCHAR2(30);
  tune_sql  CLOB;
BEGIN
  tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id          => '1ybqt7wu5hwas'
   ,plan_hash_value => '3956160932'
   ,task_name       => 'tune_test2'
   ,description     => 'Provide SQL ID'
);
END;
/

DECLARE
  tune_task VARCHAR2(30);
  tune_sql  CLOB;
BEGIN
  tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id      => '1tbu2jp7kv0pm'
   ,begin_snap  => 21690
   ,end_snap    => 21864
   ,task_name   => 'tune_test3'
);
END;
/

SQL> select sql_id, sql_text from dba_hist_sqltext;

SQL> select snap_id, end_interval_time from dba_hist_snapshot order by snap_id;

select snap_id, sql_id, plan_hash_value
from dba_hist_sqlstat
order by snap_id;

SQL> exec dbms_workload_repository.add_colored_sql('98u3gf0xzq03f'),

SQL> variable mytt varchar2(30);
SQL> exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'PROD_WORKLOAD', -
                                                    task_name => 'tune_test4'),
SQL> print :mytt

How It Works

Before manually executing the SQL Tuning Advisor, you first need to define what SQL statements will be used as input. You do this by creating a SQL tuning task and associating SQL statements with the tuning task. Oracle provides a great deal of flexibility on how you add SQL statements to a tuning task. As shown in the “Solution” section, you can do the following:

• Hard-code the text for a specific SQL query
• Use a SQL query currently in memory
• Use a SQL query in the AWR
• Define a SQL tuning set when tuning multiple queries

The prior techniques provide a variety of ways to identify SQL statements to be analyzed by the SQL Tuning Advisor. Once you’ve created a tuning task, you can view its details via this query:

select owner, task_name, advisor_name, how_created, created
from dba_advisor_tasks
where advisor_name = 'SQL Tuning Advisor'
order by created;

Once you have created a tuning task, you can now manually execute the SQL Tuning Advisor (Recipe 11-17). If you need to drop the tuning task, you can do so as follows:

SQL> exec dbms_sqltune.drop_tuning_task(task_name => '&&task_name'),

Problem

You want to manually execute SQL Tuning Advisor and get tuning advice for a SQL statement.

Solution

You can run the SQL Tuning Advisor from several different tools:

• SQL*Plus
• SQL Developer
• Enterprise Manager

Examples of accessing the SQL Tuning Advisor are shown in the following subsections:

DECLARE
  tune_task VARCHAR2(30);
  tune_sql  CLOB;
BEGIN
  tune_sql := 'select a.emp_id, b.dept_name ' ||
              'from emp a, dept b ' ||
              'where a.dept_id = b.dept_id';
  --
  tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text    => tune_sql
   ,user_name   => 'MV_MAINT'
   ,scope       => 'COMPREHENSIVE'
   ,time_limit  => 60
   ,task_name   => 'tune_test5'
   ,description => 'Tune a SQL statement.'
);
END;
/

SQL> exec dbms_sqltune.execute_tuning_task(task_name => 'tune_test5'),

SQL> set long 10000 longchunksize 10000 linesize 132 pagesize 200
SQL> select dbms_sqltune.report_tuning_task('tune_test5') from dual;

1- Statistics Finding
---------------------
  Table "MV_MAINT"."DEPT" was not analyzed.
  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
....
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");

How It Works

The SQL Tuning Advisor helps automate the task of tuning poorly performing queries. The tool is fairly easy to use, and it provides suggestions on how to tune a query, such as the following:

• Rewriting the SQL
• Adding indexes
• Implementing a SQL profile or plan baselines
• Generating statistics

The SQL Tuning advisor is easily accessed through the DBMS_SQLTUNE package, Enterprise Manager, or SQL Developer. This easy access provides everybody on the team a way to identify problematic SQL queries and generate potential tuning advice.

One key to understanding how the SQL Tuning Advisor operates is that the query optimizer can operate in two different modes: normal and tuning. When a user runs a SQL statement, the optimizer operates in normal mode and quickly identifies a reasonable execution plan. In this mode, the optimizer spends only a fraction of a second to try to determine the best plan.

Whereas when the SQL Tuning Advisor analyzes a query, it runs the optimizer in tuning mode. In this mode, the optimizer can take several minutes to analyze each step of the execution plan and generate an execution plan that is potentially much more efficient than the plan generated under normal mode.

This is somewhat similar to a computer chess game. When you allow the chess software to spend only a second or less on each move, it’s easy to beat the game. However, if you allow the chess game to spend a minute or more on each move, in this mode the game makes much more optimal decisions.

Problem

You’re having performance issues. You realize that the Automatic Database Diagnostic Monitor (ADDM) report is a good place to get initial advice on identifying problematic SQL statements and potential recommendations. Therefore you want to run an ADDM report.

Solution

You can view an ADDM report from the following tools:

• SQL*Plus script
• DBMS_ADDM package
• Enterprise Manager

These techniques are elaborated on in the following subsections.

SQL> @?/rdbms/admin/addmrpt.sql

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
O12C         O12C              1766 29 May 2013 08:00      1
                               1767 29 May 2013 09:00      1
                               1768 29 May 2013 10:00      1
 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:

The default report file name is addmrpt_1_26468_26486.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:

Finding 1: Top SQL Statements
Impact is .79 active sessions, 72.17% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
   Recommendation 1: SQL Tuning
   Estimated benefit is .58 active sessions, 53.07% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the INSERT statement with SQL_ID "2nw0mmysuma43" for
      possible performance improvements. You can supplement the information
      given here with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 2nw0mmysuma43.
         INSERT INTO bling
         ( registration_id,company
         ,soa_id,product_name

SQL> var task_name varchar2(30);
SQL> exec DBMS_ADDM.ANALYZE_DB(:task_name, 1766, 1767);
SQL> print :task_name

TASK_NAME
-------------------------------
TASK_3041

SQL> SET LONG 1000000 PAGESIZE 0;
SQL> SELECT DBMS_ADDM.GET_REPORT('TASK_3041') FROM DUAL;

Action
   Run SQL Tuning Advisor on the DELETE statement with SQL_ID
   "0s6gq1c890p4s".
   Related Object
      SQL statement with SQL_ID 0s6gq1c890p4s.
      delete from "MVS"."MGMT_DB_FEAT_USE_ECM_LATEST"
Rationale
   The SQL spent 98% of its database time on CPU, I/O and Cluster waits.
   This part of database time may be improved by the SQL Tuning Advisor.

How It Works

The ADDM analyzes AWR snapshots every hour (by default) and produces performance recommendations. The suggestions are ranked by the expected benefit of implementing a recommendation. Listed next are the types of recommendations you can expect from ADDM:

• Expensive SQL statements
• Expensive PL/SQL
• RAC issues
• CPU bottlenecks
• Memory sizing recommendations
• Database configuration recommendations
• I/O bottlenecks

If you are having database performance issues, the ADDM report is an excellent place to first look for bottlenecks and problem areas of the database. The ADDM also details top resource-consuming SQL statements and makes recommendations on how to tune these queries.