[20180725]index skip-scan operation.txt

[20180725]index skip-scan operation.txt --//上午看了1会生产系统awr报表,发现一条语句出现问题,选择了错误的执行计划. --//当时看执行计划有点怪,明明执行计划查询中查询字段出现在索引的第一列,为什么执行计划出现skip-scan. --//仔细看才发现skip-scan不一定发生在第一列.我单独抽取sql语句访问该表的部分: --//sql_id = 7n4kqvamms25z select *  FROM yf_db01 a where  WHERE a.mbyf in (3, 168, 6)    AND a.ckbz =  1    AND a.ckrq >= to_date(:V00001, 'yyyy-mm-dd hh24:mi:ss')    AND a.ckrq <= to_date(:V00002, 'yyyy-mm-dd hh 24:mi:ss')    AND a.mbyf =  :V00003; --//yf_db01表建立索引如下: I_YF_DB01_CKBZ_TJBZ_TYPB_MBYF 包括字段CKBZ, TJBZ, TYPB, MBYF. I_YF_DB01_CKRQ_SQYF 包括字段 CKRQ, SQYF EXPLAIN PLAN FOR SELECT * FROM yf_db01 a WHERE     a.mbyf IN (3, 168, 6) AND a.ckbz = 1 AND a.ckrq >= TO_DATE ( :V00001, 'yyyy-mm-dd hh24:mi:ss') AND a.ckrq <= TO_DATE ( :V00002, 'yyyy-mm-dd hh24:mi:ss') AND a.mbyf = :V00003; SQL> @ &r/dp '' '' PLAN_TABLE_OUTPUT --------------------------- Plan hash value: 2010999957 -------------------------------------------------------------------------------------------------------------- | Id  | Operation                    | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                               |     1 |    67 |    30   (0)| 00:00:01 | |*  1 |  FILTER                      |                               |       |       |            |          | |*  2 |   TABLE ACCESS BY INDEX ROWID| YF_DB01                       |     1 |    67 |    30   (0)| 00:00:01 | |*  3 |    INDEX SKIP SCAN           | I_YF_DB01_CKBZ_TJBZ_TYPB_MBYF |   370 |       |    10   (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------    1 - SEL$1    2 - SEL$1 / A@SEL$1    3 - SEL$1 / A@SEL$1 Outline Data   /*+       BEGIN_OUTLINE_DATA       INDEX_SS(@"SEL$1" "A"@"SEL$1" ("YF_DB01"."CKBZ" "YF_DB01"."TJBZ" "YF_DB01"."TYPB" "YF_DB01"."MBYF"))       OUTLINE_LEAF(@"SEL$1")       ALL_ROWS       DB_VERSION('11.2.0.4')       OPTIMIZER_FEATURES_ENABLE('11.2.0.4')       IGNORE_OPTIM_EMBEDDED_HINTS       END_OUTLINE_DATA   */ Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_DATE(:V00002,'yyyy-mm-dd hh 24:mi:ss')>=TO_DATE(:V00001,'yyyy-mm-dd hh24:mi:ss') AND               (TO_NUMBER(:V00003)=3 OR TO_NUMBER(:V00003)=168 OR TO_NUMBER(:V00003)=6))    2 - filter("A"."CKRQ">=TO_DATE(:V00001,'yyyy-mm-dd hh24:mi:ss') AND               "A"."CKRQ"<=TO_DATE(:V00002,'yyyy-mm-dd hh 24:mi:ss'))    3 - access("A"."CKBZ"=1 AND "A"."MBYF"=TO_NUMBER(:V00003))        filter("A"."MBYF"=TO_NUMBER(:V00003) AND ("A"."MBYF"=3 OR "A"."MBYF"=6 OR "A"."MBYF"=168)) Column Projection Information (identified by operation id): -----------------------------------------------------------    1 - "A"."SQYF"[NUMBER,22], "A"."SQDH"[NUMBER,22], "A"."MBYF"[NUMBER,22], "A"."SQRQ"[DATE,7],        "A"."CZGH"[VARCHAR2,10], "A"."TJBZ"[NUMBER,22], "A"."CKBZ"[NUMBER,22], "A"."CKGH"[VARCHAR2,10],        "A"."CKRQ"[DATE,7], "A"."RKBZ"[NUMBER,22], "A"."RKGH"[VARCHAR2,10], "A"."RKRQ"[DATE,7],        "A"."TYPB"[NUMBER,22], "A"."BZXX"[VARCHAR2,100], "A"."AUTOCREATE"[NUMBER,22]    2 - "A"."SQYF"[NUMBER,22], "A"."SQDH"[NUMBER,22], "A"."MBYF"[NUMBER,22], "A"."SQRQ"[DATE,7],        "A"."CZGH"[VARCHAR2,10], "A"."TJBZ"[NUMBER,22], "A"."CKBZ"[NUMBER,22], "A"."CKGH"[VARCHAR2,10],        "A"."CKRQ"[DATE,7], "A"."RKBZ"[NUMBER,22], "A"."RKGH"[VARCHAR2,10], "A"."RKRQ"[DATE,7],        "A"."TYPB"[NUMBER,22], "A"."BZXX"[VARCHAR2,100], "A"."AUTOCREATE"[NUMBER,22]    3 - "A".ROWID[ROWID,10], "A"."CKBZ"[NUMBER,22], "A"."TJBZ"[NUMBER,22], "A"."TYPB"[NUMBER,22],        "A"."MBYF"[NUMBER,22] 55 rows selected. --//可以查询的第一列是CKBZ,也就是查询第2列不在where条件中,这样也可能出现INDEX SKIP SCAN.开始有点不理解. --//数据分布如下:ckbz=1占大部分.在ckbz=1的情况下走这个索引不合适. SQL> select CKBZ, TJBZ,count(*) from YF_DB01 group by CKBZ, TJBZ;       CKBZ       TJBZ   COUNT(*) ---------- ---------- ----------          1          0          4          0          0        327          1          1     103822          0          1         58 SQL> @ &r/bind_cap_awr 7n4kqvamms25z ''    SNAP_ID SQL_ID        WAS LAST_CAPTURED       NAME                   POSITION MAX_LENGTH DATATYPE_STR VALUE_STRING ---------- ------------- --- ------------------- -------------------- ---------- ---------- ------------ ------------------- ...      32796 7n4kqvamms25z YES 2018-07-25 08:49:09 :V00001                       1         32 VARCHAR2(32) 2018-07-25 08:30:00                                                  :V00002                       2         32 VARCHAR2(32) 2018-07-25 09:00:00                                                  :V00003                       3         32 VARCHAR2(32) 3 --//很明显走I_YF_DB01_CKRQ_SQYF索引效果更好.最后使用sql-profile控制执行计划. --//exec dbms_stats.gather_table_stats(user,'YF_DB01',cascade => true,method_opt => 'for columns (ckbz,MBYF) size 254 'FOR ALL COLUMNS SIZE repeat ', No_Invalidate  => FALSE); --//exec dbms_stats.delete_column_stats('portal_his','YF_DB01','SYS_STUYKI9FJXHL_64MMDTLG9Z0E6'); --//这样不用!!