谓词越界“场景"/>
传说中的“谓词越界“场景
开发环境,碰见一个谓词越界的问题,模拟这条SQL,如下所示,其中A_ID是表test的外键,并且存在索引,
SELECT 1 FROM test WHERE A_ID = 6052138 AND IS_VALID = 1
这张表的数据量,大约10万,
SQL> select count(*) from test;COUNT(*)
----------99044
查看select 1这条SQL的10053,
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::Table: TEST Alias: TEST#Rows: 265702 #Blks: 13157 AvgRowLen: 180.00 ChainCnt: 0.00
Index Stats::Index: IDX_TEST_01 Col#: 2LVLS: 2 #LB: 1777 #DK: 119696 LB/K: 1.00 DB/K: 1.00 CLUF: 118505.00Index: IDX_TEST_02 Col#: 3LVLS: 2 #LB: 2339 #DK: 381 LB/K: 6.00 DB/K: 272.00 CLUF: 103794.00Index: IDX_TEST_03 Col#: 7LVLS: 2 #LB: 786 #DK: 2292 LB/K: 1.00 DB/K: 36.00 CLUF: 82804.00Index: PK_TEST_ID Col#: 1LVLS: 2 #LB: 1652 #DK: 265702 LB/K: 1.00 DB/K: 1.00 CLUF: 238444.00
Access path analysis for TEST
***************************************
SINGLE TABLE ACCESS PATH Single Table Cardinality Estimation for TEST[TEST] Column (#2): A_ID(AvgLen: 6 NDV: 119696 Nulls: 0 Density: 0.000008 Min: 5586857 Max: 5726449Column (#60): IS_VALID(AvgLen: 3 NDV: 1 Nulls: 0 Density: 0.000002 Min: 1 Max: 1Histogram: Freq #Bkts: 1 UncompBkts: 10049 EndPtVals: 1
Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value predTable: TEST Alias: TESTCard: Original: 265702.000000 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50Access Path: TableScanCost: 3577.48 Resp: 3577.48 Degree: 0Cost_io: 3565.00 Cost_cpu: 460365831Resp_io: 3565.00 Resp_cpu: 460365831Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value predAccess Path: index (AllEqRange)Index: IDX_TEST_01resc_io: 4.00 resc_cpu: 30301ix_sel: 0.000002 ix_sel_with_filters: 0.000002 Cost: 4.00 Resp: 4.00 Degree: 1Best:: AccessPath: IndexRangeIndex: IDX_TEST_01Cost: 4.00 Degree: 1 Resp: 4.00 Card: 0.50 Bytes: 0
***************************************
...
CBRID: TEST @ SEL$1 TableLookup allocation - Failure - : disabled by parameter
看见提示,#2这列,即A_ID,对于超出范围的、不存在的值,使用0.000002作为选择率,即这种选择率,是预估的值,不是实际计算的,换句话说,有可能对执行成本的计算,产生偏差,
Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value pred
我们从这张表,A_ID字段实际的存储,看下是否存在他所说的,“超出范围”,
SQL> select min(A_ID), max(A_ID) from TEST;
MIN(A_ID) MAX(A_ID)
--------- ---------6006992 6052756
上述结果展示,A_ID的取值范围是6006992-6052756,而trace中,标记A_ID的min和max则是5586857-5726449,因此,这条SQL,出现了传说中的“谓词越界”,
Min: 5586857 Max: 5726449
trace中的min和max,怎么得来的?他是读取的dba_tab_col_statistics视图,通过换算得到的,
SQL> select table_name, column_name, utl_raw.cast_to_number(low_value) low,2 utl_raw.cast_to_number(high_value) hight3 from dba_tab_col_statistics4 WHERE table_name='TEST' AND column_name='A_ID'5 and owner='BISAL';
TABLE_NAME COLUMN_NAME LOW HIGHT
------------------------ ------------------------------ -------- -----------
TEST A_ID 5586857 5726449
但是庆幸的是,虽然出现了谓词越界的问题,并没有因为成本值计算偏差,导致CBO选择错误的执行计划,我觉得和这条SQL的谓词条件比较简单,有一定的关系,可选择的执行计划就这两种,
SELECT /*+gather_plan_statistics*/ 1 FROM test WHERE A_ID = 6052138 AND IS_VALID = 1select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));Plan hash value: 1000423460
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 6 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 1 | 2 |00:00:00.01 | 6 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_01 | 1 | 1 | 2 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("IS_VALID"=1)2 - access("A_ID"=6052138)
因此这个案例中,虽然出现了“谓词越界”,对COST的计算,会有误差,但并未影响执行计划的选择,如果是一条谓词复杂的SQL,包含多种执行计划的可能,出现“谓词越界”,选错执行计划,形成性能问题,就是大概率了。
解决方法,就是重采集统计信息,以让COST的计算,更接近实际,避免使用默认值,让CBO作出正确选择。
更多推荐
传说中的“谓词越界“场景
发布评论