Oracle 调优是一个复杂的主题。关于调优可以写整整一本书Q不q，Z改善 Oracle 数据库的性能Q有一些基本的概念是每?Oracle DBA 都应该遵从的?
在这简介中Q我们将要地介绍以下?Oracle 主题Q?
-- 外部调整Q我们应该记?Oracle q不是单独运行的。因此我们将查看一下通过调整 Oracle 服务器以得到高的性能?
--Row re-sequencing 以减磁?I/O Q我们应该懂?Oracle 调优最重要的目标是减少 I/O ?
--Oracle SQL 调整?Oracle SQL 调整?Oracle 调整中最重要的领域之一Q只要通过一些简单的 SQL 调优规则可以大q度地提?SQL 语句的性能Q这是一炚w不奇怪的?
-- 调整 Oracle 排序Q排序对?Oracle 性能也是有很大媄响的?
-- 调整 Oracle 的竞争：表和索引的参数设|对?UPDATE ?INSERT 的性能有很大的影响?
我们首先从调?Oracle 外部的环境开始。如果内存和 CPU 的资源不的话，M?Oracle 调整都是没有帮助的?
外部的性能问题
Oracle q不是单独运行的?Oracle 数据库的性能和外部的环境有很大的关系。这些外部的条g包括有：
Q?CPU--CPU 资源的不o查询变慢。当查询过?Oracle 服务器的 CPU 性能Ӟ你的数据库性能受?CPU 的限制?
Q内?-- 可用?Oralce 的内存数量也会媄?SQL 的性能Q特别是在数据缓冲和内存排序斚w?
Q网l?-- 大量?Net8 通信?SQL 的性能变慢?
许多新手都错误的认ؓ应该首先调整 Oracle 数据库，而不是先认外部资源是否_。实际上Q如果外部环境出现瓶颈，再多?Oracle 调整都是没有帮助的?
在检?Oracle 的外部环境时Q有两个斚w是需要注意的Q?
1 、当q行队列的数目超q服务器?CPU 数量Ӟ服务器的性能׃受到 CPU 的限制。补救的Ҏ(gu)是ؓ服务器增加额外的 CPU 或者关闭需要很多处理资源的lgQ例?Oracle Parallel Query ?
2 、内存分c当内存分页Ӟ内存定w已经不Q而内存页是与盘上的交换行交互的。补救的Ҏ(gu)是增加更多的内存Q减?Oracle SGA 的大，或者关?Oracle 的多U程服务器?
可以使用各种标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top ?sar ?DBA 的目标是保数据库服务器拥有_?CPU 和内存资源来处理 Oracle 的请求?
以下让我们来看一?Oracle ?row-resequencing 是如何能够极大地减少盘 I/O 的?
Row-resequencing Q行的重新排序）
p我们上面提到的，有经验的 Oracle DBA 都知?I/O 是响应时间的最大组成部分。其中磁?I/O 特别厉害Q因为当 Oracle q盘上的一个数据文件得C个数据块Ӟȝq程必ȝ待物?I/O 操作完成。磁盘操作要比数据缓冲慢 10,000 倍。因此，如果可以?I/O 最化Q或者减由于磁盘上的文件竞争而带来的瓉Q就可以大大地改?Oracle 数据库的性能?
如果pȝ响应很慢Q通过减少盘 I/O 可以有一个很快的改善。如果在一个事务中通过按一定的范围搜烦 primary-key 索引来访问表Q那么重C CTAS 的方法组l表是你减?I/O 的首要策略。通过在物理上行排序为和 primary-key 索引一L(fng)序Q就可以加快获得数据的速度?
p盘的负载^衡一P行的重新排序也是很简单的Q而且也很快。通过与其它的 DBA 理技巧一起用，可以在?I/O 的系l中大大地减响应的旉?
在高定w的在U事务处理环境中Q?online transaction processing Q?OLTP Q，数据是由一?primary 索引得到的，重新排序表格的行可以oq箋块的序和它们的 primary 索引一Pq样可以在索引驱动的表格查询中Q减物?I/O q且改善响应旉。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜烦和应用发出多个查询来得到q箋?key 时有效。对于随机的唯一 primary-key Q主键）的访问将不会p重新排序中得到好处?
让我们看一下它是如何工作的。考虑以下的一?SQL 的查询，它用一个烦引来得到 100 行：
selectsalaryfromemployeewherelast_name like 'B%';
q个查询会使用 last_name_index Q搜索其中的每一行来得到目标行。这个查询将会至?100 ơ物理磁盘的dQ因?employee 的行存放在不同的数据块中?
不过Q如果表中的行已l重新排序ؓ?last_name_index 的一P同样的查询又会怎样处理呢？我们可以看到q个查询只需要三ơ的盘 I/O p完全?100 个员工的资料Q一ơ用作烦引的dQ两ơ用作数据块的读取）Q减了 97 ơ的块读取?
重新排序带来的性能改善的程度在于在你开始的时候行的ؕ序性如何，以及你需要由序列中访问多行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据字典中的 dba_indexes ?dba_tables 视图得到?
?dba_indexes 的视图中Q查?clustering_factor 列。如?clustering_factor 的值和表中的块数目大致一P那么你的表和索引的顺序是一L(fng)。不q，如果 clustering_factor 的值接q表中的行数目，那就表明表格中的行和索引的顺序是不一L(fng)?
行重新排序的作用是不可以看的。在需要进行大范围的烦引搜索的大表中，行重新排序可以o查询的性能提高三倍?
一旦你已经军_重新排序表中的行Q你可以使用以下的工具之一来重新组l表根{?
. 使用 Oracle ?Create Table As Select (CTAS) 语法来拷贝表?
. Oracle9i 自带的表格重新组l工?
以下Q我们来看以?SQL 语句的调优?
SQL 调优
Oracle ?SQL 调优是一个复杂的主题Q甚x需要整本书来介l?Oracle SQL 调优的细微差别。不q有一些基本的规则是每?Oracle DBA 都需要跟从的Q这些规则可以改善他们系l的性能?SQL 调优的目标是单的Q?
. 消除不必要的大表全表搜烦Q不必要的全表搜索导致大量不必要?I/O Q从而拖慢整个数据库的性能。调优专安先会Ҏ(gu)查询q回的行数目来评?SQL 。在一个有序的表中Q如果查询返回少?40% 的行Q或者在一个无序的表中Q返回少?7% 的行Q那么这个查询都可以调整Z用一个烦引来代替全表搜烦。对于不必要的全表搜索来_最常见的调优方法是增加索引。可以在表中加入标准?B ?wi)烦引，也可以加?bitmap 和基于函数的索引。要军_是否消除一个全表搜索，你可以仔l检查烦引搜索的 I/O 开销和全表搜索的开销Q它们的开销和数据块的读取和可能的ƈ行执行有养Iq将两者作Ҏ(gu)。在一些情况下Q一些不必要的全表搜索的消除可以通过强制使用一?index 来达刎ͼ只需要在 SQL 语句中加入一个烦引的提示可以了?
. 在全表搜索是一个最快的讉KҎ(gu)Ӟ小表的全表搜烦攑ֈ~存中，调优专家应该保有一个专门的数据~冲用作行缓册Ӏ在 Oracle7 中，你可以?alter table xxx cache 语句Q在 Oracle8 或以上，表可以被强制ؓ攑ֈ KEEP 池中~冲?
. 保最优的索引使用 Q对于改善查询的速度Q这是特别重要的。有?Oracle 可以选择多个索引来进行查询，调优专家必须查每个烦引ƈ且确?Oracle 使用正确的烦引。它q包?bitmap 和基于函数的索引的用?
. 保最优的 JOIN 操作Q有些查询?NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则?sort-merge join 更快?
q些规则看来单，不过它们?SQL 调优d?90% Qƈ且它们也无需完全懂得 Oracle SQL 的内部运作。以下我们来单概览以?Oracle SQL 的优化?
我们首先要查?Oracle 的排序，q且看一看排序操作是如何影响性能的?
调整 Oracle 的排序操?
排序?SQL 语法中一个小的方面，但很重要Q在 Oracle 的调整中Q它常常被忽略。当使用 create index ?ORDER BY 或?GROUP BY 的语句时Q?Oracle 数据库将会自动执行排序的操作。通常Q在以下的情况下 Oracle 会进行排序的操作Q?
使用 Order by ?SQL 语句
使用 Group by ?SQL 语句
在创建烦引的时?
q行 table join Ӟ׃现有索引的不导?SQL 优化器调?MERGE SORT
当与 Oracle 建立起一?session Ӟ在内存中׃ session 分配一个私有的排序区域。如果该q接是一个专用的q接 (dedicated connection) Q那么就会根?init.ora ?sort_area_size 参数的大在内存中分配一?Program Global Area (PGA) 。如果连接是通过多线E服务器建立的，那么排序的空间就?large_pool 中分配。不q的是，对于所有的 session Q用做排序的内存量都必须是一L(fng)Q我们不能ؓ需要更大排序的操作分配额外的排序区域。因此，设计者必MZ个^衡，在分配够的排序区域以避免发生大的排序Q务时出现盘排序Q?disk sorts Q的同时Q对于那些ƈ不需要进行很大排序的dQ就会出C些浪贏V当Ӟ当排序的I间需求超Z sort_area_size 的大时Q这时将会在 TEMP 表空间中分页q行盘排序。磁盘排序要比内存排序大概慢 14,000 倍?
上面我们已经提到Q私有排序区域的大小是有 init.ora 中的 sort_area_size 参数军_的。每个排序所占用的大由 init.ora 中的 sort_area_retained_size 参数军_。当排序不能在分配的I间中完成时Q就会用磁盘排序的方式Q即?Oracle 实例中的临时表空间中q行?
盘排序的开销是很大的Q有几个斚w的原因。首先，和内存排序相比较Q它们特别慢Q而且盘排序会消耗(f)时表I间中的资源?Oracle q必d配缓冲池块来保持临时表空间中的块。无Z么时候，内存排序都比盘排序好，盘排序会令Q务变慢，q且会媄?Oracle 实例的当前Q务的执行。还有，q多的磁盘排序将会o free buffer waits 的值变高，从而o其它d的数据块q冲中U走?
接着Q让我们看一?Oracle 的竞争，q且看一下表的存储参数的讄是如何媄?SQL UPDATE ?INSERT 语句的性能的?
调整 Oracle 的竞?
Oracle 的其中一个优Ҏ(gu)它可以管理每个表I间中的自由I间?Oracle 负责处理表和索引的空间管理，q样可以让我们无需懂得 Oracle 的表和烦引的内部q作。不q，对于有经验的 Oracle 调优专家来说Q他需要懂?Oracle 是如何管理表?extent 和空闲的数据块。对于调整拥有高?insert 或?update 的系l来_q是非常重要的?
要精通对象的调整Q你需要懂?freelists ?freelist l的行ؓQ它们和 pctfree ?pctused 参数的值有兟뀂这些知识对于企业资源计划（ ERP Q的应用是特别重要的Q因为在q些应用中，不正的表设|通常?DML 语句执行慢的原因?
对于初学者来_最常见的错误是认ؓ默认?Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设|表?pctfree ?pctused 参数Ӟ必考虑q_的行长和数据库的块大，q样I的块才会被有效地放?freelists 中。当q些讄不正时Q那些得到的 freelists 也是 "read" 块，因ؓ它们没有_的空间来存储一行，q样会D明显的处理gq?
Freelists 对于有效地重C?Oracle 表空间中的空间是很重要的Q它?pctfree ?pctused q两个存储参数的讄直接相关。通过?pctused 讄Z个高的|q时数据库就会尽快地重新使用块。不q，高性能和有效地重新使用表的块是对立的。在调整 Oracle 的表格和索引Ӟ需要认真考虑I竟需要高性能q是有效的空间重用，q且据此来设|表的参数。以下我们来看一下这?freelists 是如何媄?Oracle 的性能的?
当有一个请求需要插入一行到表格中时Q?Oracle ׃?freelist 中寻找一个有_的空间来容纳一行的块。你也许知道Q?freelist 串是攑֜表格或者烦引的W一个块中，q个块也被称为段_ segment header Q?pctfree ?pctused 参数的唯一目的是Z控制块如何在 freelists 中进出。虽?freelist link ?unlink 是简单的 Oracle 功能Q不q设|?freelist link (pctused) ?unlink (pctfree) ?Oracle 的性能实有媄响?
?DBA 的基本知识知道， pctfree 参数是控?freelist un-links 的（卛_块由 freelists 中移除）。设|?pctfree=10 意味着每个块都保留 10% 的空间用作行扩展?pctused 参数是控?freelist re-links 的。设|?pctused=40 意味着只有在块的用低?40% 时才会回到表格的 freelists 中?
许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实，一旦由于一个删除的操作而o块被重新加入?freelist 中，它将会一直保留在 freelist 中即使空间的使用过?60% Q只有在到达 pctfree 时才会将数据块由 freelist 中移走?
表格和烦引存储参数设|的要求ȝ
以下的一些规则是用来讄 freelists, freelist groups, pctfree ?pctused 存储参数的。你也知道， pctused ?pctfree 的值是可以很容易地通过 alter table 命o修改的，一个好?DBA 应该知道如何讄q些参数的最?jng)_{?
有效C用空间和高性能之间是有矛盾的，而表格的存储参数是控制q个斚w的矛盾：
. 对于需要有效地重新使用I间Q可以设|一个高?pctused |不过副作用是需要额外的 I/O 。一个高?pctused 值意味着相对满的块都会放?freelist 中。因此，q些块在再次满之前只可以接受几行记录Q从而导致更多的 I/O ?
. q求高性能的话Q可以将 pctused 讄Z个低的|q意味着 Oracle 不会数据块攑ֈ freelists 中直到它几乎是空的。那么块可以在满之前接收更多的行，因此可以减少插入操作?I/O 。要C Oracle 扩展新块的性能要比重新使用现有的块高。对?Oracle 来说Q扩展一个表比管?freelists 消耗更的资源?
让我们来回顾一下设|对象存储参数的一些常见规则：
Q经常将 pctused 讄为可以接收一条新行。对于不能接受一行的 free blocks 对于我们来说是没有用的。如果这样做Q将会o Oracle 的性能变慢Q因?Oracle 在扩展表来得到一个空的块之前Q企图读?5 ?"read" ?free block ?
Q表g chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下， RAW ?LONG RAW 列都很巨大，以至过?Oracle 的最大块的大，q时 chained rows 是不可以避免的?
Q如果一个表有同时插入的 SQL 语句Q那么它需要有同时删除的语句。运行单一个一个清除的工作会把全部的I闲块放C?freelist 中，而没有其它包含有MI闲块的 freelists 出现?
Q?freelist 参数应该讄格同时更新的最大倹{例如，如果在Q何时候，某个表最多有 20 个用h行插入的操作Q那么该表的参数应该讄?freelists=20 ?
应记住的?freelist groups 参数的值只是对?Oracle Parallel Server ?Real Application Clusters 才是有用的。对于这c?Oracle Q?freelist groups 应该讄问该表格?Oracle Parallel Server 实例的数目?/div>
]]>
一、问题的提出
在应用系l开发初期，׃开发数据库数据比较?yu)，对于查询SQL语句Q复杂视囄的编
写等体会不出SQL语句各种写法的性能优劣Q但是如果将应用pȝ提交实际应用后，随着
数据库中数据的增加，pȝ的响应速度成为目前系l需要解决的最主要的问题之一?
pȝ优化中一个很重要的方面就是SQL语句的优化。对于v量数据，劣质SQL语句和优?
SQL语句之间的速度差别可以辑ֈ上百倍，可见对于一个系l不是简单地能实现其功能?
可，而是要写出高质量的SQL语句Q提高系l的可用性?
在多数情况下QOracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高?
能。但是，如果在SQL语句的where子句中写的SQL代码不合理，׃造成优化器删ȝ?
而用全表扫描，一般就q种SQL语句是所谓的劣质SQL语句。在~写SQL语句时我们应
清楚优化器根据何U原则来删除索引Q这有助于写出高性能的SQL语句?
二、SQL语句~写注意问题
下面某些SQL语句的where子句~写中需要注意的问题作详l介l。在q些where子句?
Q即使某些列存在索引Q但是由于编写了劣质的SQLQ系l在q行该SQL语句时也不能?
用该索引Q而同样用全表扫描，q就造成了响应速度的极大降低?
1. IS NULL ?IS NOT NULL
不能用null作烦引，M包含null值的列都不会被包含在烦引中。即使烦引有多列q?
L(fng)情况下，只要q些列中有一列含有nullQ该列就会从索引中排除。也是说如果某
列存在空|即对该列徏索引也不会提高性能?
M在where子句中用is null或is not null的语句优化器是不允许使用索引的?
2. 联接?
对于有联接的列，即最后的联接gؓ一个静态|优化器是不会使用索引的。我们一
h看一个例子，假定有一个职工表QemployeeQ，对于一个职工的姓和名分成两列存
放（FIRST_NAME和LAST_NAMEQ，现在要查询一个叫比尔.克林（Bill ClitonQ的职工
?
下面是一个采用联接查询的SQL语句Q?
select * from employss
where
first_name||''||last_name ='Beill Cliton';
上面q条语句完全可以查询出是否有Bill Clitonq个员工Q但是这里需要注意，pȝ?
化器对基于last_name创徏的烦引没有用?
当采用下面这USQL语句的编写，Oraclepȝ可以采用基于last_name创徏的烦引?
Select * from employee
where
first_name ='Beill' and last_name ='Cliton';
遇到下面q种情况又如何处理呢Q如果一个变量（nameQ中存放着Bill Clitonq个员工
的姓名，对于q种情况我们又如何避免全E遍历，使用索引呢？可以使用一个函敎ͼ?
变量name中的姓和名分开可以了Q但是有一炚w要注意，q个函数是不能作用在索引
列上。下面是SQL查询脚本Q?
select * from employee
where
first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
and
last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)
3. 带通配W（%Q的like语句
同样以上面的例子来看q种情况。目前的需求是q样的，要求在职工表中查询名字中?
含cliton的h。可以采用如下的查询SQL语句Q?
select * from employee where last_name like '%cliton%';
q里׃通配W（%Q在搜寻词首出现Q所以Oraclepȝ不用last_name的烦引。在?
多情况下可能无法避免q种情况Q但是一定要心中有底Q通配W如此用会降低查询?
度。然而当通配W出现在字符串其他位|时Q优化器p利用索引。在下面的查询中?
引得C使用Q?
select * from employee where last_name like 'c%';
4. Order by语句
ORDER BY语句军_了Oracle如何返回的查询l果排序。Order by语句对要排序的列?
有什么特别的限制Q也可以函数加入列中（象联接或者附加等Q。Q何在Order by?
句的非烦引项或者有计算表达式都降低查询速度?
仔细查order by语句以找出非索引Ҏ(gu)者表辑ּQ它们会降低性能。解册个问题的
办法是重写order by语句以用烦引，也可以ؓ所使用的列建立另外一个烦引，同时
应绝寚w免在order by子句中用表辑ּ?
5. NOT
我们在查询时l常在where子句使用一些逻辑表达式，如大于、小于、等于以及不{于{?
{，也可以用andQ与Q、orQ或Q以及notQ非Q。NOT可用来对M逻辑q算W号?
反。下面是一个NOT子句的例子：
... where not (status ='VALID')
如果要用NOTQ则应在取反的短语前面加上括Pq在短语前面加上NOTq算W。NOTq?
符包含在另外一个逻辑q算W中Q这是不等于（<>Q运符。换句话_即不在
查询where子句中显式地加入NOT词，NOT仍在q算W中Q见下例Q?
... where status <>'INVALID';
再看下面q个例子Q?
select * from employee where salary<>3000;
对这个查询，可以改写Z使用NOTQ?
select * from employee where salary<3000 or salary>3000;
虽然q两U查询的l果一P但是W二U查询方案会比第一U查询方案更快些。第二种
查询允许Oracle对salary列用烦引，而第一U查询则不能使用索引?
6. IN和EXISTS
有时候会一列和一pd值相比较。最单的办法是在where子句中用子查询。在w
here子句中可以用两U格式的子查询?
W一U格式是使用IN操作W：
... where column in(select * from ... where ...);
W二U格式是使用EXIST操作W：
... where exists (select 'X' from ...where ...);
我相信绝大多Ch会用第一U格式，因ؓ它比较容易编写，而实际上W二U格式要q?
比第一U格式的效率高。在Oracle中可以几乎将所有的IN操作W子查询改写Z用EXIS
TS的子查询?
W二U格式中Q子查询?#8216;select 'X'开始。运用EXISTS子句不管子查询从表中抽取什
么数据它只查看where子句。这样优化器׃必遍历整个表而仅Ҏ(gu)索引可完成工作Q?
q里假定在where语句中用的列存在烦引）。相对于IN子句来说QEXISTS使用相连子查
询，构造v来要比IN子查询困难一些?
通过使用EXISTQOraclepȝ会首先检查主查询Q然后运行子查询直到它找到第一个匹?
，q就节省了时间。Oraclepȝ在执行IN子查询时Q首先执行子查询Qƈ获得的l?
果列表存攑֜在一个加了烦引的临时表中。在执行子查询之前，pȝ先将L询挂P
待子查询执行完毕Q存攑֜临时表中以后再执行主查询。这也就是用EXISTS比用IN
通常查询速度快的原因?
同时应尽可能使用NOT EXISTS来代替NOT INQ尽二者都使用了NOTQ不能用烦引而降
低速度Q，NOT EXISTS要比NOT IN查询效率更高?
]]>