针对Oracle数据库优化策略

一、Oracle数据库系统安装配置及基础优化

1. 操作系统安装

• 操作交换区

交换区是Oracle的一项基本的要求。可以根据Oracle的发行要求来确定。一般交换区大小的要求是该服务器内存的2倍至4倍之间。过小的交换区可能导致Oracle系统安装的失败，所以建议交换区最好是内存的4倍为佳。

• 硬盘格式化的考虑

在安装操作系统时，安装程序回提示将硬盘化分为不同大小的部分。在安装操作系统时就开始考虑哪个硬盘是用来安装Oracle系统的，哪个是用来存放数据文件的等。建议用于存放Oracle数据库系统的目录一定比Oracle系统发行要求的2倍以上；其次就是考虑Oracle数据库系统的数据文件的目录所对应的硬盘的大小。Oracle系统所在硬盘建议不要与其他的软件混在一起。

     2、Oracle系统安装

•      安装类型方案

采用自定义安装进行Oracle数据库系统的安装，这样考虑根据需要定义包括字符集、数据库块的大小、数据文件的大小等。

• 安置路径的考虑

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

• SYSTEM表空间对应数据文件

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

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

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

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

为回滚表空间建立较大的数据文件。最好数据文件在300MB至500MB之间，如果不够在完成安装后再进行扩展。但是不要采用默认值。

• 日志文件的大小

日志文件的大小对于Oracle系统的运行也是相当重要。默认值是太小。建议日志文件大小在10MB至50MB左右。

• 控制文件的大小

建议在该目录应该有200MB 以上空间。

• 数据库块的大小

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

• 关于创建多个Oracle实例问题

建议不要在同一台服务器上创建两个以上的数据库实例。因为每个数据库实例在启动后都回占用大量的内存和CPU时间。如果有多个不同的应用系统，只要分别为不同的应用系统建立的表空间即可。

• Oracle系统有关文件的保护

将所有文件，特别是数据文件、控制文件几次日志文件的设置为不可删除的状态。避免误删除。

• 避免新用户使用默认system系统表空间

修改SYS和SYSTEM帐号的口令。

避免任何人都可随意创建用户。

创建的新用户，一定要为其指定默认的表空间。

二、Oracle用户权限管理

• Oracle数据安全策略

1. 建立系统级的安全保证
   系统级特权是通过授予用户系统级的权利来实现，系统级的权利（系统特权）包括：建立表空间、建立用户、修改用户的权利、删除用户等。系统特权可授予用户，也可以随时回收。
2. 建立对象级的安全保证
   对象级特权通过授予用户对数据库中特定的表、视图、序列等进行操作（查询、增、删改）的权利来实现。
3. 建立用户级的安全保证
   用户级安全保障通过用户口令和角色机制（一组权利）来实现。引入角色机制的目的是简化对用户的授权与管理。做法是把用户按照其功能分组，为每个用户建立角色，然后把角色分配给用户，具有同样角色的用户有相同的特权。

• Oracle用户管理

ORACLE用户管理的内容主要包括用户的建立、修改和删除

1. 用户的建立
   CREATE USER jxzy
   IDENTIFIED BY jxzy_password
   DEFAULT TABLESPACE system
   QUATA 5M ON system; //供用户使用的最大空间限额
    
2. 用户的修改
   CREATE USER jxzy
   IDENTIFIED BY jxzy_pw
   QUATA 10M ON system;
3. 删除用户及其所建对象
   DROP USER jxzy CASCADE; //同时删除其建立的实体

• Oracle角色管理

在ORACLE中，授予系统用户以下DBA、CONNECT、RESOURCE三种角色。

三、Oracle数据库逻辑结构

• 系统表空间

system表空间用于存放ORACLE系统的有关信息，一般的用户建立的对象（object）不要放在system表空间中。保护SYSTEM表空间，增加SYSTEM表空间大小。另外，由于oracle 日常的运行会在system表空间中存储一些信息，如表名，索引名，以及审计信息等，所以需对system表空间增加一些新的空间，如：

确认没有用户使用SYSTEM表空间作为缺省表空间：

如果已经有个别用户的用了SYSTEM表空间，则需要进行及时的处理。包括：

* 修改该用户的缺省表空间；

* 备份该用户的对象和移动数据。

确认没有用户使用SYSTEM表空间作为临时表空间：

如果有用户使用SYSTEM 表空间当临时表空间，则要用下命令加以阻止：

• 数据表空间和索引空间分开

与前面提到的一样，较大的应用系统一般都需要将数据和索引分开存储，这样可以提高系统运行速度，比如为“产品设计制造系统”规划表空间，可以建如下数据表空间和索引表空间：

 

create  tablespace  Desg_Deve_data

datafile  /oracle_home/……  Desg_Deve_data01.dbf

size  500m;  /* 数据表空间*/

 

create  tablespace  Desg_Deve _indx   datafile

       /oracle-home/…  Desg_deve_indxd1.dbf

size  100m;  /*索引表空间*/

 

如果已将索引与数据存放于一个表空间内，可以用下面命令将索引迁移到索引表空间中：

alter  index  emp_no  rebuild

tablespace  Desg_deve _indx

storage (initial  2m  next  2m);

 

1. 临时表空间设计规划

临时表空间主要用于查询操作中的distinct , union , order  by以及create        index 操作。Oracle缺省表空间为Temp，其大小为1MB，显然对于一个真正的中、大型应用系统是不够的，因此需总设计师或DBA作如下工作：

1.增加Temp表空间

alter  table  space  temp  datafile

'/oracle_hime/…/temp02.ddf'  size  100M;

2.指定用户缺省使用temp空间

1） 建立用户时指定该用户缺省使用表空间Temp

create  user  user_name  identified  by  pass_word

default  tablespace  tablespace_name

temporary  tablespace  TEMP;

2） 已建立的用户，将其缺省临时表空间为Temp：

alter  use  user-name

temporary  tsble  space  Temp;

3.为应用系统建立专门的大的临时表空间，用于进行系统的月报、季报、年报统计等。

处理比如为产品设计与开发系统建立一个大的临时表空间prod_temp

1) create  table  space  prod_temp

   datafile  '/…/…/prod_temp_data.dbf '  size  200M;

2) alter  tablespace  prod_temp      temporary;

• 何时创建索引

在利用索引的情况下，由于只从表中选择部分行，所以能够提高查询速度。对于只从表中查询出总行数的2%到4%行的表，可以考虑创建索引。下面是创建索引的基本原则：

1. 对于所有分配给 表的数据块，经常以查询关键字为基础，并且表中的行遵从均匀分布；
2. 以查询关键字为基础，表中的行为随机排序；
3. 表只能感包含的列数相对比较少；
4. 表中的大多数查询都包含相对简单的WHERE从句；
5. 缓存命中率较低，并且不需要操作系统缓存。

• 索引创建原则

1. 需要平衡query和DML的需要，常用于(子)查询的表应建立索引；
2. 把索引建到不同的表空间中；
3. 用统一的extent大小: 五个block的倍数或者tablespace指定的MINIMUM EXTENT的倍数；
4. 创建索引考虑用NOLOGGING参数，重建索引的时候也一样；
5. 创建索引时INITRANS值应该比相应的table的值高一些；
6. 对常用SQL语句的where条件中的列建立唯一索引或组合索引，组合条件查询中相应的组合索引更有效；
7. 对于组合索引，根据列的唯一值概率，安排索引顺序；
8. 如果一个列具有很低的数据基数，并且或者可具有空值，不应作为索引列；
9. 如果where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应函数索引；
10. 对于低基数集的列，并包含OR等逻辑运算，考虑用Bitmap索引，对于从大量行的表中返回大量的行时也可以考虑Bitmap索引；
11. 避免在有大量并发DML运算的表中使用Bitmap索引；

• 表和索引的空间预分配

在设计Oracle应用时，我们经常采用在分析阶段得到的数据来估计空间的需要量。这样当然是由用户给出的，我们一般都是根据经验来决定。虽然是经验，但也有一个基本的规则。这些规则就是：以用户给出的各种表的每个字段的类型及长度、各表的年数据量（不要以月来估计）、加上相应的余量后得到表及索引的预分配空间大小。这里所谓的预分配就是说先给每个对象分配一个估计的空间。在以后运行一段时间后在进行调整。

另外建议是：在对象的存储空间分配中，不要使用pctincrease 这样的参数，最好是一次就分配够一年的数据量，而且下次扩展（NEXT 参数）也要分配以半年左右的数据量为基本值。这样对于性能有好处。

四、Oracle数据库物理结构

• 数据文件

1. Oracle的数据文件是用来存放实际数据的物理文件；
2. Oracle数据文件必须对应于一个表空间；
3. Oracle系统安装完成并创建数据库成功后，Oracle会自动创建几个数据库文件。它们被分配给SYSTEM、USERS、TEMP等表空间；
4. 用户可以根据需要创建表空间时创建一个或多个数据文件；
5. 对已经创建完成的数据文件可以用ALTER ADTABASE … AUTOEXTEND 、RESIZE来改变数据文件的大小。
6. 数据文件可用下面命令查到：

select tablespace_name,file_name ,bytes from dba_data_files;

• 控制文件

1. Oracle数据库至少有一个控制文件；
2. 一般数据库系统安装完成后，自动创建两个以上控制文件；
3. 为了安全一般建议创建多个控制文件；
4. 控制文件可用下面命令查到：

 

select name,value from v$parameter where name like  'control_files';

 

• 数据文件和日志文件在不同磁盘上

数据文件的写入是通DBWR后台进程实现，日志文件的写入是通过LGWR后台进程实现，由于日志文件是连续的写入，因此无并发处理现象。而数据文件的写入相对是随机的，为避免在同一时间内DBWR和LGWR的冲突，应将日志文件和数据文件放在不同的硬盘上，如果一定要将数据文件与日志文件存在同一磁盘，则数据文件不应当属system RBS 表空间，也不应当属于非常活跃的DATA，IDEX表空间。否则将可能导致与日志文件的直接冲突，并且增加日志文件被数据库操作破坏的可能性。

另外，在ARCHIVELOG模式时，数据库会在覆盖（第一个）日志文件之前将其进行拷贝（即写入磁盘或磁带），这些拷贝操作由ARCH后台进程执行。当数据库正在处理频繁的事务时也产生日志文件间的磁盘冲突，这是由于LGWR总是在当ARCH试图读取另一个日志文件时，正在同时写入一个日志文件，这种冲突的避免只能通过将日志文件分配到多个磁盘才能解决。因此在ARCHIVELOG模式运行一个面向事务的数据库时，要避免LGWR与ARCH之间的冲突，可以将日志文件分配到多个设备中去。

 五、Oracle数据库内存结构调整(SGA)

• SGA的基础结构

系统全局区（System Global Area）Oracle 系统用于存放系统信息的一块存储区域，用户进程和Oracle后台进程都可以使用SGA.在SGA中含有如下组件：

1、数据高速缓冲区（Data Buffer Cache）
在数据高速缓冲区中存放着Oracle系统最近使用过的数据块（即用户的高速缓冲区），当把数据写入数据库时，它以数据块为单位进行读写，当数据高速缓冲区填满时，则系统自动去掉一些不常被用访问的数据。如果用户要查的数据不在数据高速缓冲区时，Oracle自动从磁盘中去读取。数据高速缓冲区包括三个类型的区：
1） KEEP 缓冲池（KEEP buffer pool）：在内存中保留数据块，它们不会被从内存中挤掉；2） RECYCLE缓冲池从不需要的内存将数据移掉；3） DEFAULT缓冲池包含有被分配的块。

2、重做日志缓冲区（Rado Log Buffer）
任何事务（Transaction）在记录到重做日志（恢复工作需要使用联机重做日志）之前都必须首先放到重做日志缓冲区（Redo Log Buffer）中。然后由日志写入进程（LGWR）定期将此缓冲区的内容写入重做日志中。
3、共享池（Shared Pool）
共享池是SGA保留的区，用于存储如SQL、PL/SQL存储过程及包、数据字典、锁、字符集信息、安全属性等。共享池包含有：1） 库高速缓存（Library Cache）；2） 字典高速缓冲区（Dictionary Cache ）。

4、库高速缓存（Library Cache）
该区包含有：1） 共享SQL区（Shared Pool Area）；2） 私有SQL区（Private SQL Area）；3） PL/SQL存储过程及包（PL/SQL Procedure and Package）；4） 控制结构（Control Structure）。也就是说该区存放有经过语法分析并且正确的SQL语句，并随时都准备被执行。

5、字典高速缓冲区（Data Dictionary Cache）
用于存放Oracle系统管理自身需要的所有信息，这些信息是登录到Oracle的用户名，这些用户有那些数据库对象以及这些数据库对象的位置等。

• SGA的调优

公式：OS 使用内存+ SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM 为好

log_buffer : 128K ---- 1M 之间通常问题不大，不应该太大

large_pool_size :如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在10M --- 50M 应该差不多了。

java_pool_size : 若不使用java，给30M通常就够了

data buffer ,在做了前面的设置后，凡可以提供给oracle的内存，都应该给

data buffer = (db_block_size * db_block_buffers)

不能设置 shared_pool_size 过大，通常应该控制在200M--300M

　　

再具体化，注意满足上面的原则的基础上可以参考如下设置

物理内存	shared_pool_size	data buffer
512M RAM	50M	200M
1G RAM	100M	500M
2G	150M	1.2G