Oracle 系统优化的步骤与方法

 

1、优化前提

   应用系统方案制定准确，对应用系统运行环境分析合理、正确，在数据库服务器性能、存储空间、网络带宽等方面的配置能够达到系统运行要求。

2、优化目标

    响应时间与吞吐量平衡

    临界资源

响应时间与吞吐量平衡

   根据应用类型的不同，性能优化的目标不同：

   在线事务处理系统(OLTP)把吞吐量定义为性能指标；

   决策支持系统(DSS)把响应时间定义为性能指标。

响应时间

   响应时间=服务时间+等待时间

系统吞吐量

   系统吞吐量指在给定的时间内所完成的工作量。有以下两种技术：

    以相同的资源来完成更多的工作（减少服务时间）；

    通过减少整个响应时间来更快完成工作。

等待时间

   当竞争增强的时候，某个任务的服务时间也许保持不变，但它的等待时间将增长。

   我们开发的系统一般为OLTP和DSS的复合系统，侧重于OLTP,在硬件允许的情况下最好能够将运行数据库、分析数据库分离。

临界资源

   诸如 CPU、内存、I/O容量、网络带宽等资源，都是减少时间的关键因素。性能好坏取决于以下因素：

    可用资源的数量

    需要该资源的客户方的数目

    客户方等待资源所消耗的时间

    客户保持资源的时间长短

   随着请求单元的增加，服务时间也增加。为了处理这种情形，用户可以选择：

    通过限制请求的速率，从而维护可接受响应时间

    还可通过增加资源数目，如CPU和硬盘（增加资源的前提是应用系统设计良好，并且已经做了充分的优化）

3、优化阶段

   从实际做的项目过程来看，除了系统安装优化外，系统优化往往都是在系统实施、运行时才考虑，其实到这阶段做系统优化的局限性比较大，因为系统架构设计都成型、固化，大幅度调整设计的代价非常昂贵，一般只能在局部领域做优化，只能通过重新分配内存或优化I/O来或多或少地提高性能，实际上优化应该贯穿系统设计、开发、安装、测试、运行整个过程。

3.1 设计阶段

   为了达到最佳的效果，优化工作应当从设计阶段进行，而不是在系统实施后进行。

   在数据库设计阶段，个人认为需要注意如下几个方面：

    业务对象不能建立在系统表空间；

    索引表空间和业务表空间分开；

    将LOB类型的字段与其它的类型分开；

    根据应用系统功能确定是否要采用冗余字段；

    正确的主键字段的选择，建议采用数字，不推荐使用复合主键；

3.2 开发、测试阶段

   在开发实现阶段，个人认为需要注意如下几个方面：

    执行sql使用变量绑定的方式，尽可能的保留在共享内存中，提高sql命中率；

   多表关联查询时采用有效的连接顺序；

    尽可能的降低客户端和服务器的网络数据交互，某个业务功能点需要频繁和数据库交互的，建议采用存储过程、临时表实现；

    根据查询条件建立必要的索引，查询条件中使用oracle函数建立相对应的函数索引,数据值范围较小的采用位图索引

    多张表关联查询时，有时可采用先查询符合条件对应的表中关键字，然后通过关键字再查询对应表中相关信息；

    频繁访问，较少更新的数据量较小的表信息可采用缓存的方式；

    在实现批量更新、插入时，要采用jdbc批量执行方法，并且调整对应的fetchsize参数。

   在测试阶段，应该模拟实际运行环境，测试出相关性能较差的功能点。

   因为在设计、开发阶段往往因为并发用户少、数据量小，很多性能问题显现不出来，如果软件测试充分，很多性能问题都可以显现出来，现在有很多优秀的软件测试工具，如LoadRunner、Robert在做压力测试方面都比较方便、优秀。

尽量将系统因程序设计、编码不当导致的性能问题暴露在测试阶段。

3.3 安装阶段

   一般在安装生产数据库时，我们根据系统最早的规划，集合软、硬件环境，需要调整操作系统以及数据库参数，

3.3.1操作系统交换区

   交换区是Oracle的一项基本的要求。可以根据Oracle的发行要求来确定。一般交换区大小的要求是该服务器内存的2倍至4倍之间，建议是内存的4倍

3.3.2操作系统内核参数

      

shmmax	共享内存段，建议设大点， 达到最大SGA
shmmin	最小的共享内存段.
shmmni	共享内存标志符的数量.
shmseg	一个进程可分配的最大内存段数.
shmall	最大可允许的内存数，比SGA还要大.
semmns	信号量，跟ORACLE的PROCESS数有关.
semmsl	一个信号量中最大的信号量数.

3.3.3 oracle 文件设置

   当服务器平台已完成操作系统的安装后，就应该开始认真的考虑下面的问题：

    是否采用裸设备

   实际应用的生产系统基本都是采用裸设备，使用裸设备对于读写频繁的数据库应用来说，可以极大地提高数据库系统的性能。

 

    安装点的考虑

    Oracle的安装点就是指数据文件、日志文件和控制文件的安置路径，为了使系统在以后运行性能达到优化，建议将数据文件、日志文件和控制文件的安置路径与数据库系统存放在不同的路径上。最好将数据文件、日志文件和控制文件分别存放在不同的路径。

 

    SYSTEM表空间对应数据文件

   在自定义安装会话中，建议你根据需要设置system表空间所对应的数据文件的大小。一般要设置比默认值的2倍。该数据文件的大小最好是在300MB至500MB间。因为数据文件太小不利于系统的运行。

 

     临时表空间对应的数据文件

    临时表空间对应的数据文件可以根据将来系统存放的应用的处理情况来定。比如系统将来可能要经常进程排序处理，则需要设置较大的临时表空间，也可能需要再建立新的临时表空间。这里建议临时表空间的数据文件在100MB至300MB左右。

 

     回滚段表空间对应的数据文件

9i回滚表空间都是系统管理，初始值也是根据系统事务量预估计的值，实际到运行阶段如果系统常出现ora-01555错误的时候，可能就需要增加回滚表空间的大小。

 

    日志文件的大小

   日志文件的大小对于Oracle系统的运行也是相当重要。默认值是太小。实际根据事务繁忙预估计日志大小，没有固定的具体值范围，建议重做日志切换时间不能过短也不能过长，一般在20－40分钟左右。该参数可以在系统运行期间根据数据库系统日志切换时间重新调整，控制文件的大小。

 

      数据库块的大小

    如果你的应用系统是OLTP的话，可以采用较小的数据库块。如果是DSS类型的应用系统，则可以设置较大的数据库块，目前Oracle产品所允许的数据库块可以是2KB至64KB之间。无论你选择较大的块或较小的块，它的值都必须是2的整数倍，比如2048,4096,8192等。但需要注意的是，如果操作系统为64位，则可选择较大的块。

 

   字符集的选择

   字符集是Oracle系统专门支持的一项技术。详细请参考另外的章节。一般不要与另外的已经存放的Oracle系统的字符集产生冲突即可。但如果你的环境是一个新的平台，不需要与其它平台进行数据交换的话，建议选择默认的字符集。这样可以利于将来的修改。

 

3.3.4数据库启动参数

sga_max_size	例程存活期间所占用的系统全局区的最大大小，一般为物理内存的1/2-1/3
shared_pool_size	指定共享池的大小，共享池包含：共享游标、存储的过程、控制结构和并行执行消息缓冲区等对象，较大的值用于改善多用户系统的性能，该参数调整不能过大，会增加管理负担和latch 的开销，一般是在200M-500M左右
db_cache_size	该参数指定数据缓冲区的大小，原则上是越大越好，取代了8i中的db_block_size * db_block_buffers
log_buffer	重做日志缓冲区大小，该参数设置大没有意义，Oracle推荐log_buffer最大为cpu_count乘以128KB或512KB中最大值
processes	系统用户进程的最大数量，该参数设置为系统最繁忙时估计并发用户数
large_pool_size	如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在10M --50M应该差不多了。可以考虑为 session * (sort_area_size + 2M)。
Java_pool_size	它用于存放java代码，若不使用java，建议设置为30M
pga_aggregate_target	程序全局区大小,1.对于OLTP系统PGA_AGGREGATE_TARGET= （ * 80%） * 20% 2.对于DSS系统PGA_AGGREGATE_TARGET =（ * 80%） * 50%
timed_statistics	建议将timed_statistics 设置为true,否则无法查看到准确的统计信息（9i版本后的设置为true对系统性能影响较小，千分之一）

 

上述参数基本是初始估计值，在运行阶段可能会根据实际运行情况再调整。

 

3.4 运行阶段

   这也是实际优化工作最多的阶段，个人认为运行阶段优化的真正工作是解决因为实际运行数据库参数设置不当、表、索引统计信息不准确，执行路径不当等导致的性能问题。

   优化工作应该作为日常工作的一部分，而不是等到用户反映系统慢，系统宕机时才去优化，那时已经是亡羊补牢，为时有点晚，从实际项目来看，往往都是应用程序编写的sql、表、索引统计信息不准确，执行路径不当而导致的性能问题，个人认为一般的sql调优还是有章可循的,基本三步： 查找、分析、优化。

3.4.1查找

3.4.1.1 非实时查找

   查找工具常用的就是statspack,该工具的安装、使用比较简便。
   脚本路径${oracle_home}/rdbms/admin目录下，常用脚本如下：

spdrop.sql	删除脚本，丢弃统计分析的相关包、视图、表、同义词等对象(首次创建无须执行)
spcreate.sql	创建脚本，生成统计分析的相关包、视图、表、同义词等对象（首次执行前建议创建一个统计用的表空间）
spreport.sql	生成报告记录sql ,生成的报告文件在系统当前路径下，文件名默认为：sp_开始快照号_结束快照号.lst
sprepsql.sql	分析相关快照中的sql执行计划。
sppurge.sql	删除在两个快照号之间包括本身的所有统计分析数据。
sptrunc.sql	截取statspack统计分析的相关数据  在统计分析的对应用户perfstat下执行

 

   执行时间：

   统计时生成两次快照，一般在30-40分钟左右
   执行方法：
   用sys登陆sqlplus后间隔对应时间执行两次 exec statspack.snap;

统计结果视图：

stats$snapshot	快照相关信息； selectsnap_id,snap_time from stats$snapshot;
stats$sqltext	快照统计sql信息,查询统计sql(statspack报告中sql过长会被截掉）select sql_text  from stats$sqltext where hash_value=&value and last_snap_id=begin_snap_id order by piece;

 

3.4.1.2 实时查找

   如果需要实时的查找性能隐患的相关sql,通过  v$session_wait,v$session,v$sqltext_with_newlines三张动态视图就可以基本查找到相关的sql，脚本如下：

select sql_text ,sw.event
 from v$sqltext_with_newlines st,v$session se,v$session_wait sw
  where st.address=se.sql_address and st.hash_value=se.sql_hash_value
  and se.sid =sw.sid and  
 (sw.event = 'buffer busy waits' or
  sw.event = 'enqueue' or
  sw.event = 'free buffer waits' or
  sw.event = 'global cache freelist wait' or
  sw.event = 'latch free' or
  sw.event = 'log buffer space' or
  sw.event = 'parallel query qref latch' or
  sw.event = 'pipe put' or
  sw.event = 'write complete waits' or
  sw.event like 'library cache%' or
  sw.event like 'log file switch%' 
 )  
  order by st.hash_value,st.piece; 

3.4.2分析

   分析报告个人一般主要关注top 5 event以及相关的读逻辑块、物理块、执行次数较多的sql.实际上更多的侧重在sql分析上

   一般常见的top 5 事件如下：

db file sequential read	等待事件，一般问题出现在读索引上，建议将业务表空间和索引表空间分开存储在不同的物理卷下，以提高磁盘的I/O性能。
db file scattered read	建议程序中尽量避免使用全表扫描的语句，或者可以增大db_file_multiblock_read_count的值，提高全表扫描一次读取数据块的速度，减少磁盘I/O。
db file parallel write	说明DBWR进程正等待把缓冲区的内容并行写入数据文件中去,等待将一直持续到所有的I/O全部完成。建议增大初始化参数中的db_writer_processes的值
log file sync	说明任何时候一个事物提交时，它将通知LGWR将LOG_BUFFER写入日志文件，如果此部分占用时间较长，应减少COMMIT的次数，建议将重做日志放到较快的磁盘上进行存储。
log file parallel write	等待事件，和上面一样建议将重做日志放到较快的磁盘上进行存储。

   提取出sql以后就可以进行分析，主要采用分析执行计划的方式。个人一般喜欢如下的方式进行分析：

    生成计划表（初次）

   以sys用户执行脚本${oracle_home}/rdbms/admin/utlxplan.sql，

    创建公用同义词，方便在每个用户下生成执行计划（初次）

   Create public synonym plan_table for plan_table;

   Grant all on plan_table to public;

    每次分析时设置sqlplus环境变量

   Set timing on

   Set autotrace traceonly

    查看相关sql执行计划

   其他客户端软件pl/sql developer,toad 分析执行计划都比较方便。

    执行计划路径解释

   常见路径解释：

Full Table Scans	全表扫描、无可用索引
Index Unique Scans	索引唯一扫描
IndexRange Scans	索引范围扫描
IndexRange Scans Descending	索引降序范围扫描
Index Skip Scans	索引跳跃扫描
Full Scans	全索引扫描
Fast Full Index Scans	快速全索引扫描
Index Joins	索引连接
Bitmap Joins	位图连接

 

   常见连接解释：

Nested Loops	会循环外表（驱动表），逐个比对和内表的连接是否符合条件。 基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。	优点：在驱动表比较小，内表比较大，而且内外表的连接列有索引的时候比较好，嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。 缺点：如果内部行源表(读取的第二张表（内表）已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。
SORT- merge JOIN	将两表的连接列各自排序然后合并，只能用于连接列相等的情况，适合两表大小相若的情况	优点：在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。 局限性：排列合并连接只能用于等价连接排列合并连接需要临时的内存块,以用于排序( 如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O
HASH JOIN	在其中一表的连接列上作散列，因此只有另外一个表做排序合并， 只有基于代价的优化器才可以使用哈希连接。	当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢

3.4.3优化

    Oracle运行阶段优化的更多是对sql的优化，个人理解工作主要是：

    分析性能较差sql;

    调整性能较差的sql的实现方式，协助程序员更改相关程序;

    对相关的查询条件建立合理的索引;

    根据需要合理的更新表、索引的程序信息;

3.4.3.1 oracle 优化器

     优化器优化方式

   Oracle的优化器共有两种优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

   A.RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。

   B.CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle推荐用CBO的方式。

    优化器的优化模式(Optermizer Mode)

   Rule:走基于规则的方式。

   Choose:默认的情况下Oracle用的是这种方式，不建议修改该参数。指的是当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。

   First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。

   All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

3.4.3.2 常见优化问题

     明明有索引，表的数据量也非常大，执行路径不走索引

    对应表、索引的统计信息有误，可以通过dba_tables,dba_indexes视图中的num_rows查看对应表、索引的统计信息，如果有误，重新统计。

   Analyze table table_name compute statistics

   For table  /*统计表*/

   For all indexed columns  /*统计有索引的表列*/

    统计后性能反而变差

   虽然oracle推荐采用CBO方式，但有时对应的执行路径并不是最佳，所以我们在统计信息时应该有针对性的统计相关表、索引信息。

   一般有两种处理方法

   1. 删除对应的统计信息

    Analyze table table_name delete statistics

    Analyze index index_name delete statistics

   对应的系统包dbms_stats也可实现生成、删除表、索引的统计信息

   2. 使用hints 明确指定对应的执行路径

3.4.3.3 hints

  常用的几种hints如下：

Hint Syntax	Description
优化方式
/*+ ALL_ROWS */	表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化
/*+ CHOOSE */	表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量; 表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
/*+ FIRST_ROWS */	表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化，在做分页查询时有时可以改善性能
/*+ RULE */	表明对语句块选择基于规则的优化方法
处理方法
/*+ AND_EQUAL(table index) */	提示明确进行执行规划的选择,将几个单列索引的扫描合起来. SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306'
/*+ CLUSTER(table) */	Explicitly chooses a cluster scan to access the specified table.
/*+ FULL(table) */	表明对表选择全局扫描的方法 SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT'
/*+ INDEX(table index) */	表明对表选择索引的扫描方法. SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
/*+ INDEX_ASC(table index) */	表明对表选择索引升序的扫描方法
/*+ INDEX_COMBINE(table index) */	为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式. 例如:     SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS 　　WHERE SAL<5000000 AND HIREDATE
/*+ INDEX_DESC(table index) */	表明对表选择索引降序的扫描方法.
/*+ INDEX_FFS(table index) */	对指定的表执行快速全索引扫描,而不是全表扫描的办法.
/*+ NO_INDEX(table index) */	表明优化器不采用扫描相对应的索引
/*+ ROWID(table) */	明确表明对指定表根据ROWID进行访问. SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'　AND EMP_NO='SCOTT';
/*+ USE_CONCAT */	对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
连接操作
/*+ USE_HASH(table) */	将指定的表与其他行源通过哈希连接方式连接起来. SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
/*+ USE_MERGE(table) */	将指定的表与其他行源通过合并排序连接方式连接起来. SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
/*+ USE_NL(table) */	将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

4、参考资料

《ORACLE9i_优化设计与系统调整》

 

 

 

以上是作者提供的对于系统优化一个比较详细的步骤与方法，大家可以借鉴，主要是学习方法，个别地方还要根据不同的系统环境做相应的改变。