Introduction to Oracle 10g’s New SQL Tuning Advisor

Come along and see how Oracle has helped us in alleviating the
pain associated with tuning SQL.

Gone are the days of staring at countless structures and
statistics just attempting to tune a SQL statement. Oracle has once again given
us a great tool that will assist us with manual tuning of SQL statements. This
tool is called the SQL tuning advisor and it will eat SQL statements for lunch
and spit out optimizing techniques and recommendations we can use in our
efforts. Who better to tune a SQL statement than the Oracle engine itself? Now,
whether it produces the best suggestions all of the time will be up for debate
as we all get into the habit of using this tool. However, in theory, since Oracle
knows the optimizer, and we can still throw out "intelligent" hints,
the advisor should be able to tune better than anyone else should. Well, I
suggest we look at it and then you can experiment and let me know the instances
where it did not decide the proper execution path or didn’t re-write the query
any better.

If you were to use Oracle’s method of providing the SQL
Tuning Advisor with SQL statements, you would have to capture them through the
Automatic Database Diagnostic Monitor (ADDM) or through the Automatic Workload
Repository AWR. Others of us, who do not want to use OEM or actually want to
generate a tuning effort on statements that are working their way through
development, will want to provide a user defined tuning set. The interface to
the tuning advisor is through a package call DBMS_SQLTUNE. The best way to show
its capabilities is to actually just step through the procedures required to
produce and analyze a tuning set. The example I am showing here is definitely
the simplest to get you up and running with this advisor but should give you a
firm jumping off spot to further you investigation.

Creation of a Tuning Task

The first step in using the Tuning Advisor
is the creation of a tuning task. The particular example I will be taking you
through will only create a tuning task for a single SQL statement, but in theory,
you can create tuning sets composed of many SQL statements that can be supplied
by you or selected out of the shared pool. For this article, we will follow a
very simple example that will take you through the use of the tuning advisor in
order to get familiar with it and see how it works and if it provides us with
adequate information to assist us in our tuning efforts.

Our Particular Example

  1. The Purpose
    of our SQL is to join two tables, CUSTOMER & CUST_ORDER to get a count of
    the number of orders a company has.

  2. These
    two tables were created with

    1. NO
      indexes

    2. NO
      statistics

    3. NO
      referential integrity
  3. The
    desired outcome is to have Oracle’s Tuning Advisor step us through making
    modifications to the table structures and SQL to get an optimal execution

  4. After
    taking the advice of the tuning advisor, we will execute the SQL statement to
    see if we actually improved the execution of the SQL.

  5. We
    will iterate through changes to the structures and SQL until we are happy with
    the outcome.

The first step in using the advisor is to define the tuning
task we desire. For our purposes here, we will just be supplying a single SQL
statement into the tuning task and thus we should create the following procedure
and execute it. The only in-puts to the CREATE_TUNING_TASK that may need
explanation are the scope and time_limit variables. Scope can take the form of
two values LIMITED & COMPREHENSIVE. The limited scope, basically, only
produces a reasonable explain plan so if you want anything more you will find
yourself running this advisor in the comprehensive scope mode. Comprehensive
will go through iterations and different analysis of the SQL statement looking
at statistics and structures to determine alternate access methodologies that
can be used as well as point out deficiencies in the structures or SQL
provided. The time_limit variable tells the tuning advisor how long to run (in
seconds) and analyze your SQL statement.

create_tuning_task_comprehensive

SQL > CREATE or REPLACE PROCEDURE create_tuning_task IS
  2   tuning_task VARCHAR2(30);
  3   sqltext     CLOB;
  4   BEGIN
  5   sqltext :=   'select cust_name,count(*)'
  6           ||'  from customer, cust_order'
  7           ||' where customer.cust_no = cust_order.cust_no' 
  8           ||'   and customer.cust_no = 8'
  9           ||' group by cust_name';
 10   tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 11               sql_text    => sqltext,
 12               user_name   => 'SYS',
 13               scope       => 'COMPREHENSIVE',
 14               time_limit  => 30,
 15               task_name   => 'CUST_ORDERS',
 16               description => 'Tuning effort for counting customer orders');
 17   END create_tuning_task;
 18   /

SQL > exec create_tuning_task
PL/SQL procedure successfully completed.

Execution of a Tuning Task

Once you have created the tuning task you
need only execute it through the EXECUTE_TUNING_TASK function. This should run
for the designated time_limit you specified in the tuning task.

execute_tuning_task

SQL > BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘CUST_ORDERS’ );
3 END;
4 /
PL/SQL procedure successfully completed.

Reporting on the tuning task

After you have executed the tuning task,
you need only use the REPORT_TUNING_TASK function to get a comprehensive
display of what the advisor has found. After looking over a few of these reports,
you will soon develop an eye for their format. A header section tells you about
the when and how you executed the tuning task. Following that, Oracle then produces
a "Findings" section where it details what it has found and supplies recommendations
that it may have for you. Then it produces an explain area showing the explain
path it has for the current SQL.

report_tuning_task


SQL > SET LONG 1000
SQL > SET LONGCHUNKSIZE 1000
SQL > SET LINESIZE 100
SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘CUST_ORDERS’) FROM DUAL;

GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : CUST_ORDERS
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 07/18/2004 17:17:52
Completed at : 07/18/2004 17:18:11

——————————————————————————-
SQL ID : a1s4nzcnjc70f
SQL Text: select cust_name,count(*) from customer, cust_order where
customer.cust_no = cust_order.cust_no and customer.cust_no = 8
group by cust_name

——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-

1- Statistics Finding
———————
Table “SYS”.”CUST_ORDER” was not analyzed.

Recommendation
————–
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘SYS’, tabname =>
‘CUST_ORDER’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’)

Rationale
———
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

2- Statistics Finding
———————
Table “SYS”.”CUSTOMER” was not analyzed.

Recommendation
————–
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘SYS’, tabname =>
‘CUSTOMER’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’)

Rationale
———
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original
———–

————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 183K| 13M| 49567 (2)| 00:09:55 |
| 1 | SORT GROUP BY | | 183K| 13M| 49567 (2)| 00:09:55 |
| 2 | MERGE JOIN CARTESIAN| | 183K| 13M| 49553 (1)| 00:09:55 |
| 3 | TABLE ACCESS FULL | CUSTOMER | 177 | 11505 | 49 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1034 | 13442 | 49518 (2)| 00:09:55 |
| 5 | TABLE ACCESS FULL | CUST_ORDER | 1034 | 13442 | 279 (2)| 00:00:04 |
————————————————————————————

Initial Performance

This is the current real-time
experienced performance of our example SQL that we are providing to the tuning
advisor. As you can see, the execution plan is what the advisor spit out and we
are doing quit a bit of physical and logical reads. Moreover, the recursive
calls and sorts could hopefully be tuned.

Initial Performance


SQL > select cust_name,count(*)
2 from customer, cust_order
3 where customer.cust_no = cust_order.cust_no
4 and customer.cust_no = 8
5 group by cust_name;
Elapsed: 00:00:02.81

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=649 Card=171805 Bytes=13400790)
1 0 SORT (GROUP BY) (Cost=649 Card=171805 Bytes=13400790)
2 1 MERGE JOIN (CARTESIAN) (Cost=635 Card=171805 Bytes=13400790)
3 2 TABLE ACCESS (FULL) OF ‘CUSTOMER’ (TABLE) (Cost=51 Card=2 Bytes=130)
4 2 BUFFER (SORT) (Cost=598 Card=71961 Bytes=935493)
5 4 TABLE ACCESS (FULL) OF ‘CUST_ORDER’ (TABLE) (Cost=292 Card=71961 Bytes=935493)

Statistics
———————————————————-
895 recursive calls
0 db block gets
1772 consistent gets
1160 physical reads
0 redo size
465 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles