Oracle 11g DBMS_SQLTUNE

1. 创建调优任务

1.1 语法

• SQL text format:

  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_text         IN CLOB,
    bind_list        IN sql_binds := NULL,
    user_name        IN VARCHAR2  := NULL,
    scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE, --limited/comprehensive，默认comprehensive值
    time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,
    task_name        IN VARCHAR2  := NULL,
    description      IN VARCHAR2  := NULL)
  RETURN VARCHAR2;
  

• SQL ID format:

  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id           IN VARCHAR2,
    plan_hash_value  IN NUMBER    := NULL,
    scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,--limited/comprehensive，默认comprehensive值
    time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,--默认1800s
    task_name        IN VARCHAR2  := NULL,
    description      IN VARCHAR2  := NULL)
  RETURN VARCHAR2;
  

• Workload Repository format:

  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    begin_snap       IN NUMBER,
    end_snap         IN NUMBER,
    sql_id           IN VARCHAR2,
    plan_hash_value  IN NUMBER    := NULL,
    scope            IN VARCHAR2  := SCOPE_COMPREHENSIVE,--limited/comprehensive，默认comprehensive值
    time_limit       IN NUMBER    := TIME_LIMIT_DEFAULT,--默认1800s
    task_name        IN VARCHAR2  := NULL,
    description      IN VARCHAR2  := NULL)
  RETURN VARCHAR2;
  

• SQLSET format:

  DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sqlset_name       IN VARCHAR2,
    basic_filter      IN VARCHAR2 :=  NULL,
    object_filter     IN VARCHAR2 :=  NULL,
    rank1             IN VARCHAR2 :=  NULL,
    rank2             IN VARCHAR2 :=  NULL,
    rank3             IN VARCHAR2 :=  NULL,
    result_percentage IN NUMBER   :=  NULL,
    result_limit      IN NUMBER   :=  NULL,
    scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,--limited/comprehensive，默认comprehensive值
    time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,--默认1800s
    task_name         IN VARCHAR2 :=  NULL,
    description       IN VARCHAR2 :=  NULL
    plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
    sqlset_owner      IN VARCHAR2 :=  NULL)
  RETURN VARCHAR2;
  

• SQL Performance Analyzer format:

  DBMS_SQLTUNE.CREATE_TUNING_TASK(
     spa_task_name     IN VARCHAR2,
     spa_task_owner    IN VARCHAR2 :=  NULL,
     spa_compare_exec  IN VARCHAR2 :=  NULL,
     basic_filter      IN VARCHAR2 :=  NULL,
     time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
     task_name         IN VARCHAR2 :=  NULL,
     description       IN VARCHAR2 :=  NULL)
   RETURN VARCHAR2; 
  

2. 执行调优任务

2.1语法

DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL)
 RETURN VARCHAR2;


DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
   task_name         IN VARCHAR2,
   execution_name    IN VARCHAR2               := NULL,
   execution_params  IN dbms_advisor.argList   := NULL,
   execution_desc    IN VARCHAR2               := NULL);

示例：
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);

3. 查看调优报告

3.1 语法

DBMS_SQLTUNE.REPORT_TUNING_TASK(
   task_name       IN   VARCHAR2,
   type            IN   VARCHAR2   := 'TEXT',
   level           IN   VARCHAR2   := 'TYPICAL', --有 BASIC/TYPICAL/ALL 参数
   section         IN   VARCHAR2   := ALL,       --有SUMMARY/FINDINGS/PLAN/INFORMATION/ERROR/ALL 参数
   object_id       IN   NUMBER     := NULL,
   result_limit    IN   NUMBER     := NULL,
   owner_name      IN    VARCHAR2  := NULL,
   execution_name  IN  VARCHAR2    := NULL)
RETURN CLOB;


示例：
-- Get the whole report for the single statement case.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task) from dual;
 
-- Show me the summary for the sts case.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY')
FROM DUAL;
 
-- Show me the findings for the statement I'm interested in.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;

4. 删除SQL调优任务

4.1 语法

DBMS_SQLTUNE.DROP_TUNING_TASK(
 task_name         IN VARCHAR2);

5. 示例

SQL_ID方式

--创建调优任务并执行
DECLARE
  my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id      => '18j0qammkwqfr',
                                                  time_limit  => 3600,
                                                  task_name   => 'SQL_TUNE_18j0qammkwqfr',
                                                  description => 'Task to tune a query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SQL_TUNE_18j0qammkwqfr');
END;

/

-- 查看调优任务状态
select t.task_name, t.ADVISOR_NAME, t.STATUS
  from dba_advisor_tasks t
 where t.task_name = 'SQL_TUNE_18j0qammkwqfr'

   	TASK_NAME	               ADVISOR_NAME	       STATUS
   	--------                  ------------          --------
 	SQL_TUNE_18j0qammkwqfr	   SQL Tuning Advisor	  COMPLETED


-- 查看调优任务报告

select  dbms_sqltune.report_tuning_task('SQL_TUNE_18j0qammkwqfr','TEXT','ALL') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQL_TUNE_18j0qammkwqfr
Tuning Task Owner  : SYS
Tuning Task ID     : 5671
Workload Type      : Single SQL Statement
Execution Count    : 1
Current Execution  : EXEC_6034
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status  : COMPLETED
Started at         : 12/15/2018 21:24:39
Completed at       : 12/15/2018 21:25:10

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 18j0qammkwqfr
SQL Text   : update t_objects a
                set (a.tablespace_name, a.flag) =
                    (select distinct b.tablespace_name, '2'
                       from t_tables b
                      where a.owner = b.owner
                        and a.object_name = b.table_name)
              where exists (select distinct b.tablespace_name
                       from t_tables b
                      where a.owner = b.owner
                        and a.object_name = b.table_name)

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Alternative Plan Finding
---------------------------
  Some alternative execution plans for this statement were found by searching
  the system's real-time and historical performance data.

  The following table lists these plans ranked by their average elapsed time.
  See section "ALTERNATIVE PLANS SECTION" for detailed information on each
  plan.

  id plan hash  last seen            elapsed (s)  origin          note            
  -- ---------- -------------------- ------------ --------------- ----------------
   1 1460256570  2018-12-15/21:09:22       54.846 Cursor Cache    not reproducible

  Information
  -----------
  - All alternative plans other than the Original Plan could not be
    reproduced in the current environment.
  - The plan with id 1 could not be reproduced in the current environment.
    For this reason, a SQL plan baseline cannot be created to instruct the
    Oracle optimizer to pick this plan in the future.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3244764335

 
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |           | 29227 |  2311K|       |   280M  (1)|936:39:02 |
|   1 |  UPDATE              | T_OBJECTS |       |       |       |            |          |
|*  2 |   HASH JOIN SEMI     |           | 29227 |  2311K|  1688K|  6712   (1)| 00:01:21 |
|   3 |    TABLE ACCESS FULL | T_OBJECTS | 29227 |  1341K|       |    71   (2)| 00:00:01 |
|   4 |    VIEW              | VW_SQ_1   |   801K|    25M|       |  4804   (1)| 00:00:58 |
|   5 |     TABLE ACCESS FULL| T_TABLES  |   801K|    20M|       |  4804   (1)| 00:00:58 |
|   6 |   HASH UNIQUE        |           |     5 |   170 |       |  4806   (1)| 00:00:58 |
|*  7 |    TABLE ACCESS FULL | T_TABLES  |    11 |   374 |       |  4805   (1)| 00:00:58 |
------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$A419B674
   3 - SEL$A419B674 / A@UPD$1
   4 - SEL$683B0107 / VW_SQ_1@SEL$1E93CC81
   5 - SEL$683B0107 / B@SEL$2
   6 - SEL$1       
   7 - SEL$1        / B@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("A"."OWNER"="ITEM_1" AND "A"."OBJECT_NAME"="ITEM_2")
   7 - filter("B"."TABLE_NAME"=:B1 AND "B"."OWNER"=:B2)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   2 - (#keys=2; upd=6,7; cmp=1,3) "A"."OWNER"[VARCHAR2,30], 
       "ITEM_1"[VARCHAR2,30], "A"."OBJECT_NAME"[VARCHAR2,128], "ITEM_2"[VARCHAR2,30], 
       "SYS_ALIAS_2".ROWID[ROWID,10], "A"."FLAG"[VARCHAR2,2], 
       "A"."TABLESPACE_NAME"[VARCHAR2,30]
   3 - "SYS_ALIAS_2".ROWID[ROWID,10], "A"."OWNER"[VARCHAR2,30], 
       "A"."OBJECT_NAME"[VARCHAR2,128], "A"."TABLESPACE_NAME"[VARCHAR2,30], 
       "A"."FLAG"[VARCHAR2,2]
   4 - "ITEM_1"[VARCHAR2,30], "ITEM_2"[VARCHAR2,30]
   5 - "B"."OWNER"[VARCHAR2,30], "B"."TABLE_NAME"[VARCHAR2,30]
   6 - (#keys=2) "B"."TABLESPACE_NAME"[VARCHAR2,30], '2'[1]
   7 - "B"."OWNER"[VARCHAR2,30], "B"."TABLE_NAME"[VARCHAR2,30], 
       "B"."TABLESPACE_NAME"[VARCHAR2,30]

-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------

Plan 1
------
 
  Plan Origin                 :Cursor Cache                  
  Plan Hash Value             :1460256570                    
  Executions                  :4                             
  Elapsed Time                :54.846 sec                    
  CPU Time                    :54.336 sec                    
  Buffer Gets                 :24506044                      
  Disk Reads                  :452                           
  Disk Writes                 :0                             
 
Notes: 
  1. Statistics shown are averaged over multiple executions.
  2. The plan with id  could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this plan in the future.
 
-----------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |           |       |       |  7710K(100)|          |
|   1 |  UPDATE               | T_OBJECTS |       |       |            |          |
|   2 |   HASH JOIN RIGHT SEMI|           | 25032 |  1980K|   225   (2)| 00:00:03 |
|   3 |    VIEW               | VW_SQ_1   | 25032 |   831K|   153   (1)| 00:00:02 |
|   4 |     TABLE ACCESS FULL | T_TABLES  | 25032 |   660K|   153   (1)| 00:00:02 |
|   5 |    TABLE ACCESS FULL  | T_OBJECTS | 29227 |  1341K|    71   (2)| 00:00:01 |
|   6 |   HASH UNIQUE         |           |     1 |    34 |   154   (2)| 00:00:02 |
|   7 |    TABLE ACCESS FULL  | T_TABLES  |     1 |    34 |   153   (1)| 00:00:02 |
-----------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$A419B674
   3 - SEL$683B0107 / VW_SQ_1@SEL$1E93CC81
   4 - SEL$683B0107 / B@SEL$2
   5 - SEL$A419B674 / A@UPD$1
   6 - SEL$1       
   7 - SEL$1        / B@SEL$1

-------------------------------------------------------------------------------

6. 参考文档

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#CHDDCCBJ