文档章节

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

c
 catlover
发布于 2016/07/14 10:36
字数 1716
阅读 8
收藏 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子句存放大量明细。

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

 

四、路径选择代价

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

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

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

© 著作权归作者所有

共有 人打赏支持
下一篇: http请求
c
粉丝 1
博文 13
码字总数 7741
作品 0
浦东
项目经理
私信 提问
带您了解Oracle查询工作原理

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

0o清风徐来o0
2012/10/26
0
1
认识MySQL---JC MySQL系列(一)

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

jk88
2013/05/24
0
0
ORACLE将执行过的SQL语句存放在内存的共享池

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

技术小甜
2017/11/09
0
0
Oracle SQL 性能优化技巧

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

ghostwl
2014/12/19
0
0
数据库查询性能优化问题

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

lindianlide
2014/08/29
0
0

没有更多内容

加载失败,请刷新页面

加载更多

CentOS配置Tomcat监听80端口,虚拟主机

Tomcat更改默认端口为80 更改的配置文件是: /usr/local/tomcat/conf/server.xml [root@test-a ~]# vim /usr/local/tomcat/conf/server.xml # 找到 Connector port="8080" protocol="HTTP/1......

野雪球
今天
5
0
《稻盛和夫经营学》读后感心得体会3180字范文

《稻盛和夫经营学》读后感心得体会3180字范文: 一代日本经营之圣稻盛和夫凭借刻苦勤奋的精神以及深植于佛教的商业道德准则,成为了“佛系”企业家的代表人物。在《稻盛和夫经营学》“领导人...

原创小博客
今天
3
0
java框架学习日志-5(常见的依赖注入)

依赖注入(dependency injection) 之前提到控制反转(Inversion of Control)也叫依赖注入,它们其实是一个东西,只是看的角度不同,这章详细说一下依赖注入。 依赖——指bean对象创建依赖于...

白话
今天
4
0
红外接收器驱动开发

背景:使用系统的红外遥控软件没有反应,然后以为自己接线错误,反复测试,结果烧坏了一个红外接收器,信号主板没有问题。所以自己开发了一个红外接收器的python驱动。接线参见https://my.os...

mbzhong
今天
2
0
ActiveMQ消息传送机制以及ACK机制详解

AcitveMQ是作为一种消息存储和分发组件,涉及到client与broker端数据交互的方方面面,它不仅要担保消息的存储安全性,还要提供额外的手段来确保消息的分发是可靠的。 一. ActiveMQ消息传送机...

watermelon11
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部