这应该是优化器的BUG吧？

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

上面，同样的采样命令，在测试库执行后，执行计划恢复正常，但在正式库上就不行，

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

明天我把正式库的 system 统计信息，搬到测试库上恢复，再试试语句的执行计划，

newkid · 发表于 2017-8-31 23:16

搜到一篇浮云猫的文章作参考：
http://www.hellodba.com/reader.php?ID=64&lang=CN

ZALBB · 发表于 2017-9-1 10:04

ZALBB 发表于 2017-8-31 23:03
明天我把正式库的 system 统计信息，搬到测试库上恢复，再试试语句的执行计划，

才发现，测试环境的 SYSTEM 统计信息，被我设置成和正式环境的一样了，

HLWL@orcl2>/

SNAME             PNAME                   PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO       STATUS                         COMPLETED
SYSSTATS_INFO       DSTART                         08-02-2017 09:06
SYSSTATS_INFO       DSTOP                         08-02-2017 11:06
SYSSTATS_INFO       FLAGS                      0
SYSSTATS_MAIN       CPUSPEEDNW          1751.75879
SYSSTATS_MAIN       IOSEEKTIM                   10
SYSSTATS_MAIN       IOTFRSPEED                4096
SYSSTATS_MAIN       SREADTIM                .632
SYSSTATS_MAIN       MREADTIM                .042
SYSSTATS_MAIN       CPUSPEED                1970
SYSSTATS_MAIN       MBRC                         1

SNAME             PNAME                   PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_MAIN       MAXTHR             585259008
SYSSTATS_MAIN       SLAVETHR             3132416

13 rows selected.

SQLTX@orcl>/

SNAME             PNAME                   PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO       STATUS                         COMPLETED
SYSSTATS_INFO       DSTART                         08-20-2017 12:33
SYSSTATS_INFO       DSTOP                         08-20-2017 12:33
SYSSTATS_INFO       FLAGS                      1
SYSSTATS_MAIN       CPUSPEEDNW          1751.75879
SYSSTATS_MAIN       IOSEEKTIM                   10
SYSSTATS_MAIN       IOTFRSPEED                4096
SYSSTATS_MAIN       SREADTIM                .632
SYSSTATS_MAIN       MREADTIM                .042
SYSSTATS_MAIN       CPUSPEED                1970
SYSSTATS_MAIN       MBRC                         1

SNAME             PNAME                   PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_MAIN       MAXTHR             585259008
SYSSTATS_MAIN       SLAVETHR             3132416

13 rows selected.

Elapsed: 00:00:00.00
SQLTX@orcl>

newkid · 发表于 2017-9-1 22:52

10053里面有全表扫描的COST计算，和文章中提到的计算公式是否能相符？
如果不符，建议给ORACLE开个SR。

ZALBB · 发表于 2017-9-2 18:32

newkid 发表于 2017-9-1 22:52
10053里面有全表扫描的COST计算，和文章中提到的计算公式是否能相符？
如果不符，建议给ORACLE开个SR。

我开不了SR了，不过问题看起来解决了，

1 先后重启RAC两实例，
2 一重启完两节点，我赶紧测试，但执行计划仍未变，
3 查看字段FCREATETIME的直方图，发现是1，赶紧采集254桶，
4 再次测试，这次发现，取一个月的范围（得读2个分区，半个月一个分区）时，做分区全表扫描了，
5 缩小时间段，若是1 - 5号，走分区索引， 1 - 6号，就走分区扫描，，，

这结果符合我的预期，

ZALBB · 发表于 2017-9-2 18:47

此前在测试环境发现对表采样100%后，执行计划达到预期，但在生产库上总不行，所以总觉得是生产库上的某个设置阻碍着了，但分析来分析去，总找不出，
最后只能怀疑是受共享池中现存的统计信息等影响，由于生产库是 RAC 环境，平时要求高可用，没法轻易重启，拖了好久，才考虑测试到这一步。

ZALBB · 发表于 2017-9-2 19:18

--1  中午重启两实例
$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 2 12:07:41 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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SYS@orcl1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl1>startup
ORACLE instance started.

Total System Global Area 7.6964E+10 bytes
Fixed Size                2236488 bytes
Variable Size          3.8386E+10 bytes
Database Buffers       3.8386E+10 bytes
Redo Buffers             189489152 bytes
Database mounted.
Database opened.
SYS@orcl1>

--2  重启后，执行计划还是没达到期望（在另外一个窗口测试，可惜内容没及时拷贝下来，被后面的测试冲走了），
-- 后发现是 fcreatetime 的直方图为1，于是采集254桶，采集完后，终于看到执行计划达到预期，
HLWL@orcl2>connect hlwl
Enter password:
Connected.
HLWL@orcl2>exec dbms_stats.gather_table_stats(user,'CT_OPE_WAYBILL',method_opt=>'for columns fcreatetime size 254');

PL/SQL procedure successfully completed.

Elapsed: 00:06:38.54
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.02
HLWL@orcl2>HLWL@orcl2>HLWL@orcl2>
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2319931727

-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |             | 174K|  9737K| 27054 (2)| 00:00:18 |    |    |
| 1 |  PARTITION RANGE ITERATOR|             | 174K|  9737K| 27054 (2)| 00:00:18 | 73 | 74 |
|*  2 | TABLE ACCESS FULL    | CT_OPE_WAYBILL | 174K|  9737K| 27054 (2)| 00:00:18 | 73 | 74 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

2 - filter("W"."FCREATETIME"<=TIMESTAMP' 2016-06-29 23:59:59')

14 rows selected.

Elapsed: 00:00:00.04

--3 此前参数 optimizer_index_cost_adj = 50，刚把它改成 70
HLWL@orcl1>show parameter optimizer_index_cost_adj

NAME                               TYPE                VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_index_cost_adj          integer             70

HLWL@orcl1>set autot trace exp stat;
--3.1 默认情况下，时间段范围为1-4号时，走索引，逻辑读13254
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-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9       AND w.FCreateTime <= to_date('2016-05-04 23:59:59','yyyy-MM-dd HH24:mi:ss');

13843 rows selected.

Elapsed: 00:00:00.24

Execution Plan
----------------------------------------------------------
Plan hash value: 1403195337

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         | 13617 | 731K|  8486 (1)| 00:00:06 |    |    |
| 1 |  PARTITION RANGE SINGLE          |                         | 13617 | 731K|  8486 (1)| 00:00:06 | 71 | 71 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          | 13617 | 731K|  8486 (1)| 00:00:06 | 71 | 71 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 | 13617 |    | 77 (3)| 00:00:01 | 71 | 71 |
---------------------------------------------------------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   13254  consistent gets
      0  physical reads
      0  redo size
   765642  bytes sent via SQL*Net to client
   10666  bytes received via SQL*Net from client
      924  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   13843  rows processed

--3.2 强制不走索引，走全分区扫描，逻辑读13317，确实比走索引的 13254 大,也即：此时间段走索引是最佳，
SELECT  /*+ no_index(w) */
      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-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9       AND w.FCreateTime <= to_date('2016-05-04 23:59:59','yyyy-MM-dd HH24:mi:ss');

13843 rows selected.

Elapsed: 00:00:00.27

Execution Plan
----------------------------------------------------------
Plan hash value: 1025046548

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |             | 13617 | 731K| 12768 (1)| 00:00:09 |    |    |
| 1 |  PARTITION RANGE SINGLE|             | 13617 | 731K| 12768 (1)| 00:00:09 | 71 | 71 |
|*  2 | TABLE ACCESS FULL | CT_OPE_WAYBILL | 13617 | 731K| 12768 (1)| 00:00:09 | 71 | 71 |
---------------------------------------------------------------------------------------------------------

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

2 - filter("W"."FCREATETIME"<=TIMESTAMP' 2016-05-04 23:59:59')

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
   13317  consistent gets
      0  physical reads
      0  redo size
   773953  bytes sent via SQL*Net to client
   10666  bytes received via SQL*Net from client
      924  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   13843  rows processed

--3.3 再测试，默认情况下，时间段范围为1-5号时，走全分区扫描，逻辑读13736，
SELECT  /* no_index(w) */
      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-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9       AND w.FCreateTime <= to_date('2016-05-05 23:59:59','yyyy-MM-dd HH24:mi:ss');

21202 rows selected.

Elapsed: 00:00:00.34

Execution Plan
----------------------------------------------------------
Plan hash value: 1025046548

---------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |             | 21001 |  1127K| 12780 (1)| 00:00:09 |    |    |
| 1 |  PARTITION RANGE SINGLE|             | 21001 |  1127K| 12780 (1)| 00:00:09 | 71 | 71 |
|*  2 | TABLE ACCESS FULL | CT_OPE_WAYBILL | 21001 |  1127K| 12780 (1)| 00:00:09 | 71 | 71 |
---------------------------------------------------------------------------------------------------------

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

2 - filter("W"."FCREATETIME"<=TIMESTAMP' 2016-05-05 23:59:59')

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   13736  consistent gets
      0  physical reads
      0  redo size
1182577  bytes sent via SQL*Net to client
   16067  bytes received via SQL*Net from client
   1415  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   21202  rows processed

--3.4 强制走索引时，逻辑读20251，比走分区的13736大，说明此时间范围段，走分区扫描是最佳，
SELECT  /*+ index(w) */
      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-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9       AND w.FCreateTime <= to_date('2016-05-05 23:59:59','yyyy-MM-dd HH24:mi:ss');

21202 rows selected.

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 1403195337

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         | 21001 |  1127K| 13087 (1)| 00:00:09 |    |    |
| 1 |  PARTITION RANGE SINGLE          |                         | 21001 |  1127K| 13087 (1)| 00:00:09 | 71 | 71 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          | 21001 |  1127K| 13087 (1)| 00:00:09 | 71 | 71 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 | 21001 |    | 118 (3)| 00:00:01 | 71 | 71 |
---------------------------------------------------------------------------------------------------------------------------------

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

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

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   20251  consistent gets
      0  physical reads
      0  redo size
1170208  bytes sent via SQL*Net to client
   16067  bytes received via SQL*Net from client
   1415  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   21202  rows processed

HLWL@orcl1>

HLWL@orcl1>
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-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9       AND w.FCreateTime <= to_date('2016-05-30 23:59:59','yyyy-MM-dd HH24:mi:ss');

160798 rows selected.

Elapsed: 00:00:05.44

Execution Plan
----------------------------------------------------------
Plan hash value: 2319931727

-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |             | 155K|  8666K| 28307 (2)| 00:00:18 |    |    |
| 1 |  PARTITION RANGE ITERATOR|             | 155K|  8666K| 28307 (2)| 00:00:18 | 71 | 72 |
|*  2 | TABLE ACCESS FULL    | CT_OPE_WAYBILL | 155K|  8666K| 28307 (2)| 00:00:18 | 71 | 72 |
-----------------------------------------------------------------------------------------------------------

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

2 - filter("W"."FCREATETIME"<=TIMESTAMP' 2016-05-30 23:59:59')

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
   36814  consistent gets
   5654  physical reads
   223208  redo size
8998726  bytes sent via SQL*Net to client
   118433  bytes received via SQL*Net from client
   10721  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   160798  rows processed

--4 这个时间段跨了3个分区，第3个分区只读取一天，我觉得优化器应该是对两分区全扫描，对第3个分区（只读一天的数据）走分区索引，才是最佳，
--  但这里看到的却是3个分区都全扫描，
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-05-01 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9       AND w.FCreateTime <= to_date('2016-06-01 23:59:59','yyyy-MM-dd HH24:mi:ss');

173572 rows selected.

Elapsed: 00:00:02.61

Execution Plan
----------------------------------------------------------
Plan hash value: 2319931727

-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |             | 168K|  9378K| 41072 (2)| 00:00:26 |    |    |
| 1 |  PARTITION RANGE ITERATOR|             | 168K|  9378K| 41072 (2)| 00:00:26 | 71 | 73 |
|*  2 | TABLE ACCESS FULL    | CT_OPE_WAYBILL | 168K|  9378K| 41072 (2)| 00:00:26 | 71 | 73 |
-----------------------------------------------------------------------------------------------------------

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

2 - filter("W"."FCREATETIME"<=TIMESTAMP' 2016-06-01 23:59:59')

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
   50215  consistent gets
      0  physical reads
      0  redo size
9722241  bytes sent via SQL*Net to client
   127805  bytes received via SQL*Net from client
   11573  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   173572  rows processed

HLWL@orcl1>

ZALBB · 发表于 2017-9-3 11:49

跨分区操作，也没问题，
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-05-15 00:00:00','yyyy-MM-dd HH24:mi:ss')
  9          AND w.FCreateTime <= to_date('2016-05-16 23:59:59','yyyy-MM-dd HH24:mi:ss');

7328 rows selected.

Elapsed: 00:00:00.40

Execution Plan
----------------------------------------------------------
Plan hash value: 3771011500

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                      | Rows  | Bytes | Cost (%CPU)| Time    | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                |                         | 13177 | 733K|  8059 (1)| 00:00:06 |    |    |
| 1 |  PARTITION RANGE ITERATOR       |                         | 13177 | 733K|  8059 (1)| 00:00:06 | 71 | 72 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CT_OPE_WAYBILL          | 13177 | 733K|  8059 (1)| 00:00:06 | 71 | 72 |
|*  3 | INDEX RANGE SCAN             | IDX_CT_OPE_WAYBILL_HLIT_01 | 13374 |    | 58 (4)| 00:00:01 | 71 | 72 |
---------------------------------------------------------------------------------------------------------------------------------

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

3 - access("W"."FCREATETIME">=TIMESTAMP' 2016-05-15 00:00:00' AND "W"."FCREATETIME"<=TIMESTAMP' 2016-05-16 23:59:59')

Statistics
----------------------------------------------------------
      8  recursive calls
      0  db block gets
   6733  consistent gets
      124  physical reads
   6448  redo size
   404169  bytes sent via SQL*Net to client
   5892  bytes received via SQL*Net from client
      490  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   7328  rows processed

HLWL@orcl1>

newkid · 发表于 2017-9-4 22:28

那么到最后就是直方图引起的？你在比较两个环境的时候没有比较直方图吗？