《oracle数据库优化方案》由会员分享，可在线阅读，更多相关《oracle数据库优化方案（24页珍藏版）》请在金锄头文库上搜索。
1、详解 Oracle 数据库优化方案与实践时间:2010-02-26 14:04:51 来源: 网络 作者:未知 点击:817次一、前言二、ORACLE 数据库优化概述1、内存等参数配置的优化2、减少物理读写的优化3、批量重复操作的 SQL 语句及大表操作的优化二、ORACLE 数据库优化方案1、内存等 Oracle 系统参数配置2、使用索引3、表分区4、Procedure 优化5、其他改造6、维护作业计划三、ORACLE 数据库优化前后比较1、批量重复的 SQL 语句执行性能2、一些单次、不常用的操作的语句执行性能四、参考1、常用的优化工具2、参考文献一、前言随着实际项目的启动，实际项目中使用
2、的 Oracle 数据库经过一段时间的运行，在线保存的数据量和业务处理的数据量在逐渐增大，最初的 Oracle 设置，与现在实际需要的运行性能有一定差距，需要进行一些优化调整。本文将结合本人实际维护经验，相应地提出实际项目数据处理的一些优化方法，以供参考。适用于 Oracle 9i。二、Oracle 数据库优化概述Oracle 数据库的优化，针对不同的应用，会有侧重点不同的优化方法，根据我们实际项目的应用特点，我们主要关心的是每次事务执行完成的时间长短。从 Oracle 数据库本身的特点，我们可以把优化工作划分为初始优化设置，微优化。在初始优化设置时，我们只能根据硬件情况，估计业务运行的情况，
3、综合经验，给出一种经验设置，大体上来说，这种经验设置离满足优化需求的目标不是很远。在完成了初始优化设置后，经过一段时间的业务运行，已可开始收集实际运行环境的性能数据，此时，就可以对各种 Oracle 性能指标、各种关心的事务操作进行性能评估，然后进行微优化了。Oracle 优化，不是一个一蹴而就的工作，也不是一个一劳永逸的工作，需要定期维护，定期观察，在发现性能瓶颈时及时进行调整。Oracle 总是存在性能瓶颈的，不使用、不操作的数据库总是最快的，在解决当前瓶颈后，总是会有另一个瓶颈出现，所以在优化前，我们需要确定一个优化目标，我们的目标是满足我们的应用性能要求就可以了。Oracle 优化，涉
4、及的范围太广泛，包含的有主机性能，内存使用性能，网络传输性能，SQL 语句执行性能等等，从我们面向网管来说，满足事务执行速度性能主要表现在：1）批量重复的 SQL 语句执行性能（主要是通过 Procedure 计算完成数据合并和数据汇总的性能和批量数据采集入库的性能） ；2）一些单次、不常用的操作的语句执行性能（主要是 GUI 的非规律操作） 。根据这两个特点，我们可把优化方法归纳到 3 个重要方向：1）内存等参数配置的优化。内存优化，是性能受益最快的地方。2）减少物理读写的优化。内存逻辑 I/O 操作的时间，远远小于物理 I/O 的操作时间。3）批量重复操作的 SQL 语句及大表操作的优化。
5、减少 SQL 执行次数，减少大表操作次数。下面主要针对得益最大的这三个方向的优化进行阐述。1、内存等参数配置的优化对于大多数应用来说，最直接、最快速得到优化收益的，肯定属于内存的优化。给每个Oracle 内存块分配合理的大小，可以有效的使用数据库。通过观察各种数据库活动在内存里的命中率，执行情况，我们能很快的掌握数据库的主要瓶颈。我们从下面的一条 SQL 语句的执行步骤就可知道。一个 SQL 语句，从发布到执行，会按顺序经历如下几个步骤：1）Oracle 把该 SQL 的字符转换成它们的 ASCII 等效数字码。2）该 ASCII 数字码被传送给一个散列算法，生成一个散列值。3）用户 serv
6、er process 查看该散列值是否在 shared pool 内存块中存在。若存在：4）使用 shared pool 中缓存的版本来执行。若不存在：4）检查该语句的语义正确性。5）执行对象解析（这期间对照数据字典，检查被引用的对象的名称和结构的正确性） 。6）检查数据字典，收集该操作所引用的所有对象的相关统计数据。7）准备执行计划，从可用的执行计划中选择一个执行计划。 （包括对 stored outline 和materialized view 的相关使用的决定）8）检查数据字典，确定所引用对象的安全性。9）生成一个编译代码（P-CODE） 。10）执行。这里，通过内存的合理分配，参数的合
7、理设置，我们主要解决：1）减少执行到第五步的可能，节约 SQL 语句解析的时间。第五步以后的执行过程，是一个很消耗资源的操作过程。2）通过内存配置，尽可能让 SQL 语句所做的操作和操作的数据都在内存里完成。大家都知道，从内存读取数据的速度，要远远快于从物理硬盘上读数据，一次内存排序要比硬盘排序快很多倍。3）根据数据库内存活动，减少每个内存块活动的响应时间，充分利用每个内存块，减少内存 latch 争用发生的次数。2、减少物理读写的优化无论如何配置 Oracle 数据库，我们的网管系统，每小时周期性的都会有新数据被处理，就会发生物理读写，这是避免不了的。减少物理读写的优化，一般所用的方法有：1
8、） 增加内存 data buffer 的大小，尽可能让数据库操作的数据都能在内存里找到，不需要进行物理读写操作。2） 通过使用索引，避免不必要的全表扫描。3） 大表物理分区，Oracle 具有很好的分区识别功能，减少数据扫描范围。上述 3 个方法，是从整体上改善数据库物理 I/O 性能最明显的 3 个方法。能非常快速的减少数据库在物理 I/O，最直接的反应是数据库事务执行时间能能以数量级为单位减少。其他的一些减少物理读写的优化方法，比如使用 materialized view，Cluster 等方法；还有一些分散 I/O 的方法，比如 Oracle 日志文件不与数据文件放在一个物理硬盘，数据热
9、点文件物理 I/O 分开等等方法，就目前我们的网管系统而言，能得到的效果不是很明显，在网管系统中，为了不增加数据库维护的复杂性，不推荐使用。3、批量重复操作的 SQL 语句及大表操作的优化批量重复执行的 SQL 语句，一般出现在每个周期时间内的数据批量入库的 insert 语句，和数据合并、汇总的周期性 select、delete 、insert 操作。我们需要注意以下几点：1） 减少不必要的 SQL 语句执行和 SQL 语句的执行次数。每条 SQL 语句执行，都会消费系统资源，都有执行时间。减少不必要的 SQL 语句执行和减少 SQL 语句的执行次数，自然能减少业务执行时间。需要根据业务流程
10、，重新设计数据处理的代码。此方法主要适用于 procedure 执行的数据合并、汇总。2） 这些 SQL 语句，由于每个 SQL 语句都要执行很多次，应该尽量让该 SQL 的散列值在shared pool 内存块中存在。也就是使用动态 SQL，避免 SQL 硬解析。可通过 Oracle 参数的设置，和动态 SQL 语句的应用，通过绑定变量的方式，减少 SQL 语句的解析次数。3）减少大表的操作，确保在一次事务中，同类操作只对大表执行一次。主要在数据合并和数据汇总的 pprocedure 和数据采集时出现三、Oracle 数据库优化方案1、内存等 Oracle 系统参数配置Oracle 的 pa
11、rameter 参数，分动态参数和静态参数，静态参数需要重新启动数据库才能生效，动态参数不需要重新启动数据库即可生效。Oracle 9i 可以使用 spfile 的特性，使用 alter system set 参数名=参数值 scope=bothspfile;的方法进行修改。也可以直接修改 pfile。以下给出了网管 Oracle 数据库重点关注的 parameter 的初始优化设置。最大可使用的内存 SGA 总和静态参数 sga_max_size=物理内存的大小减 1.5GShared pool动态参数 shared_pool_size= 600 800 M静态参数 shared_pool_
12、reserved_size= 300 M动态参数 open_cursors= 400 600静态参数 cursor_space_for_time= TRUE静态参数 session_cached_cursors= 60 100动态参数 cursor_sharing= SIMILARData buffer动态参数 db_cache_advice= READY动态参数 db_cache_size动态参数 Db_keep_cache_size动态参数 db_recycle_cache_size（sga_max_size 大小，除了分配给所有非 data buffer 的 size，都分配给 data
13、 buffer）Sga other memory动态参数 large_pool_size= 50 M静态参数 java_pool_size= 100 M动态参数 log_buffer= 3 MOther 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_
14、multiblock_read_count= 16静态参数 dbwr_io_slaves= 0静态参数 db_writer_processes= 3静态参数 undo_management= AUTO动态参数 undo_retention= 72002、使用索引我们初步定义，表数据超过 1000 行的表，我们都要求使用索引。 （不区分事务操作的数据在表数据中所占的比例）索引所包含的字段不超过 4 个。检查 SQL 语句是否使用了索引，我们使用 execute plan 来看，获得 explain 的方法，我们通过 SQL*PLUS 工具，使用如下命令进行查看：setautotraceonset
15、autotracetraceonlyexplain settimingon 或通过 SQL*PLUS trace，然后查看user_dump_dest 下的跟踪文件，使用 tkprof 工具格式化后阅览。altersessionsetevents10046tracenamecontextforever,level12; altersessionsetevents10046tracenamecontextoff; SELECTp.spid,s.usernameFROMv$sessions,v$processpWHEREs.audsid=USERENV(sessionid)ANDs.paddr=p
16、.addr;3、表分区在网管数据库里，比较突出的大表有小区表和告警表。性能表，使用范围分区。告警表，使用 range-hash 的混合分区和范围分区。范围分区以时间点 starttime 为分区字段，混合分区增加 ALARMNUMBER 为字段的 hash子分区。同时，创建本地分区索引。4、Procedure 优化1）取消地市一级的 Procedure，只保留其上层调用 Procedure，并保持参数输入方法，调用方法不变。2）确保大表数据查询操作只有 1 次，确保大表数据删除只有一次。3）确保单条 SQL 语句执行已优化。4）减少 SQL 执行次数。5、其他改造修改表存储参数，提前预先分配 extents。修改表空间存储参数（采集表空间所用块设置为大块，比如 32k 一个块；修改ptcfree,pctused,pctincrease 等） 。避免使用唯一索引和非空约束。创建合理的索引。各模块 SQL 语句优化，比如使用提示固定索引等。确认每一条历史数据删除语句已优化和删除方法。临时表的使用。6、维
