Oracle 11g收集多列统计信息详解

前言

通常，当我们将SQL语句提交给Oracle数据库时，Oracle会选择一种最优方式来执行，这是通过查询优化器Query Optimizer来实现的。CBO（Cost-Based Optimizer）是Oracle默认使用的查询优化器模式。在CBO中，SQL执行计划的生成，是以一种寻找成本（Cost）最优为目标导向的执行计划探索过程。所谓成本（Cost）就是将CPU和IO消耗整合起来的量化指标，每一个执行计划的成本就是经过优化器内部公式估算出的数字值。

我们在写SQL语句的时候，经常会碰到where子句后面有多个条件的情况，也就是根据多列的条件筛选得到数据。默认情况下，oracle会把多列的选择率（selectivity）相乘从而得到where语句的选择率，这样有可能造成选择率（selectivity）不准确，从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断，从而生成准确的执行计划，oracle在11g数据库中引入了收集多列统计信息。本文通过对测试表的多条件查询，介绍收集多列统计信息的重要性。

一、环境准备

我们在Oracle 11g中进行试验。

SQL> 
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>

在hr用户下创建测试表hoegh，重复插入数据，数据量相当于16个employees表（总行数1712＝107*16）。

SQL> 
SQL> conn hr/hr
Connected.
SQL> 
SQL> create table hoegh as select * from employees;

Table created.

SQL> select count(*) from hoegh;

 COUNT(*)
----------
  107

SQL> 
SQL> insert into hoegh select * from hoegh;

107 rows created.

SQL> /

214 rows created.

SQL> /

428 rows created.

SQL> /

856 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from hoegh;

 COUNT(*)
----------
  1712

SQL>

二、按照常规方法收集统计量信息；

SQL> 
SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\');

PL/SQL procedure successfully completed.

SQL>

三、查看执行单个条件的where语句的执行计划

SQL> 
SQL> explain plan for select * from hoegh where employee_id=110;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 774871165

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1104 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1104 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter(\"EMPLOYEE_ID\"=110)

13 rows selected.

SQL>

从执行计划可以看出返回了16行记录，结果没有问题。可是，这个16是哪儿来的呢，我们先要了解选择率（selectivity）和返回行数是如何计算的：

选择率（selectivity）＝在本例中是 1/唯一值

返回行数＝选择率（selectivity）＊表记录总数

也就是说，在这个查询语句中，选择率＝1/107，返回行数＝1/107*1712=16

四、查看执行两个条件的where语句的执行计划

SQL> 
SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';

Explained.

SQL> 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 774871165

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HOEGH | 1 | 69 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')

13 rows selected.

SQL>

从执行计划可以看出返回了1行记录，而事实又是什么样的呢？我们执行一下这条sql语句。

SQL> select count(*) from hoegh where employee_id=110 and email=\'JCHEN\';

 COUNT(*)
----------
  16

SQL>

由此看出，测试表hoegh符合查询条件的数据有16行，而执行计划提示的只有1行，出错了。这是怎么回事呢，也就是我们在开篇提到的选择率（selectivity）出了问题。

在这个多列条件查询语句中，选择率＝1/107＊1/107，返回行数＝1/107＊1/107*1712=16/107<1；由于表中存在符合条件的记录，并且返回行数不可能小于1，所以Oracle返回了1。

五、收集多列统计信息，再次查看两个条件的where语句的执行计划

SQL> 
SQL> exec dbms_stats.gather_table_stats(\'HR\',\'HOEGH\',method_opt=>\'for columns(employee_id,email)\');

PL/SQL procedure successfully completed.

SQL> 
SQL> explain plan for select * from hoegh where employee_id=110 and email=\'JCHEN\';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 774871165

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 1152 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| HOEGH | 16 | 1152 | 8 (0)| 00:00:01 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

 1 - filter(\"EMPLOYEE_ID\"=110 AND \"EMAIL\"=\'JCHEN\')

13 rows selected.

SQL>

从执行计划的结果来看，同样的一条sql查询语句，在收集多列统计信息后，Oracle的选择率（selectivity）由错变对，这是由于sql语句中的两个条件是有关联的，即employee_id和email在employees表中都是唯一的，都可以唯一标识一行记录；而在收集多列统计信息之前，Oracle并不知道这两个查询条件有关联，所以在计算选择率（selectivity）时，只是简单地采取了相乘的方法。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。

# oracle  # 多列统计  # 收集统计信息  # 11g  # 统计信息  # Oracle收集和查看统计信息的方法  # Oracle 12c新特性之如何检测有用的多列统计信息详解  # Oracle统计信息的导出导入测试示例详解  # oracle自动统计信息时间的修改过程记录  # ORACLE中锁住/解锁统计信息的操作代码  # Oracle数据库统计信息方法详解  # 行数  # 这是  # 在这个  # 可以看出  # 最优  # 子句  # 不可能  # 出了  # 是有  # 又是  # 多个  # 有可能  # 而在  # 这两个  # 错了  # 这条  # 当我们  # 会把  # 这篇文章 

相关文章： 如何用IIS7快速搭建并优化网站站点？  如何用西部建站助手快速创建专业网站？  在线制作视频网站免费,都有哪些好的动漫网站？  如何破解联通资金短缺导致的基站建设难题？  建站10G流量真的够用吗？如何应对访问高峰？  c# 在高并发下使用反射发射（Reflection.Emit）的性能  相册网站制作软件,图片上的网址怎么复制？  股票网站制作软件,网上股票怎么开户？  香港服务器建站指南：外贸独立站搭建与跨境电商配置流程  网站制作专业公司有哪些,如何制作一个企业网站，建设网站的基本步骤有哪些？  如何快速建站并高效导出源代码？  如何制作公司的网站链接,公司想做一个网站，一般需要花多少钱？  电商网站制作公司有哪些,1688网是什么意思？  如何在Golang中使用replace替换模块_指定本地或远程路径  建站之星如何保障用户数据免受黑客入侵？  如何在建站主机中优化服务器配置？  定制建站流程解析：需求评估与SEO优化功能开发指南  西安大型网站制作公司,西安招聘网站最好的是哪个？  为什么Go需要go mod文件_Go go mod文件作用说明  建站之星如何取消后台验证码生成？  ,网页ppt怎么弄成自己的ppt？  如何零基础开发自助建站系统？完整教程解析  如何在服务器上配置二级域名建站？  如何配置支付宝与微信支付功能？  建站主机选哪种环境更利于SEO优化？  建站主机默认首页配置指南：核心功能与访问路径优化  建站主机CVM配置优化、SEO策略与性能提升指南  网站视频怎么制作,哪个网站可以免费收看好莱坞经典大片？  正规网站制作公司有哪些,目前国内哪家网页网站制作设计公司比较专业靠谱？口碑好？  网站制作怎么样才能赚钱,用自己的电脑做服务器架设网站有什么利弊，能赚钱吗？  广东专业制作网站有哪些,广东省能源集团有限公司官网？  外贸公司网站制作哪家好,maersk船公司官网？  极客网站有哪些,DoNews、36氪、爱范儿、虎嗅、雷锋网、极客公园这些互联网媒体网站有什么差异？  建站之星与建站宝盒如何选择最佳方案？  ,南京靠谱的征婚网站？  学校为何禁止电信移动建设网站？  网站微信制作软件,如何制作微信链接？  建站之星如何助力企业快速打造五合一网站？  如何用手机制作网站和网页,手机移动端的网站能制作成中英双语的吗？  香港服务器租用每月最低只需15元？  如何快速查询网址的建站时间与历史轨迹？  美食网站链接制作教程视频,哪个教做美食的网站比较专业点？  盐城做公司网站,江苏电子版退休证办理流程？  建站之家VIP精选网站模板与SEO优化教程整合指南  建站一年半SEO优化实战指南：核心词挖掘与长尾流量提升策略  如何制作新型网站程序文件,新型止水鱼鳞网要拆除吗？  如何零基础在云服务器搭建WordPress站点？  音乐网站服务器如何优化API响应速度？  定制建站是什么？如何实现个性化需求？  品牌网站制作公司有哪些,买正品品牌一般去哪个网站买？