生产系统SQL低效索引案例

前不久,无意中看到生产中一则TOP SQL,消耗资源较多,感觉有很大优化空间

SQL_ID  1agm6bmnhh609, child number 24
--------------------------------------
select *
  from UMETRIP_EVENTLOG
where FLIGHTDATE = :1 
   and EVENTTYPE = 'GATE_CHG' 
   and PASSREASON = '-91000

TIME         EXECU_D      BUFFER        DISK      ET_D     ROWS_PRO                                        
------------ ------- ----------- ----------- --------- ------------                                        
2020052004         2      229527       75055       205         4201                                        
2020052101         2      288633       67290       166         4061                                        
2020052202         2      226528       74570       181         4224                                        
2020052203         2      205266       50359       134         4421                                        
2020052303         2      206985       67949       156         4638                                        
2020052306         1      152840       76679       196         1723                                        
2020052601         2      213294       47103       125         3441                                        
2020052603         2      209591        9441        36         3765                                        
2020052810         2      248329       30198        91         4765                                        
2020052813         2      271982       46553       123         4849

这条SQL执行次数并不多,却在AWR中进入了TOP 10 SQL,执行1-2次消耗了200000+的逻辑读,表记录数25042676行,NDV如下:

COLUMN_NAME                       NDV NUL  NUM_NULLS     DATA_TYPE      LAST_ANALYZED        HISTOGRAM
------------------------------ ---------- --- ---------- ------------ -------------------- ---------
CITYCODE                             2379 Y         6073 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
DESTA                                2365 Y          849 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
EVENTTYPE                              59 Y            0 VARCHAR2     2020-05-27 22:09:30  FREQUENCY
EXTRAINFO                         2189824 Y     14600645 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDATE                           1891 Y          723 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
FLIGHTDYNFIELD                         45 Y         3651 VARCHAR2     2020-05-27 22:09:30  NONE
FLIGHTNO                            58972 Y            0 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
ID                               25042676 Y            0 NUMBER       2020-05-27 22:09:30  HEIGHT BA
INFOAFTER                          713856 Y          336 VARCHAR2     2020-05-27 22:09:30  HEIGHT BA
INFOBEFOR                          243808 Y     21175542 VARCHAR2     2020-05-27 22:09:30  NONE
INSERTTIME                         626176 Y            0 DATE         2020-05-27 22:09:30  HEIGHT BA
ISUSED                                  2 Y          165 NUMBER       2020-05-27 22:09:30  FREQUENCY
PASSREASON                             91 Y      3295111 NUMBER       2020-05-27 22:09:30  FREQUENCY


Plan hash value: 3303868666
-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       | 22722 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| UMETRIP_EVENTLOG      |    72 |  7776 | 22722   (1)| 00:04:33 |
|*  2 |   INDEX RANGE SCAN          | UMETRIP_EVENTLOG_IDX2 | 17964 |       |  5724   (1)| 00:01:09 |
-----------------------------------------------------------------------------------------------------

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


   1 - filter("PASSREASON"=(-91000))
   2 - access("EVENTTYPE"='GATE_CHG' AND "FLIGHTDATE"=:1)
       filter("FLIGHTDATE"=:1)

目前SQL执行计划走了UMETRIP_EVENTLOG_IDX2的索引,EVENTTYPE/ID/CITYCODE/FLIGHTDATE,起到过滤作用的只有EVENTTYPE,而FLIGHTDATE只能起到过滤作用。

范例说明:
SQL> create index t2_idx01 on t2(owner,status,object_id);


Index created.

SQL_ID  ba3cn8urz9zw5, child number 0
-------------------------------------
select * from t2 where owner='ALBERT' and OBJECT_ID =69794


Plan hash value: 2520814956


-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |     5 (100)|          |      1 |00:00:00.08 |       7 |      2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |     5   (0)| 00:00:01 |      1 |00:00:00.08 |       7 |      2 |
|*  2 |   INDEX SKIP SCAN           | T2_IDX01 |      1 |      1 |     4   (0)| 00:00:01 |      1 |00:00:00.08 |       6 |      2 | 
-----------------------------------------------------------------------------------------------------------------------------------
--如果STATUS NDV低,CBO会自动选择INDEX SKIP SCAN

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_SS(@"SEL$1" "T2"@"SEL$1" ("T2"."OWNER" "T2"."STATUS" "T2"."OBJECT_ID"))
      END_OUTLINE_DATA
  */


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


   2 - access("OWNER"='ALBERT' AND "OBJECT_ID"=69794)
       filter("OBJECT_ID"=69794)


SQL> create index t2_idx02 on t2(owner,object_id,status);


Index created.



SQL_ID  ba3cn8urz9zw5, child number 0
-------------------------------------
select * from t2 where owner='ALBERT' and OBJECT_ID =69794


Plan hash value: 1171872799


-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |     4 (100)|          |      1 |00:00:00.02 |       5 |      2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2       |      1 |      1 |     4   (0)| 00:00:01 |      1 |00:00:00.02 |       5 |      2 |
|*  2 |   INDEX RANGE SCAN          | T2_IDX02 |      1 |      1 |     3   (0)| 00:00:01 |      1 |00:00:00.02 |       4 |      2 |
-----------------------------------------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T2@SEL$1
   2 - SEL$1 / T2@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T2"@"SEL$1" ("T2"."OWNER" "T2"."OBJECT_ID" "T2"."STATUS"))
      END_OUTLINE_DATA
  */


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

   2 - access("OWNER"='ALBERT' AND "OBJECT_ID"=69794)  --直接access,起到索引的作用

回到本案例中,虽然谓词字段的NDV没有那么完美,但是查看数据分布 ,

select *
  from UMETRIP_EVENTLOG
where FLIGHTDATE = :1 --数据量占比最多:14.57%
   and EVENTTYPE = 'GATE_CHG' --数据量占比2.19%
   and PASSREASON = '-91000  --数据量占比.65%

可以创建如下索引:create index index_name on UMETRIP_EVENTLOG(EVENTTYPE,PASSREASON,FLIGHTDATE) online; --PASSREASON字段选择性很好,可以直接access index进行过滤,避免回表再过滤
优化前后对比:


Historical Plans Summary(dba_hist_sqlstat):


  RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS      EXECS FIRST_SNAP          LAST_SNAP
---- --------------- ------------ ------------ ----------- ---------- ---------- ---------- ------------------- -------------------
   1      3303868666       29.691        1.592       76693      12824       1304          3 2020-06-07 05:00:20 2020-06-08 01:00:17
   2      1439626452       64.673        4.832      113712      26366       1529          5 2020-06-07 05:00:20 2020-06-09 02:00:22




Current Plans Summary(gv_sql):


RN PLAN_HASH_VALUE AVG_ETIME_S  AVG_CPU_S    AVG_BUFFERS  AVG_READS   AVG_ROWS TOTAL_EXEC FIRST_LOAD_TIME      LAST_ACTIVE
--- --------------- ------------ ------------ ----------- ---------- ---------- ---------- -------------------- --------------------
  1      3778090478        0.056        0.011        2697          4       2593         32 2020-05-08/15:25:06  2020-06-12 13:06:01


执行计划:
SQL_ID  1agm6bmnhh609, child number 0
-------------------------------------
select *         from UMETRIP_EVENTLOG         where FLIGHTDATE = :1
       and EVENTTYPE = 'GATE_CHG'           and PASSREASON = '-91000'


Plan hash value: 3778090478


-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |       |       |    58 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| UMETRIP_EVENTLOG      |    79 |  8611 |    58   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | UMETRIP_EVENTLOG_IDX6 |    79 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------



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


   2 - access("EVENTTYPE"='GATE_CHG' AND "PASSREASON"=(-91000) AND "FLIGHTDATE"=:1)