Oracle数据库系统性能优化策略

一个数据库系统的生命周期可以分成设计 开发和成品三个阶段 在设计阶段进行数据库性能优化的成本最低 收益最大 在成品阶段进行数据库性能优化的成本最高 收益最小 数据库的优化可以通过对网络 硬件 操作系统 数据库参数和应用程序的优化来进行 最常见的优化手段就是对硬件的升级 据统计 对网络 硬件 操作系统 数据库参数进行优化所获得的性能提升 全部加起来只占数据库系统性能提升的 %左右 其余的 %系统性能提升来自对应用程序的优化 许多优化专家认为 对应用程序的优化可以得到 %的系统性能的提升

10年积累的网站设计制作、做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有苏仙免费网站建设让你可以放心的选择与我们合作。

一 数据库性能的优化

数据库设计是应用程序设计的基础 其性能直接影响应用程序的性能 数据库性能包括存储空间需求量的大小和查询响应时间的长短两个方面 为了优化数据库性能 需要对数据库中的表进行规范化 规范化的范式可分为第一范式 第二范式 第三范式 BCNF范式 第四范式和第五范式 一般来说 逻辑数据库设计会满足规范化的前 级标准 但由于满足第三范式的表结构容易维护且基本满足实际应用的要求 因此 实际应用中一般都按照第三范式的标准进行规范化 但是 规范化也有缺点 由于将一个表拆分成为多个表 在查询时需要多表连接 降低了查询速度

由于规范化有可能导致查询速度慢的缺点 考虑到一些应用需要较快的响应速度 在设计表时应同时考虑对某些表进行反规范化 反规范化可以采用以下几种方法

分割表

分割表包括水平分割和垂直分割

水平分割是按照行将一个表分割为多个表 这可以提高每个表的查询速度 但查询 更新时要选择不同的表 统计时要汇总多个表 因此应用程序会更复杂

垂直分割是对于一个列很多的表 若某些列的访问频率远远高于其它列 就可以将主键和这些列作为一个表 将主键和其它列作为另外一个表 通过减少列的宽度 增加了每个数据页的行数 一次I/O就可以扫描更多的行 从而提高了访问每一个表的速度 但是由于造成了多表连接 所以应该在同时查询或更新不同分割表中的列的情况比较少的情况下使用

保留冗余列

当两个或多个表在查询中经常需要连接时 可以在其中一个表上增加若干冗余的列 以避免表之间的连接过于频繁 由于对冗余列的更新操作必须对多个表同步进行 所以一般在冗余列的数据不经常变动的情况下使用

增加派生列

派生列是由表中的其它多个列计算所得 增加派生列可以减少统计运算 在数据汇总时可以大大缩短运算时间

二 应用程序性能的优化

应用程序的优化通常可分为两个方面 源代码和SQL语句 由于涉及到对程序逻辑的改变 源代码的优化在时间成本和风险上代价很高 而对数据库系统性能的提升收效有限 因此应用程序的优化应着重在SQL语句的优化 对于海量数据 劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍 可见对于一个系统不是简单地能实现其功能就行 而是要写出高质量的SQL语句 提高系统的可用性

下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍 在这些where子句中 即使某些列存在索引 但是由于编写了劣质的SQL 系统在运行该SQL语句时也不能使用该索引 而同样使用全表扫描 这就造成了响应速度的极大降低

IS NULL 与 IS NOT NULL

不能用null作索引 任何包含null值的列都将不会被包含在索引中 即使索引有多列的情况下 只要这些列中有一列含有null 该列就会从索引中排除 也就是说如果某列存在空值 即使对该列建索引也不会提高性能

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的

联接列

对于有联接的列 即使最后的联接值为一个静态值 优化器不会使用索引的 例如 假定有一个职工表（employee） 对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME） 现在要查询一个叫乔治？布什（Gee Bush）的职工 下面是一个采用联接查询的SQL语句

select * from employee where first_name|| ||last_name = Gee Bush

上面这条语句完全可以查询出是否有Gee Bush这个员工 但是这里需要注意 系统优化器对基于last_name创建的索引没有使用

当采用下面这种SQL语句的编写 Oracle系统就可以采用基于last_name创建的索引

Select * From employee where first_name = Gee and last_name = Bush

遇到下面这种情况又如何处理呢？如果一个变量（name）中存放著Gee Bush这个员工的姓名 对于这种情况我们又如何避免全程遍历使用索引呢？可以使用一个函数 将变量name中的姓和名分开就可以了 但是有一点需要注意 这个函数是不能作用在索引列上 下面是SQL查询脚本

select * from employee where first_name = SUBSTR（ name INSTR（ name ） ）

and last_name = SUBSTR（ name INSTR（ name ）+ ）

带通配符（%）的like语句

同样以上面的例子来看这种情况 目前的需求是这样的 要求在职工表中查询名字中包含Bush的人 可以采用如下的查询SQL语句

select * from employee where last_name like %Bush%

这里由于通配符（%）在搜寻词首出现 所以Oracle系统不使用last_name的索引 在很多情况下可能无法避免这种情况 但是一定要心中有底 通配符如此使用会降低查询速度 然而当通配符出现在字符串其他位置时 优化器就能利用索引 例如 在下面的查询中索引得到了使用

select * from employee where last_name like c%

Order by语句

Order by语句决定了Oracle如何将返回的查询结果排序 Order by语句对要排序的列没有什么特别的限制 也可以将函数加入列中（象联接或者附加等） 任何在Order by语句的非索引项或者有计算表达式都将降低查询速度

仔细检查order by语句以找出非索引项或者表达式 它们会降低性能 解决这个问题的办法就是重写order by语句以使用索引 也可以为所使用的列建立另外一个索引 同时应绝对避免在order by子句中使用表达式

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

对这个查询 可以改写为不使用NOT的语句

select * from employee where　salary or salary

虽然这两种查询的结果一样 但是第二种查询方案会比第一种查询方案更快些 第二种查询允许Oracle对salary列使用索引 而第一种查询则不能使用索引

IN和EXISTS

有时候会将一列和一系列值相比较 最简单的办法就是在where子句中使用子查询 在where子句中可以使用两种格式的子查询

第一种格式是使用IN操作符 …… where column in（select * from …… where ……）

第二种格式是使用EXIST操作符 …… where exists （select X from ……where ……）

绝大多数人会使用第一种格式 因为它比较容易编写 而实际上第二种格式要远比第一种格式的效率高 在Oracle中可以将几乎所有的IN操作符子查询改写为使用EXISTS的子查询

第二种格式中 子查询以 select X 开始 运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句 这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引） 相对于IN子句来说 EXISTS使用相连子查询 构造起来要比IN子查询困难一些

通过使用EXISTS Oracle系统会首先检查主查询 然后运行子查询直到找到第一个匹配项 这就节省了时间 Oracle系统在执行IN子查询时 首先执行子查询 并将获得的结果列表存放在一个加了索引的临时表中 在执行子查询之前 系统先将主查询挂起 待子查询执行完毕 存放在临时表中以后再执行主查询 这也就是使用EXISTS比使用IN通常查询速度快的原因

同时应尽可能使用NOT EXISTS来代替NOT IN 尽管二者都使用了NOT（不能使用索引而降低速度） 但NOT EXISTS要比NOT IN查询效率更高

lishixinzhi/Article/program/Oracle/201311/17060

如何提高oracle数据库的性能

在公路建设中，通过建立多条车道可以提高道路的流量。其实这个道理在Oracle数据库中也行得通。即可以将关键数据文件存储在多块硬盘上，以提高Oracle数据库的性能。可惜的是，不少数据库管理员没有意识到这一点。在这篇文章中笔者就以Oracle11G为例，说明如何通过在硬盘之间分布关键数据文件来提高性能。 一、在硬盘之间分布关键数据文件的基本原则。

在传统的文件系统上(即不是在裸机上)部署Oracle数据库，可以通过将关键的数据文件分布到多个可用的文件系统上或者不同的硬盘上来提高数据库的性能。具体的来说，需要遵循如下几个原则。

一是对于表来说，往往包含两个部分，即基本表与索引表。只要为基本表中的字段创建了索引，其对应的就有一张索引表。当用户访问表中的数据时，应用系统需要同时访问到索引表与数据表。此时我们可以将这两张表比喻成两辆车。如果现在只有一个车道(即将他们同时存放在一个硬盘或者文件系统中)，那么两辆车必须前后行使。而如果现在有两个车道(即将基本表与其相对应的索引表存放在不同的硬盘或者文件系统中)，那么这两辆车就可以并排行使。显然，后者的效率更高。为此笔者建议，可将经常需要访问的表和与之对应的索引表分开来存放。

二是可以将日志文件也分开来存放。不光光是数据表与索引表存在着这种状况。其实在日志文件管理中也是如此。只要条件允许，那么最好能够将联机重做日志和归档日志与其它数据文件存放在不同的硬盘或者文件系统上。因为当用户往数据库中写入数据时，需要同时往数据文件与重做日志文件中写入数据。此时如果将它们分开来存放，那么就相当于有了多条车道，分别往不同的文件中写入数据。这无疑就可以提高数据写入的效率，从而提高数据库的性能。

二、哪些文件最好能够分开存放?

在讲到硬盘之间分布关键数据文件的基本原则的时候，笔者举了几个需要分开存放的几个案例。但是在实际工作中，并不仅仅局限于上面提到的这些文件。笔者认为，如果条件允许的话，那么可以考虑将如下文件放置在不同的硬盘上。

一是表空间，如临时表空间、系统表空间、UNDO表空间等等。这三个表空间可能系统会同时进行访问。为此需要将其分开来存放。二是数据文件和索引文件。上面提到过，需要将经常访问的数据文件与其对应的索引文件存放在不同的硬盘上。因为这两类文件在访问数据时也可能会同时访问到。三是操作系统盘与数据库文件单独存放。显然Oracle系统肯定是与操作系统同时运行的。为了避免他们之间的I/Q冲突，就需要将Oracle部署在操作系统盘以外的磁盘上。四是联机重做日志文件。这个文件比较复杂，不但要将其与其他文件分开来存放。而且还需要注意的是，最好能够将其存放在性能最佳的硬盘上。

最后需要说明的一点是，增加磁盘也会增加成本。这不光光是购买磁盘所需要的花费，还包括管理的成本。所以这之间也会涉及到成本与性能之间的一个均衡问题。如果企业的数据不是很多，或者主要是涉及到查询操作，那么这么设计的话，就可能不怎么合理。因为投入要大于回报。

三、如何确定是否需要将文件分开来存放?

在实际工作中，企业的数据是一个从少到多的过程。也就是说，刚开始使用数据库的时候，可能数据量比较少，此时出于成本的考虑，没有将相关文件存放在不同的磁盘上。但是随着工作的深入，用户会发现数据库的性能在逐渐的降低。此时管理员就需要考虑，能够采取这种多建车道的措施，来提高数据库性能。当然在采取这个措施之前，管理员需要先进性评估。此时评估所需要用到的一个指标就是磁盘的I/O争用。

磁盘争用通常发生在有多个进程试图同时访问一个物理磁盘的情况下。如现在用户需要访问某个数据表中的数据，此时系统需要访问索引文件与数据表文件。如果将它们放置在同一磁盘上，那么在访问时就会发生I/O冲突。所以评估I/O冲突的严重程度，可以帮我们来确定是否需要将关键文件存放在不同的磁盘上。

将I/O平均的分布到多个可用的磁盘上，这可以有效的减少磁盘之间的争用情况，提高数据存储与读取的性能。从而提高Oracle等应用程序的效率。在实际工作中，数据库控制文件中有两个参数可以用来帮助我们评估这个指标。这两个参数是文件平均读取时间和文件平均写入时间。不过在使用这两个参数的时候，其只评估所有与数据库相关联的文件。管理员如果有需要的话，也可以通过下面的查询语句来查询数据文件是否存在I/O问题。查询的语法与结果如下图所示：

从如上的查询结果中可以看出某个数据文件是否繁忙，数据文件之间是否存在着/I/O冲突文件。这里需要注意的是，这个结果是一个动态的结果。在不同的时刻、用户进行不同的操作时往往会得出不同的结论。为此笔者建议，在使用这个数据的时候，最好能够多跟踪几次。然后分析多次运行的结果。只有如此，才能够得到比较合乎情理的判断。 通常情况下，管理员根据上面的结果可以得出三种结论。

第一种结论是上面这些数据文件都不是很忙。即文件的平均读取时间与写入时间都比较短，表示这两个文件都是比较空闲的。此时正常情况下，数据库的性能应该是不错的。也就是说，如果此时数据库的性能不理想的话，那么就不是磁盘的I/O所造成的。管理员应该从其他角度来改善数据库的性能。

第二种结论是每个数据库文件都非常的繁忙。此时有可能是读取时间或者写入时间比较长，或者说两个时间都比较长。当多个数据文件同时比较繁忙并且他们处于同一磁盘的话，那么管理员就需要考虑购买新的磁盘，然后将上面提到的这些关键文件重新整理，让他们部署在不同的磁盘上。

第三种结论是某几个特定的数据文件比较繁忙，而其他数据文件还可以。此时管理员如果成本受到限制，那么也不需要重新购买硬盘。在磁盘上的物理写入和读取次数上如果出现比较大的差异，就表明某个磁盘负载过大，即有很严重的I/O冲突。此时最好能够将这个磁盘中的文件进行调整，如将某些文件移动到另外的一块I/O相对不怎么严重的磁盘上。不过在采取这个操作的时候，需要注意一点。对于联机重做日志文件来说，即使其所在的磁盘I/O冲突比较低，或者访问这个文件的时间比较短，但是也不建议将其他数据文件转移到其所在的磁盘上来。因为通常情况下，为了保障数据库的性能，我们都建议将联机重做日志文件单独存放，并且还需要讲起放置在性能比较高的硬盘上。

总之，将关键的Oracle数据库文件分开放置。如此的话可以有效避免磁盘争用成为Oracle数据库系统的性能瓶颈。

Oracle设置系统参数进行性能优化

一 SGA

Shared pool tunning

Shared pool的优化应该放在优先考虑 因为一个cache miss在shared pool中发生比在data buffer中发生导致的成本更高 由于dictionary数据一般比library cache中的数据在内存中保存的时间长 所以关键是library cache的优化

Gets （parse）在namespace中查找对象的次数

Pins （execution）在namespace中读取或执行对象的次数

Reloads (reparse)在执行阶段library cache misses的次数 导致sql需要重新解析

） 检查v$librarycache中sql area的gethitratio是否超过 ％ 如果未超过 ％ 应该检查应用代码 提高应用代码的效率

Select gethitratio from v$librarycache where namespace= sql area ;

) v$librarycache中reloads/pins的比率应该小于 ％ 如果大于 ％ 应该增加参数shared_pool_size的值

Select sum(pins) executions sum(reloads) cache misses sum(reloads)/sum(pins) from v$librarycache;

reloads/pins %有两种可能 一种是library cache空间不足 一种是sql中引用的对象不合法

）shared pool reserved size一般是shared pool size的 ％ 不能超过 ％ V$shared_pool_reserved中的request misses＝ 或没有持续增长 或者free_memory大于shared pool reserved size的 % 表明shared pool reserved size过大 可以压缩

）将大的匿名pl/sql代码块转换成小的匿名pl/sql代码块调用存储过程

）从 i开始 可以将execution plan与sql语句一起保存在library cache中 方便进行性能诊断 从v$sql_plan中可以看到execution plans

）保留大的对象在shared pool中 大的对象是造成内存碎片的主要原因 为了腾出空间许多小对象需要移出内存 从而影响了用户的性能 因此需要将一些常用的大的对象保留在shared pool中 下列对象需要保留在shared pool中

a 经常使用的存储过程

b 经常操作的表上的已编译的触发器

c Sequence 因为Sequence移出shared pool后可能产生号码丢失

查找没有保存在library cache中的大对象

Select * from v$db_object_cache where sharable_mem and

type in ( PACKAGE PROCEDURE FUNCTION PACKAGE BODY ) and kept= NO ;

将这些对象保存在library cache中

Execute dbms_shared_pool keep( package_name );

对应脚本 dbmspool sql

)查找是否存在过大的匿名pl/sql代码块 两种解决方案

A．转换成小的匿名块调用存储过程

B．将其保留在shared pool中

查找是否存在过大的匿名pl/sql块

Select sql_text from v$sqlarea where mand_type= and length(sql_text) ;

）Dictionary cache的优化

避免出现Dictionary cache的misses 或者misses的数量保持稳定 只能通过调整shared_pool_size来间接调整dictionary cache的大小

Percent misses应该很低 大部分应该低于 ％ 合计应该低于 ％

Select sum(getmisses)/sum(gets) from v$rowcache;

若超过 ％ 增加shared_pool_size的值

Buffer Cache

）granule大小的设置 db_cache_size以字节为单位定义了default buffer pool的大小

如果SGA M granule= M 否则granule＝ M 即需要调整sga的时候以granule为单位增加大小 并且sga的大小应该是granule的整数倍

) 根据v$db_cache_advice调整buffer cache的大小

SELECT size_for_estimate buffers_for_estimate estd_physical_read_factor estd_physical_reads

FROM v$db_cache_advice WHERE NAME= DEFAULT AND advice_status= ON

AND block_size=(SELECT Value FROM v$parameter WHERE NAME= db_block_size );

estd_physical_read_factor=

) 统计buffer cache的cache hit ratio % 如果低于 ％ 可以用下列方案解决

◆增加buffer cache的值

◆使用多个buffer pool

◆Cache table

◆为 sorting and parallel reads 建独立的buffer cache

SELECT NAME value FROM v$sysstat WHERE NAME IN ( session logical reads

physical reads physical reads direct physical reads direct(lob) );

Cache hit ratio= (physical reads physical reads direct physical reads direct (lob))/session logical reads;Select (phy value dir value lob value)/log value from v$sysstat log v$sysstat phy v$sysstat dir v$sysstat LOB where log name= session logical reads and phy name= physical reads and dir name= physical reads direct and lob name= physical reads direct (lob) ;

影响cache hit ratio的因素 全表扫描 应用设计 大表的随机访问 cache hits的不均衡分布

）表空间使用自动空间管理 消除了自由空间列表的需求 可以减少数据库的竞争

其他SGA对象

）redo log buffer

对应的参数是log_buffer 缺省值与 OS相关 一般是 K 检查v$session_wait中是否存在log buffer wait v$sysstat中是否存在redo buffer allocation retries

A 检查是否存在log buffer wait

Select * from v$session_wait where event= log buffer wait ;

如果出现等待 一是可以增加log buffer的大小 也可以通过将log 文件移到访问速度更快的磁盘来解决

B

Select name value from v$sysstat where name in

( redo buffer allocation retries redo entries )

Redo buffer allocation retries接近 小于redo entries 的 ％ 如果一直在增长 表明进程已经不得不等待redo buffer的空间 如果Redo buffer allocation retries过大 增加log_buffer的值

C 检查日志文件上是否存在磁盘IO竞争现象

Select event total_waits time_waited average_wait from v$system_event

where event like log file switch pletion% ;

如果存在竞争 可以考虑将log文件转移到独立的 更快的存储设备上或增大log文件

D 检查点的设置是否合理

检查alert log文件中 是否存在 checkpoint not plete

Select event total_waits time_waited average_wait from v$system_event

where event like log file switch (check% ;

如果存在等待 调整log_checkpoint_interval log_checkpoint_timeout的设置

E 检查log archiver的工作

Select event total_waits time_waited average_wait from v$system_event

where event like log file switch (arch% ;

如果存在等待 检查保存归档日志的存储设备是否已满 增加日志文件组 调整log_archiver_max_processes

F DB_block_checksum=true 因此增加了性能负担 （为了保证数据的一致性 oracle的写数据的时候加一个checksum在block上 在读数据的时候对checksum进行验证）

）java pool

对于大的应用 java_pool_size应= M 对于一般的java存储过程 缺省的 M已经够用了

）检查是否需要调整DBWn

lishixinzhi/Article/program/Oracle/201311/17744

优化数据库的三板斧 大幅提高Oracle性能

几个简单的步骤大幅提高Oracle性能 我优化数据库的三板斧数据库优化的讨论可以说是一个永恒的主题 资深的Oracle优化人员通常会要求提出性能问题的人对数据库做一个statspack 贴出数据库配置等等 还有的人认为要抓出执行最慢的语句来进行优化 但实际情况是 提出疑问的人很可能根本不懂执行计划 更不要说statspack了 而我认为 数据库优化 应该首先从大的方面考虑 网络 服务器硬件配置 操作系统配置 Oracle服务器配置 数据结构组织 然后才是具体的调整 实际上网络 硬件等往往无法决定更换 应用程序一般也无法修改 因此应该着重从数据库配置 数据结构上来下手 首先让数据库有一个良好的配置 然后再考虑具体优化某些过慢的语句 我在给我的用户系统进行优化的过程中 总结了一些基本的 简单易行的办法来优化数据库 算是我的三板斧 呵呵 不过请注意 这些不一定普遍使用 甚至有的会有副作用 但是对OLTP系统 基于成本的数据库往往行之有效 不妨试试 （注 附件是Burleson写的用来报告数据库性能等信息的脚本 本文用到） 一．设置合适的SGA 常常有人抱怨服务器硬件很好 但是Oracle就是很慢 很可能是内存分配不合理造成的 ( )假设内存有 M 这通常是小型应用 建议Oracle的SGA大约 M 其中 共享池（SHARED_POOL_SIZE）可以设置 M到 M 根据实际的用户数 查询等来定 数据块缓冲区可以大致分配 M M i下需要设置DB_BLOCK_BUFFERS DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小 i 下的数据缓冲区可以用db_cache_size来直接分配 ( )假设内存有 G Oracle 的SGA可以考虑分配 M 共享池分配 M到 M 数据缓冲区分配 M到 M ( )内存 G SGA可以考虑分配 G 共享池 M到 M 剩下的给数据块缓冲区 ( )内存 G以上 共享池 M到 M就足够啦 再多也没有太大帮助 (Biti_rainy有专述)数据缓冲区是尽可能的大 但是一定要注意两个问题 一是要给操作系统和其他应用留够内存 二是对于 位的操作系统 Oracle的SGA有 G的限制 有的 位操作系统上可以突破这个限制 方法还请看Biti的大作吧 二．分析表和索引 更改优化模式 Oracle默认优化模式是CHOOSE 在这种情况下 如果表没有经过分析 经常导致查询使用全表扫描 而不使用索引 这通常导致磁盘I/O太多 而导致查询很慢 如果没有使用执行计划稳定性 则应该把表和索引都分析一下 这样可能直接会使查询速度大幅提升 分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令 对于少于 万的表 可以考虑分析整个表 对于很大的表 可以按百分比来分析 但是百分比不能过低 否则生成的统计信息可能不准确 可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间 索引可以通过DBA_INDEXES的LAST_ANALYZED列 下面通过例子来说明分析前后的速度对比 （表CASE_GA_AJZLZ大约有 万数据 有主键）首先在SQLPLUS中打开自动查询执行计划功能 (第一次要执行\RDBMS\ADMIN\utlxplan sql来创建PLAN_TABLE这个表)SQL SET AUTOTRACE ONSQLSET TIMING ON通过SET AUTOTRACE ON 来查看语句的执行计划 通过SET TIMING ON 来查看语句运行时间 SQL select count(*) from CASE_GA_AJZLZ;COUNT(*) 已用时间: : : Execution Plan SELECT STATEMENT Optimizer=CHOOSE SORT (AGGREGATE) TABLE ACCESS (FULL) OF CASE_GA_AJZLZ ……………………请注意上面分析中的TABLE ACCESS(FULL) 这说明该语句执行了全表扫描 而且查询使用了 秒 这时表还没有经过分析 下面我们来对该表进行分析 SQL *** yze table CASE_GA_AJZLZ pute statistics;表已分析 已用时间: : : 然后再来查询 SQL select count(*) from CASE_GA_AJZLZ;COUNT(*) 已用时间: : : Execution Plan SELECT STATEMENT Optimizer=FIRST_ROWS (Cost= Card= ) SORT (AGGREGATE) INDEX (FAST FULL SCAN) OF PK_AJZLZ (UNIQUE) (Cost= Card= )…………………………请注意 这次时间仅仅用了 秒！这要归功于INDEX(FAST FULL SCAN) 通过分析表 查询使用了PK_AJZLZ索引 磁盘I/O大幅减少 速度也大幅提升！下面的实用语句可以用来生成分析某个用户的所有表和索引 假设用户是GAXZUSR SQL set pagesize SQL spool d:\ *** yze_tables sql;SQL select *** yze table ||owner|| ||table_name|| pute statistics; from dba_tables where owner= GAXZUSR ;SQL spool offSQL spool spool d:\ *** yze_indexes sql;SQL select *** yze index ||owner|| ||index_name|| pute statistics; from dba_indexes where owner= GAXZUSR ;SQL spool offSQL @d:\ *** yze_tables sqlSQL @d:\ *** yze_indexes sql解释 上面的语句生成了两个sql文件 分别分析全部的GAXZUSR的表和索引 如果需要按照百分比来分析表 可以修改一下脚本 通过上面的步骤 我们就完成了对表和索引的分析 可以测试一下速度的改进啦 建议定期运行上面的语句 尤其是数据经过大量更新 当然 也可以通过dbms_stats来分析表和索引 更方便一些 但是我仍然习惯上面的方法 因为成功与否会直接提示出来 另外 我们可以将优化模式进行修改 optimizer_mode值可以是RULE CHOOSE FIRST_ROWS和ALL_ROWS 对于OLTP系统 可以改成FIRST_ROWS 来要求查询尽快返回结果 这样即使不用分析 在一般情况下也可以提高查询性能 但是表和索引经过分析后有助于找到最合适的执行计划 三．设置cursor_sharing=FORCE 或SIMILAR 这种方法是 i才开始有的 oracle 不支持 通过设置该参数 可以强制共享只有文字不同的语句解释计划 例如下面两条语句可以共享 SQL SELECT * FROM MYTABLE WHERE NAME= tom SQL SELECT * FROM MYTABLE WHERE NAME= turner 这个方法可以大幅降低缓冲区利用率低的问题 避免语句重新解释 通过这个功能 可以很大程度上解决硬解析带来的性能下降的问题 个人感觉可根据系统的实际情况 决定是否将该参数改成FORCE 该参数默认是exact 不过一定要注意 修改之前 必须先给ORACLE打补丁 否则改之后oracle会占用 %的CPU 无法使用 对于ORACLE i 可以设置成SIMILAR 这个设置综合了FORCE和EXACT的优点 不过请慎用这个功能 这个参数也可能带来很大的负面影响！ 四．将常用的小表 索引钉在数据缓存KEEP池中 内存上数据读取速度远远比硬盘中读取要快 据称 内存中数据读的速度是硬盘的 倍！如果资源比较丰富 把常用的小的 而且经常进行全表扫描的表给钉内存中 当然是在好不过了 可以简单的通过ALTER TABLE tablename CACHE来实现 在ORACLE i之后可以使用ALTER TABLE table STORAGE(BUFFER_POOL KEEP) 一般来说 可以考虑把 数据块之内的表放在keep池中 当然要根据内存大小等因素来定 关于如何查出那些表或索引符合条件 可以使用本文提供的access sql和access_report sql 这两个脚本是著名的Oracle专家 Burleson写的 你也可以在读懂了情况下根据实际情况调整一下脚本 对于索引 可以通过ALTER INDEX indexname STORAGE(BUFFER_POOL KEEP)来钉在KEEP池中 将表定在KEEP池中需要做一些准备工作 对于ORACLE i 需要设置DB_KEEP_CACHE_SIZE 对于 i 需要设置buffer_pool_keep 在 i中 还要修改db_block_lru_latches 该参数默认是 无法使用buffer_pool_keep 该参数应该比 * *CPU数量少 但是要大于 才能设置DB_KEEP_CACHE_BUFFER buffer_pool_keep从db_block_buffers中分配 因此也要小于db_block_buffers 设置好这些参数后 就可以把常用对象永久钉在内存里 五．设置optimizer_max_permutations 对于多表连接查询 如果采用基于成本优化(CBO) ORACLE会计算出很多种运行方案 从中选择出最优方案 这个参数就是设置oracle究竟从多少种方案来选择最优 如果设置太大 那么计算最优方案过程也是时间比较长的 Oracle 和 i默认是 建议改成 对于 i 已经默认是 了 六．调整排序参数 ( ) SORT_AREA_SIZE:默认的用来排序的SORT_AREA_SIZE大小是 K 通常显得有点小 一般可以考虑设置成 M（ ） 这个参数不能设置过大 因为每个连接都要分配同样的排序内存 ( ) SORT_MULTIBLOCK_READ_COUNT:增大这个参数可以提高临时表空间排序性能 该参数默认是 可以改成 来对比一下排序查询时间变化 注意 这个参数的最大值与平台有关系 七．调整其它几个关键的性能参数 很多人认为使用oracle数据库 系统的默认参数就是最好的 其实不是这样 lishixinzhi/Article/program/Oracle/201311/16704

Oracle性能调优思路

问 oracle进程内存占用一直增加 达到 G左右的时候就会连接失败 监听进程死掉 或者CPU达到 % 如何解决?

Peak Wong

Oracle性能调优一直是一个很有意思的命题 增强硬件配置是一种方法 但我们平时遇到的最多的问题是如何在没办法增强硬件配置的情况下 将数据库性能优化 这里给出一个思维流程 希望对各位有益

PATCH是否都打了 ORACLE系统内存参数是否太大 超出OS的MEMORY

查查是不是程序没有关闭连接导致连接数不断上升引起的 你是什么操作系统?

服务器都作了什么设置呢?比如sga的分配 是什么情况呢?

要进行调优 及参数设置

启动 Enterprise Management Console 以SYS/**** as SYSDBA身份进入系统

ORACLE i调优只涉及如下几个参数

a) processes = ;

b) open_links = ;

c)open_cursors = ;

d)sessions= ;

e) parallel_automatic_tuning=true

f) undo_retention=

g) undo_management=AUTO

请确保在 SPFILE 中保存 在Oracle i缺省的启动参数是spfile 不要用pfile文件启动数据库

物理内存大于 G以上的通用设置:

启动 Enterprise Management Console 以SYS/**** as SYSDBA身份进入系统

配置SGA和PGA大小方法如下

物理内存大于 G以上的通用设置

中文名 参数名 参数值 设置方法

SGA的最大大小 Sga_max_size M 例程 配置 内存项卡

日志缓冲区 Log_buffer 例程 配置 一般信息选项卡 所有初始化参数

大型池 Large_pool_size M 例程 配置 内存项卡

Java池 Java_pool_size M 例程 配置 一般信息选项卡 所有初始化参数

共享池 Shared_pool_size M 例程 配置 内存项卡

数据缓冲区高速缓存 Db_cache_size M 例程 配置 内存项卡

Keep池 Db_keep_cache_size M 例程 配置 一般信息选项卡 所有初始化参数

Pga自动管理 workarea_size_policy AUTO 例程 配置 一般信息选项卡 所有初始化参数

总计pga目标 pga_aggregate_target M 例程 配置 内存项卡

说明:

此内存设置不包含在数据库服务器上的其它应用程序的物理内存的大小 如果有其它的应用程序 可以参照下面的计算: sga_max_size+ pga_aggregate_target+应用程序物理内存+OS物理内存 = 系统物理内存* % 如果服务器上只有Oracle服务器 在 G以上物理内存的服务器上Oracle内存参数都可以参照上面的设置 如果服务器上有其它的应用 而服务器总的物理内存大于 请自己计算后再选择的方案

sga_max_size+ pga_aggregate_target = G 在 bit操作系统上有这个限制

lishixinzhi/Article/program/Oracle/201311/17386

如何提高oracle模糊查询的性能？

1、使用两边加‘%’号的查询，Oracle是不通过索引的，所以查询效率很低。

例如：select count(*) from lui_user_base t where t.user_name like '%cs%';

2、like '...%'和 like'%...'虽然走了索引，但是效率依然很低。

3、有人说使用如下sql，他的效率提高了10倍，但是数据量小的时候

select count(*) from lui_user_base where rowid in (select rowid from lui_user_base t where t.user_name like '%cs%')

我拿100w跳数据做了测试，效果一般，依然很慢，原因：

select rowid from lui_user_base t where t.user_name like '%cs%'   这条sql执行很快，那是相当的快，但是放到select count(*) from lui_user_base where rowid in（）里后，效率就会变的很慢了。

4、select count(*) from lui_user_base t where instr(t.user_name,'cs') 0

这种查询效果很好，速度很快，推荐使用这种。因为我对oracle内部机制不是很懂，只是对结果做了一个说明。

5、有人说了用全文索引，我看了，步骤挺麻烦，但是是个不错的方法，留着备用：

对cmng_custominfo 表中的address字段做全文检索：

1，在oracle9201中需要创建一个分词的东西：

BEGIN

ctx_ddl.create_preference ('SMS_ADDRESS_LEXER', 'CHINESE_LEXER');

--ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); 不用

end;

2，创建全文检索：

CREATE INDEX INX_CUSTOMINFO_ADDR_DOCS ON cmng_custominfo(address) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER SMS_ADDRESS_LEXER');

3，查询时候，使用：

select * from cmng_custominfo where contains (address, '金色新城')1;

4，需要定期进行同步和优化：

同步：根据新增记录的文本内容更新全文搜索的索引。

begin

ctx_ddl.sync_index('INX_CUSTOMINFO_ADDR_DOCS');

end;

优化：根据被删除记录清除全文搜索索引中的垃圾

begin

ctx_ddl.optimize_index('INX_CUSTOMINFO_ADDR_DOCS', 'FAST');

end;

5，采用job做步骤4中的工作：

1)该功能需要利用oracle的JOB功能来完成

因为oracle9I默认不启用JOB功能，所以首先需要增加ORACLE数据库实例的JOB配置参数:

job_queue_processes=5

重新启动oracle数据库服务和listener服务。

2)同步 和 优化

--同步 sync:

variable jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''INX_CUSTOMINFO_ADDR_DOCS'');',

SYSDATE, 'SYSDATE + (1/24/4)');

commit;

END;

--优化

variable jobno number;

begin

DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''INX_CUSTOMINFO_ADDR_DOCS'',''FULL'');', SYSDATE, 'SYSDATE + 1');

commit;

END;

其中， 第一个job的SYSDATE + (1/24/4)是指每隔15分钟同步一次，第二个job的SYSDATE + 1是每隔1天做一次全优化。具体的时间间隔，可以根据应用的需要而定。

6，索引重建

重建索引会删除原来的索引，重新生成索引，需要较长的时间。

重建索引语法如下：

ALTER INDEX INX_CUSTOMINFO_ADDR_DOCS REBUILD;

据网上一些用家的体会，oracle重建索引的速度也是比较快的，有一用家这样描述：

Oracle 的全文检索建立和维护索引要比ms sql server都要快得多，笔者的65万记录的一个表建立索引只需要20分钟，同步一次只需要1分钟。

因此，也可以考虑用job的办法定期重建索引。

网站标题：oracle如何提高性能 oracle性能参数优化
路径分享：http://myzitong.com/article/hjddsp.html