Oracle性能优化学习心得byLYH

Oracle性能优化学习心得

一，优化总的原则

1，查看系统的使用情况

2，查看SGA分配情况，结合系统具体情况进行分析。

3，表的设计分析

4，SQL语句分析

实施要则

1，查看系统的使用情况，CPU占用，内存，I/O读取等

Oracle10G提供的Oracle Enterprise Manager图形化工具中的ADDM 和SQL Tuning Advisor等可以方便的查看系统状况

2，OPS上负载均衡，不同查询用不同Instance

3，提供脚本查看SGA使用情况

4，分析SQL执行情况（trace及其他工具）

实施细节

1，外部调整：我们应该记住Oracle并不是单独运行的。因此我们将查看一下通过调整Oracle服务器以得到高的性能。

2，Row re-sequencing以减少磁盘I/O：我们应该懂得Oracle调优最重要的目标是减少I/O。

3，Oracle SQL调整。Oracle SQL调整是Oracle调整中最重要的领域之一，只要通过一些简单的SQL调优规则就可以大幅度地提升SQL语句的性能，这是一点都不奇怪的。

4，调整Oracle排序：排序对于Oracle性能也是有很大影响的。

5，调整Oracle的竞争：表和索引的参数设置对于UPDATE和INSERT的性能有很大的影响。

二，调优分类：

对Oracle数据库进行性能调整时，应当按照一定的顺序进行，因为系统在前面步骤中进行的调整可以避免后面的一些不必要调整或者代价很大的调整。一般来说可以从两个阶段入手：

1、设计阶段：对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下,系统性能达到最佳，系统开销达到最小；

2、数据库运行阶段：采取操作系统级、数据库级的一些优化措施来使系统性能最佳；

㈠设计阶段：

A，数据库设计优化

较多修改较少查询的数据和较多查询较少修改的数据分别对待。

a，结构优化

1，根据应用程序进行数据库设计。

即应用程序采用的是传统的C/S两层体系结构，还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。

2，遵循3大范式规范化数据结构，减少不必要的冗余。

3，反规范设计，增加必要冗余，提高查询速度。

4，针对变化较少的数据，合理创建临时表和视图，需注意对临时表和视图的及时同步更新

5，在服务器端使用过程和函数，减少网络数据传输

b，I/O优化

1，使用分区，对查询条件字段（主外键）建立索引，依据Rowid物理地址查询，减少I/O 的消耗。应避免可能参与计算的字段

2，建立合适的索引。

缺省情况下建立的索引是非群集索引，但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。一般来说:

①.有大量重复值、且经常有范围查询(between, >,< ，>=,< =)和order

by、group by发生的列，可考虑建立群集索引;

②.②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引;

③.③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最

频繁的列。

a,定期的重构索引是有必要的.

ALTER INDEX REBUILD

b,避免在索引列上使用计算，判断或函数处理

3，使用聚簇（对列的选择原则同索引）。

4，使用存档功能。

5，数据缓冲，快照，视图，远程调用等，减少I/O和网络消耗

6，I/O调整

这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。在磁盘比较富裕的情况下还应该遵循以下原则：

将表和索引分开；

创造用户表空间，与系统表空间（system）分开磁盘；

创建表和索引时指定不同的表空间；

创建回滚段专用的表空间，防止空间竞争影响事务的完成；

创建临时表空间用于排序操作，尽可能的防止数据库碎片存在于多个表空间中。

7，确定数据块大小和存储参数。

由于数据库的块大小在数据库创建以后就不能在修改（除非重建数据库），因此为了减少数据链接和行迁移，又提高磁盘空间的利用率，在设计数据库时要确定合适的数据块大小和存储参数。通常我们是根据样例数据确定数据块大小，而根据业务现状和未来发展趋势确定存储参数。

㈡运行阶段：

B，系统优化

许多新手都错误的认为应该首先调整Oracle数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的Oracle调整都是没有帮助的。

1，多处理器系统调整优化，启用并行查询。

2，减少内存交换，减少分页，使SGA(System Globle Area)可留驻内存。

3，优化系统I/O环境。

4，优化网络环境

C，数据库连接与网络优化

1，使用直接OLE DB连接。

2，使用Connection Pool（数据连接池）机制

3，优化程序设计，减少网络传输量

D，Oracle参数配置优化

Oracle参数设置，根据具体应用情况而定。

1，SGA的分配及使用效率优化

分配给每个实例的内存，即SGA的使用效率如何，会大大影响数据库系统的性能。SGA 由下列部分组成：共享池（Shared pool）、数据块缓冲区（Db block buffer）、重做日志缓冲区（Log buffer）、大池组成（Large pool）。

2，使用SQL*Loader Direct Path选项进行大量数据装载

3，Database Resource Manager（DRM,数据库资源管理器）来控制用户的资源分配

4，减少资源竞争

在Oracle中，需要采用一些机制来保证数据库对象在使用期间的稳定性和数据的一致性，如使用锁存器(latch)、锁(lock)等。因此争用和这些机制相关的资源会影响数据库的性能。为了减少这种资源竞争，可以通过调整数据库的相关初始化参数（如db_block_lru_latches、dml_locks）来减少资源的争用，优化数据库性能。

在对Oracle数据库进行优化时，需要用到许多的参数，其中有一部分参数对系统性能影响较大，这部分参数叫可变参数。可变参数按其作用可以分为两大类，一大类是起限制作用的，如OPEN_CURSORS;另一大类是影响系统性能的，如DB_BLOCK_BUFFERS。

5，可变参数优化

在对Oracle数据库进行优化时，需要用到许多的参数，其中有一部分参数对系统性能影响较大，这部分参数叫可变参数。可变参数按其作用可以分为两大类，一大类是起限制作用的，如OPEN_CURSORS;另一大类是影响系统性能的，如DB_BLOCK_BUFFERS。

在进行数据库系统性能优化时，需要熟练掌握和了解一些可变参数。本文讨论了一些对系统性能有较大影响的参数。

限制类可变参数

(1)DML_LOCKS

该参数表明多少个用户，可同时能修改多少张表。例如:有三个用户同时修改二张表，则要求表上的总数为6。若置为0，则组织队列不起作用，其性能会稍有提高。使用该参数时不能用DROP TABLE、CREATE INDEX或显式封锁。

(2)LICENSE_MAX_SESSION

该参数指出允许并发用户会话的最大数。若此参数为0，则不能实施并发。若并发的用户会话数已达到此极限，则只有具有RESTRICTED SESSION权限的用户才能连接到服务器。

(3)LICENSE_MAX_USERS

该参数指出在一个数据库上可建立的最大用户数。当达到最大值时，便不能再建新用户，可改变此值以放松限制。在LICENSE_MAX_SESSION或LICENSE_MAX_USER为0时，则并发会话或任何用户都不能用。若对不同的实例，此参数不同时，则以第一个登录的数据库实例的参数为准。

(4)MAX_DUMP_FILE_SIZE

该参数指定操作系统中写跟踪文件的块的最大值。可用此值来限制跟踪文件的空间。

(5)OPEN_CURSORS

该参数指明一个用户进程能同时打开光标的最大数，它能限制每个用户进程占用的内存