自动SQL调优

1. 性能故障说明

应用又报告业务系统比较慢了,由于比较着急,也就不慢慢分析了,登录数据库服务器直接查看等待事件,发现如下情况:

fkrsvc1xb96v6执行这个SQL的会话等待较多,主要是:latch: cache buffers chains

                                                                                    SQL Child
       SID USERNAME EVENT                       SQL_ID             Number                     P1               P2           P3 LAST_CALL_ET
---------- -------- --------------------------- --------------- --------- ---------------------- ---------------- ------------ ------------
......
      3096 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165326157272              124            0           73
      3183 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165326157272              124            0           71
      3117 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165326157272              124            0          183
      2653 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165539065456              124            0          168
      3152 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165594113552              124            1          154
      6521 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165690186224              124            1           71
      4724 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165690186224              124            0          176
      2551 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          143
      2585 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0           64
      2730 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          178
      2760 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          145
      2796 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0           45
      2808 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          145
      3081 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          194
      3131 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          197
      3270 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0          185
      3378 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0           13
      3441 XXXXX    latch: cache buffers chains fkrsvc1xb96v6           2   13835058165876015776              124            0           30
......

一般碰到这种情况是需要分析SQL,根据情况,收集统计信息或者交给开发进行修改,或者采取其它手段。

2. 检查SQL情况

2.1 查看SQL文本

SQL> select sql_fulltext from v$sqlarea where sql_id = 'fkrsvc1xb96v6';

select prod_serv_id
  from (select distinct sp.prod_serv_id
          from srv_ro_2_res a, srv_pso_2_ro b, srv_prod_serv_order sp
         where a.object_spec_id = 1839
           and a.ro_id = b.ro_id
           and b.pso_id = sp.pso_id
           and exists (select 1
                  from srv_ro_2_res c
                 where c.ro_id = a.ro_id
                   and c.res_id = :1
                   and c.object_spec_id = 214)
           and exists (select 1
                  from srv_ro_2_res c
                 where c.ro_id = a.ro_id
                   and c.res_id = :2
                   and c.object_spec_id = 6249)
        union
        select distinct sp.prod_serv_id
          from srv_ro_2_res a, srv_pso_2_ro b, srv_prod_serv_order sp
         where a.object_spec_id in (1840, 1841)
           and a.ro_id = b.ro_id
           and b.pso_id = sp.pso_id
           and exists (select 1
                  from srv_ro_2_res c
                 where c.ro_id = a.ro_id
                   and c.res_id = :3
                   and c.object_spec_id = 214)
           and exists (select 1
                  from srv_ro_2_res c
                 where c.ro_id = a.ro_id
                   and c.res_id = :4
                   and c.object_spec_id = 6249))
 where rownum <= 10

2.2 查看SQL的执行计划

SQL> set linesize 160 pagesize 999
SQL> select * from table(dbms_xplan.display_cursor('fkrsvc1xb96v6',2, 'advanced'));
......
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |       |       |    15 (100)|          |
|*  1 |  COUNT STOPKEY                        |                        |       |       |            |          |
|   2 |   VIEW                                |                        |     2 |    26 |    15  (27)| 00:00:01 |
|*  3 |    SORT UNIQUE STOPKEY                |                        |     2 |   182 |    15  (67)| 00:00:01 |
|   4 |     UNION-ALL                         |                        |       |       |            |          |
|   5 |      NESTED LOOPS                     |                        |     1 |    91 |     6  (17)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                        |     1 |    73 |     5  (20)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                        |     1 |    55 |     4  (25)| 00:00:01 |
|   8 |         MERGE JOIN CARTESIAN          |                        |     1 |    42 |     3  (34)| 00:00:01 |
|   9 |          SORT UNIQUE                  |                        |     1 |    21 |     1   (0)| 00:00:01 |
|  10 |           TABLE ACCESS BY INDEX ROWID | SRV_RO_2_RES           |     1 |    21 |     1   (0)| 00:00:01 |
|* 11 |            INDEX RANGE SCAN           | IDX_SRV_RO_2_RES_RESID |     1 |       |     1   (0)| 00:00:01 |
|  12 |          BUFFER SORT                  |                        |     1 |    21 |            |          |
|  13 |           SORT UNIQUE                 |                        |     1 |    21 |     1   (0)| 00:00:01 |
|  14 |            TABLE ACCESS BY INDEX ROWID| SRV_RO_2_RES           |     1 |    21 |     1   (0)| 00:00:01 |
|* 15 |             INDEX RANGE SCAN          | IDX_SRV_RO_2_RES_RESID |     1 |       |     1   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS BY INDEX ROWID   | SRV_RO_2_RES           |     1 |    13 |     1   (0)| 00:00:01 |
|* 17 |          INDEX RANGE SCAN             | IDX_SRV_RO_2_RES_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  18 |        TABLE ACCESS BY INDEX ROWID    | SRV_PSO_2_RO           |     1 |    18 |     1   (0)| 00:00:01 |
|* 19 |         INDEX RANGE SCAN              | IDX_SRV_PSO_2_RO_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  20 |       TABLE ACCESS BY INDEX ROWID     | SRV_PROD_SERV_ORDER    |     1 |    18 |     1   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN              | PK_SRV_PROD_SERV_ORDER |     1 |       |     1   (0)| 00:00:01 |
|  22 |      NESTED LOOPS                     |                        |     1 |    91 |     6  (17)| 00:00:01 |
|  23 |       NESTED LOOPS                    |                        |     1 |    73 |     5  (20)| 00:00:01 |
|  24 |        NESTED LOOPS                   |                        |     1 |    55 |     4  (25)| 00:00:01 |
|  25 |         MERGE JOIN CARTESIAN          |                        |     1 |    42 |     3  (34)| 00:00:01 |
|  26 |          SORT UNIQUE                  |                        |     1 |    21 |     1   (0)| 00:00:01 |
|  27 |           TABLE ACCESS BY INDEX ROWID | SRV_RO_2_RES           |     1 |    21 |     1   (0)| 00:00:01 |
|* 28 |            INDEX RANGE SCAN           | IDX_SRV_RO_2_RES_RESID |     1 |       |     1   (0)| 00:00:01 |
|  29 |          BUFFER SORT                  |                        |     1 |    21 |            |          |
|  30 |           SORT UNIQUE                 |                        |     1 |    21 |     1   (0)| 00:00:01 |
|  31 |            TABLE ACCESS BY INDEX ROWID| SRV_RO_2_RES           |     1 |    21 |     1   (0)| 00:00:01 |
|* 32 |             INDEX RANGE SCAN          | IDX_SRV_RO_2_RES_RESID |     1 |       |     1   (0)| 00:00:01 |
|* 33 |         TABLE ACCESS BY INDEX ROWID   | SRV_RO_2_RES           |     1 |    13 |     1   (0)| 00:00:01 |
|* 34 |          INDEX RANGE SCAN             | IDX_SRV_RO_2_RES_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  35 |        TABLE ACCESS BY INDEX ROWID    | SRV_PSO_2_RO           |     1 |    18 |     1   (0)| 00:00:01 |
|* 36 |         INDEX RANGE SCAN              | IDX_SRV_PSO_2_RO_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  37 |       TABLE ACCESS BY INDEX ROWID     | SRV_PROD_SERV_ORDER    |     1 |    18 |     1   (0)| 00:00:01 |
|* 38 |        INDEX UNIQUE SCAN              | PK_SRV_PROD_SERV_ORDER |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
......

粗看这个执行计划,感觉问题点在MERGE JOIN CARTESIAN上,产生了MERGE JOIN笛卡尔积。执行计划使用MERGE JOINCARTESIAN一般原因如下:
a)SQL写得有问题,没有等值关联条件
b)表的统计信息不正确,导致生成了错误的执行计划

应用说,这个SQL以前跑得没问题,突然之间出现的问题,这就是典型的执行计划不稳定。产生这种问题的原因,一般是这个SQL涉及的表被重新表分析了,
但是分析的结果又不准确,导致Oracle生成了错误的执行计划。

这个SQL涉及到的相关表还挺多,考虑到当前系统负载已经很重,重新全部分析表代价太大,而且时间会很长,又不想手工绑定执行计划(需要找到正确的执行计划,人懒了)
先试下自动SQL调整吧,不行再进行手工处理。

3. 自动SQL调整

SQL> set serveroutput on
SQL> var tuning_task varchar2(1000);
SQL> DECLARE
  2  l_sql_id v$session.prev_sql_id%TYPE;
  3    l_tuning_task VARCHAR2(30);
  4  BEGIN
  5    l_sql_id:='fkrsvc1xb96v6';
  6    l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
  7    :tuning_task := l_tuning_task;
  8    dbms_sqltune.execute_tuning_task(l_tuning_task);
  9    dbms_output.put_line(l_tuning_task);
 10  END;
 11  /

TASK_317438

PL/SQL procedure successfully completed.

SQL> SQL> print tuning_task

TUNING_TASK
--------------------------------------------------------------------------------
TASK_317438

SQL> set linesize 160 pagesize 0 long 10000
SQL> select dbms_sqltune.report_tuning_task(:tuning_task) from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : TASK_317438
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 12/23/2016 12:46:49
Completed at                      : 12/23/2016 12:47:50
Number of SQL Profile Findings    : 1
Number of SQL Restructure Findings: 3

-------------------------------------------------------------------------------
Schema Name: xxxx
SQL ID     : fkrsvc1xb96v6
SQL Text   : select prod_serv_id from (  select distinct sp.prod_serv_id from
             srv_ro_2_res a, srv_pso_2_ro b ,srv_prod_serv_order sp where
             a.object_spec_id = 1839 and a.ro_id = b.ro_id  and b.pso_id =
             sp.pso_id  and exists(select 1 from srv_ro_2_res c where c.ro_id
             = a.ro_id  and c.res_id = :1 and c.object_spec_id = 214 )  and
             exists(select 1 from srv_ro_2_res c where c.ro_id = a.ro_id  and
             c.res_id = :2 and c.object_spec_id = 6249 )  union  select
             distinct sp.prod_serv_id from srv_ro_2_res a, srv_pso_2_ro b
             ,srv_prod_serv_order sp where a.object_spec_id in (1840,1841)
             and a.ro_id = b.ro_id and b.pso_id = sp.pso_id  and
             exists(select 1 from srv_ro_2_res c where c.ro_id = a.ro_id  and
             c.res_id = :3 and c.object_spec_id = 214 )  and exists(select 1
             from srv_ro_2_res c where c.ro_id = a.ro_id  and c.res_id = :4
             and c.object_spec_id = 6249 ) )  where rownum <= 10 ------------------------------------------------------------------------------- FINDINGS SECTION (4 findings) ------------------------------------------------------------------------------- 1- SQL Profile Finding (see explain plans section below) --------------------------------------------------------   A potentially better execution plan was found for this statement.   Recommendation (estimated benefit: 99.93%)   ------------------------------------------   - Consider accepting the recommended SQL profile.     execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_317438',
            replace => TRUE);

2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 25 of the
  execution plan.

  Recommendation
  --------------
  - Consider removing the disconnected table or view from this statement or
    add a join condition which refers to it.

  Rationale
  ---------
    A cartesian product should be avoided whenever possible because it is an
    expensive operation and might produce a large amount of data.

3- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive cartesian product operation was found at line ID 8 of the
  execution plan.

  Recommendation
  --------------
  - Consider removing the disconnected table or view from this statement or
    add a join condition which refers to it.

  Rationale
  ---------
    A cartesian product should be avoided whenever possible because it is an
    expensive operation and might produce a large amount of data.

4- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  An expensive "UNION" operation was found at line ID 3 of the execution plan.

  Recommendation
  --------------
  - Consider using "UNION ALL" instead of "UNION", if duplicates are allowed
    or uniqueness is guaranteed.

  Rationale
  ---------
    "UNION" is an expensive and blocking operation because it requires
    elimination of duplicate rows. "UNION ALL" is a cheaper alternative,
    assuming that duplicates are allowed or uniqueness is guaranteed.

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 2 of the execution plan.
  The optimizer cannot merge a view that contains a set operator.

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

1- Original With Adjusted Cost
------------------------------
Plan hash value: 572919274

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                        |    10 |   130 |  2791K  (1)| 09:18:16 |
|*  1 |  COUNT STOPKEY                        |                        |       |       |            |          |
|   2 |   VIEW                                |                        |   351 |  4563 |  2791K  (1)| 09:18:16 |
|*  3 |    SORT UNIQUE STOPKEY                |                        |   351 | 31941 |  2791K (51)| 09:18:16 |
|   4 |     UNION-ALL                         |                        |       |       |            |          |
|   5 |      NESTED LOOPS                     |                        |   245 | 22295 |  1395K  (1)| 04:39:09 |
|   6 |       NESTED LOOPS                    |                        |   287 | 20951 |  1395K  (1)| 04:39:07 |
|   7 |        NESTED LOOPS                   |                        |    73 |  4015 |  1395K  (1)| 04:39:06 |
|   8 |         MERGE JOIN CARTESIAN          |                        |  1032K|    41M|   155K  (1)| 00:31:02 |
|   9 |          SORT UNIQUE                  |                        |  1335 | 28035 |   360   (0)| 00:00:05 |
|  10 |           TABLE ACCESS BY INDEX ROWID | SRV_RO_2_RES           |  1335 | 28035 |   360   (0)| 00:00:05 |
|* 11 |            INDEX RANGE SCAN           | IDX_SRV_RO_2_RES_RESID |  1335 |       |    38   (0)| 00:00:01 |
|  12 |          BUFFER SORT                  |                        |   774 | 16254 |   154K  (1)| 00:30:58 |
|  13 |           SORT UNIQUE                 |                        |   774 | 16254 |   232   (0)| 00:00:03 |
|  14 |            TABLE ACCESS BY INDEX ROWID| SRV_RO_2_RES           |   774 | 16254 |   232   (0)| 00:00:03 |
|* 15 |             INDEX RANGE SCAN          | IDX_SRV_RO_2_RES_RESID |   857 |       |    24   (0)| 00:00:01 |
|* 16 |         TABLE ACCESS BY INDEX ROWID   | SRV_RO_2_RES           |     1 |    13 |     1   (0)| 00:00:01 |
|* 17 |          INDEX RANGE SCAN             | IDX_SRV_RO_2_RES_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  18 |        TABLE ACCESS BY INDEX ROWID    | SRV_PSO_2_RO           |     1 |    18 |     1   (0)| 00:00:01 |
|* 19 |         INDEX RANGE SCAN              | IDX_SRV_PSO_2_RO_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  20 |       TABLE ACCESS BY INDEX ROWID     | SRV_PROD_SERV_ORDER    |     1 |    18 |     1   (0)| 00:00:01 |
|* 21 |        INDEX UNIQUE SCAN              | PK_SRV_PROD_SERV_ORDER |     1 |       |     1   (0)| 00:00:01 |
|  22 |      NESTED LOOPS                     |                        |   106 |  9646 |  1395K  (1)| 04:39:08 |
|  23 |       NESTED LOOPS                    |                        |   124 |  9052 |  1395K  (1)| 04:39:07 |
|  24 |        NESTED LOOPS                   |                        |   123 |  6765 |  1395K  (1)| 04:39:06 |
|  25 |         MERGE JOIN CARTESIAN          |                        |  1032K|    41M|   155K  (1)| 00:31:02 |
|  26 |          SORT UNIQUE                  |                        |  1335 | 28035 |   360   (0)| 00:00:05 |
|  27 |           TABLE ACCESS BY INDEX ROWID | SRV_RO_2_RES           |  1335 | 28035 |   360   (0)| 00:00:05 |
|* 28 |            INDEX RANGE SCAN           | IDX_SRV_RO_2_RES_RESID |  1335 |       |    38   (0)| 00:00:01 |
|  29 |          BUFFER SORT                  |                        |   774 | 16254 |   154K  (1)| 00:30:58 |
|  30 |           SORT UNIQUE                 |                        |   774 | 16254 |   232   (0)| 00:00:03 |
|  31 |            TABLE ACCESS BY INDEX ROWID| SRV_RO_2_RES           |   774 | 16254 |   232   (0)| 00:00:03 |
|* 32 |             INDEX RANGE SCAN          | IDX_SRV_RO_2_RES_RESID |   857 |       |    24   (0)| 00:00:01 |
|* 33 |         TABLE ACCESS BY INDEX ROWID   | SRV_RO_2_RES           |     1 |    13 |     1   (0)| 00:00:01 |
|* 34 |          INDEX RANGE SCAN             | IDX_SRV_RO_2_RES_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  35 |        TABLE ACCESS BY INDEX ROWID    | SRV_PSO_2_RO           |     1 |    18 |     1   (0)| 00:00:01 |
|* 36 |         INDEX RANGE SCAN              | IDX_SRV_PSO_2_RO_ROID  |     1 |       |     1   (0)| 00:00:01 |
|  37 |       TABLE ACCESS BY INDEX ROWID     | SRV_PROD_SERV_ORDER    |     1 |    18 |     1   (0)| 00:00:01 |
|* 38 |        INDEX UNIQUE SCAN              | PK_SRV_PROD_SERV_ORDER |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
  11 - access("C"."RES_ID"=:2 AND "C"."OBJECT_SPEC_ID"=6249)
  15 - access("C"."RES_ID"=:1 AND "C"."OBJECT_SPEC_ID"=214)
  16 - filter("A"."OBJECT_SPEC_ID"=1839)
  17 - access("C"."RO_ID"="A"."RO_ID")
       filter("C"."RO_ID"="A"."RO_ID")
  19 - access("A"."RO_ID"="B"."RO_ID")
  21 - access("B"."PSO_ID"="SP"."PSO_ID")
  28 - access("C"."RES_ID"=:4 AND "C"."OBJECT_SPEC_ID"=

解读一下自动调优后的报告:

自动调优找到了4个点:
1)找到了更好的执行计划,使用 dbms_sqltune.accept_sql_profile 就可以生成SQL profile绑定这个好的执行计划,可以加快99.93%
这就是当前需要的。
2)在执行计划的第25行产生了笛卡尔积,笛卡尔积会产生大量的数据,需要避免
3)在第8行也发现了笛卡尔积,需要避免
4)发现了UNION,UNION会合并重复数据,建议使用UNION ALL替换

当然2,3,4需要开发确认,这里第一条已经找到更好的执行计划,就接受试试吧。

4. 接受执行计划

SQL> execute dbms_sqltune.accept_sql_profile(task_name => :tuning_task, replace => TRUE, force_match => true);

PL/SQL procedure successfully completed.

自动绑定后的执行计划

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------
    SQL_ID  fkrsvc1xb96v6, child number 1
    -------------------------------------------------
    select prod_serv_id from (  select distinct sp.prod_serv_id from srv_ro_2_res a, srv_pso_2_ro b
    ,srv_prod_serv_order sp where a.object_spec_id = 1839 and a.ro_id = b.ro_id  and b.pso_id = sp.pso_id
    and exists(select 1 from srv_ro_2_res c where c.ro_id = a.ro_id  and c.res_id = :1 and
    c.object_spec_id = 214 )  and exists(select 1 from srv_ro_2_res c where c.ro_id = a.ro_id  and
    c.res_id = :2 and c.object_spec_id = 6249 )  union  select distinct sp.prod_serv_id from srv_ro_2_res
    a, srv_pso_2_ro b ,srv_prod_serv_order sp where a.object_spec_id in (1840,1841) and a.ro_id = b.ro_id
    and b.pso_id = sp.pso_id  and exists(select 1 from srv_ro_2_res c where c.ro_id = a.ro_id  and
    c.res_id = :3 and c.object_spec_id = 214 )  and exists(select 1 from srv_ro_2_res c where c.ro_id =
    a.ro_id  and c.res_id = :4 and c.object_spec_id = 6249 ) )  where rownum <= 10

    Plan hash value: 4209573060

    ---------------------------------------------------------------------------------------------------------------------------
    | Id  | Pid | Ord | Operation                            | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------------------------------------
    |   0 |     |  35 | SELECT STATEMENT                     |                        |       |       |    17 (100)|          |
    |*  1 |   0 |  34 |  COUNT STOPKEY                       |                        |       |       |            |          |
    |   2 |   1 |  33 |   VIEW                               |                        |     2 |    26 |    17  (24)| 00:00:01 |
    |*  3 |   2 |  32 |    SORT UNIQUE STOPKEY               |                        |     2 |   182 |    17  (59)| 00:00:01 |
    |   4 |   3 |  31 |     UNION-ALL                        |                        |       |       |            |          |
    |   5 |   4 |  15 |      NESTED LOOPS                    |                        |     1 |    91 |     8  (13)| 00:00:01 |
    |   6 |   5 |  12 |       NESTED LOOPS                   |                        |     1 |    73 |     6  (17)| 00:00:01 |
    |   7 |   6 |   9 |        NESTED LOOPS SEMI             |                        |     1 |    55 |     5  (20)| 00:00:01 |
    |   8 |   7 |   6 |         NESTED LOOPS                 |                        |     1 |    34 |     4  (25)| 00:00:01 |
    |   9 |   8 |   3 |          SORT UNIQUE                 |                        |     1 |    21 |     2   (0)| 00:00:01 |
    |  10 |   9 |   2 |           TABLE ACCESS BY INDEX ROWID| SRV_RO_2_RES           |     1 |    21 |     2   (0)| 00:00:01 |
    |* 11 |  10 |   1 |            INDEX RANGE SCAN          | IDX_SRV_RO_2_RES_RESID |     1 |       |     1   (0)| 00:00:01 |
    |* 12 |   8 |   5 |          TABLE ACCESS BY INDEX ROWID | SRV_RO_2_RES           |     1 |    13 |     2   (0)| 00:00:01 |
    |* 13 |  12 |   4 |           INDEX RANGE SCAN           | IDX_SRV_RO_2_RES_ROID  |     6 |       |     1   (0)| 00:00:01 |
    |* 14 |   7 |   8 |         TABLE ACCESS BY INDEX ROWID  | SRV_RO_2_RES           |     1 |    21 |     1   (0)| 00:00:01 |
    |* 15 |  14 |   7 |          INDEX RANGE SCAN            | IDX_SRV_RO_2_RES_ROID  |     6 |       |     1   (0)| 00:00:01 |
    |  16 |   6 |  11 |        TABLE ACCESS BY INDEX ROWID   | SRV_PSO_2_RO           |     1 |    18 |     1   (0)| 00:00:01 |
    |* 17 |  16 |  10 |         INDEX RANGE SCAN             | IDX_SRV_PSO_2_RO_ROID  |     1 |       |     1   (0)| 00:00:01 |
    |  18 |   5 |  14 |       TABLE ACCESS BY INDEX ROWID    | SRV_PROD_SERV_ORDER    |     1 |    18 |     2   (0)| 00:00:01 |
    |* 19 |  18 |  13 |        INDEX UNIQUE SCAN             | PK_SRV_PROD_SERV_ORDER |     1 |       |     1   (0)| 00:00:01 |
    |  20 |   4 |  30 |      NESTED LOOPS                    |                        |     1 |    91 |     8  (13)| 00:00:01 |
    |  21 |  20 |  27 |       NESTED LOOPS                   |                        |     1 |    73 |     6  (17)| 00:00:01 |
    |  22 |  21 |  24 |        NESTED LOOPS SEMI             |                        |     1 |    55 |     5  (20)| 00:00:01 |
    |  23 |  22 |  21 |         NESTED LOOPS                 |                        |     1 |    34 |     4  (25)| 00:00:01 |
    |  24 |  23 |  18 |          SORT UNIQUE                 |                        |     1 |    21 |     2   (0)| 00:00:01 |
    |  25 |  24 |  17 |           TABLE ACCESS BY INDEX ROWID| SRV_RO_2_RES           |     1 |    21 |     2   (0)| 00:00:01 |
    |* 26 |  25 |  16 |            INDEX RANGE SCAN          | IDX_SRV_RO_2_RES_RESID |     1 |       |     1   (0)| 00:00:01 |
    |* 27 |  23 |  20 |          TABLE ACCESS BY INDEX ROWID | SRV_RO_2_RES           |     1 |    13 |     2   (0)| 00:00:01 |
    |* 28 |  27 |  19 |           INDEX RANGE SCAN           | IDX_SRV_RO_2_RES_ROID  |     6 |       |     1   (0)| 00:00:01 |
    |* 29 |  22 |  23 |         TABLE ACCESS BY INDEX ROWID  | SRV_RO_2_RES           |     1 |    21 |     1   (0)| 00:00:01 |
    |* 30 |  29 |  22 |          INDEX RANGE SCAN            | IDX_SRV_RO_2_RES_ROID  |     6 |       |     1   (0)| 00:00:01 |
    |  31 |  21 |  26 |        TABLE ACCESS BY INDEX ROWID   | SRV_PSO_2_RO           |     1 |    18 |     1   (0)| 00:00:01 |
    |* 32 |  31 |  25 |         INDEX RANGE SCAN             | IDX_SRV_PSO_2_RO_ROID  |     1 |       |     1   (0)| 00:00:01 |
    |  33 |  20 |  29 |       TABLE ACCESS BY INDEX ROWID    | SRV_PROD_SERV_ORDER    |     1 |    18 |     2   (0)| 00:00:01 |
    |* 34 |  33 |  28 |        INDEX UNIQUE SCAN             | PK_SRV_PROD_SERV_ORDER |     1 |       |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------------------------------------

5. 避免MERGE JOIN CARTESIAN的其它方法

a)设置隐含参数_optimizer_mjc_enabled=false来避免MERGE JOIN CARTESIAN,但是如果在系统级别设置,那样所有的SQL都不会使用MERGE JOIN CARTESIAN,这可能会出大事。
b)另外的办法是在登陆触发器中设置SESSION级别的_optimizer_mjc_enabled=false;不到万不得已也别用这种办法。
c)可以在单个SQL级别使用HINT来禁止MERGE JOIN CARTESIAN
/*+ OPT_PARAM(‘_optimizer_mjc_enabled’,’false’) */
但是这需要开发修改SQL
d)通过手工生成SQL profile,给指定SQL绑定执行计划,后续计划写篇sql profile生成的更种方法

关于紫砂壶

感悟技术人生
此条目发表在Oracle性能问题分类目录,贴了标签。将固定链接加入收藏夹。

3 则回应给 自动SQL调优

  1. 越yan说:

    我遇到过很多情况是,临时表统计信息不稳定导致这个笛卡尔集,固定统计信息避免这种情况,或者动态采样。

  2. 紫砂壶说:

    动态采样,不一定有效果,可以试试。固定统计信息,自动化能做的,就不手工费劲了,有些时侯,我发现使用hint还调不出sql_tune的效果,机器人是越来越厉害了.

  3. 越烟说:

    确实,有些涉及到直方图,所以我用的是动态采样级别6的,自动动态采样为级别2的,应用中就怕SQL_ID变化,反正综合考虑,做平横点,SQL_TUNE也经常用

评论已关闭。