记一次处理oracle分区表索引"乱用"引起的SQL性能问题

来源：互联网发布：linux运行anaconda 编辑：程序博客网时间：2024/05/16 19:30

某系统uat环境有支SQL执行很频繁，消耗cpu资源占全部SQL的11%，用户反应系统运行较慢。
原SQL如下：

select count(*) as COUNT__ from (SELECT
-------省略-----------------------
VWOBCF WHERE 1=1 AND VWOBCF.EF_NO
= :1 ) c_____

执行计划如下：
Plan hash value: 3735712924

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                   | Name                  | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |       |       |   212 (100)|          |       |       |
|   1 | SORT AGGREGATE                             |                       |     1 |       |            |          |       |       |
|   2 |   VIEW                                      |                       |     1 |       |   212   (0)| 00:00:03 |       |       |
|   3 |    SORT AGGREGATE                           |                       |     1 |   183 |            |          |       |       |
|   4 |     NESTED LOOPS OUTER                      |                       |     2 |   366 |   212   (0)| 00:00:03 |       |       |
|   5 |      VIEW                                   |                       |     2 |   338 |   212   (0)| 00:00:03 |       |       |
|   6 |       NESTED LOOPS OUTER                    |                       |     2 |   312 |   212   (0)| 00:00:03 |       |       |
|   7 |        NESTED LOOPS OUTER                   |                       |     2 |   244 |   210   (0)| 00:00:03 |       |       |
|* 8 |         FILTER                              |                       |       |       |            |          |       |       |
|   9 |          NESTED LOOPS OUTER                 |                       |     2 |   228 |   210   (0)| 00:00:03 |       |       |
| 10 |           TABLE ACCESS BY INDEX ROWID       | EO_C_ORDER_FEE        |     2 |   138 |     4   (0)| 00:00:01 |       |       |
|* 11 |            INDEX RANGE SCAN                 | IDX_NO                |     2 |       |     3   (0)| 00:00:01 |       |       |
| 12 |           PARTITION RANGE ALL               |                       |     1 |    45 |   103   (0)| 00:00:02 |     1 |    51 |
| 13 |            TABLE ACCESS BY LOCAL INDEX ROWID| EO_C_ORDER            |     1 |    45 |   103   (0)| 00:00:02 |     1 |    51 |
|* 14 |             INDEX RANGE SCAN                | IDX_EOOR_NO           |     1 |       |   102   (0)| 00:00:02 |     1 |    51 |
|* 15 |         INDEX UNIQUE SCAN                   | UK_WH_CODE            |     1 |     8 |     0   (0)|          |       |       |
|* 16 |        TABLE ACCESS BY INDEX ROWID          | EO_ORDER_ITEM_PRICE   |     1 |    34 |     1   (0)| 00:00:01 |       |       |
|* 17 |         INDEX UNIQUE SCAN                   | UNIQUE_EOIP_ITEM_CODE |     1 |       |     0   (0)|          |       |       |
|* 18 |      INDEX UNIQUE SCAN                      | UNI_WAREHOUSE_CARRIER |     1 |    14 |     0   (0)|          |       |       |
-------------------------------------------------------------------------------------------------------------------------------------

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

   8 - filter(("EOOR"."EOOR_DELIVERY_TYPE"='0' OR "EOOR"."EOOR_DELIVERY_TYPE" IS NULL))
11 - access("EF"."EF_NO"=:1)
14 - access("EF"."EF_NO"="EOOR"."EOOR_NO")
15 - access("EOOR"."EOOR_WAREHOUSER_CODE"="CDWH"."WH_CODE")
16 - filter(("EOIP"."REC_STATUS"=0 AND "EF"."CREATE_TIME"<="EOIP"."EOIP_DATE_END" AND
              "EF"."CREATE_TIME">="EOIP"."EOIP_DATE_START"))
17 - access("EF"."EF_AD_ITEM_CODE"="EOIP"."EOIP_ITEM_CODE")
18 - access("CDWH"."WH_CODE"="ECWC"."ECWC_WAREHOUSER_CODE" AND "EOOR"."EOOR_FACT_CARRIER_CODE"="ECWC"."ECWC_CARRIER_CODE")

原SQL执行时间为10秒，逻辑读为2080771。

从执行计划上看，似乎没有明显存在性能问题的地，但仔细检查发现EO_C_ORDER表是通过local分区索引来访问的，表现在执行中的TABLE ACCESS BY LOCAL INDEX ROWID，而且扫描了多个分区（PARTITION RANGE ALL)，并确认IDX_EOOR_NO
索引字段EOOR_NO不是分区键，但建成了local索引，而oracle要求建成global索引时性能才好。所以调整如下：

重建为global索引:
drop index IDX_EOOR_NO;
CREATE INDEX IDX_EOOR_NO ON EO_C_ORDER(EOOR_NO) TABLESPACE IDX parallel 8;
alter index IDX_EOOR_NO noparallel;

再看执行计划，执行计划中TABLE ACCESS BY LOCAL INDEX ROWID变成了TABLE ACCESS BY GLOBAL INDEX ROWID。执行时间变成1秒，逻辑读降为163019。
Execution Plan
----------------------------------------------------------
Plan hash value: 732454644

-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                   | Name                  | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                       |     1 |       |    12   (0)| 00:00:01 |       |       |
|   1 | SORT AGGREGATE                             |                       |     1 |       |            |          |       |       |
|   2 |   VIEW                                      |                       |     1 |       |    12   (0)| 00:00:01 |       |       |
|   3 |    SORT AGGREGATE                           |                       |     1 |   183 |            |          |       |       |
|   4 |     NESTED LOOPS OUTER                      |                       |     2 |   366 |    12   (0)| 00:00:01 |       |       |
|   5 |      VIEW                                   |                       |     2 |   338 |    12   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS OUTER                    |                       |     2 |   312 |    12   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS OUTER                   |                       |     2 |   244 |    10   (0)| 00:00:01 |       |       |
|* 8 |         FILTER                              |                       |       |       |            |          |       |       |
|   9 |          NESTED LOOPS OUTER                 |                       |     2 |   228 |    10   (0)| 00:00:01 |       |       |
| 10 |           TABLE ACCESS BY INDEX ROWID       | EO_C_ORDER_FEE        |     2 |   138 |     4   (0)| 00:00:01 |       |       |
|* 11 |            INDEX RANGE SCAN                 | IDX_NO                |     2 |       |     3   (0)| 00:00:01 |       |       |
| 12 |           TABLE ACCESS BY GLOBAL INDEX ROWID| EO_C_ORDER            |     1 |    45 |     3   (0)| 00:00:01 | ROWID | ROWID |
|* 13 |            INDEX RANGE SCAN                 | IDX_EOOR_NO           |     1 |       |     2   (0)| 00:00:01 |       |       |
|* 14 |         INDEX UNIQUE SCAN                   | UK_WH_CODE            |     1 |     8 |     0   (0)| 00:00:01 |       |       |
|* 15 |        TABLE ACCESS BY INDEX ROWID          | EO_ORDER_ITEM_PRICE   |     1 |    34 |     1   (0)| 00:00:01 |       |       |
|* 16 |         INDEX UNIQUE SCAN                   | UNIQUE_EOIP_ITEM_CODE |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 17 |      INDEX UNIQUE SCAN                      | UNI_WAREHOUSE_CARRIER |     1 |    14 |     0   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------------------------

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

   8 - filter("EOOR"."EOOR_DELIVERY_TYPE"='0' OR "EOOR"."EOOR_DELIVERY_TYPE" IS NULL)
11 - access("EF"."EF_NO"='9999999')
13 - access("EF"."EF_NO"="EOOR"."EOOR_NO"(+))
14 - access("EOOR"."EOOR_WAREHOUSER_CODE"="CDWH"."WH_CODE"(+))
15 - filter("EOIP"."REC_STATUS"(+)=0 AND "EF"."CREATE_TIME"<="EOIP"."EOIP_DATE_END"(+) AND
              "EF"."CREATE_TIME">="EOIP"."EOIP_DATE_START"(+))
16 - access("EF"."EF_AD_ITEM_CODE"="EOIP"."EOIP_ITEM_CODE"(+))
17 - access("CDWH"."WH_CODE"="ECWC"."ECWC_WAREHOUSER_CODE"(+) AND
              "EOOR"."EOOR_FACT_CARRIER_CODE"="ECWC"."ECWC_CARRIER_CODE"(+))

Statistics
----------------------------------------------------------
        450 recursive calls
          0 db block gets
     163019 consistent gets
         38 physical reads
          0 redo size
        525 bytes sent via SQL*Net to client
        520 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         80 sorts (memory)
          0 sorts (disk)
          1 rows processed

调整前后性能对比：
索引调整前的执行时间为10s，索引更正后不到1s，执行时间提升了10倍;
索引调整前逻辑读为2080771,索引更正后逻辑读163019，cpu和逻辑读减少了92.2%.

为什么global索引要比非前缀的local索引性能要好？
global索引比非前缀的local索引扫描block要少。

0 0