Oracle SQLTUNING优化SQL

通过AWR的TOPSQL观察到sqlid为6pk9xfmjd0s8j的sql语句耗时1分钟。


通过ash观察到，该SQL由于全表扫描占用了大量的IO资源。

解析执行计划，看到是MED_SALE_ORDER表做了全表扫描。

该表有678W+条记录，全表扫描必定IO指标会冲高，影响数据库性能。

在不了解业务逻辑的情况下，可以通过Oracle的SQL TUNING工具对该SQL进行优化。

declare
 l_tuning_task varchar2(30);
begin
 l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => '6pk9xfmjd0s8j');
 dbms_sqltune.execute_tuning_task(l_tuning_task);
 dbms_output.put_line(l_tuning_task);
end;
/

创建优化任务，可以通过DBA_ADVISOR_LOG查看任务名称。

col owner for a15
col execution_start for a25
col execution_end for a25
set linesize 200
select owner,task_id,task_name,to_char(execution_start,'yyyy-mm-dd hh24:mi:ss') execution_start,to_char(execution_end,'yyyy-mm-dd hh24:mi:ss') execution_end from dba_advisor_log order by 3;

此次优化过程中，优化任务名称查询出来是TASK_97635。
任务创建完成后，执行优化任务。

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TASK_97635' );
END;
/

通过DBA_ADVISOR_TASKS视图查看任务执行状态，当状态为COMPLETED表示任务执行完成，可以输出优化报告了。

SELECT status 
FROM   DBA_ADVISOR_TASKS 
WHERE  task_name = 'TASK_97635' ;

输出优化报告

SET LONG 10000 
SET LONGCHUNKSIZE 1000
SET LINESIZE 100      
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635' )
FROM   DUAL;

以下是优化报告内容：

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_97635
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_100531
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
Started at         : 05/22/2023 17:55:04
Completed at       : 05/22/2023 18:00:47

-------------------------------------------------------------------------------
Schema Name: ZTCX
SQL ID     : 6pk9xfmjd0s8j
SQL Text   :

...

2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 99.99%)
  ------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index OCC_B2B.IDX$$_17D630001 on
    OCC_B2B.MED_SALE_ORDER(TO_NUMBER("PLATFORM_ID"),"ORGANIZATION_ID","DR","TS"
    );

  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.

3- 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.

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------

  id plan hash  last seen            elapsed (s)  origin          note
  -- ---------- -------------------- ------------ --------------- ----------------
   1 1648087086  2023-05-22/15:00:59       13.422 AWR
   2 1676945439  2023-05-22/16:45:01       28.123 Cursor Cache    original plan

  Recommendation
  --------------
  - Consider creating a SQL plan baseline for the plan with the best average
    elapsed time.
    execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_97635',

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
            owner_name => 'SYS', plan_hash_value => 1648087086);

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

1- Original
-----------
Plan hash value: 1676945439

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
----------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)|
Time     |
----------------------------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT                  |                          |     1 |   328 |   174K  (1)|
00:34:51 |
|   1 |  HASH UNIQUE                      |                          |     1 |   328 |   174K  (1)|
00:34:51 |
|*  2 |   FILTER                          |                          |       |       |            |
         |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
|   3 |    NESTED LOOPS                   |                          |     1 |   328 |   174K  (1)|
00:34:51 |
|   4 |     NESTED LOOPS                  |                          |     1 |   328 |   174K  (1)|
00:34:51 |
|   5 |      NESTED LOOPS                 |                          |     1 |   267 |   174K  (1)|
00:34:51 |
|   6 |       NESTED LOOPS                |                          |     1 |   204 |   174K  (1)|
00:34:51 |
|*  7 |        TABLE ACCESS FULL          | MED_SALE_ORDER           |     1 |   117 |   174K  (1)|
00:34:51 |
|*  8 |        TABLE ACCESS BY INDEX ROWID| MED_SALE_ORDER_ITEM      |     2 |   174 |     4   (0)|

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
00:00:01 |
|*  9 |         INDEX RANGE SCAN          | FK_SALE_ORDER__ITEM_H_ID |     3 |       |     2   (0)|
00:00:01 |
|* 10 |       TABLE ACCESS BY INDEX ROWID | MED_OUTER_ORDER_ITEM     |     1 |    63 |     3   (0)|
00:00:01 |
|* 11 |        INDEX RANGE SCAN           | I_MED_SRC_ORDER_ITEM_ID  |     1 |       |     2   (0)|
00:00:01 |
|* 12 |      INDEX UNIQUE SCAN            | I_ID                     |     1 |       |     1   (0)|
00:00:01 |
|* 13 |     TABLE ACCESS BY INDEX ROWID   | MED_OUTER_ORDER          |     1 |    61 |     2   (0)|
00:00:01 |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SYSDATE@!>=SYSDATE@!-:SYS_B_5/:SYS_B_6)
   7 - filter("B"."ORGANIZATION_ID"=:1 AND TO_NUMBER("B"."PLATFORM_ID")=:SYS_B_4 AND
              "B"."DR"=:SYS_B_1 AND "B"."TS"<=SYSDATE@! AND "B"."TS">=SYSDATE@!-:SYS_B_5/:SYS_B_6)
   8 - filter("A"."SRC_ROWID" IS NOT NULL AND "A"."DR"=:SYS_B_0)
   9 - access("A"."SALEORDER_ID"="B"."ID")

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
  10 - filter("C"."DR"=:SYS_B_2)
  11 - access("A"."SRC_ROWID"="C"."SRC_ORDER_ITEM_ID")
  12 - access("C"."OUTER_ORDER_ID"="D"."ID")
  13 - filter("D"."DR"=:SYS_B_3 AND ("B"."CUSTOMER_ID"<>"D"."CUSTOMER_ID" OR
              "A"."OUTER_ORDER_ID"<>"D"."ID"))

2- Using New Indices
--------------------
Plan hash value: 4060887534

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

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
----------
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)|
Time     |
----------------------------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT                  |                          |     1 |   328 |    15   (7)|
00:00:01 |
|   1 |  HASH UNIQUE                      |                          |     1 |   328 |    15   (7)|
00:00:01 |
|*  2 |   FILTER                          |                          |       |       |            |
         |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
|   3 |    NESTED LOOPS                   |                          |     1 |   328 |    14   (0)|
00:00:01 |
|   4 |     NESTED LOOPS                  |                          |     1 |   328 |    14   (0)|
00:00:01 |
|   5 |      NESTED LOOPS                 |                          |     1 |   267 |    12   (0)|
00:00:01 |
|   6 |       NESTED LOOPS                |                          |     1 |   204 |     9   (0)|
00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID| MED_SALE_ORDER           |     1 |   117 |     5   (0)|
00:00:01 |
|*  8 |         INDEX RANGE SCAN          | IDX$$_17D630001          |     1 |       |     4   (0)|

DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_97635')
----------------------------------------------------------------------------------------------------
00:00:01 |
|*  9 |        TABLE ACCESS BY INDEX ROWID| MED_SALE_ORDER_ITEM      |     2 |   174 |     4   (0)|
00:00:01 |
|* 10 |         INDEX RANGE SCAN

从优化输出报告中看出，需要创建索引：
create index OCC_B2B.IDX$$_17D630001 on OCC_B2B.MED_SALE_ORDER(TO_NUMBER(“PLATFORM_ID”),“ORGANIZATION_ID”,“DR”,“TS”
);

创建索引后新给的执行计划规避了全表扫描，性能也有极大的提升


将该优化建议提交给业务部门，让其在开发测试环境先做充分测试，若切实有效再上生产