网站首页 > 技术文章 正文
概述
通常,当我们将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;
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(*) ---------- 9 SQL> 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.
按照常规方法收集统计信息;
exec dbms_stats.gather_table_stats('SCOTT','T1');
查看执行单个条件的where语句的执行计划
explain plan for select * from t1 where empno=7499; select * from table(dbms_xplan.display);
从执行计划可以看出返回了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);
从执行计划可以看出返回了14行记录,而事实又是什么样的呢?我们执行一下这条sql语句。
select count(*) from t1 where empno=7499 and ename='ALLEN';
由此看出,测试表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);
是不是又变回了128呢...
从执行计划的结果来看,同样的一条sql查询语句,在收集多列统计信息后,Oracle的选择率(selectivity)由错变对,这是由于sql语句中的两个条件是有关联的,即empno和ename在T1表中都是唯一的,都可以唯一标识一行记录;而在收集多列统计信息之前,Oracle并不知道这两个查询条件有关联,所以在计算选择率(selectivity)时,只是简单地采取了相乘的方法。
看完是不是又学会了一招呢?所以要相信Oracle收集统计信息未必也是正确的,具体情况还是要具体分析。
后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注一下~
- 上一篇: 多表关联查询的性能优化技巧:预关联
- 下一篇: 性能优化技巧:大事实表与大维表关联
猜你喜欢
- 2024-10-19 详解oracle分页存储过程---附实例分享
- 2024-10-19 详解Oracle 数据库enq:TX-index contention等待事件及解决方案
- 2024-10-19 分享一个在表关联时降低业务逻辑复杂度的查询sql
- 2024-10-19 「Oracle」为大家带来connect by的用法,Oracle的递归
- 2024-10-19 基于dba_lobs查找Oracle中lobsegment、lobindex与表之间关系
- 2024-10-19 Oracle项目管理系统之沟通协作 oracle 合作伙伴
- 2024-10-19 oracle sql优化 oracle sql优化的几种方法
- 2024-10-19 关于Oracle中重复数据只取一条记录
- 2024-10-19 ORACLE PL/SQL中异常处理全面分析
- 2024-10-19 大公司oracle笔试题及答案(2) oracle公司待遇如何
你 发表评论:
欢迎- 07-10Oracle 与 Google Cloud 携手大幅扩展多云服务
- 07-10分享收藏的 oracle 11.2.0.4各平台的下载地址
- 07-10Oracle 和 Microsoft 推出 Oracle Exadata 数据库服务
- 07-10Oracle Database@Azure 推进到南美等新区域并增加了新服务
- 07-10Oracle宣布推出 Oracle Database@AWS 的有限预览版
- 07-10Oracle与Nextcloud合作,推出主权云上的安全协作平台
- 07-10NodeRED魔改版连接MsSql、PostgreSQL、MySQL、OracleDB存储无忧
- 07-10对于企业数据云备份,“多备份”承诺的是成本更低,管理更高效#36氪开放日深圳站#
- 602℃几个Oracle空值处理函数 oracle处理null值的函数
- 594℃Oracle分析函数之Lag和Lead()使用
- 582℃0497-如何将Kerberos的CDH6.1从Oracle JDK 1.8迁移至OpenJDK 1.8
- 579℃Oracle数据库的单、多行函数 oracle执行多个sql语句
- 574℃Oracle 12c PDB迁移(一) oracle迁移到oceanbase
- 567℃【数据统计分析】详解Oracle分组函数之CUBE
- 554℃最佳实践 | 提效 47 倍,制造业生产 Oracle 迁移替换
- 548℃Oracle有哪些常见的函数? oracle中常用的函数
- 最近发表
-
- Oracle 与 Google Cloud 携手大幅扩展多云服务
- 分享收藏的 oracle 11.2.0.4各平台的下载地址
- Oracle 和 Microsoft 推出 Oracle Exadata 数据库服务
- Oracle Database@Azure 推进到南美等新区域并增加了新服务
- Oracle宣布推出 Oracle Database@AWS 的有限预览版
- Oracle与Nextcloud合作,推出主权云上的安全协作平台
- NodeRED魔改版连接MsSql、PostgreSQL、MySQL、OracleDB存储无忧
- 对于企业数据云备份,“多备份”承诺的是成本更低,管理更高效#36氪开放日深圳站#
- 解读丨《归档文件整理规则》— 电子文件元数据存储
- Data Guard跳归档恢复的实践(dataguard failover)
- 标签列表
-
- 前端设计模式 (75)
- 前端性能优化 (51)
- 前端模板 (66)
- 前端跨域 (52)
- 前端缓存 (63)
- 前端aes加密 (58)
- 前端脚手架 (56)
- 前端md5加密 (54)
- 前端路由 (61)
- 前端数组 (73)
- 前端js面试题 (50)
- 前端定时器 (59)
- 前端获取当前时间 (50)
- Oracle RAC (76)
- oracle恢复 (77)
- oracle 删除表 (52)
- oracle 用户名 (80)
- oracle 工具 (55)
- oracle 内存 (55)
- oracle 导出表 (62)
- oracle约束 (54)
- oracle 中文 (51)
- oracle链接 (54)
- oracle的函数 (57)
- 前端调试 (52)
本文暂时没有评论,来添加一个吧(●'◡'●)