查看: 3314|回复: 4

[性能调整] cost base fundamentals:selectivity issues中deadly defaults对优化器的影响

[复制链接]
论坛徽章:
2
2010新春纪念徽章
日期:2010-03-01 11:08:282011新春纪念徽章
日期:2011-02-18 11:43:34
发表于 2009-9-24 16:50 | 显示全部楼层 |阅读模式
验证《cost base fundamentals》第六章节selectivity issues中deadly defaults对优化器的影响。
deadly defaults指致命的默认值,如order表中的date的default值设置为"9999-12-31",造成数据人为的严重不均衡。
测试平台:
OS:Red Hat Enterprise Linux Server release 5.3 (Tikanga)
DB:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

创建表t9,id列生成数据1,2……2000000,左边以‘0’补齐18位。
建立索引t9。
create table t9 (id char(18),name varchar2(100));
create sequence t1_seq;
begin
    for i in 1..2000000 loop
        insert into t9
        select lpad(to_char(t1_seq.nextval),18,'0'),to_char(t1_seq.nextval)
        from dual;
    end loop;
end;
commit;

create index t9_id on t9(id);

一、数据分布均匀

收集统计信息。

begin
    dbms_stats.gather_schema_stats(ownname => 'MEISHD',estimate_percent => 100);
end;

统计信息如下:
user_tab_col_statistics.id.low_value='000000000000000001'
user_tab_col_statistics.id.high_value='000000000002000000'
user_tab_col_statistics.id.num_distinct=2000000
user_indexes.t9_id.blevel=2
user_indexes.t9_id.leaf_blocks=8369
user_indexes.t9_id.clustering_factor=8683

计算IO_COST为88

index_selectivity  = required range / total range + 1/num_distinct + 1/num_distinct
                   = (70000-60000)/(2000000-1)+2/2000000
                   = 0.005001

table_selectivity = index_selectivity = 0.005001

cost=blevel + ceiling(leaf_blocks * index_selectivity) + ceiling(clustering_factor * table_selectivity)
    =2+ceiling(8369*0.005001)+ceiling(8683*0.005001)
    =2+ceiling(41.85)+ceiling(43.42)
    =2+42+44
    =88

确认IO_COST为88,总COST为88,CPU_COST为0.46。
这里有个疑问,为什么CPU_COST没有统计进去,不知道是因为太小而忽律不计,还是因为disable CPU_COST了(我已经收集了系统信息,应该已经enable CPU_COST了)。

SQL> select * from t9 where id between '000000000000060000' and '000000000000070000';

Execution Plan
----------------------------------------------------------
Plan hash value: 3577433401

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10002 |   253K|    88   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9    | 10002 |   253K|    88   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T9_ID | 10002 |       |    44   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID">='000000000000060000' AND "ID"<='000000000000070000')

SQL>


SQL> select a.OPTIONS,a.OBJECT_NAME,a.ID,a.COST,a.CPU_COST,a.IO_COST from v$sql_plan a where plan_hash_value='3577433401';
  
OPTIONS              OBJECT_NAME             ID       COST   CPU_COST    IO_COST
-------------------- --------------- ---------- ---------- ---------- ----------
                                              0         88
BY INDEX ROWID       T9                       1         88    4327797         88
RANGE SCAN           T9_ID                    2         44    2313943         44

SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN' and pname in ('CPUSPEED','SREADTIM');

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED                             1468
SREADTIM                             6.39


CPU_COST=(v$sql_plan.CPU_COST/CPUSPEED)/SREADTIM
        =(4327797/1486*1000000)/(6.39/1000)
        =0.46


二、数据分布不均匀

如将order表中的date的default值设置为"9999-12-31",这里通过将id修改模拟。

update t9 set id='100000000000000000'where id='000000000002000000';
commit;

收集统计信息:
begin
dbms_stats.gather_schema_stats(ownname => 'MEISHD',estimate_percent => 100);
end;

统计信息如下:
user_tab_col_statistics.id.low_value='000000000000000001'
user_tab_col_statistics.id.high_value='100000000000000000'
user_tab_col_statistics.id.num_distinct=2000000
user_indexes.t9_id.blevel=2
user_indexes.t9_id.leaf_blocks=8369
user_indexes.t9_id.clustering_factor=8683

计算IO_COST为4

index_selectivity  = required range / total range + 1/num_distinct + 1/num_distinct
                   = (70000-60000)/(100000000000000000-1)+2/2000000
                   = 0.000001

cost=blevel + ceiling(leaf_blocks * index_selectivity) + ceiling(clustering_factor * table_selectivity)
    =2+ceiling(8369*0.000001)+ceiling(8683*0.000001)
    =2+ceiling(0.008369)+ceiling(0.008683)
    =2+1+1
    =4

确认IO_COST为4,总COST为4,CPU_COST为0.00308。
关于CPU_COST的疑问同上。

SQL> select * from t9 where id between '000000000000060000' and '000000000000070000';

Execution Plan
----------------------------------------------------------
Plan hash value: 3577433401

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    26 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9    |     1 |    26 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T9_ID |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID">='000000000000060000' AND "ID"<='000000000000070000')

SQL>

SQL> select a.OPTIONS,a.OBJECT_NAME,a.ID,a.COST,a.CPU_COST,a.IO_COST from v$sql_plan a where plan_hash_value='3577433401';

OPTIONS              OBJECT_NAME             ID       COST   CPU_COST    IO_COST
-------------------- --------------- ---------- ---------- ---------- ----------
                                              0          4
BY INDEX ROWID       T9                       1          4      29226          4
RANGE SCAN           T9_ID                    2          3      21764          3

CPU_COST=(v$sql_plan.CPU_COST/CPUSPEED)/SREADTIM
        =(29226/1486*1000000)/(6.39/1000)
        =0.00308

三、数据分布不均匀,收集了histogram statistics

收集histogram statistics
begin
dbms_stats.gather_table_stats(ownname => 'MEISHD',tabname => 'T9',estimate_percent => 100,method_opt => 'FOR COLUMNS SIZE 20 id');
end;
SQL> select table_name,column_name,num_buckets,histogram from user_tab_col_statistics where column_name='ID';

TABLE_NAME                     COLUMN_NAME                    NUM_BUCKETS HISTOGRAM
------------------------------ ------------------------------ ----------- ---------------
T9                             ID                                      20 HEIGHT BALANCED

SQL> select endpoint_number,endpoint_actual_value from user_tab_histograms where table_name='T9' and column_name='ID';

ENDPOINT_NUMBER ENDPOINT_ACTUAL_VALUE
--------------- ------------------------------
              0 000000000000000001
              1 000000000000100000
              2 000000000000200000
              3 000000000000300000
              4 000000000000400000
              5 000000000000500000
              6 000000000000600000
              7 000000000000700000
              8 000000000000800000
              9 000000000000900000
             10 000000000001000000
             11 000000000001100000
             12 000000000001200000
             13 000000000001300000
             14 000000000001400000
             15 000000000001500000
             16 000000000001600000
             17 000000000001700000
             18 000000000001800000
             19 000000000001900000
             20 100000000000000000

21 rows selected.

SQL>

计算IO_COST为88

index_selectivity_bucket  = required range / total range + 1/num_distinct + 1/num_distinct)
                          = (70000-60000)/(100000-1)+2/2000000
                          = 0.100002
index_selectivity = index_selectivity_bucket/num_buckets = 0.0050001


cost=blevel + ceiling(leaf_blocks * index_selectivity) + ceiling(clustering_factor * table_selectivity)
    =2+ceiling(8369*0.0050001)+ceiling(8683*0.0050001)
    =2+ceiling(41.84)+ceiling(43.42)
    =2+42+44
    =88

SQL> select * from t9 where id between '000000000000060000' and '000000000000070000';

Execution Plan
----------------------------------------------------------
Plan hash value: 3577433401

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 10000 |   253K|    88   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T9    | 10000 |   253K|    88   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T9_ID | 10000 |       |    44   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("ID">='000000000000060000' AND "ID"<='000000000000070000')

SQL>

确认IO_COST为88,总COST为88,CPU_COST为0.46。
关于CPU_COST的疑问同上。

SQL> select a.OPTIONS,a.OBJECT_NAME,a.ID,a.COST,a.CPU_COST,a.IO_COST from v$sql_plan a where plan_hash_value='3577433401';

OPTIONS              OBJECT_NAME             ID       COST   CPU_COST    IO_COST
-------------------- --------------- ---------- ---------- ---------- ----------
                                              0         88
BY INDEX ROWID       T9                       1         88    4327057         88
RANGE SCAN           T9_ID                    2         44    2313543         44

CPU_COST=(v$sql_plan.CPU_COST/CPUSPEED)/SREADTIM
        =(4327057/1486*1000000)/(6.39/1000)
        =0.46

总结
在数据分布严重不均衡的情况下(尤其是认为设置了致命的default值),根据统计信息算出的COST严重不符事实。因此:
1、设置default值时,应在正常的数据范围内;
2、如果数据自身很不均衡,则需要通过收集histogram统计信息来保证COST的准确性。

[ 本帖最后由 newcomer1981 于 2009-9-24 17:57 编辑 ]
论坛徽章:
98
2009日食纪念
日期:2009-07-22 09:30:002010新春纪念徽章
日期:2010-03-01 11:05:012010年世界杯参赛球队:美国
日期:2010-05-02 21:32:232010数据库技术大会纪念徽章
日期:2010-05-13 09:34:23ITPUB季度 技术新星
日期:2010-05-17 15:53:17数据库板块每日发贴之星
日期:2010-05-24 01:01:072010年世界杯参赛球队:意大利
日期:2010-05-31 20:55:432010年世界杯参赛球队:巴拉圭
日期:2010-07-03 13:06:582010世博会纪念徽章
日期:2010-08-18 23:53:44ITPUB9周年纪念徽章
日期:2010-10-08 09:28:51
发表于 2009-9-24 22:29 | 显示全部楼层
不错。学习。

使用道具 举报

回复
论坛徽章:
0
发表于 2009-9-25 09:20 | 显示全部楼层
支持搂主,希望能出下一部!

使用道具 举报

回复
论坛徽章:
129
2015年新春福章
日期:2015-03-06 11:57:31ITPUB9周年纪念徽章
日期:2010-10-08 09:32:26ITPUB9周年纪念徽章
日期:2010-10-08 09:32:26ITPUB9周年纪念徽章
日期:2010-10-08 09:32:26ITPUB9周年纪念徽章
日期:2010-10-08 09:32:262010世博会纪念徽章
日期:2010-10-05 15:55:362010广州亚运会纪念徽章:手球
日期:2010-09-15 22:46:19设计板块每日发贴之星
日期:2010-07-23 01:01:01行业板块每日发贴之星
日期:2010-07-22 01:01:04开发板块每日发贴之星
日期:2010-07-22 01:01:04
发表于 2009-9-25 09:21 | 显示全部楼层
学习中

使用道具 举报

回复
论坛徽章:
0
发表于 2009-9-25 13:12 | 显示全部楼层
支持原创,谢谢分享!
我也做下实验看看……

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表