1 发现问题
有一条SQL语句select * from sh.sales_no_par where time_id=to_date(‘2019-08-15’,‘YYYY-MM-DD’)，执行计划是这样的
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1227 | |
| 1 | TABLE ACCESS FULL | SALES_NO_PAR| 544 | 15K | 1227 | 00:00:15 |
-----------------------------------------+-----------------------------------+
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'YYYY-MM-DD')='2019-05-27')
执行计划中操作2全表扫描返回的行数是544，这个是应用过滤条件后的结果，看一下表实际应用这个过滤条件后的行数
select count(*) from sh.sales_no_par where time_id=to_date('2019-08-15','YYYY-MM-DD');
COUNT(*)
----------
20
表中实际符合条件的行数是20，同执行计划中显示的行数差距相当大，导致这个差距的原因是什么？是因为表的统计信息不准确吗？
2 检查表的统计信息
表的统计信息可以在视图USER_TAB_COL_STATISTICS查到，这个视图的定义如下：
SQL> desc USER_TAB_COL_STATISTICS;
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(128)
COLUMN_NAME VARCHAR2(128)
NUM_DISTINCT NUMBER
LOW_VALUE RAW(1000)
HIGH_VALUE RAW(1000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
NOTES VARCHAR2(99)
AVG_COL_LEN NUMBER
HISTOGRAM VARCHAR2(15)
SCOPE VARCHAR2(7)
列的统计信息如下
SELECT * FROM USER_TAB_COL_STATISTICS WHERE table_name='SALES_NO_PAR' AND COLUMN_NAME ='TIME_ID';
TABLE_NAME |COLUMN_NAME|NUM_DISTINCT|LOW_VALUE|HIGH_VALUE|DENSITY |NUM_NULLS|NUM_BUCKETS|LAST_ANALYZED |SAMPLE_SIZE|GLOBAL_STATS|USER_STATS|NOTES|AVG_COL_LEN|HISTOGRAM|SCOPE |
------------+-----------+------------+---------+----------+--------------------+---------+-----------+-----------------------+-----------+------------+----------+-----+-----------+---------+------+
SALES_NO_PAR|TIME_ID | 1285|xw |xz |0.000759301442672741| 0| 254|2024-12-12 06:19:05.000| 5325|YES |NO | | 8|HYBRID |SHARED|
查询表中这个列的信息是
SELECT count(DISTINCT time_id),max(TIME_ID),min(TIME_ID) FROM sh.SALES_NO_PAR;
COUNT(DISTINCTTIME_ID)|MAX(TIME_ID) |MIN(TIME_ID) |
----------------------+-----------------------+-----------------------+
1285|2022-12-31 00:00:00.000|2019-01-02 00:00:00.000|
可以看到，NUM_DISTINCT值是准确的，LOW_VALUE和HIGH_VALUE由于存储的类型是raw，需要转换以下，使用DBMS_STATS.CONVERT_RAW_TO_DATE函数（也有一个名字和这个比较象的存储过程PROCEDURE CONVERT_RAW_VALUE，也可以转换），在查询中可以直接使用这个函数，这个只有一个参数。
SELECT DBMS_STATS.CONVERT_RAW_TO_DATE(HIGH_VALUE),DBMS_STATS.CONVERT_RAW_TO_DATE(LOW_VALUE) FROM USER_TAB_COL_STATISTICS WHERE table_name='SALES_NO_PAR' AND COLUMN_NAME ='TIME_ID';
DBMS_STATS.CONVERT_RAW_TO_DATE(HIGH_VALUE)|DBMS_STATS.CONVERT_RAW_TO_DATE(LOW_VALUE)|
------------------------------------------+-----------------------------------------+
2022-12-31 00:00:00.000| 2019-01-02 00:00:00.000|
转换后的值和从表里查出的数据相同，可以看到统计信息是准确的，执行计划显示的评估的行数和实际函数的明显差距不是由于统计信息的错误引起的。那是什么原因造成的呢？这个可以从对优化器跟踪的10053事件看出来。
3 Oracle优化器怎样计算表达式的选择性
3.1 设置优化器跟踪事件，获取跟踪文件
alter system flush shared_pool；--清除现有的执行计划，生产环境谨慎执行
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';--设置事件
--执行要跟踪的sql语句
ALTER SESSION SET EVENTS '10053 trace name context off';--关闭事件
3.2 Cardinality、Selectivity and Density
分析事件跟踪文件前，先介绍以下以下这三个术语。在Oracle优化器看来，这三个术语都是关于一个表达式返回的数据的，Cardinality（基数）是一个操作返回的行的数量，Selectivity是符合一个条件的行数占所有行数的比例，当知道了Selectivity后，Cardinality可以由Selectivity*行的数量来得到。Density也是存储在列的统计信息视图的一列，DENSITY值的计算方式如下
如果列有直方图信息，这个列显示直方图跨越少于2个端点（endpoint）的值的选择性。它不表示跨越2个以上节点的选择性。如果列上没有直方图，列上的这个值是1/NUM_DISTINCT。
3.3 优化器怎样计算一个条件的返回值
这部分计算在访问路径分析部分可以看到，下面截取了这一片段
Access path analysis for SALES_NO_PAR
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for SALES_NO_PAR[SALES_NO_PAR]
SPD: Return code in qosdDSDirSetup: NODIR, estType = TABLE
kkecdn: Single Table Predicate:“SALES_NO_PAR”.“TIME_ID”=TO_DATE(’ 2019-08-15 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’)
Column (#3):
NewDensity:0.000749, OldDensity:0.000759 BktCnt:5325.000000, PopBktCnt:246.000000, PopValCnt:11, NDV:1285
Column (#3): TIME_ID(DATE)
AvgLen: 8 NDV: 1285 Nulls: 0 Density: 0.000749 Min: 2458486.000000 Max: 2459945.000000
Histogram: Hybrid #Bkts: 254 UncompBkts: 5325 EndPtVals: 254 ActualVal: yes
Using density: 7.4867e-04 of col #3 as selectivity of pred having unreasonably low value
Table: SALES_NO_PAR Alias: SALES_NO_PAR
Card: Original: 725960.000000rsel = 0.000749 Rounded: 544 Computed: 543.502899 Non Adjusted: 543.502899
在表达式的选择性（selectivity）非常低的时候，优化器会使用列的density值作为选择性的值，在计算成本时，不考虑数据倾斜，这个列选择性值是0.00177（1285/725960），优化器选择的值是0.000749，这个值在列的统计信息里可以查到。
3.4 函数在表达式左侧
kkecdn: Single Table Predicate:TO_CHAR("SALES_NO_PAR"."TIME_ID",'YYYY-MM-DD')='2019-08-15' Table: SALES_NO_PAR Alias: SALES_NO_PAR Card: Original: 724873.000000rsel = 0.010000 Rounded: 7249 Computed: 7248.730000 Non Adjusted: 7248.730000
如果函数在表达式的左侧，比如上面的形式，优化器直接假设的选择性是0.01，计算出的值是7259，执行计划如下
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1233 | |
| 1 | TABLE ACCESS FULL | SALES_NO_PAR| 7260 | 206K | 1233 | 00:00:15 |
3.5 函数索引
在为表达式创建了函数索引后，优化器的表现又有了改变
kkecdn: Single Table Predicate:TO_CHAR(“SALES_NO_PAR”.“TIME_ID”,‘YYYY-MM-DD’)=‘2019-08-15’
Column (#8): SYS_NC00008(VARCHAR2) NO STATISTICS (using defaults)
AvgLen: 7 NDV: 22652 Nulls: 0 Density: 0.000044
Table: SALES_NO_PAR Alias: SALES_NO_PAR
Card: Original: 724873.000000rsel = 0.010000
Single Tab Card adjusted from 7248.730000 to 20.000000 due to adaptive dynamic sampling
***** Virtual column Adjustment ******
Column name SYS_NC00008
cost_cpu 550
cost_io
***** End virtual column Adjustment ******
Rounded: 20 Computed: 20.000000 Non Adjusted: 7248.730000
Index Card adjusted from 2899.492000 to 20.000000 due to adaptive dynamic sampling
执行计划如下
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:00.01 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SALES_NO_PAR | 1 | 20 | 20 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | INX_SALES_NP_T | 1 | 20 | 20 |00:00:00.01 | 6 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SALES_NO_PAR"."SYS_NC00008$"='2019-08-15')
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO (SYSTEM))
24 rows selected.
由于执行了动态采样，执行计划的评估同实际行数相同。
4 写在后面
Oracle优化器是十分复杂的，对操作返回行数的评估根据不同的情况也有不同的方式，导致执行计划评估行数和实际行数差异的原因也有多种，需要具体分析。
