现在的位置: 首页 > 数据库 > 正文

Oracle SQL性能调优【基础篇】

2016年04月12日 数据库 ⁄ 共 2288字 暂无评论 ⁄ 阅读 737 次
文章目录

Oracle调优主要有三个领域:一是应用程序,主要针对SQL语句;二是实例,主要针对内存、数据结构和实例配置等;三是操作系统交互,主要针对I/O、参数设置等。

性能调优的目的,一方面是想提升SQL语句执行效率减少执行时间,另一方面是降低资源消耗,如CPU消耗、内存消耗和I/O消耗。降低CPU消耗主要是减少不必要的计算,如排序操作(如order by)、对比操作(如distinct)等,降低I/O消耗和内存消耗主要是减少不必要的表扫描。

【SQL执行流程】

1)SQL语句发送,编写SQL在客户端执行(如PL/SQL)发送给Oracle服务器;

2)解析SQL,根据SQL语句生成HASH值并查找Library Cach,若HASH值已存在则跳过解析,否则将对语句进行解析;

第一步:检查SQL语法合法性;

第二步:对照Dictionary Cach(LRU管理)检查对象(表、字段、序列等)存在性;

第三步:获取对象锁信息;

第四步:核对访问权限;

第五步:查询转换(视图合并、子查询解嵌套等)并确定执行计划;

第六步:若语句中存在绑定变量,则带入变量值;

3)执行SQL,较复杂,详见“Oracle SQL语句执行流程与顺序原理解析”,本处以较简单的SELECT语句为例;

第一步:判断对象(如表或序列)数据是否存在于DB BUFFER(LRU管理),存在则直接取数据,不存在则需要去数据文件中将数据读入DB BUFFER;

第二步:对FROM子句所有表生成笛卡尔积并生成一个虚拟表T1;

第三步:判断表连接的ON条件,保留匹配行生成虚拟表T2;

第三步:判断WHERE条件,保留TRUE值生成虚拟表T3;

第四步:GROUP BY对数据进行分析排序,并生成虚拟表T4;

第五步:CUTE、ROLLUP对数据进行再排序计算,并生成虚拟表T5;

第六步:判断HAVING条件,保留TRUE值生成虚拟表T6;

第七步:提取并计算SELECT字段数据,并生成虚拟表T7;

第八步:执行ORDER BY对数据进行排序,并生成虚拟表T8,并最终数据返回给用户;

我们重点要理解和记住的是执行SQL时的流程顺序,以便于我们有步骤的优化SQL。当然,以上步骤流程都是个人理解并简化过的,实际执行的步骤要多于我们的想象。

【解析优化】

1)使用绑定变量,以减少解析次数;

2)合并SQL,以减少SQL数量(使用DECODE、WITH子句、合并子查询);

3)规范SQL,以减少多用户间相同语句多次解析(如大小写、空格等进行规范);

4)SQL语句中多表连接时,使用表别名以减少解析时间;

【FROM子句优化】

1)小表驱动大表,记录少的表放在最后;

2)3个及以上表连接,交叉表作为基表后置;

3)尽量避免通过DUAL表赋值,过多的使用会导致该表等待时间过长;

【WHERE子句优化】

1)将最大程度过滤记录的条件放到最后,一般情况下表连接条件可过滤最大记录(Oracle解析WHERE子句时顺序为由后向前);

2)尽量使用WHERE条件过滤,而不是HAVING(至于为什么我也不知道,而且没查到相关资料,看执行计划HAVING条件确实比WHERE要吃掉更多的CPU和内存);

3)能使用表连接的尽量使用表连接,其次使用EXISTS或IN(大外表小内表用IN,小外表大内表使用EXISTS);

4)使用NOT EXISTS代替NOT IN;

5)用>=代替>,因为>也会先定位值然后再比较,较>=多了一步无效定位;

6)尽量使用UNION ALL代替UNION或OR(OR效率低于UNION,至于原因我也不知道,只是看其他人有这些的例子),如:

SELECT ename FROM emp WHERE emp_no = 1 or empno = 2;

可改写成

SELECT ename FROM emp WHERE emp_no = 1

UNION ALL

SELECT ename FROM emp WHERE emp_no = 2;

【WHERE子句索引优化】

要避免索引失效,索引失效的情形包括:

1)在索引字段上使用函数;

2)在索引字段上做计算;

3)索引字段有隐式转换(Oracle会自动对索引字段加函数转换);

4)使用条件IS NULL或IS NOT NULL(空值不入索引);

5)LIKE条件索引字段第一个字符使用%通配符;

6)!=或<>或NOT等均不使用索引而采用全表扫描(如使用> or <来代替<>);

7)索引建在多字段上时,只有第一个字段在WHERE条件中会使用索引,所以建索引时注意字段顺序;

8)若过滤后数据超过表的10%以上,则使用索引的效率可能并不如全表扫描(I/O超高),此时可屏蔽索引,屏蔽索引方法:数值值就在表达式后+0,字符型就加上空串||’’;

【SELECT优化】

在SELECT子句中列出所有字段,而不是使用*,因为在Oracle解析时会将*转换成所有的列名,而这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间。

【排序优化】

1)用EXISTS代替DISTINCT

SELECT DISTINCT d.dname,e.ename FROM dept d,emp e WHERE d.deptno = e.deptno;

SELECT d.dname,e.ename FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);

2)能用UNION ALL不用UNION

UNION会对结果集进行排序去重,若非必要,应避免该过程耗费的CPU。

3)尽量不使用DISTINCT、ORDER BY、GROUP BY、UNION、MINUS等排序语句,尤其是VARCHAR2(2000)之类较大的字段;

» 声明:本站文章源于个人经验总结或书籍、互联网转载,内容仅用于个人学习,请勿转载,否则后果自负!

给我留言

留言无头像?