这篇文章主要介绍“数据库中谓词越界问题分析”,在日常操作中,相信很多人在数据库中谓词越界问题分析问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”数据库中谓词越界问题分析”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!
开发环境,碰见一个谓词越界的问题,模拟这条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#: 2
LVLS: 2 #LB: 1777 #DK: 119696 LB/K: 1.00 DB/K: 1.00 CLUF: 118505.00
Index: IDX_TEST_02 Col#: 3
LVLS: 2 #LB: 2339 #DK: 381 LB/K: 6.00 DB/K: 272.00 CLUF: 103794.00
Index: IDX_TEST_03 Col#: 7
LVLS: 2 #LB: 786 #DK: 2292 LB/K: 1.00 DB/K: 36.00 CLUF: 82804.00
Index: PK_TEST_ID Col#: 1
LVLS: 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: 5726449
Column (#60): IS_VALID(
AvgLen: 3 NDV: 1 Nulls: 0 Density: 0.000002 Min: 1 Max: 1
Histogram: Freq #Bkts: 1 UncompBkts: 10049 EndPtVals: 1
Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value pred
Table: TEST Alias: TEST
Card: Original: 265702.000000 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50
Access Path: TableScan
Cost: 3577.48 Resp: 3577.48 Degree: 0
Cost_io: 3565.00 Cost_cpu: 460365831
Resp_io: 3565.00 Resp_cpu: 460365831
Using prorated density: 0.000002 of col #2 as selectvity of out-of-range/non-existent value pred
Access Path: index (AllEqRange)
Index: IDX_TEST_01
resc_io: 4.00 resc_cpu: 30301
ix_sel: 0.000002 ix_sel_with_filters: 0.000002
Cost: 4.00 Resp: 4.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_TEST_01
Cost: 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) hight
3 from dba_tab_col_statistics
4 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 = 1
select * 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作出正确选择。
到此,关于“数据库中谓词越界问题分析”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注亿速云网站,小编会继续努力为大家带来更多实用的文章!
亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。
原文链接:http://blog.itpub.net/7192724/viewspace-2286274/