ORA-4031错误深入解析

想要彻底理解4031错误发生的原因就要了解SQL语句的执行过程以及Oracle共享内存的结构

             客户端与Oracle之间通信过程图

 

客户端输入sql语句，sql语句通过网络到达数据库实例，server process接受sql语句。Server process接受到SQL语句后将sql语句解析成执行计划，然后才能执行。

需要说明的是在将SQL语句解析成执行计划的过程中会消耗计算机大量CPU资源因此就会产生一个问题，例如：

A用户执行一个SQL语句解析换成执行，B用户也有可能执行同样的SQL。如何才能高效利用CPU资源，不去做重复的解析工作呢？

 

因此就诞生了shared pool，shared pool就是SGA中用来缓存SQL语句以及对应解析出的执行计划的一片内存区域。

                     Oracle实例管理

 

这里结合Oracle实例管理的这张图对上面一幅客户端与Oracle之间会话通信的过程进行简单的解释说明：

1. 首先客户端会在同Oracle实例间建立的连接池当中的众多连接中选择一条空闲的连接传输SQL语句（server process是服务器进程，可以连接到Oracle实例，在用户建立会话时启动 ）。
2. Server Process首先会先去shared pool中查找是否已经有了该条SQL语句对应的已缓存的执行计划，如果有直接执行（该种SQL解析也叫作软解析）。如果没有则会自己生成执行计划并缓存执行（该种解析也叫作硬解析）。
3. 其中shared pool是SGA（共享全局区）中的一部分内存资源，由所有服务器进程和后台进程共享。
4. Buffer Cache也是SGA中的一块区域，用来缓存Data files中取出的数据，如果没有buffer cache的话那么每次访问数据的时候都需要消耗I/O。
5. 所以当执行一个SQL语句对应的执行计划要访问数据，server process会先进入buffer cache找是否有所需要的数据，如果有就直接取出返回没有才会去Data files去取并将其先放入buffer cache中，并在其中对数据进行修改。之后再通过物理I/O写回到Oracle的数据文件中去。
6. 在对数据进行修改的同时会向SGA中另一块内存区域叫作Redolog Buffer中写入相关的日志信息。之后再写回到Oracle的日志文件中区。
7. 将返回的数据或信息通过连接返回给客户端。

 

上述过程中可以看出共享池是SGA中一块核心的内容，经典的Oracle4031错误也与这一块内存区域有密切的关系。

Free Cache:

顾名思义就是shared pool中的一块空闲的内存区域。

 

Library Cache（库缓存）：

主要缓存的是SQL语句，以及SQL句解析出来的执行计划。

 

Raw Cache （字典缓存）：

Oracle数据库的自身信息都存储在数据字典中（比如说：数据库中有多少表，有多少用户，表中有多少列每个表多大等等）

 

Shared Pool主要的三块空间中一般Free Cache 和 Library Cache较容易有问题。我们可以整体上设置Shared Pool的大小但不能控制当中的Library Cache和Raw Cache的大小。

需要理解的是，Free Cache并不是一个大块连续的内存空间而是一个个内存块通过chain链将其链接如下图所示

 图中橙色的圆代表一个个内存块在Oracle中称之为chunk,而这些chunk会根据大小的不容被归类挂载一条条chain上，从下往上越来越大。

 

在这里举个例子：

如果有一条SQL语句解析出来大小为10K，那么就在第8K-12K的内存链上找比如找到一个11K的块那么就将其中的10K丢到Library Cache中，而剩下的1K再挂到相应的空间链里面去。这就是Free空间的内存组织情况。

 

这里需要特别提醒强调的是——什么时候需要在Free空间你面找chunk？答案是在执行硬解析的时候。

 

由此可见当有大量硬解析的时候，除了要去Free空间中找chunk还会产生大量的小碎片，于是就有可能产生这种情况，及有大量足够的Free空间但是被分割成很多小的碎片，没有适合可用的内存块。这种情况便会产生Oracle经典的4031报错。

 

总结一下Oracle产生4031错误的背景条件：

1. 大量的硬解析产生了很多小碎片
2. 产生了大量的小碎片后突然来了一条大的SQL语句需要解析。

本文原创首发于Cobub官网博客，作者：钟泽

如有转载请注明作者和出处！

推荐一款开源私有化部署的移动应用数据统计分析系统Cobub Razor

项目地址：https://github.com/cobub/razor

官网：www.cobub.com

 

开源社区技术交流QQ群：194022996