文档章节

oracle数据库查询语句优化机制

c
 catlover
发布于 2016/07/14 10:36
字数 1716
阅读 4
收藏 0
点赞 0
评论 0

1、 名词解释

状态统计(Statistics):表相关列存储值的规模、分布、密度等。

选中率(Selectivity):限定语句起作用时,将被访问的行数占总行数的百分比。

耗费(Cost):访问行时,所花费的CPU时间、I/O吞吐量、网络流量。

2、 优化机制

选择使总代价{规模×选中率×耗费}达到最小的访问路径。

Oracle在处理时,对索引引起的代价没有记入到访问路径选择中来,文档中指出可以进行用户定义。

参见《Data Cartridge Developer’s Guide—8.Query Optimization》。

在单表访问的时候,该机制很容易理解。即若存在索引,则选择命中率最小的索引进行访问。

若两表联接,代价为{本表规模×本表选中率×本表耗费×联接表基于联接的选中率×联接表的耗费}。在多表联合访问时,先前访问的估算结果集作为后继访问中的状态统计,进而计算其后继访问代价。对按不同访问路径计算的累计代价进行比较,选择使代价最小路径进行访问。

当存在偏差时,后继代价的估算会受到较大的影响。最终的访问路径未必是最优的。

对CPU和I/O的平衡,在多表联合访问时,势必要求尽可能首先选择中间结果集小的优先访问。

查询操作主要有数据提取、数据筛选(表内限定与表间联接)、语义规整、运算、输出几个步骤。后三步,没有太大的优化空间。优化主要在前两步,数据筛选的顺序又直接影响了数据提取量,是关键的一步。

3、 非常规语句分析

一、WITH子句

With v_pol as

( select * from t_pol where app_date>=to_date’20040101’,’yyyymmdd’)

)

select * from v_pol;

该子句需要创建缓冲区以容纳中间结果,对中间结果集较少的可以采用,反之,建议不要使用。若中间结果集被反复多次用到,采用该语句效果较好。

 

二、ORDER子句

select * from t_pol where app_date>=to_date’20040101’,’yyyymmdd’ order by polno

该子句一般需要缓冲区以进行排序,当order by建立在已排序的索引基础上,并且也正好使用了该访问路径,则不需要额外的缓冲区、也不需要做排序处理。未参考文档。

 

三、Window类函数

select polno,count(*) over(partition by agentno) agent_pol_num

from t_pol where app_date>=to_date’20040101’,’yyyymmdd’

该语句需要缓冲区以进行排序,大部分窗口函数内建排序机制,所需缓冲区的大小视窗口的规模而定。当窗口函数激活了排序功能时,若中间结果集过大,应慎重处理。

 

四、CONNECT BY

Select deptno,parentno,level

from t_dept

start with parentno is null

connect by parentno=prior deptno

该语句不能与ORDER子句联合使用。本语句采用中序遍历方式访问森林。当存在多表连接时,首先进行连接;否则,首先处理自连接,后执行Where子句。

 

五、GROUPING SETS

Select deptno,agentno,month,count(*) pol_num

From t_pol

Group by grouping sets     ( (deptno,agentno),

                                        (deptno,month),

                                        ()

                                       )

同多次执行简单的group by子句相比,其降低了细节数据访问的次数,若聚类函数支持Merge功能,那么()分组的汇总可以由(deptno,agentno)分组的汇总结果集计算得到,降低了计算量。

 

六、DISTINCT子句

该语句需要缓冲区以进行排序,当中间结果集较大时,应慎重处理。

 

七、UNION语句

该语句等价于DISTINCT语句,是建立在两个数据源上的。

UNION ALL只是简单的数据源归并。不需要建立中间结果集。

4、 优化处理

一、建立索引

建立索引的目的为降低访问耗费。

被用来联接的列必须建立索引,且联接列不能在运算后参与联接。

当存在多个可用索引时,系统会自动选择代价较小的索引优先使用。可以指定索引参与代价估算。

Select /*+ index idx_agentno*/

From t_pol where agentno=1

其中/*+ */中间的内容,指示访问选择器的处理方式。

同一索引键值下存在记录数越多,则访问耗费越高;没有索引的表访问耗费最高。对于同一索引,不同键值下的记录数分布不理想,会造成非优路径的选择。

当列在计算后参与联接或选择时,该列不参与含该列的索引的耗费估算中。即该列仅作为普通列处理而不是索引列。应慎重处理。

 

二、指定联接顺序

在没有特别说明下,系统采用基于代价的方式选择路径。允许采用说明的方式来指定联接顺序。

Select /*+ ordered */

From t_pol a,t_agent b,t_dept c

Where a.agentno=1

 And a.agentno=b.agentno

 And b.deptno=c.deptno

 And a.deptno=c.deptno

该语句由t_pol表开始逐次联接t_agent、t_dept。以表出现的先后次序作为联接次序而不是以Where联接语句为准。

可以按照选中率由小到大,依次联接各表。

 

三、估算中间结果集

对于大型应用来说,应考虑中间结果集的大小。若中间结果集较大,又不可避免,则可以通过汇总方式来降低结果集的规模。

避免使用具有ORDER性质的子句。可以通过建立合适的索引并启用索引来达到排序的目的。索引值是已排序的。

避免使用WITH子句存放大量明细。

避免选择不需要的列,不参与后继运算的列不需要读取。

 

四、路径选择代价

对于查询语句的分析需要一定的代价,联接的表越多、可用的索引越多、则分析的代价越高,准确度越低。语句的复杂程度由可选访问路径数决定。

避免多表联接,不可避免时,建立有效的索引、指定索引、指定联接顺序达到最优化路径选择。

在路径选择分析时,需要对统计状态进行访问,并且相关的记录项进行锁定,处理结束后解锁。若分析时失败且未能解锁,则系统将处于瘫痪状态。未参考文档。

© 著作权归作者所有

共有 人打赏支持
c
粉丝 1
博文 12
码字总数 7741
作品 0
浦东
项目经理
oracle查询工作原理

假如,我们现在利用Select语句从数据库查询数据,Oracle数据库是如何运作的呢?下面就让我们来了解一下Oracle查询工作原理。 结合一条简单的select语句,看看Oracle数据库后台的运作机制。这对...

lvzjane ⋅ 2012/12/26 ⋅ 0

带您了解Oracle查询工作原理

假如,我们现在利用Select语句从数据库查询数据,Oracle数据库是如何运作的呢?下面就让我们来了解一下Oracle查询工作原理。 结合一条简单的select语句,看看Oracle数据库后台的运作机制。这对...

0o清风徐来o0 ⋅ 2012/10/26 ⋅ 1

认识MySQL---JC MySQL系列(一)

(一)认识MySQL MySQL数据库是开放源代码的关系型数据库。目前,它可以提供的功能有:支持sql语言、子查询、存储过程、触发器、视图、索引、事务、锁、外键约束和影像复制等。 同Oracle和S...

jk88 ⋅ 2013/05/24 ⋅ 0

ORACLE将执行过的SQL语句存放在内存的共享池

Oracle SQL性能优化深入浅出 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执...

技术小甜 ⋅ 2017/11/09 ⋅ 0

Mysql 和 Postgresql 对比

Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql, 很多开源软件都是以Mysql 作为数据库标准,并且以Mysql 作为抽象基础的,但是具体使用过程中,发现Mysql 有很多问题...

宏哥 ⋅ 2010/12/14 ⋅ 63

从Select语句看Oracle查询原理

Select语句可以说是DBA和数据库开发者在工作中使用最多的语句之一,但这条语句是如何执行?在Oracle数据库中又是如何运作的呢?今天我们就从一条简单的Select语句开始,看看Oracle数据库后台的...

孟飞阳 ⋅ 2016/07/15 ⋅ 0

数据库性能优化有哪些措施

1、1、调整数据结构的设计。这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等。 2、2、调整应用程序结构设计。这...

穆思阁 ⋅ 2015/04/11 ⋅ 0

Oracle SQL 性能优化技巧

1.选用适合的ORACLE优化器  ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZERMODE参数的各种声明,...

ghostwl ⋅ 2014/12/19 ⋅ 0

数据库查询性能优化问题

查询、新增、修改及删除数据库等操作是影响web应用程序性能指标的重大因素。下面一些常见的建议可以提高查询性能问题。 1. 优化JDBC连接 采用数据库连接池机制可以将曾打开的数据库连接保存在...

lindianlide ⋅ 2014/08/29 ⋅ 0

《高性能MySQL》读书笔记之MySQL 优化

关于MySQL的执行过程: 1 MySQL中存在对SQL语句的改写,通过改写SQL达到优化SQL语句的目的,如将子查询改为关联查询. 2 MySQL并没有像Oracle 11g那样缓存执行计划,在Java开发中PreparedStateme...

周翔 ⋅ 2016/11/02 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Centos7重置Mysql 8.0.1 root 密码

问题产生背景: 安装完 最新版的 mysql8.0.1后忘记了密码,向重置root密码;找了网上好多资料都不尽相同,根据自己的问题总结如下: 第一步:修改配置文件免密码登录mysql vim /etc/my.cnf 1...

豆花饭烧土豆 ⋅ 今天 ⋅ 0

熊掌号收录比例对于网站原创数据排名的影响[图]

从去年下半年开始,我在写博客了,因为我觉得业余写写博客也还是很不错的,但是从2017年下半年开始,百度已经推出了原创保护功能和熊掌号平台,为此,我也提交了不少以前的老数据,而这些历史...

原创小博客 ⋅ 今天 ⋅ 0

LVM讲解、磁盘故障小案例

LVM LVM就是动态卷管理,可以将多个硬盘和硬盘分区做成一个逻辑卷,并把这个逻辑卷作为一个整体来统一管理,动态对分区进行扩缩空间大小,安全快捷方便管理。 1.新建分区,更改类型为8e 即L...

蛋黄Yolks ⋅ 今天 ⋅ 0

Hadoop Yarn调度器的选择和使用

一、引言 Yarn在Hadoop的生态系统中担任了资源管理和任务调度的角色。在讨论其构造器之前先简单了解一下Yarn的架构。 上图是Yarn的基本架构,其中ResourceManager是整个架构的核心组件,它负...

p柯西 ⋅ 今天 ⋅ 0

uWSGI + Django @ Ubuntu

创建 Django App Project 创建后, 可以看到路径下有一个wsgi.py的问题 uWSGI运行 直接命令行运行 利用如下命令, 可直接访问 uwsgi --http :8080 --wsgi-file dj/wsgi.py 配置文件 & 运行 [u...

袁祾 ⋅ 今天 ⋅ 0

JVM堆的理解

在JVM中,我们经常提到的就是堆了,堆确实很重要,其实,除了堆之外,还有几个重要的模块,看下图: 大 多数情况下,我们并不需要关心JVM的底层,但是如果了解它的话,对于我们系统调优是非常...

不羁之后 ⋅ 昨天 ⋅ 0

推荐:并发情况下:Java HashMap 形成死循环的原因

在淘宝内网里看到同事发了贴说了一个CPU被100%的线上故障,并且这个事发生了很多次,原因是在Java语言在并发情况下使用HashMap造成Race Condition,从而导致死循环。这个事情我4、5年前也经历...

码代码的小司机 ⋅ 昨天 ⋅ 2

聊聊spring cloud gateway的RetryGatewayFilter

序 本文主要研究一下spring cloud gateway的RetryGatewayFilter GatewayAutoConfiguration spring-cloud-gateway-core-2.0.0.RC2-sources.jar!/org/springframework/cloud/gateway/config/G......

go4it ⋅ 昨天 ⋅ 0

创建新用户和授予MySQL中的权限教程

导读 MySQL是一个开源数据库管理软件,可帮助用户存储,组织和以后检索数据。 它有多种选项来授予特定用户在表和数据库中的细微的权限 - 本教程将简要介绍一些选项。 如何创建新用户 在MySQL...

问题终结者 ⋅ 昨天 ⋅ 0

android -------- 颜色的半透明效果配置

最近有朋友问我 Android 背景颜色的半透明效果配置,我网上看资料,总结了一下, 开发中也是常常遇到的,所以来写篇博客 常用的颜色值格式有: RGB ARGB RRGGBB AARRGGBB 这4种 透明度 透明度...

切切歆语 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部