请教partition pruning的问题

Minsic

这两天遇到一个问题，请看以下两个SQL的执行计划：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> explain plan for
  2  select * from nok_nmc.nok_bts_hour_per_total
  3  where PERIOD_START>=to_date('20120227','yyyymmdd')
  4  and period_start<to_date('20120228','yyyymmdd');

Explained.

SQL> select * from table(dbms_xplan.display);

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

------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                   | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                         |   611K|   585M| 50996 |       |       |
|   1 |  TABLE ACCESS FULL   | NOK_BTS_HOUR_PER_TOTAL  |   611K|   585M| 50996 |    38 |    38 |
------------------------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

9 rows selected.

SQL> explain plan for
  2  select * from nok_nmc.nok_bts_hour_per_total
  3  where PERIOD_START>=trunc(sysdate-5)
  4  and period_start<trunc(sysdate-4);

Explained.

SQL> select * from table(dbms_xplan.display);

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

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           |  Name                   | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         | 13140 |    12M|  3915 |       |       |
|   1 |  FILTER                             |                         |       |       |       |       |       |
|   2 |   PARTITION RANGE ITERATOR          |                         |       |       |       |   KEY |   KEY |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| NOK_BTS_HOUR_PER_TOTAL  | 13140 |    12M|  3915 |   KEY |   KEY |
|   4 |     INDEX RANGE SCAN                | NOK_BTS_HOUR_PER_INDX   | 23651 |       |    66 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

12 rows selected.

SQL>

据管理该数据库的DBA表示，其中的nok_bts_hour_per_total表和其上的索引都已经分析，看了CBO估算的基数，使用FTS的查询的估算更接近实际条数，实际应该为50W条。进一步检查数据字典视图，发现dba_indexes和dba_ind_partitions视图里该索引的global_stats字段都是NO，重新用dbms_stats包收集该索引信息，但两句SQL的计划和上面依旧没有明显的差别。
问题：1、为什么使用to_date函数和trunc+sysdate函数会有这么大的差别？2、为什么使用索引扫描的计划中，CBO没能估计到只需要扫描38#分区？
若还需要其他信息请说明，谢谢。

Maclean Liu(刘相兵

请你上传10053 trace：

alter system flush shared_pool;
alter session set events '10053 trace name context forever,level 1';
RUN ALL YOUR STATEMENT;

upload tracefile

2.
可以的话也运行一下 http://www.oracledatabase12g.com ... h-check-script.html 这个脚本

Minsic

不好意思，今天才有机会去跑个10053，为了得到27～28号的数据，稍微该了下第二个查询，trace文件请见附件。
至于那个sqlhc脚本，需要输入SQL_ID，不支持9i？

Maclean Liu(刘相兵

ODM Finding :

FOR to_date

SINGLE TABLE ACCESS PATH
Column: PERIOD_STA  Col#: 4      Part#: 37     Table: NOK_BTS_HOUR_PER_TOTAL   Alias: NOK_BTS_HOUR_PER_TOTAL
    NDV: 216       NULLS: 0         DENS: 4.6296e-03 LO:  2455979  HI: 2455988
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: PERIOD_STA  Col#: 4      Table: NOK_BTS_HOUR_PER_TOTAL   Alias: NOK_BTS_HOUR_PER_TOTAL
    NDV: 216       NULLS: 0         DENS: 4.6296e-03 LO:  2455979  HI: 2455988
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: NOK_BTS_HOUR_PER_TOTAL     ORIG CDN: 5255870  ROUNDED CDN: 611034  CMPTD CDN: 611034
  Access path: tsc  Resc:  50996  Resp:  50996
  Access path: index (scan)
      Index: NOK_BTS_HOUR_PER_INDX
  TABLE: NOK_BTS_HOUR_PER_TOTAL
      RSC_CPU: 0   RSC_IO: 102148
  IX_SEL:  1.1626e-01  TB_SEL:  1.1626e-01
  BEST_CST: 50996.00  PATH: 2  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
        Join order[1]:  NOK_BTS_HOUR_PER_TOTAL[NOK_BTS_HOUR_PER_TOTAL]#0
        Best so far: TABLE#: 0  CST:      50996  CDN:     611034  BYTES:  613478136
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 50996  CDN: 611034  RSC: 50996  RSP: 50996  BYTES: 613478136
  IO-RSC: 50996  IO-RSP: 50996  CPU-RSC: 0  CPU-RSP: 0
QUERY
select * from nok_nmc.nok_bts_hour_per_total
where PERIOD_START>=to_date('20120227','yyyymmdd')
and period_start<to_date('20120228','yyyymmdd')


CBO 考虑了 SINGLE TABLE ACCESS PATH Part#: 37 和   Access path: index (scan)

SINGLE TABLE ACCESS PATH Part#: 37   ROUNDED CDN: 611034  的Cost 50996
Access path: index (scan)  的 Cost 102148
IX_SEL:  1.1626e-01  TB_SEL:  1.1626e-01


CBO 选择了  成本较低的 Access path: tsc


For trunc

SINGLE TABLE ACCESS PATH
Column: PERIOD_STA  Col#: 4      Part#: 37     Table: NOK_BTS_HOUR_PER_TOTAL   Alias: NOK_BTS_HOUR_PER_TOTAL
    NDV: 216       NULLS: 0         DENS: 4.6296e-03 LO:  2455979  HI: 2455988
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: PERIOD_STA  Col#: 4      Table: NOK_BTS_HOUR_PER_TOTAL   Alias: NOK_BTS_HOUR_PER_TOTAL
    NDV: 216       NULLS: 0         DENS: 4.6296e-03 LO:  2455979  HI: 2455988
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: NOK_BTS_HOUR_PER_TOTAL     ORIG CDN: 5255870  ROUNDED CDN: 13140  CMPTD CDN: 13140
  Access path: tsc  Resc:  50996  Resp:  50996
  Access path: index (scan)
      Index: NOK_BTS_HOUR_PER_INDX
  TABLE: NOK_BTS_HOUR_PER_TOTAL
      RSC_CPU: 0   RSC_IO: 3956
  IX_SEL:  4.5000e-03  TB_SEL:  4.5000e-03
  BEST_CST: 3956.00  PATH: 4  Degree:  1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]:  NOK_BTS_HOUR_PER_TOTAL[NOK_BTS_HOUR_PER_TOTAL]#0
Best so far: TABLE#: 0  CST:       3956  CDN:      13140  BYTES:   13192560
prefetching is on for NOK_BTS_HOUR_PER_INDX
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 3956  CDN: 13140  RSC: 3956  RSP: 3956  BYTES: 13192560
  IO-RSC: 3956  IO-RSP: 3956  CPU-RSC: 0  CPU-RSP: 0
QUERY
select * from nok_nmc.nok_bts_hour_per_total
where PERIOD_START>=trunc(sysdate-8)
and period_start<trunc(sysdate-7)

CBO 考虑了 SINGLE TABLE ACCESS PATH Part#: 37 和   Access path: index (scan)

SINGLE TABLE ACCESS PATH  ROUNDED CDN: 13140  的 Cost :50996
  Access path: index (scan) 的Cost 3956

  IX_SEL:  4.5000e-03  TB_SEL:  4.5000e-03

optmizer 选择了 成本较低的  Access path: index (scan)


分区扫描的成本没有变化 始终是50996

变化的是 index(scan) 的成本从 102148  下降到  3956



IX_SEL:  1.1626e-01   VS   IX_SEL:  4.5000e-03

to_date vs trunc 2种不同的函数表达式 造成了不同的 IX_SEL 索引选择率

Maclean Liu(刘相兵

NO HISTOGRAM: #BKT: 1 #VAL: 2

建议你在 PERIOD_STA 列上构建 histogram 直方图 让CBO 得到正确的  IX_SEL

ora-hansey

学习了、一直为trace困扰

Minsic

感谢解答。

可否再详细些？trace文件里面没有提到to_date和trunc函数，直接给出了COST，不知道102148和3956两个COST是怎样得出的？
maclean提到了直方图，但是在某个有填满数据的分区内，period_start段的分布还是比较均匀的，除非算上还未用到的一部分分区，但这个索引是local索引，收集直方图会为什么会影响索引的选择率？我本来的理解是，CBO应该一开始就能发现索引分区的存在，这时候再考虑该分区内的数据分布，不是这样子工作的吗？