这应该是优化器的BUG吧？

ZALBB · 发表于 2017-8-30 18:16

当前测试环境是3月份的数据，测试的结果符合期望，，，为判断是数据库层面，还是表统计信息的问题，我于是在在测试环境上新建一个用户，再从正式库导出此表，导入到测试库新的用户中，结果，重现了正式库上，走索引的执行计划，

$sqlplus sqltx/1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 30 18:11:01 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.CFBillStates != 9
      AND
      (
            w.CFChangeEffect IS NULL
            OR w.CFChangeEffect != 2
      )
      AND w.CFCustomerHanderID ='PKIAAAAAf5eA733t'
      AND w.CFEndStation LIKE '%石家庄%'
      AND w.FCreateTime >= to_date('2016-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-07-31 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.03
SQLTX@orcl>SQLTX@orcl>SQLTX@orcl>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         |    1 | 105 |  2322 (1)| 00:00:02 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         |    1 | 105 |  2322 (1)| 00:00:02 | 75 | 76 |
|*  2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          |    1 | 105 |  2322 (1)| 00:00:02 | 75 | 76 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 |  5400 |    | 18 (6)| 00:00:01 | 75 | 76 |
---------------------------------------------------------------------------------------------------------------------------------

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

2 - filter("W"."CFCUSTOMERHANDERID"='PKIAAAAAf5eA733t' AND "W"."CFENDSTATION" LIKE U'%\77F3\5BB6\5E84%' AND
            TO_NUMBER("W"."CFCHANGEEFFECT")<>2 AND TO_NUMBER("W"."CFBILLSTATES")<>9)
3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-07-01 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-07-31 23:59:59')

17 rows selected.

Elapsed: 00:00:00.12
SQLTX@orcl>

这样，初步判断，是表统计信息的问题，，，

ZALBB · 发表于 2017-8-30 20:54

重新采集整个表的统计信息，这次100%采集，结束后，执行计划恢复正常，不再走索引了，

$sqlplus sqltx/1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 30 18:11:01 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.CFBillStates != 9
      AND
      (
            w.CFChangeEffect IS NULL
            OR w.CFChangeEffect != 2
      )
      AND w.CFCustomerHanderID ='PKIAAAAAf5eA733t'
      AND w.CFEndStation LIKE '%石家庄%'
      AND w.FCreateTime >= to_date('2016-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-07-31 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.03
SQLTX@orcl>SQLTX@orcl>SQLTX@orcl>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         |    1 | 105 |  2322 (1)| 00:00:02 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         |    1 | 105 |  2322 (1)| 00:00:02 | 75 | 76 |
|*  2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          |    1 | 105 |  2322 (1)| 00:00:02 | 75 | 76 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 |  5400 |    | 18 (6)| 00:00:01 | 75 | 76 |
---------------------------------------------------------------------------------------------------------------------------------

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

2 - filter("W"."CFCUSTOMERHANDERID"='PKIAAAAAf5eA733t' AND "W"."CFENDSTATION" LIKE U'%\77F3\5BB6\5E84%' AND
            TO_NUMBER("W"."CFCHANGEEFFECT")<>2 AND TO_NUMBER("W"."CFBILLSTATES")<>9)
3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-07-01 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-07-31 23:59:59')

17 rows selected.

Elapsed: 00:00:00.12
SQLTX@orcl>exec dbms_stats.gather_table_stats(user,'CT_OPE_WAYBILL',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:51:15.92
EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.CFBillStates != 9
      AND
      (
            w.CFChangeEffect IS NULL
            OR w.CFChangeEffect != 2
      )
      AND w.CFCustomerHanderID ='PKIAAAAAf5eA733t'
      AND w.CFEndStation LIKE '%石家庄%'
      AND w.FCreateTime >= to_date('2016-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-07-31 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.08
SQLTX@orcl>SQLTX@orcl>SQLTX@orcl>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2319931727

-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |             | 41 |  4305 | 30083 (2)| 00:00:20 |    |    |
| 1 |  PARTITION RANGE ITERATOR|             | 41 |  4305 | 30083 (2)| 00:00:20 | 75 | 76 |
|*  2 | TABLE ACCESS FULL    | CT_OPE_WAYBILL | 41 |  4305 | 30083 (2)| 00:00:20 | 75 | 76 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

2 - filter("W"."CFCUSTOMERHANDERID"='PKIAAAAAf5eA733t' AND "W"."CFENDSTATION" LIKE
            U'%\77F3\5BB6\5E84%' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-07-31 23:59:59' AND
            TO_NUMBER("W"."CFCHANGEEFFECT")<>2 AND TO_NUMBER("W"."CFBILLSTATES")<>9)

16 rows selected.

Elapsed: 00:00:00.25
SQLTX@orcl>

但目前还不知是哪个统计信息异常导致？

newkid · 发表于 2017-8-30 22:00

既然你能够重现，就可以比较100%采样前后的统计信息，看看一哪项出现了重大差异。

ZALBB · 发表于 2017-8-30 22:39

newkid 发表于 2017-8-30 22:00
既然你能够重现，就可以比较100%采样前后的统计信息，看看一哪项出现了重大差异。

比较了 user_tables, user_tab_col_statistics 视图里表的分区字段 fcreatetime 字段，还有索引
IDX_CT_OPE_WAYBILL_HLIT_01 在 user_ind_partitions 视图里的信息，基本没差别，，，

在正式库里，我做了 exec dbms_stats.gather_table_stats(user,'CT_OPE_WAYBILL'); 默认比例采样，但还是没法改变执行计划，还是走索引，

newkid · 发表于 2017-8-30 23:20

比较一下两个库的db_file_multiblock_read_count设置?

ZALBB · 发表于 2017-8-31 00:02

newkid 发表于 2017-8-30 23:20
比较一下两个库的db_file_multiblock_read_count设置?

最早就比较过测试，正式库上两进程的各个参数值，v$ses_optimizer_env

ZALBB · 发表于 2017-8-31 11:10

昨晚对测试库的该表作了100%的采集，操作后，该语句的执行计划恢复正常，据此，我也在正式库对该表作了100%的采样操作，但很奇怪，同样的采集命令，采集后在正式库上却没达到预期的结果，

--此处对关键索引做了采集，采集比例 100%
HLWL@orcl2>exec dbms_stats.gather_index_stats(user,'IDX_CT_OPE_WAYBILL_HLIT_01',estimate_percent=>100);

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.26

--再次测试，没效果
EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.FCreateTime >= to_date('2016-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-07-31 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.02
HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         | 158K|  8804K| 68118 (1)| 00:00:45 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         | 158K|  8804K| 68118 (1)| 00:00:45 | 75 | 76 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          | 158K|  8804K| 68118 (1)| 00:00:45 | 75 | 76 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 | 158K|    | 477 (4)| 00:00:01 | 75 | 76 |
---------------------------------------------------------------------------------------------------------------------------------

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

3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-07-01 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-07-31 23:59:59')

15 rows selected.

Elapsed: 00:00:00.03

--对该表作了100%采样，但因为上面已经对关键索引IDX_CT_OPE_WAYBILL_HLIT_01作了100%采样，因此，这里采样时，就不像测试库上的那样，没有这个选项：cascade=>true
HLWL@orcl2>exec dbms_stats.gather_table_stats(user,'CT_OPE_WAYBILL',estimate_percent=>100);  -- 这里

PL/SQL procedure successfully completed.

Elapsed: 03:18:02.20
HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>

--采集完后，马上在另外一个窗口测试，却没得到如期的结果

Connected.
EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.FCreateTime >= to_date('2016-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-07-31 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.02
HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         | 181K|    9M| 78089 (1)| 00:00:51 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         | 181K|    9M| 78089 (1)| 00:00:51 | 75 | 76 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          | 181K|    9M| 78089 (1)| 00:00:51 | 75 | 76 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 | 181K|    | 547 (4)| 00:00:01 | 75 | 76 |
---------------------------------------------------------------------------------------------------------------------------------

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

3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-07-01 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-07-31 23:59:59')

15 rows selected.

Elapsed: 00:00:00.08

--今早再试，结果还是不行，但COST比起昨晚的，大大降低了，估计是今早定时作业又对该表作了采样统计，

EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.FCreateTime >= to_date('2016-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-07-31 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.02
HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         |  5406 | 300K|  2350 (1)| 00:00:02 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         |  5406 | 300K|  2350 (1)| 00:00:02 | 75 | 76 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          |  5406 | 300K|  2350 (1)| 00:00:02 | 75 | 76 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 |  5462 |    | 18 (6)| 00:00:01 | 75 | 76 |
---------------------------------------------------------------------------------------------------------------------------------

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

3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-07-01 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-07-31 23:59:59')

15 rows selected.

Elapsed: 00:00:00.05
HLWL@orcl2>

newkid · 发表于 2017-8-31 21:24

ZALBB 发表于 2017-8-31 11:10
昨晚对测试库的该表作了100%的采集，操作后，该语句的执行计划恢复正常，据此，我也在正式库对该表作了100% ...

那就比较一下sys.aux_stats$中存储的系统统计信息，看看有什么不同？

ZALBB · 发表于 2017-8-31 22:59

一分钟前的结果，正式环境库

HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>exec dbms_stats.gather_table_stats(user,'CT_OPE_WAYBILL',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 03:20:52.72
EXPLAIN PLAN FOR
SELECT
      w.fid                                                 AS id                   ,
      w.FNumber                                           AS waybillnumber          ,
      w.CFBillFrom                                        AS billfrom             ,
      w.CFCustomerOrderNumber                               AS ordernumber          ,
      w.CFCustomerTransNumber                               AS transnumber
FROM CT_OPE_WayBill w
WHERE w.FCreateTime >= to_date('2016-06-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
      AND w.FCreateTime <= to_date('2016-06-29 23:59:59','yyyy-MM-dd HH24:mi:ss');

--SELECT * FROM DISPLAY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Explained.

Elapsed: 00:00:00.01
HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         | 157K|  8780K| 67934 (1)| 00:00:45 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         | 157K|  8780K| 67934 (1)| 00:00:45 | 73 | 74 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          | 157K|  8780K| 67934 (1)| 00:00:45 | 73 | 74 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 | 157K|    | 476 (4)| 00:00:01 | 73 | 74 |
---------------------------------------------------------------------------------------------------------------------------------

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

3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-06-01 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-06-29 23:59:59')

15 rows selected.

Elapsed: 00:00:00.04
HLWL@orcl2>

ZALBB · 发表于 2017-8-31 23:00

newkid 发表于 2017-8-31 21:24
那就比较一下sys.aux_stats$中存储的系统统计信息，看看有什么不同？

我也注意到这点，两者相差不大，应该不是这个原因，