oracle SGA

文章来源：http://blog.sina.com.cn/s/blog_5421dfd20100ffn8.html
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了，会对它进行重新解析。
例如：

select id, name from members where id = 1403   
select id, name From members where id = 1403   
select name, id from members where id = 1403  
这三条SQL在Oracle看来就是三条不同的SQL。

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

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

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

Statement stmt=conn.createStatement();   
String member_id=member.id;   
String sql="select id,name from members where id ="+member_id;   
stmt.executeQuery(sql);  
上面的例子里，当member.id的值为1207时，我们传给stmt的SQL实际上是：

select id, name from members where id = 1207  
当member.id的值为1208时，就是：

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

String sql="select id,name from members where id =?";   
PreparedStatement pstmt=conn.createStatement(sql);   
pstmt.setString(1,member.id);//将member.id传给第一个问号。  
这样，PreparedStatement会自动把这条SQL在传给Oracle时转化为类似下面的SQL：

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

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

show parameter shared_pool_size;  
结果见下图：


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

alter system set shared_pool_size=90M scope=both;  (这个我没有执行成功)
shared_pool_size是一个动态参数，可以在运行时修改，因此这里的scope设置为both，新的内存大小马上生效，并且还将修改保存在Oracle的启动文件里。

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

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

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

3.2.4.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%以上，如果你的命中率过低，说明你的数据库效率很低，需要调整数据缓冲区的大小。
可以通过下面的命令查看命中率：

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