您现在的位置是：首页 >技术杂谈 >Oracle SQLTUNING优化SQL网站首页技术杂谈
Oracle SQLTUNING优化SQL
简介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”
);
创建索引后新给的执行计划规避了全表扫描，性能也有极大的提升
将该优化建议提交给业务部门，让其在开发测试环境先做充分测试，若切实有效再上生产
风语者！平时喜欢研究各种技术，目前在从事后端开发工作，热爱生活、热爱工作。
站长推荐
- SpringSecurity实现前后端分离认证授权
SpringSecurity实现前后端分离认证授权 - stm32使用HAL库配置串口中断收发数据（保姆级教程）
stm32使用HAL库配置串口中断收发数据（保姆级教程） - U8W/U8W-Mini使用与常见问题解决
U8W/U8W-Mini使用与常见问题解决 - 【社区图书馆】伴我前行的一本书《The C Programming Language》
【社区图书馆】伴我前行的一本书《The C Programming Language》 - 【云原生进阶之容器】第六章容器网络6.6.1--Cilium网络方案概述
【云原生进阶之容器】第六章容器网络6.6.1--Cilium网络方案概述