概述
通常,当我们将SQL语句提交给Oracle数据库时,Oracle会选择一种最优方式来执行,这是通过查询优化器Query Optimizer来实现的。CBO是Oracle默认使用的查询优化器模式。在CBO中,SQL执行计划的生成,是以一种寻找成本最优为目标导向的执行计划探索过程。所谓成本就是将CPU和IO消耗整合起来的量化指标,每一个执行计划的成本就是经过优化器内部公式估算出的数字值。
我们在写SQL语句的时候,经常会碰到where子句后面有多个条件的情况,也就是根据多列的条件筛选得到数据。默认情况下,oracle会把多列的选择率(selectivity)相乘从而得到where语句的选择率,这样有可能造成选择率(selectivity)不准确,从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断,从而生成准确的执行计划,oracle在11g数据库中引入了收集多列统计信息。
环境准备
1、查看版本信息
SQL> select * from v$version;
![2f71a0ae11795a52ca21c6ae1a19863f.png](https://i-blog.csdnimg.cn/blog_migrate/574ea840db10d07ae6dad998efab691e.jpeg)
2、创建基础数据
在scott用户下创建测试表t1,重复插入数据,数据量相当于128个emp表(总行数1152=9*128)。
SQL> drop table t1;Table dropped.SQL> create table t1 as select * from emp;Table created.SQL> select count(*) from t1; COUNT(*)---------- 9SQL> insert into t1 select * from t1;9 rows created.SQL> /18 rows created.SQL> /36 rows created.SQL> /72 rows created.SQL> /144 rows created.SQL> /288 rows created.SQL> /576 rows created.
![cddb2663deae3a0fe065c8610bea5fc9.png](https://i-blog.csdnimg.cn/blog_migrate/2858cc030b32efa15a078f17898b9ded.jpeg)
按照常规方法收集统计信息;
exec dbms_stats.gather_table_stats('SCOTT','T1');
![4266914d62060ed0b5720c4e960bb8dd.png](https://i-blog.csdnimg.cn/blog_migrate/8f3dbda676abec64ede303d0182c14ac.jpeg)
查看执行单个条件的where语句的执行计划
explain plan for select * from t1 where empno=7499;select * from table(dbms_xplan.display);
![bd40f772b47fb664dddf4bbfc087b735.png](https://i-blog.csdnimg.cn/blog_migrate/5c455e77970086a805cc5ccbc3e3b087.jpeg)
从执行计划可以看出返回了128行记录,结果没有问题。可是,这个128是哪儿来的呢,我们先要了解选择率(selectivity)和返回行数是如何计算的:
选择率(selectivity)=在本例中是 1/唯一值
返回行数=选择率(selectivity)*表记录总数
也就是说,在这个查询语句中,选择率=1/9,返回行数=1/9*1152=128
查看执行两个条件的where语句的执行计划
explain plan for select * from t1 where empno=7499 and ename='ALLEN';select * from table(dbms_xplan.display);
![350e4f40029b1d848ebd2ece5bc84e99.png](https://i-blog.csdnimg.cn/blog_migrate/55b6ee09e45ca09fdcc917469f83aba3.jpeg)
从执行计划可以看出返回了14行记录,而事实又是什么样的呢?我们执行一下这条sql语句。
select count(*) from t1 where empno=7499 and ename='ALLEN';
![eb6ff482402fb0f7cc48e54206c1e361.png](https://i-blog.csdnimg.cn/blog_migrate/6b0c8ef363fcc50700e044afd950511e.jpeg)
由此看出,测试表t1符合查询条件的数据有128行,而执行计划提示的只有14行,出错了。这是怎么回事呢,也就是我们前面提到的选择率(selectivity)出了问题。
在这个多列条件查询语句中,选择率=1/9*1/9,返回行数=1/9*1/9 *1152=1152/81=14.22,所以Oracle返回了14行。
收集多列统计信息,再次查看两个条件的where语句的执行计划
exec dbms_stats.gather_table_stats('SCOTT','T1',method_opt=>'for columns(empno,ename)');explain plan for select * from t1 where empno=7499 and ename='ALLEN';select * from table(dbms_xplan.display);
![e505891756567593fe843d495cbc698b.png](https://i-blog.csdnimg.cn/blog_migrate/28d54799f009beabedf2e28ccbd7f7a9.jpeg)
是不是又变回了128呢...
从执行计划的结果来看,同样的一条sql查询语句,在收集多列统计信息后,Oracle的选择率(selectivity)由错变对,这是由于sql语句中的两个条件是有关联的,即empno和ename在T1表中都是唯一的,都可以唯一标识一行记录;而在收集多列统计信息之前,Oracle并不知道这两个查询条件有关联,所以在计算选择率(selectivity)时,只是简单地采取了相乘的方法。
看完是不是又学会了一招呢?所以要相信Oracle收集统计信息未必也是正确的,具体情况还是要具体分析。
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
![f614a11dd1722bc67762ac28b5ba6f82.gif](https://i-blog.csdnimg.cn/blog_migrate/803e67b940c3ee88c5f15eed5847a1d8.gif)