| | |
| | |
一、 为什么要进行数据库优化
数据库优化不仅仅是DBA（数据库管理员）的事情，它也是应用设计人员、应用开发人员
在确认了由谁来进行数据库优化之后，就要考虑从何时开始进行数据库优化。许多人认为
对数据库的优化不急，等到用户开始抱怨系统运行速度无法忍受时，再进行优化。但此时某些
对于熟悉软件工程的人来说，在一个系统的生命周期内，对系统进行调整，想利用较小的
人力、物力而能够收到较好的收益的话，最好在系统的设计和开发期内进行。如果一软件已成
为产品，此时再进行系统调整，则耗费的精力最多，而收益最小。同样，对于数据库的优化，
最好的时期是在系统的设计和开发阶段，尽量避免在一系统成型之后再进行优化。
无论是设计或维护数据库系统，都必须建立专门的性能指标，使人们能够有明确的目标，
知道在何时进行调整。调整一个数据库系统的最有效的步骤如下：
在设计系统时考虑系统的性能
在开发应用程序时考虑系统的性能
调整操作系统的硬件和软件设置
识别系统的性能的瓶颈
确认问题的原因
采取纠正的动作
对于任何一个系统而言，良好设计的系统可以防止在应用生命周期以后产生的性能问题。
同时，每一个系统设计人员和应用开发人员必须了解ORACLE的查询处理机制来编写有效的SQL
语句。以下提出进行系统设计时，应尽量遵循的原则：
消除客户机/服务器应用中不必要的网络传输。例如：使用ORACLE的REPORT时，尽可能对
单表进行处理，不要对多表进行JOIN处理，以免造成不必要的网络传输。
使用适用于自己系统的相应的ORACLE服务器选件（例如：并行查询或分布式数据库等）。
除非系统有特殊的需要，请使用缺省的ORACLE锁，无须自己对应用程序进行加锁处理，以
为了便于对数据库的每个应用进行跟踪调测，尽可能记住每一个用户所运行的模块。便于
在数据库建立时，需从自身的实际出发，建立合适的数据块长度。DB_BLOCK_SIZE
二、 数据库优化过程
调整数据库的性能必须有一个明确的目标，总的来说可以是以下的 几个目标之一或多个
改善指定类型的SQL语句的性能。
改善专门的数据库应用的性能。
改善所有同时应用数据库的用户及其应用的所有性能。
在调整ORACLE性能之前，首先要有一个性能良好的应用设计及高效的SQL语句，在此基础
调整内存分配
调整I/O
调整资源争用
因此，根据上述的原则并根据自己的工作经验，认为对数据库的优化大体上可分为如下几
安装数据库时，对数据库的数据块大小进行确认。此参数在数据库安装之后就不能通过修
改初始化参数进行修改或重新创建控制文件进行修改，要改变该值，唯一的方法是重新安装数
在数据库安装完毕之后，对数据库初始化参数进行修改。一个经过调优过的参数，对一个
系统而言，可作到事半功倍的功效。例如：调整数据库SGA大小，主要是DB_BLOCK_BUFFERS,
SHARE_POOL_SIZE, OPEN_CURSORS, SORT_AREA_SIZE等参数。
调整主机的硬件性能和操作系统的软件性能，使之配合数据库，发挥最大的性能。
进行应用系统的物理设计。
进行应用程序的编写时，对SQL语句的优化。
在试运行时对系统的物理设计以及应用程序的调整。
在系统运行过程中，通过对系统的监控，认识到系统的瓶颈，对系统再进行一次性能调整
数据库优化内容
1. ORACLE系统的准备知识
1) ORACLE数据库系统的数据存储的物理结构和逻辑结构构成
2) 模式对象的组成
3) ORACLE数据库系统的进程以及内存结构构成
4) ORACLE锁的概念介绍
5) 二阶段提交的概念
6) 用户、角色、权限的概念的介绍
7) 举例介绍ORACLE是如何处理一个事务
a 首先必须有一台主机或数据库服务器运行一个ORACLE INSTANCE。
b 一台本地机器或客户端工作站运行一个应用，它试图通过适当的SQLNET驱动同服务器取
c 如果该服务器也正在运行适当的SQLNET驱动。服务器检测到应用的连接请求，开始为此
d 客户端的用户执行一个SQL语句并提交此进程。
e 服务器进程收到此SQL语句，并开始检验在ORACLE的共享池中是否存在同样的SQL语句
。如果在共享池中发现该SQL语句，服务器进程开始检验该用户是否对请求的数据有操作的权
限，然后使用在共享池中的SQL语句去执行该语句。如果该SQL语句在共享池中不存在，就为此
f 服务器进程从实际的数据文件或共享池中取回必须的数据。
g 服务器进程在在共享池中修改数据。在上述所作的生效之后，DBWR后台进程把修改后的
数据块永久的写入硬盘。在此事务提交成功之后，LGWR进程立即把此事务记录到在线的redo
h 如果此事务成功，服务器进程通过网络返回一个成功的信息给应用程序。如果该事务不
i 在上述的事务过程中，其余的后台进程同样在运行，等待着条件符合而被触发。此外，
数据库服务器还管理着其他用户的事务，并且在不同事务之间提供数据一致性，防止不同事务
2. 在安装数据库时作的优化
在数据库安装时作的优化工作主要是关于DB_BLOCK_SIZE参数的设置，该参数决定了
ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改，除非重新安装数 据库。
对于一个应用而言，一般对于一个中型的应用系统，它的DB_BLOCK_SIZE大小为设为4K，而对
于一个较大型的应用而言，它的DB_BLOCK_SIZE一般设为8K或更大一点为16K。
对于一个较大的DB_BLOCK_SIZE，不仅可以加快系统的运行速度，（因为从系统的I/O吞
吐能力来说，一次性读取较多的数据可以比一次性读取较少的数据的的过程减少I/O的读取次
数）而且可以有较大的系统扩展能力。因为对于一个系统而言，在它的DB_BLOCK_SIZE确认之
后，它的最大EXTENT的数目其实也已经确认下来。如果一个系统的扩展能力有限的话，则系统
容易发生显示终止的事情。而就是说，发生ORA错误，导致系统无法正常运转。截止至目前，
在ORACLE7.3之后的版本中，ORACLE在建表空间时，有一个参数autoextent，如果此参数设置
为ON时，ORACLE在达到最大的扩展值时，ORACLE就自动扩展，不再受最大扩展数的限制。现就
DB_BLOCK_SIZE（数据块数目） MAX EXTENTS（最大扩展数）
512BYTES 25
1K 57
2K 121
4K 249
8K 505
3. 在安装之后，在数据库初始化时对INITXXX.ORA文件作的优化
对于SHARE_POOL_SIZE的设置：对于不同的系统根据用户对于内存区的要求，考虑用户是
否需要多少的内存空间存放用户的存储过程或要多少空间存放用户要编译的程序。
对于需要进行大量数据操作的用户可考虑增大用户的DB_BLOCK_BUFFERS的数目，该参数可
以使用户在缓冲区中的数据较大，使用户查找的数据尽可能的在缓冲区中，不要到表中去再次
根据用户的实际需要，设置较好的PROCESS该参数决定能够有多少个用户在系统中运行，
如果该参数设置不当会导致用户无法正常运行。并且该参数与操作系统的有些参数（如
Digital unix的max_proc_per_users）有关，该类型的参数限制了每个用户允许最大多少用户
登录的限制，因为对于我们而言，每一个用户最终都体现为一个ORACLE用户，如果此参数开的
根据用户实际使用系统的SQL语句的多少，决定最终要开的OPEN_CURSORS数目的多少，因
为一个SQL的DML语句就是一个隐含的CURSOR，如果上述参数的数目开的不够大的话，系统会提
对于要进行大量数据分组和排序工作的应用要加大系统的SORT_AREA_SIZE的大小，该参数
决定分配给每一位用户的排序空间，该参数用到系统的内存空间。
为保证系统能够正常运转，要保证系统有足够的DML_LOCKS，如果该值不够的话，会导致
为保证系统能够有足够的数据库链路可用，要保证OPEN_LINKS的数目足够大。
对于会发生CORE DUMP的用户的机器，可考虑设置使该CORE DUMP最终不要形成文件，（
在ORACLE的init参数中为shadow_core_dump=none，默认为full）。因为系统在许多时候由于
文件系统满的缘故，导致系统无法正常运转，最终会导致数据库系统崩溃。
4. 在进行空间设计时作的优化
在一个数据库安装完毕之后，系统中已存在如下表空间，它们分别是：SYSTEM，TOOLS，
RBS，USERS，TEMP等，上述表空间在安装时用户可根据当地的系统的实际情况进行系统表空间
在系统安装时，还应该考虑控制文件和可重作日志文件要尽可能的分配在不经常使用的盘
表空间设计的原则为：把由用户创建的其余表空间同SYSTEM表空间进行分离，把系统的数
据表空间同索引表空间分离，把操作频繁和不经常操作的表划分在不同的表空间中。对于表空
把用户数据与数据字典数据分开。
把不同应用组的数据分别放在不同的表空间中。
为了减少I/O的竞争，把不同表空间的数据文件放在不同的硬盘上。
分离用户数据和回滚段的数据以防止某个磁盘出现故障丢失数据。
控制数据的可用性，例如使一些表空间联机（online）而另一些表空间脱机（offline）
为特殊类型的数据库使用保留某个表空间。例如，高频率的更新操作，只读操作或临时段
在上述原则中，对日常系统最有用的原则是把索引空间同数据表空间分离开来。把它们分
离出来的理由如下：与表相关联的索引会导致I/O和表增长/碎片问题的出现。碎片问题同样要
求将数据段从系统表空间中分离出来。同时对索引的数据操纵和查询时将会由大量的输入/输
出。索引段同样因为不恰当的大小或无法预料的表增长问题而导致碎片。将应用索引分离到一
个单独的表空间中，将大大减少管理数据空间和索引空间碎片问题的工作量。
在数据库的表空间设计时，建议每一个表空间对应的数据物理文件的大小应该小于1G大小
，因为许多系统的操作系统不支持大于2G大小的文件。同时，从数据库的备份角度考虑，对小
文件的备份不仅可以提高备份的速度，也可以提高备份的安全性。
对表空间建立的时候，有一参数应该注意，它就是在建立TABLESPACE时用到的DEFAULT
STORAGE，该参数决定了今后建表或建索引时默认的存储策略（也就是说如果没有修改建表或
建索引的参数时，系统就用该默认参数）。
由于创建表空间要用到数据文件，如果一个系统很大的话，就需要许多的数据文件。而该
数据文件的个数会受到初始化参数或操作系统的限制，因此在创建表空间时，应该考虑系统的
限制，选择有效的参数。对于一个ORACLE数据库而言，它的最大数目受如下三个方面限制，它
们分别是：操作系统能够打开的最大文件数目，db_files的数目，建立数据库时的参数
maxdatafiles数目。在上述参数中，以操作系统的限制为主，其次为maxdataflies，最后为
db_files。也就是说如果操作系统允许打开的文件数为32，而maxdatafiles设置为48的话，系
统认为打开的文件数目最大为32，如果操作系统允许最大的打开文件数目为64，maxdatafiles
数目为32，db_files数目为48，那么系统允许打开的文件最大数目为32，而不是48。
在进行临时表空间的设计时，应该考虑在实际的日常应用中会引起临时表空间扩展的语句
，因为临时表空间的扩展同样会导致应用的终止。在我们日常的应用中如下DDL或DML语句会引
创建索引：create index
在日常的DML语句中存在如下关键字会引起临时表空间扩展：SELECT…DISTINCT,ORDER
没有索引的连接：none-indexed joins
包含关联的子查询：certain correlated subqueries
| |
|
|
| |
| |
|
