dbms_sqltune tips

Inside the dbms_sqltune Package tasks

SQL tuning work is one of the most time consuming and challenging tasks faced by Oracle DBAs and application developers and the Oracle SQL Tuning Advisor is intended to facilitate SQL tuning tasks and to help the DBA find the optimal SQL execution plan.

The SQL Tuning Advisor can search the SQL cache, the AWR, or user inputs searching for inefficient SQL statements. The SQL Tuning Advisor is available through the OEM console, or the dbms_sqltune package can be invoked manually.  Figure 8.3 is a representation of the SQL Tuning Advisor tool in the OEM console.

The Ion tool, which has been provided free with the purchase of this book, also supports use of the SQL Tuning Advisor and allows the DBA to tune any SQL statement that is stored in the AWR.  Figure 8.4 is a representation of the SQL Tuning Advisor in the Ion tool.

The dbms_sqltune package provides the DBA with a PL/SQL API for using the SQL Tuning Advisor tool. Running the SQL Tuning Advisor using PL/SQL API includes two steps:

§       Create the SQL tuning task.

§       Execute the SQL tuning task.

There are several options for the creation of an SQL tuning task. For example, the following process will examine the invocation of a single SQL statement. The dbms_sqltune.create_task function can be used to do the following:

SQL tuning tasks can be created with SQL from the cursor cache, the AWR views, or previously collected SQL Tuning Sets. The overloaded dbms_sqltune.create_task functions are provided to allow the DBA to change the inputs.

After successfully creating a SQL tuning task, the DBA can launch the SQL Tuning Optimizer  to produce tuning recommendations. Use the dbms_sqltune.execute_tuning_task procedure to execute the specified task:

exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task1');

Now, the DBA is ready to review recommendation details produced by the SQL Tuning Advisor.  A query like the one below can be used to retrieve the SQL analysis results:

select dbms_sqltune.report_tuning_task(‘sql_tuning_task1’) from dual;

The following is the resulting report.

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK1’)

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

GENERAL INFORMATION SECTION

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

Tuning Task Name   : SQL_TUNING_TASK1

Scope              : COMPREHENSIVE

Time Limit(seconds): 1800

Completion Status  : COMPLETED

Started at         : 09/07/2004 16:24:41

Completed at       : 09/07/2004 16:24:41

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

SQL ID  : g2wr3u7s1gtf3

SQL Text: 'SELECT object_type, count(*) FROM

           all_objects GROUP BY object_type'

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

FINDINGS SECTION (1 finding)

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

1- Statistics Finding

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

  Optimizer statistics for table "SYS"."OBJ$" and its indices are stale.

  Recommendation

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

    Consider collecting optimizer statistics for this table.

    execute dbms_stats.gather_table_stats(ownname => 'SYS',

The recommendation report contains problem findings and the corresponding recommendations for fixing them.

The analysis process could consume significant processing times. Therefore, the dbms_sqltune package provides an API to manage tuning tasks such as:

§       The interrupt_tuning_task procedure is used to stop the executing task. Any results that have already been produced will be preserved.

§       The cancel_tuning_task procedure terminates the task that is executing without preserving its results.

§       The reset_tuning_task procedure is used to stop the running task and reset it to the initial state.

§       The drop_tuning_task  procedure can be used to remove the task from the database.

During tuning analysis, the SQL Tuning Advisor can recommend and automatically create SQL profiles. The SQL profile is a special object that is used by the optimizer. The SQL Profile contains auxiliary statistics specific to a particular SQL statement.

The SQL optimizer uses the information in the SQL profile to adjust the execution plan for the SQL statement that has the associated SQL profile. SQL profiles are great for SQL tuning because it is possible to tune SQL statements without any modification of the application source code or the text of SQL queries. The dba_sql_profiles view shows information about all existing SQL profiles.

The dbms_sqltune package can be used to manage SQL profiles. The SQL Tuning Advisor  can recommend the use of a specific SQL Profile. This SQL profile can be associated with SQL statements that are being analyzed by accepting it using dbms_sqltune.accept_sql_profile:

After the profile is defined, the DBA can alter any stored SQL profile attributes such as status  , name , description , and category  using dbms_sqltune.alter_sql_profile. The category attribute is used to limit user sessions that can use the particular SQL profile. There is an initialization parameter called sqltune_category , which allows the DBA to set up the default SQL profile category for the database.

The dbms_sqltune.drop_sql_profile  procedure is used to remove the SQL profile from the database.

The dbms_sqltune package also provides a PL/SQL API to work with SQL Tuning Sets (STS). The STS is a database object that contains one or more SQL statements combined with their execution statistics and context such as particular schema, application module name, list of bind variables, etc. The STS also includes a set of basic execution statistics such as CPU and elapsed times, disk reads and buffer gets, number of executions, etc.

When creating a STS, the SQL statements can be filtered by different patterns such as application module name or execution statistics, such as high disk reads. Once created, STS can be an input source for the SQL Tuning Advisor.

Typically, the following steps are used to work with STS using the dbms_sqltune API:

1.     STS is created using the dbms_sqltune.create_sqlset procedure. For example, the following script can be used to create a STS called SQLSET1:

exec dbms_sqltune.create_sqlset ( ‘SQLSET1’);

2.     STS is loaded from such sources as the AWR, another STS, or the cursor cache. The following sample PL/SQL block loads STS from the current cursor cache:

3.     An SQL tuning task that uses STS as input can be created and executed like this:

4.     The following syntax can be used to drop a SQL tuning set when finished:

exec dbms_sqltune.drop_sqlset ( ‘SQLSET1’ );

All SQL tuning sets created in the database by querying the dba_sqlset , dba_sqlset_binds , dba_sqlset_definitions , and dba_sqlset_statements  views are reviewed. For example, the dbms_sqltune_show_sts.sql query below shows the particular SQL statements associated with STS:

Clearly, Oracle10g has introduced a rich set of powerful tools for the DBA to use to identify and resolve possible performance problems and while these advisors cannot yet replicate the behavior of a senior DBA, they promise to get more intelligent with each new release of Oracle.

One of the most encouraging new areas with Oracle 10g and 11g are the many advisors, and in particular, those to assist with SQL optimization. The days of manually working solely with cryptic explain plans are essentially over. While along the way Oracle offered interim solutions such as stored outlines and profiles, the ultimate solution has now evolved resulting in the SQL Performance Analyzer  (SPA), SQL Tuning Advisor  and the critically important concept of SQL Tuning Sets  (STS).

Now we can work with small manageable SQL workload bundles called tuning sets, which include SQL Text, Bind variables, execution plans and execution statistics, and test-encapsulated scenarios for before and after relative comparisons. That, in turn, paves the way for focusing on the results rather than the explain plans resulting in those satisfactory results. Furthermore, Oracle 11g has automated candidate identification so that DBAs need only to concentrate on those flagged as being potential issues.

There are so many new database advisors, with the list seeming to grow by the minute, that the best way to utilize them is most often via the OEM screens accessible via Advisor Central as shown here in Figure 7.6. However, the PL/SQL interface in this chapter will be examined for completeness. The next chapter will focus more on the task and its processes as well as the various screens to visit.

The following data types and constants are used by the DBMS_SQLTUNE  package:

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

  --                      global constant declarations                      --

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

  --

  -- sqltune advisor name

  ADV_SQLTUNE_NAME  CONSTANT VARCHAR2(18) := 'SQL Tuning Advisor';

  --

  -- SQLTune advisor task scope parameter values

  --

  SCOPE_LIMITED       CONSTANT VARCHAR2(7)  := 'LIMITED';

  SCOPE_COMPREHENSIVE CONSTANT VARCHAR2(13) := 'COMPREHENSIVE';

  --

  --  SQLTune advisor time_limit constants

  --

  TIME_LIMIT_DEFAULT  CONSTANT   NUMBER := 1800;

  --

  -- report type (possible values) constants

  --

  TYPE_TEXT           CONSTANT   VARCHAR2(4) := 'TEXT'       ;

  TYPE_XML            CONSTANT   VARCHAR2(3) := 'XML'        ;

  TYPE_HTML           CONSTANT   VARCHAR2(4) := 'HTML'       ;

  --

  -- report level (possible values) constants

  --

  LEVEL_TYPICAL       CONSTANT   VARCHAR2(7) := 'TYPICAL'    ;

  LEVEL_BASIC         CONSTANT   VARCHAR2(5) := 'BASIC'      ;

  LEVEL_ALL           CONSTANT   VARCHAR2(3) := 'ALL'        ;

  --

  -- report section (possible values) constants

  --

  SECTION_FINDINGS    CONSTANT   VARCHAR2(8) := 'FINDINGS'   ;

  SECTION_PLANS       CONSTANT   VARCHAR2(5) := 'PLANS'      ;

  SECTION_INFORMATION CONSTANT   VARCHAR2(11):= 'INFORMATION';

  SECTION_ERRORS      CONSTANT   VARCHAR2(6) := 'ERRORS'     ;

  SECTION_ALL         CONSTANT   VARCHAR2(3) := 'ALL'        ;

  SECTION_SUMMARY     CONSTANT   VARCHAR2(7) := 'SUMMARY'    ;

  -- some common date format

  DATE_FMT       constant varchar2(21)       :=  'mm/dd/yyyy hh24:mi:ss';

  --

  -- script section constants

  --

  REC_TYPE_ALL          CONSTANT   VARCHAR2(3)  := 'ALL';

  REC_TYPE_SQL_PROFILES CONSTANT   VARCHAR2(8)  := 'PROFILES';

  REC_TYPE_STATS        CONSTANT   VARCHAR2(10) := 'STATISTICS';

  REC_TYPE_INDEXES      CONSTANT   VARCHAR2(7)  := 'INDEXES';

  --

  -- capture section constants

  --

  MODE_REPLACE_OLD_STATS CONSTANT   NUMBER := 1;

  MODE_ACCUMULATE_STATS  CONSTANT   NUMBER := 2;

---- SYS.SQLSET_ROW ----

  --

  -- sql tuning set basic attributes

  --

  sql_id                   VARCHAR(13),   /* unique SQL ID */

  force_matching_signature NUMBER,        /* literals, case, spaces removed */

  sql_text                 CLOB,          /* unique SQL hache value */

  object_list              sql_objects,   /* objects referenced by this stmt */

  bind_data                RAW(2000),     /* bind data as captured for SQL */

  parsing_schema_name      VARCHAR2(30),  /* schema where the SQL is parsed */

  module                   VARCHAR2(48),  /* last app. module for the SQL */

  action                   VARCHAR2(32),  /* last app. action for the SQL */

  elapsed_time             NUMBER,        /* elapsed time for SQL statement */

  cpu_time                 NUMBER,        /* CPU time for this SQL */

  buffer_gets              NUMBER,        /* number of buffer gets */

  disk_reads               NUMBER,        /* number of disk reads  */

  direct_writes            NUMBER,        /* number of direct writes */

  rows_processed           NUMBER,        /* # of rows processed by this SQL */

  fetches                  NUMBER,        /* number of fetches */

  executions               NUMBER,        /* total executions of this SQL */

  end_of_fetch_count       NUMBER,        /* exec. count up to end of fetch */

  optimizer_cost           NUMBER,        /* Optimizer cost for this SQL */

  optimizer_env            RAW(2000),     /* optimizer environment */

  priority                 NUMBER,        /* user-defined priority (1,2,3) */

  command_type             NUMBER,        /* statement type - INSERT, etc. */

  first_load_time          VARCHAR2(19),  /* load time of parent cursor */

  stat_period              NUMBER,        /* period (seconds) when the */

                                          /* stats for SQL stmt collected */

  active_stat_period       NUMBER,        /* effecive time (in seconds) */

                                          /* for which SQL stmt was active */

  other                    CLOB,          /* col for user defined attrs */

  plan_hash_value          NUMBER,        /* plan hash value of the plan */

  sql_plan                 sql_plan_table_type, /* explain plan */

  bind_list                sql_binds,     /* list of user specified binds */

accept_sql_profile is both a dbms_sqltune procedure and function that creates the SQL Profile recommended by the SQL Tuning Advisor . The SQL Text is supplied via a SQL Tuning Task object. The function returns the name of the SQL Profile as a VARCHAR2.

add_sqlset_reference is a dbms_sqltune function that adds a new reference to a pre-existing SQL Tuning Set (STS). The description can be up to 256 characters. It returns the new reference’s identifier as a NUMBER.

alter_sql_profile is a dbms_sqltune procedure that modified the specified attributes of a pre-existing SQL Profile. The legal values for attributes to change are STATUS, NAME, DESCRIPTION and CATEGORY.

cancel_tuning_task is a dbms_sqltune procedure that terminates the currently executing tuning task with a complete cleanup where all intermediate data is deleted.

capture_cursor_cache_sqlset is a dbms_sqltune procedure that incrementally captures workload activity from the cursor cache into a SQL Tuning Set (STS). The capture time is expressed in seconds, the capture option can be INSERT, UPDATE or MERGE, and the capture mode can be either MODE_REPLACE_OLD_STATS or MODE_ACCUMULATE_STATS.

create_sqlset is a both a dbms_sqltune procedure and function that creates a new SQL Tuning Set (STS). When used as a function, it returns the name of the new SQL Tuning Set as a VARCHAR2.

create_stgtab_sqlprof is a dbms_sqltune procedure that creates a staging table for copying SQL Profiles across systems, like those for export and import operations.

create_stgtab_sqlset_sqlprofis a procedure that creates a staging table for copying SQL Tuning Sets (STS) across systems, like those for export and import operations.

create_tuning_task is an overloaded function for creating tuning tasks via a SQL statement’s text, a SQL statement’s cursor cache identifier, an AWR range of snapshot identifiers, or a SQL Tuning Set. In all cases, it returns the new tuning task name as a VARCHAR2. Create_tuning_task based upon a SQL statement’s text is as follows:

create_tuning_task  based upon a SQL statement’s cursor cache identifier is as follows:

create_tuning_task based upon an AWR range of snapshot identifiers is as follows:

create_tuning_task based upon an SQL Tuning Set (STS) is as follows:

delete_sqlset is a procedure that deletes or drops the SQL statements from an STS.

drop_sql_profile is a procedure that  drops the user specified SQL Profile.

drop_sqlset is a procedure that deletes the user specified STS and it must be inactive.

drop_tuning_task is a procedure that deletes the user specified SQL tuning task. The task and all intermediate data are deleted.

execute_tuning_task is both a procedure and function that executes a pre-existing SQL tuning task. As a function, it returns the new task’s name as a VARCHAR2.

implement_tuning_task is a procedure that implements the SQL Profile recommendations made by the SQL Tuning Advisor .

interrupt_tuning_task is a procedure that interrupts the currently running tuning task but does so as a normal type exit, and thus leaves intermediate results for review.

load_sqlset is a procedure that populates the user specified SQL Tuning Set (STS) with the set of selected SQL statements. It can be run multiple times and the results are simply aggregated. The valid load options are INSERT, UPDATE and MERGE. The valid update options are REPLACE and ACCUMULATE. The valid update conditions are OLD and NEW. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes.

pack_stgtab_sqlprof is a procedure that simply copies the SQL Profile data from SYS to the user specified staging table.

pack_stgtab_sqlset is a procedure that simply copies the SQL Tuning Set from SYS to the user specified staging table.

remap_stgtab_sqlprof is a procedure that permits modifications to the SQL Profile data in the staging table prior to extraction.

remap_stgtab_sqlset is a procedure that permits modifications to the STS data in the staging table prior to extraction.

remove_sqlset_reference is a procedure that drops an existing reference to a pre-existing SQL Tuning Set.

report_auto_tuning_task is a function that displays a report based upon the automatic tuning task data. It returns that report as a CLOB.

report_sql_monitoris a function that displays a report based upon the real time SQL monitoring data being captured. It returns that report as a CLOB.

report_tuning_task  is a function that displays a report based upon the tuning task data. It returns that report as a CLOB.

reset_tuning_task is a procedure that prepares a non-active SQL tuning task for re-execution, i.e. resets it to starting state.

resume_tuning_task is a procedure that resumes execution of a non-active SQL tuning task that was previously interrupted, meaning stopped and in a re-executable state. Note that one cannot resume a task to tune a single SQL statement. This procedure is for resuming those based on SQL Tuning Sets.

script_tuning_task is a function that generates a SQL*Plus script to implement a set of tuning advisor recommendations. It returns that SQL script as a CLOB. The valid record types include ALL, PROFILES, STATISTICS and any comma separated list combining these values.

select_cursor_cache is a function that collects SQL statements live from the cursor cache and returns them one at a time, i.e. pipelined function, via a return type of SYS.SQLSET. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes.

select_sqlset is a function that reads the contents of a STS and returns those values one at a time via a return type of SYS.SQLSET. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes.

select_workload_repository is an overloaded function that scans the contents of a range of AWR snapshots or a snapshot baseline and returns those values one at a time via a return type of SYS.SQLSET. The valid update attributes are NULL, BASIC, TYPICAL, ALL and a comma separated list of execution context attributes. The format for select_workload_repository when working off snapshot ranges is as follows:

The format for select_workload_repositorywhen working off a baseline representing a range of snapshots is as follows:

set_tuning_task_parameteris an overloaded procedure that updates a SQL Tuning parameter of type VARCHAR2 or NUMBER to a user specified value. The valid values for parameter are MODE, USERNAME, DAYS_TO_EXPIRE, EXECUTION_DAYS_TO_EXPIRE, DEFAULT_EXECUTION_TYPE, TIME_LIMIT, LOCAL_TIME_LIMIT, TEST_EXECUTE, BASIC_FILTER, OBJECT_FILTER, PLAN_FILTER, RANK_MEASURE1, RANK_MEASURE2, RANK_MEASURE3, RESUME_FILTER, SQL_LIMIT, SQL_PERCENTAGE, ACCEPT_SQL_PROFILES, MAX_AUTO_SQL_PROFILES, and MAX_SQL_PROFILES_PER_EXEC.

sqltext_to_signature is a function that simply returns the signature for user specified SQL text. The value is returned as a NUMBER.

unpack_stgtab_sqlprof is a procedure that copies the SQL Profile data from the user specified staging table to SYS, thus creating a profile.

unpack_stgtab_sqlset is a procedure that simply copies the SQL Tuning Set from the user specified staging table to SYS, thus creating a STS.

update_sqlset  is an overloaded procedure that modifies the user selected fields for SQL statements within a SQL Tuning Set (STS).

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.