今天帮客户优化SQL，语句用了复杂的视图嵌套视图形式，但外层条件有唯一性较好的参数代入，客户反映，平时此查询在1秒内，目前是10秒左右，于是看了一下执行计划，计划的后半部分的确消耗很大，并对某个大表进行了全表扫描操作，见下图：
Select * From (Select distinct * From ambase.exp_vd检验报告查询 Where 申请序号i='157605' and (检验组合序号i='11974' or 微生物否b=1) Order By 组合显示顺序,检验组合序号i,明细显示顺序) T
由于该SQL结构非常复杂，视图嵌套了多层子视图，为了提高优化效率，直接调用了11G的sql tunning包进行分析：
select sql_id,sql_text from v$sql where sql_text like '%Select distinct * From ambase.exp_vd检验报告查询%';
记下sql_id直接调用下面PLSQL：
Set serveroutput On
declare
tuning_task varchar2(30);
begin
tuning_task:=dbms_sqltune.create_tuning_task(sql_id => 'afzsj3v3rh062');
dbms_output.put_line(tuning_task);
end;
/
记下该任务名’任务_100206’
执行调优包：
exec dbms_sqltune.execute_tuning_task(‘任务_100206’);
查看调优报告：
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘任务_100206’) FROM DUAL;
报告具体内容：
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 任务_100206
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 05/21/2020 14:21:49
Completed at : 05/21/2020 14:24:17
-------------------------------------------------------------------------------
Schema Name: AMBASE
SQL ID : afzsj3v3rh062
SQL Text : Select * From (Select distinct * From exp_vd检验报告查询 Where
申请序号i='157605' and (检验组合序号i='11974' or 微生物否b=1) Order By
组合显示顺序,检验组合序号i,明细显示顺序) T
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 99.61%)
------------------------------------------
- 系统中存在手动创建的 SQL profile。
Name: SYS_SQLPROF_0172362f824e0001
Status: ENABLED
Validation results
------------------
已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
则另一计划可能只执行了一部分。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): 7.325027 .005704 99.92 %
CPU Time (s): 7.313888 .005699 99.92 %
User I/O Time (s): 0 0
Buffer Gets: 70549 273 99.61 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. the original plan 的统计信息是 1 执行的平均值。
2. the SQL profile plan 的统计信息是 10 执行的平均值。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1154905213
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8709 | | 89814 (1)| 00:17:58 |
| 1 | VIEW | | 1 | 8709 | | 89814 (1)| 00:17:58 |
| 2 | SORT ORDER BY | | 1 | 3285 | | 89814 (1)| 00:17:58 |
| 3 | HASH UNIQUE | | 1 | 3285 | | 89813 (1)| 00:17:58 |
|* 4 | HASH JOIN OUTER | | 1 | 3285 | | 89812 (1)| 00:17:58 |
| 5 | NESTED LOOPS OUTER | | 1 | 3280 | | 53 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 3270 | | 52 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 8 | 664 | | 28 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 8 | 616 | | 21 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 8 | 384 | | 5 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | EXP_D检验申请列 | 1 | 31 | | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_EXP_D检验申请| 1 | | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | LIS_D检验申请明 | 8 | 136 | | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | IDX_97958_申请序| 3 | | | 2 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID | LIS_D病人信息 | 1 | 29 | | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_LIS_D病人信息| 1 | | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | LIS_T检验仪器 | 1 | 6 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IK_LIS_T仪器_系 | 1 | | | 0 (0)| 00:00:01 |
|* 18 | VIEW | LIS_VI检验明细 | 1 | 3187 | | 3 (0)| 00:00:01 |
| 19 | UNION-ALL PARTITION | | | | | | |
| 20 | NESTED LOOPS | | 11 | 1782 | | 25 (0)| 00:00:01 |
|* 21 | HASH JOIN OUTER | | 11 | 1606 | | 14 (0)| 00:00:01 |
|* 22 | HASH JOIN OUTER | | 11 | 1430 | | 9 (0)| 00:00:01 |
|* 23 | HASH JOIN OUTER | | 11 | 1265 | | 6 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID| LIS_D报告内容 | 11 | 1144 | | 4 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX_97930_病人序| 11 | | | 3 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID| LIS_T敏感程度 | 3 | 33 | | 2 (0)| 00:00:01 |
| 27 | INDEX FULL SCAN | LIS_T敏感程度 | 3 | | | 1 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | LIS_T仪器检验组 | 722 | 10830 | | 3 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | LIS_T检验项目组 | 2571 | 41136 | | 5 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | LIS_T检验项目 | 1 | 16 | | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PK_LIS_T检验项目| 1 | | | 0 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 1 | 43 | | 4 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 2 | 43 | | 4 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | LIS_D细菌报告 | 2 | 20 | | 2 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | IDX_98011_病人序| 2 | | | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_LIS_T细菌档案| 1 | | | 0 (0)| 00:00:01 |
| 37 | TABLE ACCESS BY INDEX ROWID | LIS_T细菌档案 | 1 | 33 | | 1 (0)| 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | EXP_B标本 | 1 | 10 | | 1 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | PK_B标本 | 1 | | | 0 (0)| 00:00:01 |
| 40 | VIEW | | 416K| 2035K| | 89758 (1)| 00:17:58 |
| 41 | SORT GROUP BY | | 416K| 58M| | 89758 (1)| 00:17:58 |
| 42 | VIEW | | 4549K| 642M| | 89758 (1)| 00:17:58 |
| 43 | HASH UNIQUE | | 4549K| 282M| 332M| 89758 (1)| 00:17:58 |
|* 44 | HASH JOIN RIGHT OUTER | | 4549K| 282M| | 19182 (1)| 00:03:51 |
| 45 | TABLE ACCESS FULL | LIS_T检验项目组 | 2571 | 33423 | | 5 (0)| 00:00:01 |
|* 46 | HASH JOIN RIGHT OUTER | | 4549K| 225M| | 19165 (1)| 00:03:50 |
| 47 | TABLE ACCESS FULL | LIS_T仪器检验组 | 722 | 8664 | | 3 (0)| 00:00:01 |
|* 48 | HASH JOIN | | 4549K| 173M| | 19150 (1)| 00:03:50 |
| 49 | INDEX FAST FULL SCAN | PK_LIS_T检验项目| 4298 | 21490 | | 4 (0)| 00:00:01 |
| 50 | TABLE ACCESS FULL | LIS_D报告内容 | 4641K| 154M| | 19133 (1)| 00:03:50 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."系统序号"="D"."病人序号I"(+))
11 - access("C"."系统序号"=157605)
13 - access("B"."申请序号I"=157605)
14 - filter(("A"."病人类型"=U'\4F4F\9662' OR "A"."病人类型"=U'\6025\8BCA' OR "A"."病人类型"=U'\95E8\8BCA') AND
("A"."标本状态"=U'\5BA1\6838' OR "A"."标本状态"=U'\6253\5370'))
15 - access("A"."系统序号"="B"."病人序号I")
17 - access("A"."仪器序号I"="C"."系统序号"(+))
18 - filter(("A"."检验组合序号I"=11974 OR DECODE(SYS_OP_C2C("C"."接口说明"),'微生物',1,0)=1) AND
("A"."检验组合序号I"="B"."检验组合I" OR DECODE(SYS_OP_C2C("C"."接口说明"),'微生物',1,0)=1))
21 - access("A"."检验组合序号I"="D"."检验项目组合序号I"(+) AND "A"."检验项目序号I"="D"."检验项目序号I"(+) AND
"A"."隶属机构I"="D"."隶属机构I"(+))
22 - access("A"."仪器序号I"="E"."仪器序号I"(+) AND "A"."检验组合序号I"="E"."检验组合序号I"(+) AND "A"."隶属机构I"="E"."隶属机构I"(+))
23 - access("A"."EXP结果"="B"."名称"(+))
25 - access("A"."病人序号I"="A"."系统序号")
26 - filter("B"."有效状态B"(+)=1)
31 - access("A"."检验项目序号I"="C"."系统序号")
35 - access("A"."病人序号I"="A"."系统序号")
36 - access("A"."细菌序号I"="B"."系统序号")
39 - access("A"."标本类型I"="E"."系统序号"(+))
44 - access("A"."检验组合序号I"="D"."检验项目组合序号I"(+) AND "A"."检验项目序号I"="D"."检验项目序号I"(+) AND
"A"."隶属机构I"="D"."隶属机构I"(+))
46 - access("A"."仪器序号I"="E"."仪器序号I"(+) AND "A"."检验组合序号I"="E"."检验组合序号I"(+) AND "A"."隶属机构I"="E"."隶属机构I"(+))
48 - access("A"."检验项目序号I"="C"."系统序号")
2- Using SQL Profile
--------------------
Plan hash value: 103330010
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8709 | 281 (2)| 00:00:04 |
| 1 | VIEW | | 1 | 8709 | 281 (2)| 00:00:04 |
| 2 | SORT ORDER BY | | 1 | 7604 | 281 (2)| 00:00:04 |
| 3 | HASH UNIQUE | | 1 | 7604 | 280 (2)| 00:00:04 |
| 4 | NESTED LOOPS OUTER | | 1 | 7604 | 279 (1)| 00:00:04 |
| 5 | NESTED LOOPS OUTER | | 1 | 7594 | 278 (1)| 00:00:04 |
| 6 | NESTED LOOPS | | 1 | 7592 | 265 (1)| 00:00:04 |
|* 7 | HASH JOIN OUTER | | 8 | 664 | 24 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 8 | 616 | 21 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 8 | 616 | 21 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 8 | 384 | 5 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | EXP_D检验申请列 | 1 | 31 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_EXP_D检验申请| 1 | | 1 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | LIS_D检验申请明 | 8 | 136 | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_97958_申请序| 3 | | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_LIS_D病人信息| 1 | | 1 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | LIS_D病人信息 | 1 | 29 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | LIS_T检验仪器 | 71 | 426 | 3 (0)| 00:00:01 |
|* 18 | VIEW | LIS_VI检验明细 | 1 | 7509 | 30 (0)| 00:00:01 |
| 19 | UNION ALL PUSHED PREDICATE | | | | | |
| 20 | NESTED LOOPS | | 11 | 1782 | 26 (0)| 00:00:01 |
|* 21 | HASH JOIN OUTER | | 11 | 1606 | 15 (0)| 00:00:01 |
|* 22 | HASH JOIN OUTER | | 11 | 1430 | 10 (0)| 00:00:01 |
|* 23 | HASH JOIN OUTER | | 11 | 1265 | 7 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID| LIS_D报告内容 | 11 | 1144 | 4 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | IDX_97930_病人序| 11 | | 3 (0)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | LIS_T敏感程度 | 3 | 33 | 3 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | LIS_T仪器检验组 | 722 | 10830 | 3 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | LIS_T检验项目组 | 2571 | 41136 | 5 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | LIS_T检验项目 | 1 | 16 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_LIS_T检验项目| 1 | | 0 (0)| 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 43 | 4 (0)| 00:00:01 |
| 32 | NESTED LOOPS | | 2 | 43 | 4 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | LIS_D细菌报告 | 2 | 20 | 2 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | IDX_98011_病人序| 2 | | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PK_LIS_T细菌档案| 1 | | 0 (0)| 00:00:01 |
| 36 | TABLE ACCESS BY INDEX ROWID | LIS_T细菌档案 | 1 | 33 | 1 (0)| 00:00:01 |
| 37 | VIEW PUSHED PREDICATE | | 1 | 2 | 13 (8)| 00:00:01 |
| 38 | SORT GROUP BY | | 11 | 1144 | 13 (8)| 00:00:01 |
| 39 | VIEW | | 11 | 1144 | 13 (8)| 00:00:01 |
| 40 | SORT UNIQUE | | 11 | 715 | 13 (8)| 00:00:01 |
| 41 | NESTED LOOPS | | 11 | 715 | 12 (0)| 00:00:01 |
|* 42 | HASH JOIN OUTER | | 11 | 660 | 12 (0)| 00:00:01 |
|* 43 | HASH JOIN OUTER | | 11 | 517 | 7 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID| LIS_D报告内容 | 11 | 385 | 4 (0)| 00:00:01 |
|* 45 | INDEX RANGE SCAN | IDX_97930_病人序| 11 | | 3 (0)| 00:00:01 |
| 46 | TABLE ACCESS FULL | LIS_T仪器检验组 | 722 | 8664 | 3 (0)| 00:00:01 |
| 47 | TABLE ACCESS FULL | LIS_T检验项目组 | 2571 | 33423 | 5 (0)| 00:00:01 |
|* 48 | INDEX UNIQUE SCAN | PK_LIS_T检验项目| 1 | 5 | 0 (0)| 00:00:01 |
| 49 | TABLE ACCESS BY INDEX ROWID | EXP_B标本 | 1 | 10 | 1 (0)| 00:00:01 |
|* 50 | INDEX UNIQUE SCAN | PK_B标本 | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"."仪器序号I"="C"."系统序号"(+))
12 - access("C"."系统序号"=157605)
14 - access("B"."申请序号I"=157605)
15 - access("A"."系统序号"="B"."病人序号I")
16 - filter(("A"."病人类型"=U'\4F4F\9662' OR "A"."病人类型"=U'\6025\8BCA' OR "A"."病人类型"=U'\95E8\8BCA')
AND ("A"."标本状态"=U'\5BA1\6838' OR "A"."标本状态"=U'\6253\5370'))
18 - filter(("A"."检验组合序号I"=11974 OR DECODE(SYS_OP_C2C("C"."接口说明"),'微生物',1,0)=1) AND
("A"."检验组合序号I"="B"."检验组合I" OR DECODE(SYS_OP_C2C("C"."接口说明"),'微生物',1,0)=1))
21 - access("A"."检验组合序号I"="D"."检验项目组合序号I"(+) AND "A"."检验项目序号I"="D"."检验项目序号I"(+) AND
"A"."隶属机构I"="D"."隶属机构I"(+))
22 - access("A"."仪器序号I"="E"."仪器序号I"(+) AND "A"."检验组合序号I"="E"."检验组合序号I"(+) AND
"A"."隶属机构I"="E"."隶属机构I"(+))
23 - access("A"."EXP结果"="B"."名称"(+))
25 - access("A"."病人序号I"="A"."系统序号")
26 - filter("B"."有效状态B"(+)=1)
30 - access("A"."检验项目序号I"="C"."系统序号")
34 - access("A"."病人序号I"="A"."系统序号")
35 - access("A"."细菌序号I"="B"."系统序号")
42 - access("A"."检验组合序号I"="D"."检验项目组合序号I"(+) AND "A"."检验项目序号I"="D"."检验项目序号I"(+) AND
"A"."隶属机构I"="D"."隶属机构I"(+))
43 - access("A"."仪器序号I"="E"."仪器序号I"(+) AND "A"."检验组合序号I"="E"."检验组合序号I"(+) AND
"A"."隶属机构I"="E"."隶属机构I"(+))
45 - access("A"."病人序号I"="A"."系统序号")
48 - access("A"."检验项目序号I"="C"."系统序号")
50 - access("A"."标本类型I"="E"."系统序号"(+))
-------------------------------------------------------------------------------
从报告明显看出，接受其建议的SQL PROFILE后，性能提升显著，于是接受了该概要文件：
execute dbms_sqltune.accept_sql_profile(task_name => '任务_100206',task_owner => 'SYS', replace => TRUE);
重新运行此语句，果然性能提升明显，执行此语句在1秒内！
但此时客户的问题仍然未解决，原因是：此类SQL非常多，不同的参数代入，就出现一个SQL语句，这个也是当初开发的诟病吧，没有使用绑定变量，但这个时候让客户去改程序，显然不太现实，然后如果一条条这样去调用调优包去优化，也不现实：
于是想到最可能的统计信息失效或过期的问题，于是对当前库所有统计信息过期的表进行更新，并且对该语句涉及的表，做等级最高的统计信息收集，但仍然无效果！
这个时候，解决的思路是：怎样让其SQL概要文件建议，能共享到类似的语句上呢，于是对官方文档进行各种搜索和排查，终于功夫不负有心人，查到接受概要文件的时候，有一个参数可以控制其作用域，就是：force_match=>true
于是重新删掉概要文件，并重新接受，加上此参数：
exec dbms_sqltune.accept_sql_profile(task_name=>'任务_100206',task_owner=>'SYS',replace=>true,force_match=>true)
重新执行其他类似的语句，果然有效，最终执行计划的COST下降至300内，问题完美解决！
发表评论 取消回复