Oracle优化配置指南

Oracle的优化配置是非常重要的，一个合适的优化配置，可以让系统性能提高数倍；而一个不合适的配置，会导致你的系统问题不断。默认安装的Oracle是没有经过任何优化配置的，这样的配置，在自己机器上还凑合，但是千万不要把它放到用户的服务器上。
   这篇指南首先介绍Oracle的内存结构，接下来介绍如何查看和修改内存参数，最后再介绍一下其他的优化参数。

对Oracle进行优化配置，可以通过Oracle控制台，也可以通过命令行。通过命令行进行配置有几个好处，

一是可以不用输入用户名和密码，这在你忘记密码的时候很有用（只有在Oracle所在机器上才可以这样）；

二是有的时候数据库出问题了，并不能通过Oracle控制台连接到Oracle，但通过命令行有可能连接上；

三是可以显得我们更专业， 不是吗？当我们在命令行里输入一些命令，系统给我们返回一打文字，很像电影里的黑客一样。

2.1使用命令行登陆

打开开始--运行菜单，输入cmd，就可以打开一个dos窗口。

 

命令行的命令是这样的：

sqlplus  <用户名>[/<密码>][@<服务名>] as sysdba

一般我们使用sys或system用户登陆进行修改，我们输入以下命令：

sqlplus sys/huhao@sa as sysdba   

 

2.2更简单的登录命令

如果忘记了密码，或者想简单一点，可以直接输入以下命令：

sqlplus / as sysdba

 

这种方式省略了用户密码和服务名，在你忘记密码的时候非常有用，但是必须在Oracle所在的服务器上才能这样（想想看，如果在哪都能这样，那Oracle哪里还有安全性可言？）。

如果Oracle所在的服务器上安装了Oracle服务器和客户端，或者安装了多个Oracle服务器，那么在使用这个简单的命令之前，必须先设置一下oracle_sid（也就是服务名）的值，否则会出现如下图的错误：

出现这个错误是可以理解的，服务器上有多个oracle_sid，你没有指定是哪个，sqlplus当然不知道要去连哪个了。

这个命令的语法是： 

 

set oracle_sid=服务名

 

现在我们输入以下命令：

set oracle_sid=sa
sqlplus / as sysdba

 2.3 alter system

alter system命令用来修改Oracle的一些参数，我们修改Oracle的内存配置时就要用到它。

alter system 有一个scope选项，它有三个可选值：memory，spfile，both。

memory:只改变当前实例的参数，如果重启Oracle，则会恢复到修改前的值。

spfile:只改变spfile的参数，在Oracle重启后会使用修改后的值。spfile是指Oracle启动时用到的配置文件，一些参数都保存在这个文件里，Oracle在启动时读取这个文件并进行相应的初始化设置。

both:改变实例及spfile的参数。

3. Oracle内存配置
为了能让大家更清楚地了解Oracle的内存结构体系，我们先来看看Oracle数据库的总体结构是什么样的。

3.1 Oracle的总体结构

每 个Oracle数据库都是由Oracle Instance(实例)与数据库(数据文件，控制文件、重做日志文件)组成，其中所谓的实例就是用户同数据库交互的媒介，用户通过与一个实例相连来操作 数据库。而实例又是由统一的内存结构(SGA，PGA，UGA)和一批内存驻留进程组成。实例在操作系统中用ORACLE_SID来标识，在Oracle 中用参数INSTANCE_NAME来标识，它们两个的值是相同的。数据库启动时，系统首先在服务器内存中分配系统全局区(SGA)，构成了Oracle 的内存结构，然后启动若干个常驻内存的操作系统进程，即组成了Oracle的进程结构。内存区域和后台进程合称为一个Oracle实例。
实例结构图如下：
 

3.2 SGA
系统全局区--System global area(SGA) 是一组共享的内存结构，它里面存储了oracle数据库实例（instance）的数据和控制文件信息。如果有多个用户同时连接到数据库，他们会共享这一 区域，因此SGA也称之为Shared Global Area。当数据库实例启动时，SGA的内存被自动分配;当数据库实例关闭时，SGA内存被回收。
SGA是占用内存最大的一个区域，同时也是影响数据库性能的重要因素。
SGA主要包括以下几部分：
 共享池
 数据缓冲区
 大型池
 Java池
 日志缓冲区
上面几部分内存加起来，就是SGA内存的总和。其中比较重要的是共享池和数据缓冲区，后面将会重点讲解它们。

3.2.1 查看SGA
SGA是可以动态调整大小的，也 就是说调整其大小是不用shutdown数据库的。在初始化参数中设置可以设置sga_max_size这个参数，当SGA的各部分的和要大于设置的 sga_max_size的参数的时候，设置的sga_max_size将会被忽略掉，而是将各部分的大小相加。当sga_max_size的大小大于各 部分的大小相加时，会使用sga_max_size的参数。
SGA是占用内存最大的一个区域，同时也是影响数据库性能的重要因素。
通过下面的命令来查看SGA：
show parameter sga;

结果如下图：
 
sga_max_size的值就是SGA的大小，从上图可以看出sga_max_size的大小是256M。

3.2.2 修改SGA
要修改SGA的大小，应使用以下命令：
alter system set sga_max_size=164M scope=spfile;
这里我修改为164M，你可以改成你想要的大小。由于sga_max_size是一个静态参数，不能够在运行时修改，因此这里的scope只能设置为spfile，新的内存大小将在Oracle重启后生效。
结果如下图：
 

3.2.3 共享池
共享池是对SQL，PL/SQL程序进行语法分析，编译，执行的内存区域。
它包含三个部分：
 库缓冲区(Library Cache)包含SQL，PL/SQL语句的分析码，执行计划；
 数据字典缓冲区(Data Dictionary Cache)表，列定义，权限；
 用户全局区(Usr Global Area)用户MTS会话信息。
这三个部分都不可单独定义大小，必须通过共享池间接定义。
你可能会问，为什么要缓存SQL语句呢？不缓存又能怎么样呢？要想搞清楚这个问题，我们要先了解一个SQL在Oracle里的执行过程。

3.2.3.1 SQL执行过程
在Oracle里执行一个SQL语句，一般都要经过下面几个步骤：
 Create a Cursor 创建游标；
 Parse the Statement 解析语句；
 Bind Any Variables 绑定变量；
 Run the Statement 运行语句；
 Close the Cursor 关闭游标；
如果是一个查询SQL，则还要经过下面的步骤：
 Describe Results of a Query 描述查询的结果集；
 Define Output of a Query 定义查询的输出数据；
 Fetch Rows of a Query 获取查询出来的行。

3.2.3.2 SQL解析过程
从上面的步骤可以看出，每执行一个SQL，都需要对它进行解析（Parse），而一个解析过程，需要完成下面的工作：
 语法检查，验证它是否是合法的语句，有没有语法错误；
 语义检查，实现数据字典的查找，以验证是否符合表和列的定义，类型是否正确；
 （如果是CBO优化模式，关于CBO，请看后面Oracle的优化器一章）收集参考对象的统计；
 在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义；
 检查用户的权限是否足够；
 从许多可能的执行路径中选择此语句最佳的执行计划；
 将它装入共享SQL区；
 生成语句的编译版本（P-CODE）。
解析是一个昂贵的操作，因为解析过程中需要消耗许多资源，而且费时，正因为如此，Oracle创造了共享池的概念，共享池会自动将解析过的SQL缓存起来，以后碰到相同的SQL，就不用再解析了，这样可以大大提高SQL的执行速度。

3.2.3.3 缓存SQL的原理
ORACLE 执行SQL语句时，先将SQL语句的字串通过一个hash算法得出一个hash值，然后检查共享池中是否已存在这个哈希值，若有就用已缓存的执行计划来执 行这个语句（即缓存命中，后面我们会提到共享池的命中率，就是这个概念），若没有（即缓存缺失）则需进行解析。
由于Oracle是通过SQL字符的hash值来判断是否为相同的SQL语句，因此，如果你的SQL有一点小小的变换，在Oracle看来，就是另外一个SQL了，会对它进行重新解析。
例如：

1. select id, name from members where id = 1403   
2.  select id, name From members where id = 1403   
3.  select name, id from members where id = 1403  

这三条SQL在Oracle看来就是三条不同的SQL。

3.2.3.4 绑定变量
在大部分时候，sql语句里有一些经常会变化的值。例如：

1. select id, name from members where id = 1207   
2.  select id, name from members where id = 1208   
3. select id, name from members where id = 1209  

前面说过了，这样的SQL其实是三条不同的SQL，因为它们的字符明显不一样。那我们该怎么样才能让它们成为同一条SQL呢？可以通过绑定变量来实现。
下面是一条含绑定变量的sql 语句：

1. select id, name from members where id = :member_id  

这样不管member_id如何变化，Oracle都会认为这条SQL是同一条，就可以节省解析的成本了。
那么，在java开发中，怎么使用绑定变量呢？注意，不要认为下面的代码是在使用绑定变量：

1. Statement stmt=conn.createStatement();   
2. String member_id=member.id;   
3. String sql="select id,name from members where id ="+member_id;   
4. stmt.executeQuery(sql);  

上面的例子里，当member.id的值为1207时，我们传给stmt的SQL实际上是：

1. select id, name from members where id = 1207  

当member.id的值为1208时，就是：

1. select id, name from members where id = 1208  

它们在Oracle看来仍然是不同的SQL。
其实，在java中使用绑定变量非常简单，只需要使用PreparedStatement对象就可以了。如下：

1. String sql="select id,name from members where id =?";   
2. PreparedStatement pstmt=conn.createStatement(sql);   
3. pstmt.setString(1,member.id);//将member.id传给第一个问号。  

这样，PreparedStatement会自动把这条SQL在传给Oracle时转化为类似下面的SQL：

1. select id, name from members where id = :member_id  

这样就实现了绑定变量，它只需解析一次，不管member.id如何变化，都不用再做解析了。
我们使用的Hibernate，内部就是使用PreparedStatement来处理的，因此，我们不需要为使用绑定变量做任何事情，Hibernate已经帮我们做好了。

3.2.3.5 查看共享池
通过下面的命令查看共享池内存的大小：

1. show parameter shared_pool_size;  

结果见下图：
 

3.2.3.6 修改共享池
通过下面的命令可以修改共享池内存的大小：

1. alter system set shared_pool_size=90M scope=both;  

 

shared_pool_size是一个动态参数，可以在运行时修改，因此这里的scope设置为both，新的内存大小马上生效，并且还将修改保存在Oracle的启动文件里。

3.2.3.7 查看共享池命中率
共享池命中率可以反映出SQL重复使用率的高低，命中率越高，说明SQL重复使用率越高，也就是节省的SQL解析时间越多，反映在系统上就是查询数据越快。
可以通过以下命令查看命中率：

1. select sum(pinhits) / sum(pins) * 100 "看命中率咯" from v$librarycache;  

结果如下图：
 
如果命中率低于95%，则要考虑调整共享池大小。我们知道，如果没有在程序里使用绑定变量，那么就算共享池再大，也不会有什么好的效果，反而会有副作用。因此，更重要的事情是把使用最多的SQL改成绑定变量，你将会看到明显的效果。

3.2.4 数据缓冲区
如 果每次执行一个操作时，Oracle都必须从磁盘读取所有数据块并在改变它之后又必须把每一块写入磁盘，显然效率会非常低。数据缓冲区存放需要经常访问的 数据，供所有用户使用。修改数据时，首先从数据文件中取出数据，存储在数据缓冲区中，修改/插入数据也存储在缓冲区中，commit或DBWR(下面有详 细介绍)进程的其他条件引发时，数据被写入数据文件。数据缓冲区的大小是可以动态调整的，但是不能超过sga_max_size的限制。

3.2.4.1 查看数据缓冲区
通过下面的命令查看数据缓冲区内存的大小：

1. show parameter db_cache_size;  

结果见下图：

 
3.2.4.2 修改数据缓冲区
通过下面的命令可以修改数据缓冲区内存的大小：
alter system set db_cache_size=50M scope=both;
db_cache_size是一个动态参数，可以在运行时修改，因此这里的scope设置为both，新的内存大小马上生效，并且还将修改保存在Oracle的启动文件里。

3.2.4.3 查看数据缓冲区命中率
数据缓冲区也有一个命中率的概念，一般要求命中率在90%或95%以上，如果你的命中率过低，说明你的数据库效率很低，需要调整数据缓冲区的大小。
可以通过下面的命令查看命中率：

1. select (1 - ((physical.value - direct.value - lobs.value) / logical.value)) * 100 "命中率"  
2.   from v$sysstat physical,   
3.        v$sysstat direct,   
4.        v$sysstat lobs,   
5.        v$sysstat logical   
6.  where physical.name = 'physical reads'  
7.    and direct.name = 'physical reads direct'  
8.    and lobs.name = 'physical reads direct (lob)'  
9.    and logical.name = 'session logical reads';  

结果如下：
 

PGA(Process Global Area)，是server process一段私有内存区，它包含有全局变量，数据结构和一些控制信息。在Oracle8i 中，PGA调整非常复杂，要调整SORT_AREA_SIZE、HASH_AREA_SIZE、BITMAP_MERGE_AREA_SIZE、 CREATE_BITMAP_AREA_SIZE等参数。在ORACLE9I以后，只需要调整 PGA_AGGREGATE_TARGET。
每个 用户连接到Oracle,都会占用一定的内存，当然，用户一般连上之后还会做一些操作，比如一般的查询，有排序操作的查询，或用到Hash连接的查询。这 些都需要内存，而这些内存（还有一些其他的，这里就不说了。）加起来就是PGA的大小了。如果PGA设置的过小，会导致Oracle频繁同磁盘进行交换， 性能将会受到很大影响。

3.3.1 查看PGA
通过下面的命令来查看SGA：
show parameter pga;

结果如下图：
 
pga_aggregate_target的值就是PGA的大小，从上图可以看出pga_aggregate_target的大小是200M。

3.3.2 修改PGA
通过下面的命令可以修改共享池内存的大小：
alter system set pga_aggregate_target=90M scope=both;

 

pga_aggregate_target是一个动态参数，可以在运行时修改，因此这里的scope设置为both，新的内存大小马上生效，并且还将修改保存在Oracle的启动文件里。

3.3.3 查看PGA命中率
可以通过下面的命令查看命中率：
SELECT a.VALUE "看命中率咯"
  FROM V$PGASTAT a
 where a.NAME = 'cache hit percentage';

结果如下：