Introduction to Oracle 10g's New SQL Tuning Advisor

July 29, 2004

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







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers