Using Oracle 10g Tuning Utilities

by
Alex Bubernak

Oracle is always improving life for DBAs and developers
with each release of the RDBMS. 10g has many new features to help troubleshoot
and tune the database. Starting with 10g, Oracle has introduced Advisors. These
Advisors are part of the ADDM (Automatic Database Diagnostic Monitor) and run
in the background as long as the parameter STATICS_LEVEL is set to at least
TYPICAL, which is the default for a new database installed. Here we will
discuss some of the more common utilities used and how they can help.

DBMS_SQLTUNE package

This package is of great help in tuning SQL statements. Simply
put, it analyzes the SQL statement and gives back recommendations on how to improve
the statements performance if it finds a better way. It will show you the
current execution plan as well as the execution plan if you were to make the
recommended changes. It will give the reasoning for the recommendation and even
give you the commands to implement the recommended change. This is a great tool
to use in developing new applications as well as troubleshooting existing ones
in a production environment.

This package will let you create SQL profiles. SQL
profiling analyzes a statement and offers a better Execution Plan if one is
available. You can apply this plan to the SQL profile and SQL will use this
plan the next time it is run. This can give better performance without changing
application code.

You can use this to compare SQL statements using tuning
sets.

Let’s look at some of the components that make up the
DBMS_SQLTUNE package. Please see Oracle documentation for all components in the
DBMS_SQLTUNE package. I will show and explain the most commonly used procedures.

  • CREATE_TUNING_TASK – This function will create a new
    tuning task and return the name of the task. A generated name will be given if
    one is not specified. Some of the inputs are:

    • SQL_ID – This is the SQL_ID of an
      existing SQL statement in the SGA. This id can be found in V$SQL, V$SQLTEXT,
      V$SESSION and a few other V$ views. You cannot use this along with SQL_TEXT.

    • SQL_TEXT – This can be used to
      enter the SQL statement manually if it is not in the SGA. You cannot use this
      along with SQL_ID.

    • BIND_LIST – Pass in any bind
      variables for SQL statement. Type of input is SQL_BINDS.

    • USER_NAME – The owner of the SQL
      statement.

    • SCOPE – This sets the scope and
      there are two different settings.
      • LIMITED
        – This makes recommendations based on analysis of the following: SQL structure,
        statistics and access path.

      • COMPREHINSIVE
        – This makes recommendations based on all analysis of LIMITED and SQL
        Profiling. Note: This is the most resource intensive and can take much time to
        complete. A TIME_LIMIT setting can be specified to limit how long it should
        analyze.

    • TIME_LIMIT – Time in seconds of how
      long a COMPREHENSIZE analysis can run. The default is 30 minutes if not set.
      That does not mean the analysis will run that long, just that is the limit of
      how long it can run. If the analysis hasn’t completed by the time limit, it
      will give recommendations on what it has found so far.

    • TASK_NAME – Name given to identify
      a task.

    • DESCRIPTION – To give a description
      of the task.
  • DROP_TUNING_TASK – This procedure drops a tuning task
    that has been created. It only takes one input parameter.

    • TASK_NAME – This is the name of the
      task created with the CREATE_TUNING_TASK function.
  • EXECUTE_TUNING_TASK – Will run the created tuning
    task.

    • TASK_NAME – Name of the task
      created from CREATE_TUNING_TASK function.
  • CANCEL_TUNING_TASK – Cancel a task that is currently
    running.

    • TASK_NAME – Name of the task
      running from EXECUTE_TUNING_TASK.
  • INTERRUPT_TUNING_TASK – This will interrupt an
    executing task and allow you to query data collected up to the interruption.

    • TASK_NAME – Name of the task
      running from EXECUTE_TUNING_TASK.
  • RESET_TUNING_TASK – Reset a tuning task to its
    initial state and delete all data collected.

    • TASK_NAME – Name of the task
      created from CREATE_TUNING_TASK function.
  • RESUME_TUNING_TASK – Resume an interrupted tuning
    task. This can only be used with tuning sets.

    • TASK_NAME – Name of the task
      created from CREATE_TUNING_TASK function.
  • REPORT_TUNING_TASK – This will return a report of
    what it found and offer any recommendations from the analysis.

    • TASK_NAME – Name of the task
      executed from EXECUTE_TUNING_TASK procedure.

    • TYPE – Type of report to produce,
      values are: HTML, XML and TEXT. Default is TEXT.

    • LEVEL – Level of detail for the
      report, values are:

      • BASIC – Gives General
        information section, Findings and Explain Plans.

      • TYPICAL – Same as BASIC except with
        SQL profiling information, recommendations and more detailed Explain Plans.
        This is the default.

      • ALL – Same as TYPICAL except with
        detailed object information and very detailed Explain Plan.


    • SECTION – What section to show in the report; values
      are:

      • FINDINGS – Report only the Finding
        section.

      • PLANS – Report only the Explain
        Plan section for current SQL and any recommendations.

      • INFORMATION – Report only the
        General Information section.

      • ERRORS – Only report Error section
        if found, otherwise only General Information shown.

      • ALL – Report every section. This
        is the default.

  • SCRIPT_TUNING_TASK – This function will output PL/SQL
    commands to implement recommendations from an executed tuning task.

    • TASK_NAME – Name of the task
      created from CREATE_TUNING_TASK function.

    • REC_TYPE – Types of recommendations
      to include. These can be separated by commas (e.g. ‘MyTaskName’,’INDEXES,STATISTICS’),
      values are:

      • PROFILES – Only
        commands to implement recommended SQL Profiles.

      • STATISTICS – Only
        commands for stale and/or missing statistics.

      • INDEXES – Only commands
        for index recommendations.

      • ALL – Commands for all
        recommendations; this is the default.

There are more procedures in the DBMS_SQLTUNE package that I
will talk about later in this chapter. I want to show you how to use this tool
for tuning before going into detail about the other procedures. Now, enough of
the boring descriptions and lets start using this tool.

Let’s create a small sample table with data.

Create_test_table.sql
drop table tb
/
create table tb (tb_seq number, 
		var_col1 varchar2(32), 
		var_col2 varchar2(64), 
		date_col date)
/
drop sequence tb_num_seq
/
create sequence tb_num_seq start with 1
/

declare
   cnt number := 0;
begin
   for cnt in 1 .. 100
    loop
      insert into tb values (tb_num_seq.nextval,
			     'Test',
			     'Description for test',
			     sysdate);
   end loop;
end;
/
commit
/

In this example, we will tune a single SQL statement.

First, we create a new Tuning Task named ‘Useless Task’ using
the CREATE_TUNING_TASK function.

DECLARE
  my_task_name varchar2(30);
  sql_txt clob;

BEGIN

sql_txt := 'select var_col2 from tb where TB_seq = :b1 and var_col2 =  :b2';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                           sql_text => sql_txt,
--                           user_name => 'MYUSER', -- if sql owner is different than current session
                           scope => 'COMPREHENSIVE',
                           time_limit => 300,  -- 5 minute time limit
                           task_name => 'Useless_Task',
                           description => 'Tune Useless Query Task');
END;
/

Once run, the ‘Useless Task’ will be created. We must now
execute the newly created task.

SQL> execute dbms_sqltune.execute_tuning_task (task_name => 'Useless_Task’);

Note: If this task is running too long for your liking, you
can ‘execute dbms_sqltune.interrupt_tuning_task(‘Useless Task’);’ from another
session.

When the procedure completes, you can execute the following
SQL to return a report.


SQL> set long 5000 — must set this to display output
SQL> set pagesize 0
SQL> select dbms_sqltune.report_tuning_task(‘Useless_Task’) from dual;

Below is the output of the report. I numbered the lines in
the output for readability.

     1  GENERAL INFORMATION SECTION
     2  -------------------------------------------------------------------------------
     3  Tuning Task Name                  : Useless_Task
     4  Tuning Task Owner                 : MYUSER
     5  Scope                             : COMPREHENSIVE
     6  Time Limit(seconds)               : 300
     7  Completion Status                 : COMPLETED
     8  Started at                        : 01/26/2007 14:25:37
     9  Completed at                      : 01/26/2007 14:25:38
    10  Number of Statistic Findings      : 1
    11  Number of Index Findings          : 1
    12
    13  -------------------------------------------------------------------------------
    14  Schema Name: MYUSER
    15  SQL ID     : guu7ppk7pu1a5
    16  SQL Text   : select var_col2 from tb where TB_seq = :b1 and var_col2 =  :b2
    17
    18  -------------------------------------------------------------------------------
    19  FINDINGS SECTION (2 findings)
    20  -------------------------------------------------------------------------------
    21
    22  1- Statistics Finding
    23  ---------------------
    24    Table "MYUSER"."TB" was not analyzed.
    25
    26    Recommendation
    27    --------------
    28    - Consider collecting optimizer statistics for this table.
    29      execute dbms_stats.gather_table_stats(ownname => 'MYUSER', tabname =>
    30              'TB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
    31             method_opt => 'FOR ALL COLUMNS SIZE AUTO');
    32
    33    Rationale
    34    ---------
    35      The optimizer requires up-to-date statistics for the table in order to
    36      select a good execution plan.
    37
    38  2- Index Finding (see explain plans section below)
    39  --------------------------------------------------
    40    The execution plan of this statement can be improved by creating one or more
    41    indices.
    42
    43    Recommendation (estimated benefit: 100%)
    44    ----------------------------------------
    45    - Consider running the Access Advisor to improve the physical schema design
    46      or creating the recommended index.
    47      create index MYUSER.IDX$$_51CC0001 on MYUSER.TB('TB_SEQ','VAR_COL2');
    48
    49    Rationale
    50    ---------
    51      Creating the recommended indices significantly improves the execution plan
    52      of this statement. However, it might be preferable to run "Access Advisor"
    53      using a representative SQL workload as opposed to a single statement. This
    54      will allow to get comprehensive index recommendations which takes into
    55      account index maintenance overhead and additional space consumption.
    56
    57  -------------------------------------------------------------------------------
    58  EXPLAIN PLANS SECTION
    59  -------------------------------------------------------------------------------
    60
    61  1- Original
    62  -----------
    63  Plan hash value: 1750851749
    64
    65  --------------------------------------------------------------------------
    66  | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    67  --------------------------------------------------------------------------
    68  |   0 | SELECT STATEMENT  |      |     1 |    47 |     3   (0)| 00:00:01 |
    69  |*  1 |  TABLE ACCESS FULL| TB   |     1 |    47 |     3   (0)| 00:00:01 |
    70  --------------------------------------------------------------------------
    71
    72  Predicate Information (identified by operation id):
    73  ---------------------------------------------------
    74
    75     1 - filter("TB_SEQ"=:B1 AND "VAR_COL2"=:B2)
    76
    77  2- Using New Indices
    78  --------------------
    79  Plan hash value: 3914465704
    80
    81  --------------------------------------------------------------------------------
    82  ---
    83  | Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time
    84    |
    85  --------------------------------------------------------------------------------
    86  ---
    87  |   0 | SELECT STATEMENT |                |     1 |    47 |     1   (0)| 00:00:0
    88  1 |
    89  |*  1 |  INDEX RANGE SCAN| IDX$$_51CC0001 |     1 |    47 |     1   (0)| 00:00:0
    90  1 |
    91  --------------------------------------------------------------------------------
    92  ---
    93
    94  Predicate Information (identified by operation id):
    95  ---------------------------------------------------
    96
    97     1 - access("TB_SEQ"=:B1 AND "VAR_COL2"=:B2)
    98
    99 	-------------------------------------------------------------------------------

Lines 1 through 16 are the General Information section. This
will show a summery of analysis.

Line 7 will give the status of the analysis.

7  Completion Status                 : COMPLETED

In this case the analysis completed; it would show
INTERRUPTED if you executed INTERRUPT_TUNING_TASK during execution of analysis.

The following shows under the General Information section
if the task ends before the analysis completed due to its running longer than the
TIME_LIMIT set in CREATE_TUNING_TASK. A similar error would show for
INTERRUPTED or if the execution of the task was cancelled using
CANCEL_TUNING_TASK..

ERRORS SECTION

-------------------------------------------------------------------------------
- The current operation was interrupted because it timed out.

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

Line 15 is the sql id that can be linked to some of the V$
views.

15  SQL ID     : guu7ppk7pu1a5

Lines 19 through 55 are the Findings section. This will tell
what it has found during the analysis. We can see that there are two findings.

Line 43 tells us potentially how much can be gained by implementing
the recommendation.

Lines 29 and 31 give the SQL to implement the first set of
recommendations.

 	29    execute dbms_stats.gather_table_stats(ownname => 'MYUSER', tabname =>
 	30            'TB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
	31             method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Line 47 gives the SQL to implement the second set of
recommendations.

Lines 61 on show the Explain Plan of the original statement.
And here is the great part, lines 77 on shows what the new plan will look like
if you implement the recommendations.

You can see the original plan shows a cost of 3 and the new
one is 1. An index is obvious in this case to improve this statement, but it
may be to your advantage to implement one recommendation at a time and run the
analysis again because the analysis may have better recommendations after you
implement the first. In this case, the analysis pointed to the table not having
any statistics. This can affect the index it recommends.

Let’s implement the first recommendation.

Note: Always check the SQL before implementing, never
assume it to always be correct. Besides, you may want to name the objects
differently and maybe place them in a different tablespace. As in this example
we have to remove the single quotes from ‘on MYUSER.TB(‘TB_SEQ’);’ in order to
create the correct index.

SQL> execute dbms_stats.gather_table_stats(ownname => 'MYUSER', tabname =>
 	'TB', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
  method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Now if we execute, then run the report again, we will see
the index recommendation is different. That is because it now knows what the
data looks like.

 create index MYUSER.IDX$$_51EA0001 on MYUSER.TB('TB_SEQ');

Now that you’ve seen a basic example, let’s look at how to
create another tuning task by modifying the previous one. We can use the same task
name if we drop it first or use a different name; this way we can compare
different tasks.

This example is using bind variables.

tuning2.sql 
     1  DECLARE
     2    my_task_name varchar2(30);
     3    sql_txt clob;
     4
     5  BEGIN
     6
     7  sql_txt := 'select var_col2 from tb where TB_seq = :b1 and var_col2 =  :b2';
     8
     9    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    10                            sql_text => sql_txt,
    11                            bind_list => sql_binds(anydata.ConvertNumber(91),anydata.ConvertVarchar2('Test')),
    12  --                           user_name => 'MYUSER', -- if sql owner is different than current session
    13                             scope => 'COMPREHENSIVE',
    14                             time_limit => 300,
    15                             task_name => 'Useless_Task_WBinds',
    16                             description => 'Tune Useless Query Task Using Binds');
    17  END;
    18  /

Line 11 is where bind variables are entered using bind_list.

In the above we are entering two bind variables, anydata.ConvertNumber(91)
is entering the number 91 and anydata.ConvertVarchar2(‘Test’) is a varchar
value of ‘Test’. I’m not going to go into the details of SQL_BINDS, you can
find more information in Oracle documentation, but below is a list of input
functions.

	ConvertNumber(IN NUMBER) RETURN AnyData
	ConvertDate(IN DATE) RETURN AnyData
	ConvertChar(IN CHAR) RETURN AnyData
	ConvertVarchar(IN VARCHAR) RETURN AnyData
	ConvertVarchar2(IN VARCHAR2) RETURN AnyData
	ConvertRaw(IN RAW) RETURN AnyData
	ConvertBlob(IN BLOB) RETURN AnyData
	ConvertClob(IN CLOB) RETURN AnyData
	ConvertBfile(IN BFILE) RETURN AnyData
	ConvertObject(IN "<object_type>") RETURN AnyData
	ConvertRef(IN REF "<object_type>") RETURN AnyData
	ConvertCollection(IN "<COLLECTION_1>") RETURN AnyData

Once, the above is created and executed (remember that this
task will have to be dropped or a new name given before this task is created),
the report will show a new recommendation.

Recommendation (estimated benefit: 100%)
 ----------------------------------------
 - Consider running the Access Advisor to improve the physical schema design
   or creating the recommended index.
   create index MYUSER.IDX$$_52AD0001 on MYUSER.TB('VAR_COL2');

Now let’s change the bind data for the var_col2 column to a
value that is in the table. So anydata.ConvertVarchar2(‘Test’) is changed to
anydata.ConvertVarchar2(‘Description for test’). Let’s recreate the task,
execute and run the report.

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

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

Now we see that there aren’t any recommendations. From this example,
you can see how bind data can make a difference to the findings.

Latest Articles