在这里我们将介绍Oracle数据库优化方案与实践，不同的环境会有不同的调试，但是也会有差别，希望大家能合理的吸收。
一、前言
二、ORACLE数据库优化概述
1、内存等参数配置的优化
2、减少物理读写的优化
3、批量重复操作的SQL语句及大表操作的优化
二、ORACLE数据库优化方案
1、内存等Oracle系统参数配置
2、使用索引
3、表分区
4、Procedure优化
5、其他改造
6、维护作业计划
三、ORACLE数据库优化前后比较
1、批量重复的SQL语句执行性能
2、一些单次、不常用的操作的语句执行性能
四、参考
1、常用的优化工具
2、参考文献
一、前言
随着实际项目的启动，实际项目中使用的 Oracle数据库经过一段时间的运行，在线保存的数据量和业务处理的数据量在逐渐增大，最初的Oracle设置，与现在实际需要的运行性能有一定差距，需要进行一些优化调整。
本文将结合本人实际维护经验，相应地提出实际项目数据处理的一些优化方法，以供参考。
适用于Oracle 9i。
二、Oracle数据库优化概述
Oracle数据库的优化，针对不同的应用，会有侧重点不同的优化方法，根据我们实际项目的应用特点，我们主要关心的是每次事务执行完成的时间长短。
从Oracle数据库本身的特点，我们可以把优化工作划分为初始优化设置，微优化。
在初始优化设置时，我们只能根据硬件情况，估计业务运行的情况，综合经验，给出一种经验设置，大体上来说，这种经验设置离满足优化需求的目标不是很远。在完成了初始优化设置后，经过一段时间的业务运行，已可开始收集实际运行环境的性能数据，此时，就可以对各种Oracle性能指标、各种关心的事务操作进行性能评估，然后进行微优化了。
Oracle优化，不是一个一蹴而就的工作，也不是一个一劳永逸的工作，需要定期维护，tb定期观察，在发现性能瓶颈时及时进行调整。Oracle总是存在性能瓶颈的，不使用、不操作的数据库总是最快的，在解决当前瓶颈后，总是会有另一个瓶颈出现，所以在优化前，我们需要确定一个优化目标，我们的目标是满足我们的应用性能要求就可以了。
Oracle优化，涉及的范围太广泛，包含的有主机性能，内存使用性能，网络传输性能，SQL语句执行性能等等，从我们面向网管来说，满足事务执行速度性能主要表现在：
1）批量重复的SQL语句执行性能（主要是通过Procedure计算完成数据合并和数据汇总的性能和批量数据采集入库的性能）；
2）一些单次、不常用的操作的语句执行性能（主要是GUI的非规律操作）。
根据这两个特点，我们可把优化方法归纳到3个重要方向：
1）内存等参数配置的优化。内存优化，是性能受益最快的地方。
2）减少物理读写的优化。内存逻辑I/O操作的时间，远远小于物理I/O的操作时间。
3）批量重复操作的SQL语句及大表操作的优化。减少SQL执行次数，减少大表操作次数。
下面主要针对得益最大的这三个方向的优化进行阐述。
1、内存等参数配置的优化
对于大多数应用来说，最直接、最快速得到优化收益的，肯定属于内存的优化。给每个Oracle内存块分配合理的大小，可以有效的使用数据库。通过观察各种数据库活动在内存里的命中率，执行情况，我们能很快的掌握数据库的主要瓶颈。我们从下面的一条SQL语句的执行步骤就可知道。
一个SQL语句，从发布到执行，会按顺序经历如下几个步骤：
1）Oracle把该SQL的字符转换成它们的ASCII等效数字码。
2）该ASCII数字码被传送给一个散列算法，生成一个散列值。
3）用户server process查看该散列值是否在shared pool内存块中存在。
若存在：
4）使用shared pool中缓存的版本来执行。
若不存在：
4）检查该语句的语义正确性。
5）执行对象解析（这期间对照数据字典，检查被引用的对象的名称和结构的正确性）。
6）检查数据字典，收集该操作所引用的所有对象的相关统计数据。
7）准备执行计划，从可用的执行计划中选择一个执行计划。（包括对stored outline和materialized view的相关使用的决定）
8）检查数据字典，确定所引用对象的安全性。
9）生成一个编译代码（P-CODE）。
10）执行。
这里，通过内存的合理分配，参数的合理设置，我们主要解决：
1）减少执行到第五步的可能，节约SQL语句解析的时间。第五步以后的执行过程，是一个很消耗资源的操作过程。
2）通过内存配置，尽可能让SQL语句所做的操作和操作的数据都在内存里完成。大家都知道，从内存读取数据的速度，要远远快于从物理硬盘上读数据，一次内存排序要比硬盘排序快很多倍。
3）根据数据库内存活动，减少每个内存块活动的响应时间，充分利用每个内存块，减少内存latch争用发生的次数。
2、减少物理读写的优化
无论如何配置Oracle数据库，我们的网管系统，每小时周期性的都会有新数据被处理，就会发生物理读写，这是避免不了的。
减少物理读写的优化，一般所用的方法有：
1） 增加内存data buffer的大小，尽可能让数据库操作的数据都能在内存里找到，不需要进行物理读写操作。
2） 通过使用索引，避免不必要的全表扫描。
3） 大表物理分区，Oracle具有很好的分区识别功能，减少数据扫描范围。
上述3个方法，是从整体上改善数据库物理I/O性能最明显的3个方法。能非常快速的减少数据库在物理I/O，最直接的反应是数据库事务执行时间能能以数量级为单位减少。其他的一些减少物理读写的优化方法，比如使用materialized view，Cluster等方法；还有一些分散I/O的方法，比如 Oracle日志文件不与数据文件放在一个物理硬盘，数据热点文件物理I/O分开等等方法，就目前我们的网管系统而言，能得到的效果不是很明显，在网管系统中，为了不增加数据库维护的复杂性，不推荐使用。
3、批量重复操作的SQL语句及大表操作的优化
批量重复执行的SQL语句，一般出现在每个周期时间内的数据批量入库的insert语句，和数据合并、汇总的周期性select、delete、insert操作。
我们需要注意以下几点：
1） 减少不必要的SQL语句执行和SQL语句的执行次数。
每条SQL语句执行，都会消费系统资源，都有执行时间。减少不必要的SQL语句执行和减少SQL语句的执行次数，自然能减少业务执行时间。需要根据业务流程，重新设计数据处理的代码。此方法主要适用于procedure执行的数据合并、汇总。
2） 这些SQL语句，由于每个SQL语句都要执行很多次，应该尽量让该SQL的散列值在shared pool内存块中存在。也就是使用动态SQL，避免SQL硬解析。
可通过Oracle参数的设置，和动态SQL语句的应用，通过绑定变量的方式，减少SQL语句的解析次数。
3）减少大表的操作，确保在一次事务中，同类操作只对大表执行一次。主要在数据合并和数据汇总的pprocedure和数据采集时出现
三、Oracle数据库优化方案
1、内存等Oracle系统参数配置
Oracle 的parameter参数，分动态参数和静态参数，静态参数需要重新启动数据库才能生效，动态参数不需要重新启动数据库即可生效。
Oracle 9i可以使用spfile的特性，使用alter system set 参数名=参数值 scope=both[spfile];的方法进行修改。也可以直接修改pfile。
以下给出了网管Oracle 数据库重点关注的parameter的初始优化设置。
最大可使用的内存SGA总和
静态参数sga_max_size=物理内存的大小减1.5G
Shared pool
动态参数shared_pool_size= 600 ~ 800 M
静态参数shared_pool_reserved_size= 300 M
动态参数open_cursors= 400 ~ 600
静态参数cursor_space_for_time= TRUE
静态参数session_cached_cursors= 60 ~ 100
动态参数cursor_sharing= SIMILAR
Data buffer
动态参数db_cache_advice= READY
动态参数db_cache_size
动态参数Db_keep_cache_size
动态参数db_recycle_cache_size
（sga_max_size大小，除了分配给所有非data buffer的size，都分配给data buffer）
Sga other memory
动态参数large_pool_size= 50 M
静态参数java_pool_size= 100 M
动态参数log_buffer= 3 M
Other memory
动态参数sort_area_size= 3 M
静态参数sort_area_retained_size= 0.5 M
静态参数pga_aggregate_target= 800 M
动态参数workarea_size_policy= AUTO
磁盘I/O配置
静态参数sql_trace= FALSE
动态参数timed_statistics= true
动态参数db_file_multiblock_read_count= 16
静态参数dbwr_io_slaves= 0
静态参数db_writer_processes= 3
静态参数undo_management= AUTO
动态参数undo_retention= 7200
2、使用索引
我们初步定义，表数据超过1000行的表，我们都要求使用索引。（不区分事务操作的数据在表数据中所占的比例）
索引所包含的字段不超过4个。
检查SQL语句是否使用了索引，我们使用execute plan来看，获得explain的方法，我们通过SQL*PLUS工具，使用如下命令进行查看：
setautotraceonsetautotracetraceonlyexplain settimingon或通过SQL*PLUS trace，然后查看user_dump_dest下的跟踪文件，使用tkprof工具格式化后阅览。
altersessionsetevents'10046tracenamecontextforever,level12'; altersessionsetevents'10046tracenamecontextoff'; SELECTp.spid,s.usernameFROMv$sessions,v$processpWHEREs.audsid=USERENV('sessionid')ANDs.paddr=p.addr;3、表分区
在网管数据库里，比较突出的大表有小区表和告警表。
性能表，使用范围分区。
以时间点start_time为范围分区字段。
告警表，使用range-hash的混合分区和范围分区。
范围分区以时间点starttime为分区字段，混合分区增加ALARMNUMBER为字段的hash子分区。
同时，创建本地分区索引。
4、Procedure优化
1）取消地市一级的Procedure，只保留其上层调用Procedure，并保持参数输入方法，调用方法不变。
2）确保大表数据查询操作只有1次，确保大表数据删除只有一次。
3）确保单条SQL语句执行已优化。
4）减少SQL执行次数。
5、其他改造
修改表存储参数，提前预先分配extents。
修改表空间存储参数（采集表空间所用块设置为大块，比如32k一个块；修改ptcfree,pctused,pctincrease等）。
避免使用唯一索引和非空约束。
创建合理的索引。
各模块SQL语句优化，比如使用提示固定索引等。
确认每一条历史数据删除语句已优化和删除方法。
临时表的使用。
6、维护作业计划
表分析（包含确定具体的表的分析方法，分区表分析方法，索引分析方法）。
空间回收维护（包括确定HWM，回收多余分配给表的块，合并数据块碎片等）。
索引维护（包括定期重建索引，索引使用情况监视等）。
历史数据删除检查（检查保存的数据是否符合要求，检查历史数据删除方法是否正确-比如批量删除提交的方法等）。
全库性能分析和问题报告及优化（比如使用statspack进行性能趋势分析，检查有问题的SQL或事务，确定当前系统等待的top 5事件等等）。
表数据keep,default及reclye（比如把一些常用的配置表固定在内存里等）。
数据库参数核查（防止数据库参数被修改，定期对系统配置参数进行比较）。
日志文件分析（定期检查Oracle生成的日志文件，定期备份、删除）。
硬盘空间维护（定期对Oracle 对象使用的空间情况进行监视）。
四，Oracle数据库优化前后比较
1、批量重复的SQL语句执行性能
根据网元数量，各地的执行的完成时间有所区别。
用于数据合并和汇总的Procedure的计算性能
通过statspack的周期性采集数据，我们可以使用以下语句，计算我们想统计的Procedure的执行情况：
SELECTTO_CHAR(sn.snap_time,'yyyy-mm-ddhh24:mi:ss')ASsnap_time,s.disk_reads, s.buffer_gets,s.elapsed_time/1000000ASelapsedtime FROM(SELECThash_value,sql_text,address,last_snap_id FROMSTATS$SQLTEXTWHEREpiece=0ANDsql_textLIKE'%&sqltext_key%')t, (SELECTaddress,hash_value,snap_id,sql_text,disk_reads,executions, buffer_gets,rows_processed,elapsed_time FROMSTATS$SQL_SUMMARY)s,STATS$SNAPSHOTsn WHEREs.hash_value=t.hash_value ANDs.address=t.address ANDs.snap_id=t.last_snap_id ANDsn.snap_id=s.snap_id;比如，我们以perfstat用户执行该SQL，输入“to_comp”，可以观察到数据库里保存的有的to_comp存储过程的执行时间，我们发现，其执行时间，从优化前的几千秒，最后稳定在优化后的几十秒。
注：to_comp是整体调用执行一次所有网元的数据合并和汇总的procedure。
用于小区分析数据的Procedure的计算性能
使用上面的方法，我们一样可以知道，小区分析的procedure执行，从优化前的约几千秒，最后稳定在优化后的几十秒。
批量数据采集入库性能
使用bcp，能从以前约15分钟，减少到约4分钟。
2、一些单次、不常用的操作的语句执行性能
GUI上的性能数据查询，告警数据查询，响应时间都极快，几乎不再出现长时间等待响应的情况。
五，参考
常用的优化工具
statspack
sql*plus
TOAD