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.
