今天帮客户优化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内,问题完美解决!