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’);

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.

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.