使用SQL Tuning Advisor

1. 创建sql tuning任务

SQL> DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext :='SELECT /*+ ORDERED */ * '                      ||
               'FROM employees e, locations l, departments d ' ||
               'WHERE e.department_id = d.department_id AND '  ||
                     'l.location_id = d.location_id AND '      ||
                     'e.employee_id < :bnd';
 
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'HR',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'my_sql_tuning_task',
         description => 'Task to tune a query on a specified employee');
END;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16 
 
PL/SQL procedure successfully completed.

2. 执行tuning任务

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

3.查看任务执行状态。

SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
 
STATUS
-----------
COMPLETED

4.查看任务执行进度

SQL>  SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE username='HR';
 
no rows selected

5.查看tuning结果
SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
  FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : my_sql_tuning_task
Tuning Task Owner                 : HR
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 60
Completion Status                 : COMPLETED
Started at                        : 01/06/2009 16:54:53
Completed at                      : 01/06/2009 16:54:54
Number of SQL Profile Findings    : 1
Number of SQL Restructure Findings: 1
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
 
-------------------------------------------------------------------------------
Schema Name: HR
SQL ID     : dg7nfaj0bdcvk
SQL Text   : SELECT /*+ ORDERED */ * FROM employees e, locations l,
             departments d WHERE e.department_id = d.department_id AND
             l.location_id = d.location_id AND e.employee_id < :bnd
 
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.
 
  Recommendation (estimated benefit: 38.45%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_2', replace => TRUE);
 
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 2 of the
  execution plan.
 
  Recommendation
  --------------
  - Consider removing the "ORDERED" hint.
 
  Rationale
  ---------
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
    The "ORDERED" hint might force the optimizer to generate a cartesian
    product. A cartesian product should be avoided whenever possible because
    it is an expensive operation and might produce a large amount of data.
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original With Adjusted Cost
------------------------------
Plan hash value: 4204399648
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |   137 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                    |               |     1 |   137 |     7  (15)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN        |               |    23 |  2691 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    68 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_EMP_ID_PK |     1 |       |     1   (0)| 00:00:01 |
|   5 |    BUFFER SORT                |               |    23 |  1127 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | LOCATIONS     |    23 |  1127 |     2   (0)| 00:00:01 |
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
|   7 |   TABLE ACCESS FULL           | DEPARTMENTS   |    27 |   540 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND
              "L"."LOCATION_ID"="D"."LOCATION_ID")
   4 - access("E"."EMPLOYEE_ID"<:bnd> 
2- Using SQL Profile
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
--------------------
Plan hash value: 4130906763
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |   137 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |               |     1 |   137 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |               |     1 |    88 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    68 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_EMP_ID_PK |     1 |       |     1   (0)| 00:00:01 |
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
|   5 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |     1 |    20 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID | LOCATIONS     |     1 |    49 |     1   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN          | LOC_ID_PK     |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."EMPLOYEE_ID"<:bnd>   6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
 
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
   8 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
 
-------------------------------------------------------------------------------

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10972173/viewspace-530081/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10972173/viewspace-530081/