oracle cbo 默认选择率及其问题凯发app官方网站的解决方案-凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156273
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3760
  • 用 户 组： 普通用户
  • 注册时间： 2011-03-30 13:00
个人简介

about me:oracle ace pro,optimistic,passionate and harmonious. focus on oracle,mysql and other database programming,peformance tuning,db design, j2ee,linux/aix,architecture tech,etc

文章分类

全部博文（166）

文章存档

2024年（21）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
oracle cbo 默认选择率及其问题凯发app官方网站的解决方案_part3

分类： oracle

2024-05-17 17:02:04

接：oracle cbo 默认选择率及其问题凯发app官方网站的解决方案_part2

本部分主要研究下默认选择率出现问题的凯发app官方网站的解决方案。

3.默认选择率导致执行计划不准的凯发app官方网站的解决方案


遇到cbo使用默认选择率，计算的cardinality与实际差别很大，让执行计划走错的tuning方法
如果使用默认选择率，计算的cardinality与实际差别很大，则不准确。可能导致后续的join method,join order选择错误，这时候需要根据具体情况去调整选择率变得更准确。


1）如果是有统计信息，但是因为使用绑定变量，peeking关闭的，则需要打开peeking才可以
按照实际传入的值计算准确selectivity
如果全局peeking是关闭的，可以针对特定sql打开， /* opt_param('_optim_peek_user_binds', 'false')*/ 放到sql profile里绑定。


2)表有统计信息，但是列未收集统计信息的
  这时候都是使用默认选择率，列无统计信息，则需要收集缺失的列统计信息：
  不正确的收集方法，只收集索引列统计信息：  

点击(此处)折叠或打开

  1. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',estimate_percent=>10,-
  2. method_opt=>'for all indexed columns size skewonly',no_invalidate=>false);
建议收集语句如下：

点击(此处)折叠或打开

  1. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',estimate_percent=>dbms_stats.auto_sample_size,-
  2. method_opt=>'for all columns size auto',no_invalidate=>false);
如果分区表，建议增加partname,避免无数据的分区num_rows=0.

当然，如果统计信息是自动收集的，则没有这种问题。

3）有统计信息，但是对列使用了函数
因为函数不存储统计信息，这时候会使用默认统计信息，函数等值1%，函数范围5%选择率

动态采样：
如果没有统计信息，动态采样，大部分时候也是准确的,可以对函数条件采样。
有统计信息谓词复杂，就不会动态采样，函数条件按默认选择率，可以采用高级别动态采样，dynamic_sampling>=3。

对于这种情况可以使用cardinality/estimate_rows hints传入较准确值、高级别动态采样dynamic_sampling(4),扩展列统计信息，sql profile绑定等

对于这种方案还是建议收集扩展列统计信息。

sample:

点击(此处)折叠或打开

  1. explain plan for
  2. select * from sel_test
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';

  5. select * from table(dbms_xplan.display);
  6. plan_table_output
  7. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  8. plan hash value: 747827318

  10. ------------------------------------------------------------------------------
  11. | id | operation | name | rows | bytes | cost (%cpu)| time |
  12. ------------------------------------------------------------------------------
  13. | 0 | select statement | | 7 | 924 | 396 (1)| 00:00:01 |
  14. |* 1 | table access full| sel_test | 7 | 924 | 396 (1)| 00:00:01 |
  15. ------------------------------------------------------------------------------

  17. predicate information (identified by operation id):
  18. ---------------------------------------------------

  20.    1 - filter(substr("object_name",1,1)='d' and
  21.               substr("owner",1,3)='sys')

这个sql条件是substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys'，因此sel=0.01*0.01,
对应的返回行数=num_rows*0.01*0.01=round(72800*0.01*0.01)=7

但是实际情况是2631行：

点击(此处)折叠或打开

  1. select count(*) from sel_test
  2. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';
  3.   count(*)
  4. ----------
  5.       2631

很显然，不够准确，可以用cardinality/estimate_rows hints(table函数默认8168也不准，常用cardinality hints)：

3.1)使用cardinality hints

使用cardinality hints,这是undocuments hints,只能指定表返回的行数:


点击(此处)折叠或打开

  1. explain plan for
  2. select/*cardinality(sel_test 3000)*/ * from sel_test
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';

  5. select * from table(dbms_xplan.display);
  6. plan_table_output
  7. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  8. plan hash value: 747827318

  10. ------------------------------------------------------------------------------
  11. | id | operation | name | rows | bytes | cost (%cpu)| time |
  12. ------------------------------------------------------------------------------
  13. | 0 | select statement | | 3000 | 386k| 396 (1)| 00:00:01 |
  14. |* 1 | table access full| sel_test | 3000 | 386k| 396 (1)| 00:00:01 |
  15. ------------------------------------------------------------------------------

  17. predicate information (identified by operation id):
  18. ---------------------------------------------------

  20.    1 - filter(substr("object_name",1,1)='d' and
  21.               substr("owner",1,3)='sys')

3.2）使用opt_estimate hints


使用opt_estimate,这个更强大，可以控制索引访问，索引过滤，回表，join等返回行数估算，可以指定行数指定原估算倍数等：
比如下面用/* opt_estimate(table sel_test scale_rows=100)*/，原估算7行，现在扩大100倍：

点击(此处)折叠或打开

  1. explain plan for
  2. select/*opt_estimate(table sel_test scale_rows=100)*/ * from sel_test
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';
  4. select * from table(dbms_xplan.display);
现在的rows=728,原先的7是round结果，实际上=round(72800*0.01*0.01)*100=728

点击(此处)折叠或打开

  1. plan_table_output
  2. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. plan hash value: 747827318

  5. ------------------------------------------------------------------------------
  6. | id | operation | name | rows | bytes | cost (%cpu)| time |
  7. ------------------------------------------------------------------------------
  8. | 0 | select statement | | 728 | 96096 | 396 (1)| 00:00:01 |
  9. |* 1 | table access full| sel_test | 728 | 96096 | 396 (1)| 00:00:01 |
  10. ------------------------------------------------------------------------------

  12. predicate information (identified by operation id):
  13. ---------------------------------------------------
  14.    1 - filter(substr("object_name",1,1)='d' and
  15.               substr("owner",1,3)='sys')

3.3）使用高级别动态采样dynamic_sampling(3-5)

可以对表有多列复杂条件采样，5比4使用更多数据块采样，可能更准确
指定不准的表动态采样，避免过度采样：dynamic_sampling(alias 4)


点击(此处)折叠或打开

  1. explain plan for
  2. select/*dynamic_sampling(4)*/ * from sel_test
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';
  4. select * from table(dbms_xplan.display);

  6. plan_table_output
  7. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  8. plan hash value: 747827318

  10. ------------------------------------------------------------------------------
  11. | id | operation | name | rows | bytes | cost (%cpu)| time |
  12. ------------------------------------------------------------------------------
  13. | 0 | select statement | | 3916 | 504k| 396 (1)| 00:00:01 |
  14. |* 1 | table access full| sel_test | 3916 | 504k| 396 (1)| 00:00:01 |
  15. ------------------------------------------------------------------------------

  17. predicate information (identified by operation id):
  18. ---------------------------------------------------

  20.    1 - filter(substr("object_name",1,1)='d' and
  21.               substr("owner",1,3)='sys')

  23. note
  24. -----
  25.    - dynamic statistics used: dynamic sampling (level=4)

指定表动态采样，建议：/* dynamic_sampling(a 5)*/，但是note:dynamic statistics used: dynamic sampling (level=2),
不影响优化效果。

点击(此处)折叠或打开

  1. explain plan for
  2. select/*dynamic_sampling(a 5)*/ * from sel_test a
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';
  4. select * from table(dbms_xplan.display);

  6. plan_table_output
  7. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  8. plan hash value: 747827318

  10. ------------------------------------------------------------------------------
  11. | id | operation | name | rows | bytes | cost (%cpu)| time |
  12. ------------------------------------------------------------------------------
  13. | 0 | select statement | | 2474 | 362k| 396 (1)| 00:00:01 |
  14. |* 1 | table access full| sel_test | 2474 | 362k| 396 (1)| 00:00:01 |
  15. ------------------------------------------------------------------------------

  17. predicate information (identified by operation id):
  18. ---------------------------------------------------

  20.    1 - filter(substr("object_name",1,1)='d' and
  21.               substr("owner",1,3)='sys')

  23. note
  24. -----
  25.    - dynamic statistics used: dynamic sampling (level=2)

采用dynamic_sampling(5)更准确，现在估算的行数=2474，比4更接近真实行数2631.

3.4）使用扩展列统计信息


只采集组合列不采集单列的好像用不上扩展列统计信息,而组合的报错：
ora-20001: invalid extension: column group can contain only columns seperated by comma


收集扩展统计信息如下：

点击(此处)折叠或打开

  1. --收集单列扩展
  2. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',-
  3. method_opt=>'for columns size auto (substr(object_name,1,1)),(substr(owner,1,3)),',no_invalidate=>false);
  4. --收集组合扩展
  5. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',-
  6. method_opt=>'for columns size auto (substr("object_name",1,1),substr("owner",1,3))',no_invalidate=>false);
查看扩展列情况：

点击(此处)折叠或打开

  1. elect extension_name,extension from dba_stat_extensions where table_name='sel_test';
  2. extension_name extension
  3. ------------------------------ --------------------------------------------------------------------------------
  4. sys_stu5nqyw0t0y__cuous30xu1jp (substr("object_name",1,1))
  5. sys_stubtk8r9uro9qaau#1ly00shp (substr("owner",1,3))
  6. sys_stu7dgj0xnrqn13e0ka_e698y3 (substr("object_name",1,1),substr("owner",1,3))
column group会自动创建分组，删除统计信息不会删除分组，需要使用：

点击(此处)折叠或打开

  1. exec dbms_stats.drop_extended_stats(ownname=>user,tabname=>'sel_test',extension=>'(substr("object_name",1,1))');
  2. exec dbms_stats.drop_extended_stats(ownname=>user,tabname=>'sel_test',extension=>'(substr("owner",1,3))');
  3. exec dbms_stats.drop_extended_stats(ownname=>user,tabname=>'sel_test',extension=>'(substr("object_name",1,1),substr("owner",1,3))');
扩展列统计信息：

点击(此处)折叠或打开

  1. select column_name,num_distinct,histogram
  2. from dba_tab_col_statistics
  3. where table_name='sel_test'
  4. and column_name='sys_stu7dgj0xnrqn13e0ka_e698y3';
  5. column_name num_distinct histogram
  6. -------------------- ------------ ------------------------------
  7. sys_stu7dgj0xnrqn13e 244 frequency
  8. 0ka_e698y3
sql如下：

点击(此处)折叠或打开

  1. explain plan for
  2. select * from sel_test
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';
  4. select * from table(dbms_xplan.display);
  5. plan_table_output
  6. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  7. plan hash value: 747827318

  9. ------------------------------------------------------------------------------
  10. | id | operation | name | rows | bytes | cost (%cpu)| time |
  11. ------------------------------------------------------------------------------
  12. | 0 | select statement | | 298 | 44700 | 396 (1)| 00:00:01 |
  13. |* 1 | table access full| sel_test | 298 | 44700 | 396 (1)| 00:00:01 |
  14. ------------------------------------------------------------------------------

  16. predicate information (identified by operation id):
  17. ---------------------------------------------------

  19.    1 - filter(substr("object_name",1,1)='d' and
  20.               substr("owner",1,3)='sys')



返回行数=round(72800*(1/244))=298;貌似扩展统计信息没有动态采样dynamic_sampling(5)准确。

先创建扩展列，然后在收集全表的，更准：

点击(此处)折叠或打开

  1. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'sel_test',no_invalidate=>false);

点击(此处)折叠或打开

  1. explain plan for
  2. select * from sel_test
  3. where substr(object_name,1,1) = 'd' and substr(owner,1,3)='sys';
  4. select * from table(dbms_xplan.display);

现在估算是2631行，和真实行数2631行一样：

点击(此处)折叠或打开

  1. plan_table_output
  2. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. plan hash value: 747827318

  5. ------------------------------------------------------------------------------
  6. | id | operation | name | rows | bytes | cost (%cpu)| time |
  7. ------------------------------------------------------------------------------
  8. | 0 | select statement | | 2631 | 385k| 396 (1)| 00:00:01 |
  9. |* 1 | table access full| sel_test | 2631 | 385k| 396 (1)| 00:00:01 |
  10. ------------------------------------------------------------------------------

  12. predicate information (identified by operation id):
  13. ---------------------------------------------------

  15.    1 - filter(substr("object_name",1,1)='d' and
  16.               substr("owner",1,3)='sys')

下一篇：oracle cbo 默认选择率及其问题凯发app官方网站的解决方案_part4




阅读(28) | 评论(0) | 转发(0) |
0

上一篇：oracle cbo 默认选择率及其问题凯发app官方网站的解决方案_part2

下一篇：oracle cbo 默认选择率及其问题凯发app官方网站的解决方案_part4

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图