文档介绍：Oracle性能优化
一、设计上的优化 2
二、Oracle的优化器介绍 2
1、优化模式 3
2、访问路径 4
3、表连接方式 6
三、执行计划的查看 7
四、改变执行计划 9
五、调整查询 13
一、设计上的优化
Oracle性能问题的根源或瓶颈是在IO上,由于技术限制,现在硬盘访问与内存中访问数据速度相差很大,反应到数据库中大概差数十倍,上百倍,所以如何避免过多的磁盘访问,尽量让内存访问多是优化的宗旨。磁盘访问最严重的问题是磁盘争用,为了避免磁盘争用,在设计阶段就要考虑:
将数据库的控制文件,重做日志(REDOLOG)文件分开存放,放在速度最快的磁盘上
将表空间的数据文件及索引文件分别放在不同的物理磁盘上
将最常访问的表要放在不同的物理磁盘上
大表建分区,分区如果可能要放在不同的物理磁盘上。按经验,一个表如果数据上千万级,或数据量超过1G(如果OLTP系统,300M),就需要建分区。
其实很多的性能问题在设计阶段就埋下了,其他的优化都是辅助的手段。一个IO均衡的数据库系统,可以保证最糟糕的连接也不会出现IO争用问题。
二、Oracle的优化器介绍
SQL处理过程
1、优化模式
Oracle的优化模式有RBO,CBO两种
RBO:基于规则的优化器,基于ORACLE的一系列内定规则,跟数据量的多少没有关系
CBO:基于代价的优化器。代价是指oracle执行SQL需要的资源,包括IO访问,CPU占用,内存占用,ORACLE会根据数据量,数据的分布选择代价最少的执行计划来执行。
RBO这种优化模式ORACLE将在后续版本(10g)中不支持,ORACLE强烈建议选用CBO模式。
基于规则的优化器以oracle为中心,而基于代价的优化器以数据为中心即以数据的分布及数据量为中心。,下面列出几种可能使用的优化器模式:
CHOOSE ORACLE9i中默认的优化器,根据表分析的数据选择使用基于规则或基于代价的优化器。
如果数据字典里有至少一个或多个访问表的分析数据,Oracle会选择基于代价的优化器,以最大的吞吐量为目标(即使返回所有记录的速度更快)
如果有的访问表的部分分析数据,优化器会猜测其余的分析数据,当然有可能会选择到一个不理想的执行计划。后面会讲到如何进行表分析。
如果没有一点访问表的分析数据,就使用基于规则的优化器
RULE 始终使用基于规则的优化。该种模式在oracle10g以后将不再支持,或被废弃
这种优化器不推荐使用
FIRST_ROWS 以更快的速度返回第一行记录(通常会强制使用索引)
FIRST_ROWS_n 以更快的速度返回前n行(n可以为1|,10,100,1000)在需要定期把部分结果返回给用户时,该模式特别有用,比如WEB查询的分页显示,这种模式以提高响应时间为目标
ALL_ROWS 使整个语句执行得更快,以最大的吞吐量为目标。
,为了灵活使用,我们可以在会话级或语句级修改默认优化器模式。
会话级:ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
语句级修改主要是通过使用提示,/*+all_rows*/等等,如何使用提示将在后面详细讲到
2、访问路径:即从数据库提取数据的方式
Full Table Scans: 全表扫描,即Oracle将一个表的数据全部扫描提取满足条件的数据,全表扫描时,表里的每一条记录都会被扫描,比较是否满足where条件。这需要大量的IO操作。通过加大
DB_FILE_MULTIBLOCK_READ_COUNT的值,可以提高全表扫描的速度。在大数据量提取的时候,全表扫描比索引扫描更有效。在ORACLE8i以后,如果提取的数据超过全部数据的20%,则全表扫描将会更有效。索引就好像是一本字典的目录,提取数据就好像是从一本字典里查一个字一样,如果我们要查大部分的字典,我们还不如直接在字典里面翻,通过目录反倒会更慢些。
在下列情况下,优化器会使用全表扫描
缺少索引
提取大量的数据
小表:通过一次读取就可以把表全部读到内存中
高并行度查询
使用FULL提示
Rowid Scans:rowid标识了数据在数据库中的物理存放位置(即数据存放在哪个数据文件哪个块中)通过rowid提取数据是最快的一种方式。
Index Scans:这种方式是最常用到的扫描方式。Oracle会先根据where条件里的索引列先找到索引,再根据索引查找到的rowid去到表里提取满足条件的记录。如果要提取的数据已经包含在索引中,则第二步就会省略。
索引扫描