Oracle优化-leading提示和ordered提示以及materialize提示

以下内容适用于Oracle 10.2.0.5及其以上版本

一个查询很慢,原始SQL如下:

select
 a.*
  from (select       
        ssi.ID,
        'small_station_info' TB,
        (select sbi.name
            from scene_base_info sbi
          where sbi.id = ssi.antenna_selection) as antenna_selection,
        ssi.antenna_height,
        ssi.down_angle,
        ssi.azimuth_angle,
        ssi.ITI_ID,
        sa.longitude,
        sa.latitude,
        sa.attach_id
          from consolidation_demand cd
          left join demand_test_info dti
            on cd.id = dti.cd_id
          left join demand_plan_info dpi
            on dti.id = dpi.tdl_id
          left join building_plan_info bpi
            on dpi.id = bpi.dpi_id
          left join NEAR_FAR_PLACE_INFO nfpi
            on bpi.id = nfpi.bpi_id
          left join SMALL_STATION_INFO ssi
            on nfpi.id = ssi.nfpi_id
          left join site_attachment sa
            on TO_NUMBER(sa.longitude) is not null
          AND TO_NUMBER(sa.latitude) > 26.074423
          AND TO_NUMBER(sa.latitude) < 26.077573
          AND TO_NUMBER(sa.longitude) > 119.191148
          AND TO_NUMBER(sa.longitude) < 119.197649
          AND sa.attach_name =
              substr(ssi.AZIMUTH_ANGLE_PHOTO,
                      instr(ssi.AZIMUTH_ANGLE_PHOTO, '/', -1) + 1,
                      length(ssi.AZIMUTH_ANGLE_PHOTO))) a
 where a.longitude is not null

表都不大,执行计划如下:

已选择 12 行。


执行计划
----------------------------------------------------------
Plan hash value: 1917963167

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |                        |    1 |  253 |  519  (2)| 00:00:07 |
|  1 |  TABLE ACCESS BY INDEX ROWID        | SCENE_BASE_INFO        |    1 |    14 |    1  (0)| 00:00:01 |
|*  2 |  INDEX UNIQUE SCAN                | SCENE_BASE_INFO_PK      |    1 |      |    0  (0)| 00:00:01 |
|  3 |  VIEW                              |                        |    1 |  253 |  519  (2)| 00:00:07 |
|*  4 |  FILTER                            |                        |      |      |            |      |
|*  5 |    HASH JOIN OUTER                  |                        |    1 |  251 |  519  (2)| 00:00:07 |
|*  6 |    HASH JOIN OUTER                |                        |    83 |  8134 |  505  (1)| 00:00:07 |
|*  7 |      HASH JOIN OUTER                |                        |    83 |  7304 |  501  (1)| 00:00:07 |
|*  8 |      HASH JOIN OUTER              |                        |    83 |  6391 |  493  (1)| 00:00:06 |
|*  9 |        HASH JOIN OUTER              |                        |    83 |  5478 |  271  (1)| 00:00:04 |
|  10 |        MERGE JOIN CARTESIAN        |                        |    36 |  2052 |    21  (0)| 00:00:01 |
|* 11 |          TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT        |    1 |    53 |    16  (0)| 00:00:01 |
|* 12 |          INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD |    1 |      |    15  (0)| 00:00:01 |
|  13 |          BUFFER SORT                |                        |  6725 | 26900 |    5  (0)| 00:00:01 |
|  14 |          INDEX FAST FULL SCAN      | PK_CONSOLIDATION_DEMAND |  6725 | 26900 |    5  (0)| 00:00:01 |
|  15 |        TABLE ACCESS FULL          | DEMAND_TEST_INFO        | 15459 |  135K|  249  (1)| 00:00:03 |
|  16 |        TABLE ACCESS FULL            | DEMAND_PLAN_INFO        |  8787 | 96657 |  221  (1)| 00:00:03 |
|  17 |      TABLE ACCESS FULL            | BUILDING_PLAN_INFO      |  3244 | 35684 |    8  (0)| 00:00:01 |
|  18 |      TABLE ACCESS FULL              | NEAR_FAR_PLACE_INFO    |  389 |  3890 |    3  (0)| 00:00:01 |
|  19 |    TABLE ACCESS FULL              | SMALL_STATION_INFO      |  594 | 90882 |    13  (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- access("SBI"."ID"=:B1)
- filter("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOTO",'
              /',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
- access("NFPI"."ID"="SSI"."NFPI_ID"(+))
- access("BPI"."ID"="NFPI"."BPI_ID"(+))
- access("DPI"."ID"="BPI"."DPI_ID"(+))
- access("DTI"."ID"="DPI"."TDL_ID"(+))
- access("CD"."ID"="DTI"."CD_ID"(+))
- filter("SA"."LONGITUDE" IS NOT NULL)
- access(TO_NUMBER("LONGITUDE")>119.191148 AND TO_NUMBER("LATITUDE")>26.074423 AND
              TO_NUMBER("LONGITUDE")<119.197649 AND TO_NUMBER("LATITUDE")<26.077573)
      filter(TO_NUMBER("LONGITUDE") IS NOT NULL AND TO_NUMBER("LATITUDE")<26.077573 AND
              TO_NUMBER("LATITUDE")>26.074423)

这个执行计划,看起来无比正常,应该要left join的都有。

但问题的关键在于10 步骤-- MERGE JOIN CARTESIAN。笛卡尔乘积的排序合并连接,这个需要耗费很长时间。

等待这个结果要耗费几十秒,甚至要更久!
如何解决这样的问题,有以下几个方法:

  1. 重新收集每个表的统计数据--这个没有实验过,但即使那么做,可能也无效。不过从本例看,很有可能是这个导致的。
  2. 启用leading提示,结合其它提示
  3. 使用materialize提示

使用leading提示

select /*+ no_merge(a) no_push_pred(a) */
 a.*
  from (select
        /*+ leading(cd dti dpi bpi ssi) */
        ...) a
 where a.longitude is not null
/

执行计划

已选择 12 行。


执行计划
----------------------------------------------------------
Plan hash value: 1844304918

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time  |
---------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                        |    1 |  253 |  520  (2)| 00:00:07 |
|  1 |  TABLE ACCESS BY INDEX ROWID  | SCENE_BASE_INFO        |    1 |    14 |    1  (0)| 00:00:01 |
|*  2 |  INDEX UNIQUE SCAN          | SCENE_BASE_INFO_PK      |    1 |      |    0  (0)| 00:00:01 |
|  3 |  VIEW                        |                        |    1 |  253 |  520  (2)| 00:00:07 |
|*  4 |  HASH JOIN                  |                        |    1 |  251 |  520  (2)| 00:00:07 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT        |    1 |    53 |    16  (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD |    1 |      |    15  (0)| 00:00:01 |
|*  7 |    HASH JOIN RIGHT OUTER      |                        | 23606 |  4564K|  503  (2)| 00:00:07 |
|  8 |    TABLE ACCESS FULL        | SMALL_STATION_INFO      |  594 | 90882 |    13  (0)| 00:00:01 |
|*  9 |    HASH JOIN RIGHT OUTER    |                        | 15459 |  679K|  490  (2)| 00:00:06 |
|  10 |      TABLE ACCESS FULL        | NEAR_FAR_PLACE_INFO    |  389 |  3890 |    3  (0)| 00:00:01 |
|* 11 |      HASH JOIN RIGHT OUTER    |                        | 15459 |  528K|  486  (2)| 00:00:06 |
|  12 |      TABLE ACCESS FULL      | BUILDING_PLAN_INFO      |  3244 | 35684 |    8  (0)| 00:00:01 |
|* 13 |      HASH JOIN RIGHT OUTER  |                        | 15459 |  362K|  477  (1)| 00:00:06 |
|  14 |        TABLE ACCESS FULL      | DEMAND_PLAN_INFO        |  8787 | 96657 |  221  (1)| 00:00:03 |
|* 15 |        HASH JOIN OUTER        |                        | 15459 |  196K|  255  (1)| 00:00:04 |
|  16 |        INDEX FAST FULL SCAN  | PK_CONSOLIDATION_DEMAND |  6725 | 26900 |    5  (0)| 00:00:01 |
|  17 |        TABLE ACCESS FULL    | DEMAND_TEST_INFO        | 15459 |  135K|  249  (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- access("SBI"."ID"=:B1)
- access("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PH
              OTO",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
- filter("SA"."LONGITUDE" IS NOT NULL)
- access(TO_NUMBER("LONGITUDE")>119.191148 AND TO_NUMBER("LATITUDE")>26.074423 AND
              TO_NUMBER("LONGITUDE")<119.197649 AND TO_NUMBER("LATITUDE")<26.077573)
      filter(TO_NUMBER("LONGITUDE") IS NOT NULL AND TO_NUMBER("LATITUDE")<26.077573 AND
              TO_NUMBER("LATITUDE")>26.074423)
- access("NFPI"."ID"="SSI"."NFPI_ID"(+))
- access("BPI"."ID"="NFPI"."BPI_ID"(+))
- access("DPI"."ID"="BPI"."DPI_ID"(+))
- access("DTI"."ID"="DPI"."TDL_ID"(+))
- access("CD"."ID"="DTI"."CD_ID"(+))

没有笛卡尔的merge join .步骤4还是一个hash join 。
执行很快,大概可以0.17秒

使用materialize提示

1  WITH A AS
2   (select /*+MATERIALIZE */
3     .....)
4  select a.* from A WHERE a.longitude is not null

执行计划

已选择 12 行。


执行计划
----------------------------------------------------------
Plan hash value: 3536941173

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             | 23606 |  5832K|   700   (2)| 00:00:09 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |       |
|   2 |   LOAD AS SELECT               |                             |       |       |            |       |
|*  3 |    HASH JOIN RIGHT OUTER       |                             | 23606 |  5786K|   520   (2)| 00:00:07 |
|   4 |     TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT             |     1 |    53 |    16   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_SITE_ATTACHMENT_JWD     |     1 |       |    15   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER      |                             | 23606 |  4564K|   503   (2)| 00:00:07 |
|   7 |      TABLE ACCESS FULL         | SMALL_STATION_INFO          |   594 | 90882 |    13   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER     |                             | 15459 |   679K|   490   (2)| 00:00:06 |
|   9 |       TABLE ACCESS FULL        | NEAR_FAR_PLACE_INFO         |   389 |  3890 |     3   (0)| 00:00:01 |
|* 10 |       HASH JOIN RIGHT OUTER    |                             | 15459 |   528K|   486   (2)| 00:00:06 |
|  11 |        TABLE ACCESS FULL       | BUILDING_PLAN_INFO          |  3244 | 35684 |     8   (0)| 00:00:01 |
|* 12 |        HASH JOIN RIGHT OUTER   |                             | 15459 |   362K|   477   (1)| 00:00:06 |
|  13 |         TABLE ACCESS FULL      | DEMAND_PLAN_INFO            |  8787 | 96657 |   221   (1)| 00:00:03 |
|* 14 |         HASH JOIN OUTER        |                             | 15459 |   196K|   255   (1)| 00:00:04 |
|  15 |          INDEX FAST FULL SCAN  | PK_CONSOLIDATION_DEMAND     |  6725 | 26900 |     5   (0)| 00:00:01 |
|  16 |          TABLE ACCESS FULL     | DEMAND_TEST_INFO            | 15459 |   135K|   249   (1)| 00:00:03 |
|* 17 |   VIEW                         |                             | 23606 |  5832K|   180   (2)| 00:00:03 |
|  18 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D68A2_721EF047 | 23606 |  4103K|   180   (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------

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

   3 - access("SA"."ATTACH_NAME"(+)=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOT
              O",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
   5 - access(TO_NUMBER("LONGITUDE"(+))>119.191148 AND TO_NUMBER("LATITUDE"(+))>26.074423 AND
              TO_NUMBER("LONGITUDE"(+))<119.197649 AND TO_NUMBER("LATITUDE"(+))<26.077573)
       filter(TO_NUMBER("LONGITUDE"(+)) IS NOT NULL AND TO_NUMBER("LATITUDE"(+))<26.077573 AND
              TO_NUMBER("LATITUDE"(+))>26.074423)
   6 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
   8 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
  10 - access("DPI"."ID"="BPI"."DPI_ID"(+))
  12 - access("DTI"."ID"="DPI"."TDL_ID"(+))
  14 - access("CD"."ID"="DTI"."CD_ID"(+))
  17 - filter("A"."LONGITUDE" IS NOT NULL)

也很快,大约0.19~0.2左右。
之所以慢,主要是因为要先生成gt表 SYS_TEMP_0FD9D68A2_721EF047。

总结

1.最好先收集统计数据

2.在收集统计数据无效的情况下,考虑使用leading提示,其次materialize提示也会破坏oracle优化器一些自以为明智的计划(优化器的不足,oracle已经提到了,这就是hint的由来)

3.dba要优化一个库,不是一个很容易的事情,需要做很多工作。

相关推荐