语源科技BlogJava-日志记录

[转载]Oracle专家调优秘密

Ken.Lee — Sun, 27 Jul 2008 15:43:00 GMT

在过去的十年中， Oracle 已经成为世界上最专业的数据库之一。对于 IT 专家来说，就是要确保利用 Oracle 的强大特性来提高他们公司的生产力。最有效的方法之一是通过 Oracle 调优。它有大量的调整参数和技术来改进你的 Oracle 数据库的性能。
Oracle 调优是一个复杂的主题。关于调优可以写整整一本书，不过，为了改善 Oracle 数据库的性能，有一些基本的概念是每个 Oracle DBA 都应该遵从的。
　　在这篇简介中，我们将简要地介绍以下的 Oracle 主题：
　　-- 外部调整：我们应该记住 Oracle 并不是单独运行的。因此我们将查看一下通过调整 Oracle 服务器以得到高的性能。
　　--Row re-sequencing 以减少磁盘 I/O ：我们应该懂得 Oracle 调优最重要的目标是减少 I/O 。
　　--Oracle SQL 调整。 Oracle SQL 调整是 Oracle 调整中最重要的领域之一，只要通过一些简单的 SQL 调优规则就可以大幅度地提升 SQL 语句的性能，这是一点都不奇怪的。
　　-- 调整 Oracle 排序：排序对于 Oracle 性能也是有很大影响的。
　　-- 调整 Oracle 的竞争：表和索引的参数设置对于 UPDATE 和 INSERT 的性能有很大的影响。

　　我们首先从调整 Oracle 外部的环境开始。如果内存和 CPU 的资源不足的话，任何的 Oracle 调整都是没有帮助的。

　　外部的性能问题
　　
　　Oracle 并不是单独运行的。 Oracle 数据库的性能和外部的环境有很大的关系。这些外部的条件包括有：
　　． CPU--CPU 资源的不足令查询变慢。当查询超过了 Oracle 服务器的 CPU 性能时，你的数据库性能就受到 CPU 的限制。
　　．内存 -- 可用于 Oralce 的内存数量也会影响 SQL 的性能，特别是在数据缓冲和内存排序方面。
　　．网络 -- 大量的 Net8 通信令 SQL 的性能变慢。
　　许多新手都错误的认为应该首先调整 Oracle 数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的 Oracle 调整都是没有帮助的。
　　在检查 Oracle 的外部环境时，有两个方面是需要注意的：
　　1 、当运行队列的数目超过服务器的 CPU 数量时，服务器的性能就会受到 CPU 的限制。补救的方法是为服务器增加额外的 CPU 或者关闭需要很多处理资源的组件，例如 Oracle Parallel Query 。
　　2 、内存分页。当内存分页时，内存容量已经不足，而内存页是与磁盘上的交换区进行交互的。补救的方法是增加更多的内存，减少 Oracle SGA 的大小，或者关闭 Oracle 的多线程服务器。
　　可以使用各种标准的服务器工具来得到服务器的统计数据，例如 vmstat,glance,top 和 sar 。 DBA 的目标是确保数据库服务器拥有足够的 CPU 和内存资源来处理 Oracle 的请求。
　　以下让我们来看一下 Oracle 的 row-resequencing 是如何能够极大地减少磁盘 I/O 的。

　　Row-resequencing （行的重新排序）
　　
　　就象我们上面提到的，有经验的 Oracle DBA 都知道 I/O 是响应时间的最大组成部分。其中磁盘 I/O 特别厉害，因为当 Oracle 由磁盘上的一个数据文件得到一个数据块时，读的进程就必须等待物理 I/O 操作完成。磁盘操作要比数据缓冲慢 10,000 倍。因此，如果可以令 I/O 最小化，或者减少由于磁盘上的文件竞争而带来的瓶颈，就可以大大地改善 Oracle 数据库的性能。
　　如果系统响应很慢，通过减少磁盘 I/O 就可以有一个很快的改善。如果在一个事务中通过按一定的范围搜索 primary-key 索引来访问表，那么重新以 CTAS 的方法组织表将是你减少 I/O 的首要策略。通过在物理上将行排序为和 primary-key 索引一样的顺序，就可以加快获得数据的速度。
　　就象磁盘的负载平衡一样，行的重新排序也是很简单的，而且也很快。通过与其它的 DBA 管理技巧一起使用，就可以在高 I/O 的系统中大大地减少响应的时间。
　　在高容量的在线事务处理环境中（ online transaction processing ， OLTP ），数据是由一个 primary 索引得到的，重新排序表格的行就可以令连续块的顺序和它们的 primary 索引一样，这样就可以在索引驱动的表格查询中，减少物理 I/O 并且改善响应时间。这个技巧仅在应用选择多行的时候有用，或者在使用索引范围搜索和应用发出多个查询来得到连续的 key 时有效。对于随机的唯一 primary-key （主键）的访问将不会由行重新排序中得到好处。
　　让我们看一下它是如何工作的。考虑以下的一个 SQL 的查询，它使用一个索引来得到 100 行：
selectsalaryfromemployeewherelast_name like 'B%';
这个查询将会使用 last_name_index ，搜索其中的每一行来得到目标行。这个查询将会至少使用 100 次物理磁盘的读取，因为 employee 的行存放在不同的数据块中。
　　不过，如果表中的行已经重新排序为和 last_name_index 的一样，同样的查询又会怎样处理呢？我们可以看到这个查询只需要三次的磁盘 I/O 就读完全部 100 个员工的资料（一次用作索引的读取，两次用作数据块的读取），减少了 97 次的块读取。
　　重新排序带来的性能改善的程度在于在你开始的时候行的乱序性如何，以及你需要由序列中访问多少行。至于一个表中的行与索引的排序键的匹配程度，可以查看数据字典中的 dba_indexes 和 dba_tables 视图得到。
　　在 dba_indexes 的视图中，查看 clustering_factor 列。如果 clustering_factor 的值和表中的块数目大致一样，那么你的表和索引的顺序是一样的。不过，如果 clustering_factor 的值接近表中的行数目，那就表明表格中的行和索引的顺序是不一样的。
　　行重新排序的作用是不可以小看的。在需要进行大范围的索引搜索的大表中，行重新排序可以令查询的性能提高三倍。
　　一旦你已经决定重新排序表中的行，你可以使用以下的工具之一来重新组织表格。
　　. 使用 Oracle 的 Create Table As Select (CTAS) 语法来拷贝表格
　　. Oracle9i 自带的表格重新组织工具
　　
　　以下，我们来看以下 SQL 语句的调优。

　　SQL 调优
　　Oracle 的 SQL 调优是一个复杂的主题，甚至是需要整本书来介绍 Oracle SQL 调优的细微差别。不过有一些基本的规则是每个 Oracle DBA 都需要跟从的，这些规则可以改善他们系统的性能。 SQL 调优的目标是简单的：
　　. 消除不必要的大表全表搜索：不必要的全表搜索导致大量不必要的 I/O ，从而拖慢整个数据库的性能。调优专家首先会根据查询返回的行数目来评价 SQL 。在一个有序的表中，如果查询返回少于 40% 的行，或者在一个无序的表中，返回少于 7% 的行，那么这个查询都可以调整为使用一个索引来代替全表搜索。对于不必要的全表搜索来说，最常见的调优方法是增加索引。可以在表中加入标准的 B 树索引，也可以加入 bitmap 和基于函数的索引。要决定是否消除一个全表搜索，你可以仔细检查索引搜索的 I/O 开销和全表搜索的开销，它们的开销和数据块的读取和可能的并行执行有关，并将两者作对比。在一些情况下，一些不必要的全表搜索的消除可以通过强制使用一个 index 来达到，只需要在 SQL 语句中加入一个索引的提示就可以了。
　　. 在全表搜索是一个最快的访问方法时，将小表的全表搜索放到缓存中，调优专家应该确保有一个专门的数据缓冲用作行缓冲。在 Oracle7 中，你可以使用 alter table xxx cache 语句，在 Oracle8 或以上，小表可以被强制为放到 KEEP 池中缓冲。
　　. 确保最优的索引使用：对于改善查询的速度，这是特别重要的。有时 Oracle 可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保 Oracle 使用正确的索引。它还包括 bitmap 和基于函数的索引的使用。
　　. 确保最优的 JOIN 操作：有些查询使用 NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则是 sort-merge join 更快。
　　这些规则看来简单，不过它们占 SQL 调优任务的 90% ，并且它们也无需完全懂得 Oracle SQL 的内部运作。以下我们来简单概览以下 Oracle SQL 的优化。
　　我们首先简要查看 Oracle 的排序，并且看一看排序操作是如何影响性能的。

　　调整 Oracle 的排序操作
　　排序是 SQL 语法中一个小的方面，但很重要，在 Oracle 的调整中，它常常被忽略。当使用 create index 、 ORDER BY 或者 GROUP BY 的语句时， Oracle 数据库将会自动执行排序的操作。通常，在以下的情况下 Oracle 会进行排序的操作：
　　使用 Order by 的 SQL 语句
　　使用 Group by 的 SQL 语句
　　在创建索引的时候
　　进行 table join 时，由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT
　　当与 Oracle 建立起一个 session 时，在内存中就会为该 session 分配一个私有的排序区域。如果该连接是一个专用的连接 (dedicated connection) ，那么就会根据 init.ora 中 sort_area_size 参数的大小在内存中分配一个 Program Global Area (PGA) 。如果连接是通过多线程服务器建立的，那么排序的空间就在 large_pool 中分配。不幸的是，对于所有的 session ，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（ disk sorts ）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，当排序的空间需求超出了 sort_area_size 的大小时，这时将会在 TEMP 表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢 14,000 倍。
　　上面我们已经提到，私有排序区域的大小是有 init.ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init.ora 中的 sort_area_retained_size 参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle 实例中的临时表空间中进行。
　　磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。 Oracle 还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会令任务变慢，并且会影响 Oracle 实例的当前任务的执行。还有，过多的磁盘排序将会令 free buffer waits 的值变高，从而令其它任务的数据块由缓冲中移走。
　　接着，让我们看一下 Oracle 的竞争，并且看一下表的存储参数的设置是如何影响 SQL UPDATE 和 INSERT 语句的性能的。

调整 Oracle 的竞争
　　Oracle 的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle 负责处理表和索引的空间管理，这样就可以让我们无需懂得 Oracle 的表和索引的内部运作。不过，对于有经验的 Oracle 调优专家来说，他需要懂得 Oracle 是如何管理表的 extent 和空闲的数据块。对于调整拥有高的 insert 或者 update 的系统来说，这是非常重要的。
　　要精通对象的调整，你需要懂得 freelists 和 freelist 组的行为，它们和 pctfree 及 pctused 参数的值有关。这些知识对于企业资源计划（ ERP ）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是 DML 语句执行慢的原因。
　　对于初学者来说，最常见的错误是认为默认的 Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的 pctfree 和 pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到 freelists 中。当这些设置不正确时，那些得到的 freelists 也是 "read" 块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。
Freelists 对于有效地重新使用 Oracle 表空间中的空间是很重要的，它和 pctfree 及 pctused 这两个存储参数的设置直接相关。通过将 pctused 设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整 Oracle 的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些 freelists 是如何影响 Oracle 的性能的。
　　当有一个请求需要插入一行到表格中时， Oracle 就会到 freelist 中寻找一个有足够的空间来容纳一行的块。你也许知道， freelist 串是放在表格或者索引的第一个块中，这个块也被称为段头（ segment header ）。 pctfree 和 pctused 参数的唯一目的就是为了控制块如何在 freelists 中进出。虽然 freelist link 和 unlink 是简单的 Oracle 功能，不过设置 freelist link (pctused) 和 unlink (pctfree) 对 Oracle 的性能确实有影响。
　　由 DBA 的基本知识知道， pctfree 参数是控制 freelist un-links 的（即将块由 freelists 中移除）。设置 pctfree=10 意味着每个块都保留 10% 的空间用作行扩展。 pctused 参数是控制 freelist re-links 的。设置 pctused=40 意味着只有在块的使用低于 40% 时才会回到表格的 freelists 中。
　　许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到 freelist 中，它将会一直保留在 freelist 中即使空间的使用超过了 60% ，只有在到达 pctfree 时才会将数据块由 freelist 中移走。

　　表格和索引存储参数设置的要求总结
　　以下的一些规则是用来设置 freelists, freelist groups, pctfree 和 pctused 存储参数的。你也知道， pctused 和 pctfree 的值是可以很容易地通过 alter table 命令修改的，一个好的 DBA 应该知道如何设置这些参数的最佳值。
　　有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：
. 对于需要有效地重新使用空间，可以设置一个高的 pctused 值，不过副作用是需要额外的 I/O 。一个高的 pctused 值意味着相对满的块都会放到 freelist 中。因此，这些块在再次满之前只可以接受几行记录，从而导致更多的 I/O 。
. 追求高性能的话，可以将 pctused 设置为一个低的值，这意味着 Oracle 不会将数据块放到 freelists 中直到它几乎是空的。那么块将可以在满之前接收更多的行，因此可以减少插入操作的 I/O 。要记住 Oracle 扩展新块的性能要比重新使用现有的块高。对于 Oracle 来说，扩展一个表比管理 freelists 消耗更少的资源。
　　让我们来回顾一下设置对象存储参数的一些常见规则：
　　．经常将 pctused 设置为可以接收一条新行。对于不能接受一行的 free blocks 对于我们来说是没有用的。如果这样做，将会令 Oracle 的性能变慢，因为 Oracle 将在扩展表来得到一个空的块之前，企图读取 5 个 "read" 的 free block 。
　　．表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下， RAW 和 LONG RAW 列都很巨大，以至超过了 Oracle 的最大块的大小，这时 chained rows 是不可以避免的。
　　．如果一个表有同时插入的 SQL 语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个 freelist 中，而没有其它包含有任何空闲块的 freelists 出现。
　　． freelist 参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有 20 个用户执行插入的操作，那么该表的参数应该设置为 freelists=20 。
　　应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的。对于这类 Oracle ， freelist groups 应该设置为访问该表格的 Oracle Parallel Server 实例的数目。

Ken.Lee 2008-07-27 23:43 发表评论

SQL语句性能调整原则

Ken.Lee — Wed, 23 Jul 2008 02:24:00 GMT

一、问题的提出
在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编
写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着
数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。
系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质
SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就
可，而是要写出高质量的SQL语句，提高系统的可用性。
在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性
能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引
而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应
清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。
二、SQL语句编写注意问题
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中
，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使
用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。
1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这
样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某
列存在空值，即使对该列建索引也不会提高性能。
任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
2. 联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一
起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存
放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工
。
下面是一个采用联接查询的SQL语句，
select * from employss
where
first_name||''||last_name ='Beill Cliton';
上面这条语句完全可以查询出是否有Bill Cliton这个员工，但是这里需要注意，系统优
化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基于last_name创建的索引。
Select * from employee
where
first_name ='Beill' and last_name ='Cliton';
遇到下面这种情况又如何处理呢？如果一个变量（name）中存放着Bill Cliton这个员工
的姓名，对于这种情况我们又如何避免全程遍历，使用索引呢？可以使用一个函数，将
变量name中的姓和名分开就可以了，但是有一点需要注意，这个函数是不能作用在索引
列上。下面是SQL查询脚本：
select * from employee
where
first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)
and
last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)
3. 带通配符（%）的like语句
同样以上面的例子来看这种情况。目前的需求是这样的，要求在职工表中查询名字中包
含cliton的人。可以采用如下的查询SQL语句：
select * from employee where last_name like '%cliton%';
这里由于通配符（%）在搜寻词首出现，所以Oracle系统不使用last_name的索引。在很
多情况下可能无法避免这种情况，但是一定要心中有底，通配符如此使用会降低查询速
度。然而当通配符出现在字符串其他位置时，优化器就能利用索引。在下面的查询中索
引得到了使用：
select * from employee where last_name like 'c%';
4. Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没
有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语
句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的
办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时
应绝对避免在order by子句中使用表达式。
5. NOT
我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等
等，也可以使用and（与）、or（或）以及not（非）。NOT可用来对任何逻辑运算符号取
反。下面是一个NOT子句的例子：
... where not (status ='VALID')
如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运
算符包含在另外一个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在
查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：
... where status <>'INVALID';
再看下面这个例子：
select * from employee where salary<>3000;
对这个查询，可以改写为不使用NOT：
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种
查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
6. IN和EXISTS
有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在w
here子句中可以使用两种格式的子查询。
第一种格式是使用IN操作符：
... where column in(select * from ... where ...);
第二种格式是使用EXIST操作符：
... where exists (select 'X' from ...where ...);
我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远
比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXIS
TS的子查询。
第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什
么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（
这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查
询，构造起来要比IN子查询困难一些。
通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配
项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结
果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，
待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN
通常查询速度快的原因。
同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降
低速度），NOT EXISTS要比NOT IN查询效率更高。

Ken.Lee 2008-07-23 10:24 发表评论