如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：[email protected]
ORA-04031错误排查与诊断
适用于：
Oracle数据库-企业版-8.1.5.0和更高版本
本文档的内容适用于各个平台
目的：
这篇文章用意是帮助读者了解产生ORA-4031错误的原因
收集有用的诊断信息缩小错误范围
回答一些关于4031错误的常见问题。
问题和解答：
如何在SGA pool中进行内存分配和取消内存分配。
SGA包含一些固定区域，比如Log Buffers和Buffer Cache(s) 以及内存池(Shared Pool, Large Pool, Java Pool, 还有10g中的 Streams Pool).
这类似于一个表空间内有许多段。表空间是堆和段是子堆。段中的区是可以在父子堆里存在的各种子堆。共享内存区的目标和表空间是一样的 – 避免碎片。要做到这一点，我们分配内存块（chunk）最适合于存储请求，根据需要合并相邻的空闲空间和检查内存能否刷新和重用。
这些池里面，我们使用空闲列表桶。空闲列表的结构和每个列表对应于特定的大小。 Oracle在空闲列表中进行二进制搜找到合适的空闲列表。如果第一个桶大于或等于所请求的大小将被返回。在数据库的启动时，有在各池创建的内存中有各种大小的块（chunk）。我们将继续在空闲列表中寻找，直到我们找到它指向足够大的内存空间。共享池将使用最近最少使用（LRU）算法，随着时间的推移并没有得到重用的会被置换出去。
从堆的dump跟踪文件里查看空闲列表桶的概述：
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0 Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40 Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
这表明，桶1具有443个内存块，其中的最大大小为40字节，平均是40个字节。桶2是空闲列表中内存块大小在40和48字节之间。当块的空间被释放，它被添加到相应大小的桶里。如果你发现共享池可用内存块中大块和小块的混合，这表示应用程序产生碎片并且会越来越多，未来的大容量内存请求将失败，产生ORA-04031 错误。看看我们如何能够确定是否存在导致问题的应用程序的问题？跟踪低效的应用程序代码。
共享池和large池划分他们的共享内存区域成为子池（从9i开始）。每个子池将包含空闲列表桶指向子池中的内存块。
当内存池中的一个内存块被分配，它将被关联为一个存储类型。该块将被分配为永久的，FREEABLE的，或RECREATABLE的。这些内存块随后与池内部的内存结构或元件相关联。
块类型:
正常（freeable）块 – 这些块被分配了，用户可以明确地释放这些块当在内存中已经完成了。
空闲块 – 这些块是空闲可用的，应请求进入池中和这个块大小一样或比它小的。
recreatable块 – 这是“freeable”内存的一种特殊形式。这些块被放置在LRU列表中，当他们取消固定。如果需要内存，我们去LRU列表找已经一段时间没有被使用和释放“recreatable”内存。
永久块 – 这些块可以以不同的方式进行分配。有些快被分配被保留到该实例的生命周期里，一些“永久”块可以一次又一次的被使用。
设置事件，，永久块被评价为其它类型的内存块用于调试目的。
什么是子池？
在Oracle 9i和更高的版本，共享池可以被划分成子池。每个子池是一个小的共享池，有自己的一套空闲列表，内存结构条目和LRU列表。这是对共享池/大型池可扩展性的变化，增加这些池的吞吐量，现在每个子池是由池子锁保护的。这意味着在共享/大型池不再争用一个单一的锁。共享池的保留区域平分到子池中。
当出现4031错误，跟踪文件将指出是哪个子池发生错误。
例如：
ORA-04031: unable to allocate 4192 bytes of shared memory (“shared pool”,”SELECT /*+ FIRST_ROWS */ * F…”,”sql area (6,0)”,”kafco : qkacol”):4031:375:2008:ocicon.c
在这个例子中，第六个子池发生错误。
使用子池的缺点是有其中一个子池可能得到过度使用的情况。一旦子池被选中，对内存块的搜索可能失败，即使另一个子池可能有足够的可用内存。用10g开始，如果内存请求未在所选择的子池满足，我们有允许将搜索切换到其他子池的功能，但这不适用于所有存储器结构/组件。
不平衡使用子池会导致ORA-04031。我们通常看到在“session param values”内存结构中的内存分配失败。随着9i和更高版本，动态参数设置保存为配置的每个进程和子池选择在启动管理所有的“session param value”条目。如果这两个进程参数设置非常高，你没有高并发连接，在这个子池造成不必要的常驻内存分配，并可能导致ORA-04031的问题。一般情况下，从拥有多个共享池闩锁带来的性能提升超过与过度利用有关的子池可能出现的问题。
Reference (this problem appears to have spanned many bugs):
Bug 4184298 – Subpool imbalance for “session parameters”
什么是保留区？
数据字典高速缓存或库缓存缓存未命中比在高速缓存未命中更昂贵。出于这个原因，共享池的大小应确保频繁使用的数据进行高速缓存。如果在共享池中没有足够的可用空间，则Oracle必须寻找和释放足够的内存来满足这一要求。此操作可以想象，检测时间段持有锁定资源的锁，造成轻微破坏其他并发尝试在分配内存。因此，对于没有满足大尺寸的空间分配请求是非常昂贵的，因为它可能会导致许多小的空间被刷新能够腾出大的空间满足要求。
默认情况下，Oracle在共享池里面将配置一个小的保留池（或保留区）。这种内存可以用来在一般的共享池列表中空间不可用无法满足大的连续分配请求时。
这些大分配请求通常用于如PL/ SQL和触发编译或临时空间，同时装载Java对象的操作。
从保留池分配的内存被释放后，它返回到保留池。
可以放置在保留池分配的最小大小是通过隐蔽参数_shared_pool_reserved_min_alloc控制。默认设置是在最近版本的4400个字节，可以低至4000个字节进行设置。
内存分配请求比_shared_pool_reserved_min_alloc的值大可以从保留列表分配空间，如果在一般的共享池空闲列表上找不到足够大小的内存块。
如果4031错误信息显示失败的大小，或者V$SHARED_POOL_RESERVED里的LAST_FAILURE_SIZE值大于_shared_pool_reserved_min_alloc，这表示要增加保留区的大小以满足大的空间请求。
保留池的大小可以设置成固定值通过指定hared_pool_reserved_size或隐藏参数_shared_pool_reserved_pct设置一个百分比。因此参数_shared_pool_reserved_pct 会重写shared_pool_reserved_size 参数。
在一些应用环境，5%太小。如果一个4031问题表名一个很大的内存请求失败，手动设置SHARED_POOL_RESERVED_SIZE增加保留区的大小或者使用SGA_TARGET>0, 修改隐藏参数_shared_pool_reserved_pct到10或者15以确保共享池自动增长或缩小。
V$SHARED_POOL_RESERVED视图可以用来确定失败请求的大小和共享池保留区的使用率。 Document: 430473.1 ORA-4031 Common Analysis/Diagnostic Scripts contains a script for querying this view.
警告:
如果你确定使用SHARED_POOL_RESERVED_SIZE参数和SGA_TARGET>0, 在大的内存请求时你可能会遇到4031错误，因为MMAN进程随着时间的推移可能会增加共享池的大小，而保留区保持不变。共享池保留区一开始为10%, 但是随着时间的推移，可能以2%或3%结束(远小于与新共享池大小相关的预期).
保留区处理大的内存分配，减少共享池碎片的可能性。
SQL> alter system set “_shared_pool_reserved_pct”=10 scope=spfile
或者在pfile里添加：
“_shared_pool_reserved_pct”=10
对于大的内存分配，Oracle试图在共享池中按下列顺序分配空间：
1.从共享池中不是保留的部分。
2.从保留池。如果在共享池中的不是保留的部分没有足够的空间，那么Oracle检查保留池是否有足够的空间。
3.如果在共享池中不是保留和保留部分都没有足够的空间，那么Oracle尝试释放足够的内存用来分配。然后，它重试共享池不是保留和保留的部分。那些在LRU列表中有段时间没被重用的RECREATABLE/FREEABLE块被刷新。
SGA的池有什么用？
SGA中的共享池包含library cache, dictionary cache,并行执行消息缓存和控制结构。Library cache包含共享SQL区，私有SQL区（在一个共享服务器配置）， PL/SQL存储过程和包，和控制结构如锁和library cache管理。当一个新的SQL语句被解析，在共享SQL区来存储，我们从共享池中分配内存。该内存的大小取决于该语句的复杂性。理想情况下，共享池应该用于高速缓存共享SQL和避免因共享SQL缓存收缩引起的性能开销。
oracle恢复管理器（RMAN），并行处理/ IO和共享服务器的很多功能都旨在利用大的共享内存块。这些功能将会把不必要的压力施加在共享池上，因此，我们建议您使用定义large pool 的LARGE_POOL_SIZE或使用SGA_TARGET，以帮助减少在共享池中内存压力。
Java池内存用于与JVM中的所有特定会话的Java代码和数据相关的内存分配。 Java池内存以不同的方式使用，具体取决于Oracle服务器正以哪种模式运行。
如果使用流功能，您可以配置流池管理需要此功能的内存分配。
共享池采用的类似在缓冲区高速缓存发现的LRU算法。因此，调整共享池比其他池更复杂。在大多数情况下，如果发生在其他内存池之一的ORA-04031错误，这表明该池太小，你必须增加问题池的大小在未来停止这些错误。
这些其他池默认设置通常是足够的，但手动调整这些池，您可以更改参数LARGE_POOL_SIZE，STREAMS_POOL_SIZE和JAVA_POOL_SIZE。使用SGA_TARGET这些池需要由MMAN进程自动调整。
Ora-04031错误是什么？
在SGA中的内存池是由不同大小的内存块组成。当数据库启动时启动，你有一大块的内存分配在各种池和追踪空闲列表桶。随着时间的推移，随着内存分配和释放，内存块根据自己的尺寸移入池内不同的空闲列表桶中。当Oracle在SGA中的内存池不能找到一个内存块大到足以满足用户的操作的内存分配请求时，发生ORA-04031错误。
共享池比其他内存池管理不同。共享池存储关于词典和library cache信息。然而，这些内存是使用空闲列表和一个最近最少使用（LRU）算法来管理。共享池报ORA-04031，是在搜索所有空闲列表后， LRU列表可能老化了的对象，并且多次扫描空闲列表中。这意味着ORA-04031是很难预测。可以有很多因素促成的ORA-04031和跟踪信息提供在错误的时间与 “victim session”相关内存条件，而不是原因。分配代码是复杂的，但分配算法的简化版本勾勒如下：
scan regular free list for match, if not found large request, scan reserved list if (chunk found) check chunk size and perhaps truncate if (chunk is not found) scan regular free list if (chunk found) check chunk size and perhaps truncate all done if (chunk is not found) do LRU operations and repeat small request, scan regular free list do LRU operations and repeat search if (chunk found) check chunk size and perhaps truncate all done if (chunk is not found) do LRU operations and repeat
注意:在报ORA-04301错误之前会有内部检查限制查找的重复次数
总共的空闲空间可通过V $ SGASTAT获得但并不重要。重要的是，可经过一些LRU操作被释放或合并最大块的大小。从堆dump跟踪文件，我们可以看到空闲列表桶和每个桶中的块信息。
空闲列表桶的概述：
Bucket 0 [size=32 ] Count= 0 Av.Size= 0.00 Max= 0 Bucket 1 [size=40 ] Count= 443 Av.Size= 40.00 Max= 40 Bucket 2 [size=48 ] Count= 1850 Av.Size= 48.00 Max= 48
这表明，桶1具有443个内存块，其中的最大大小为40字节，平均大小是40个字节。桶2包含的内存块大小在40和48字节之间。找出产生碎片在内存中不可用的原因。有时候是Oracle的功能问题，但在很大比例低效率的应用程序编码是主要问题。
4031错误可以发生在 Large Pool, Java Pool, Streams Pool (10g新增加的),或者 Shared Pool。错误信息会指明是哪个池发生问题。如果错误指向的问题池不是共享池，通常是应用环境配置的池大小太小。增加池的15%大小并监控后续问题。如果用的10g, 自动共享内存管理 (ASMM)功能, MMAN进程随着时间的推移会尝试增加或缩减SGA的不同组件的大小。当你发现4031错误是出现在Large Pool, Streams Pool, 或Java Pool，你只要增加SGA_TARGET的大小来允许MMAN管理更大的内存。
共享池是更复杂的调整，例如：
ORA-04031: unable to allocate 4192 bytes of shared memory (“shared pool”,”SELECT /*+ FIRST_ROWS */ * F…”,”sql area”,”kafco : qkacol”):4031:375:2008:ocicon.c
在这种情况下，问题发生在共享池。错误信息包含内存请求失败的大小。这个失败是在SQL Area请求4192字节。
注意:在使用ASM环境的共享池。在10.1版本ASM实例报4031错误因为默认的适应磁盘组管理获得值太小。在这种情况下，设置SHARED_POOL_SIZE参数为50M,如果问题还是存在，那就再增加10M。
Note 146599.1 Diagnosing and Resolving Error ORA-04031
我的保留区大小是否合适？
一个4031错误定位大的内存块获取失败表名保留区太支离破碎了，你可以使用从Note 430473.1获得的脚本调查保留区内存的使用量。
| ReservedAnalysis.sql 请求丢失= 0意味着保留区域太大。请求丢失一直增加，但请求失败不增加可能意味着保留区太小。在这种情况下，在共享池中刷新满足内存存储需求。请求丢失和请求失败都增加意味着保留区太小，在共享池中刷新没有帮助（可能得到一个ORA-04031）。 |
你也可以调查保留区的效率。我们的目标是让“HIT ％”保持越接近100越好。注意：在保留区的失败并不总是等同于ORA-04031错误。我们进行小的刷新，试图找到匹配的内存请求，而且在许多情况下，我们会发现请求内存和阻止的错误消息。如果增加保留区的大小，可以增加在共享池中获取需要的内存的机会。我们建议增加相同数量的共享池和保留区的大小。
| col requests for 999,999,999 col last_failure_size for 999,999,999 head “LAST FAILURE| SIZE ” col last_miss_size for 999,999,999 head “LAST MISS|SIZE ” col pct for 999 head “HIT|% ” col request_failures for 999,999,999,999 head “FAILURES” select requests, decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_size from v$shared_pool_reserved; |
有办法找到一个“正确”的共享池大小吗？
您可以使用SHARED_POOL_SIZE参数手动配置共享池或用自动调整使用SGA_TARGET（10g和更高版本）。共享池分配的内存部分用于内存开销（根据设置一些内部参数）。此10g之前，内存开销的最大值由SHARED_POOL_SIZE参数设置的，而不是由参数SHARED_POOL_SIZE体现。10G之前的运行V $ SGASTAT查询似乎是一个计算错误。
| 例如：
SHARED_POOL_SIZE=64M SQL> Select sum(bytes) “Total Mem” from v$sgastat where pool=’shared pool’; Total Mem
|
10g中，这种内存开销现在包含在SHARED_POOL_SIZE设置里面。从上面的例子中，如果SHARED_POOL_SIZE被手动设置为64M和开销保持不变，这意味着可用共享池内存只有54525952字节。
在我的SGA中有多少内存是可用的？
你可以从视图V$ SGASTAT看到共享池的空闲内存。该视图被分解成内存结构表条目如’library cache’, ‘KGLS heap’, ‘CCursor’。
如何通过10g ASMM和11g AMM进行自动管理？
当SGA_TARGET>0时开启自动共享内存管理功能。ASMM将会管理SGA中的这些组件最合适的大小：
Shared Pool
Large Pool
Java Pool
Buffer Cache (the default one managed by db_cache_size)
Streams Pool (new to 10g Release 2)
内存块移动在“颗粒大小”。你查询V$ SGAINFO找到你数据库的颗粒大小。参见附注947152.1更多关于大尺寸颗粒的问题。非常大的SGA将默认使用非常大的颗粒大小。
| SQL> select * from v$sgainfo;
NAME BYTES RES ——————————– ———- — Fixed SGA Size 1344428 No Redo Buffers 12910592 No Buffer Cache Size 234881024 Yes Shared Pool Size 419430400 Yes Large Pool Size 16777216 Yes Java Pool Size 16777216 Yes Streams Pool Size 33554432 Yes Shared IO Pool Size 0 Yes Granule Size 16777216 No Maximum SGA Size 1121554432 No Startup overhead in Shared Pool 61193820 No
NAME BYTES RES ——————————– ———- — Free SGA Memory Available 385875968 |
其他的buffer caches (管理通过参数DB_nK_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE), Log Buffer,和 Fixed SGA areas 不会通过MMAN自动调整。SGA_TARGET设置影响实际的MMAN可用内存。
SGA_TARGET能够动态更改增长到SGA_MAX_SIZE设置。
案例分析:
配置 SGA_TARGET 为4G.
你可以配置
DB_KEEP_CACHE_SIZE=256M
LOG_BUFFER=200M
DB_4K_CACHE_SIZE=512M.
你可以手动设置一个共享池的最小值 (SHARED_POOL_SIZE=1G).
MMAN不能自动调整所有的4G内存。MMAN只能访问2,206,203,904 字节。
SGA组成部分:
Log Buffers 209,715,200
Keep Buffer Cache 268,435,456
4K Buffer Cache 536,870,912
+ Shared Pool 1,073,741,824 (共享池不能手动缩小低于这个值)
————————————
Total 2,088,763,392
我们建议在SGA中自动调整组件进行默认的/显式的设置。衡量ASMM是如何工作的，从Note 430473.1中获取执行脚本：
SGAComponents.sql (for 10.2.x)
SGAComponents11g.sql (for 11g)
一个设置ASMM的法则是
SGA_TARGET = 256M * # of CPUs
默认我可以有几个子池？
用一个简单的算法计算子池的数量。首先，子池必须在9i中释放至少128MB和至少256MB在10g中。其次，可以为系统中的每个四个CPU一个子池，最多7子池。子池的数量可以使用init.ora中的参数_kghdsidx_count显式控制。没有参数显式地控制每个子池的大小。
如果有人配置了一个12-CPU系统上9i的一个300MB的共享池，Oracle将创建两个子池，每个大小150MB的。如果共享池的大小增加到500MB，oracle将创建三个子池，每个大小166MB的。
因为128MB（甚至256MB上10g）子池可以在许多应用环境中不够大，每子池内存将有可能需要增加。没有参数来改变子池的最小大小;唯一的办法是降低子池的数量或者增加共享池的大小。请记住，增加共享池的大小不一定增加子池的大小，因为，如果有系统上许多CPU，子池的数目可以继续增加。
如何控制子池的使用数量？
参数 _kghdsidx_count 控制子池的使用数量。
| SQL> alter system set “_kghdsidx_count”=1scope=spfile;
or add this in the pfile “_kghdsidx_count”=1 |
注：当SGA创建时子池就会被创建和启动。在上面修改子池的两种方法都要重启数据库。任何_kghdsidx_coun参数的改变都会改变Large Pool中的子池数量。
| 警告：减少子池的数目可以对性能有明显的影响，特别是在RAC配置，高并发系统，或者具有非常大的large pools数据库实例。 改变这些参数将影响到共享池，共享池保留区和的large pools。 减少子池的数量可能会导致增加闩锁争用。当手动设置了_kghdsidx_count参数，子池的数量，建议逐步进行更改，监控对性能的影响，并尽量减少任何重大影响。相反，增加子池数量，没有增加整体池的大小可能会导致空间问题，因为子池空间可能过小。 |
所有的ORA-04301错误都会记录在alert日志里吗？
不。有些错误只在客户端工作站显示出来。 11g之前，一个普通用户进程中发生的任何ORA-4031错误不会在告警日志里记录。此外，在告警日志报ORA-600或ORA-7445内部错误，但内部错误是4031内存问题的副作用的情况。对于内部错误相关的跟踪可能通常包含一个默认的ORA-4031的跟踪诊断数据。
How can we see a breakdown of the data in the “miscellaneous” structure in V$SGASTAT?
当进行v$sgastat查询，有这样的情况，你会看到一个非常大的价值“miscellaneous”。直到Oracle 10.2，SGA结构的基本的内部设计保持不变。如果运行像SGAStat.sql的脚本：430473.1。这个脚本会在视图V $ SGASTAT报到共享池最大的分配领域。您可以调整脚本看任何SGA中的池
唯一深入了解”miscellaneous”的内存分配的方法是做一个heapdump跟踪.你可以执行这个命令：
| alter system set events ‘4031 trace name HEAPDUMP level 536870914’;
注意：在实例级别设置该事件都将产生较大的文件，如果经常发生的错误4031，你会得到很多的跟踪文件。这可能会影响性能和夯住（在某些情况下崩溃的数据库）。关闭此事件使用
alter system set events ‘4031 trace name HEAPDUMP off’;
|
用下面的步骤获取及时的内存dump
alter system set events 'immediate trace name heapdump level 536870914'; sqlplus /nolog connect / as sysdba oradebug setmypid oradebug unlimit oradebug dump heapdump 536870914 oradebug tracefile_name oradebug close_trace
关闭SQL*Plus会话，通过上面的’oradebug tracefile_name’命令找到heapdump跟踪文件列表。
数据库的哪些参数和4031错误有关？
CURSOR_SHARING
Oraclet替代SQL语句中的某些文字值来减少硬解析。该文字值将被替换为绑定变量，如果两个或多个会话在执行相同的SQL语句，它们可以使用相同的游标绑定变量，而不是创建两个不能共享的游标。
例如，两个用户以scott连接执行SQL语句”select ename from emp where empno = 20″和 “select ename from emp where empno =100”.如果cursor_sharing设置成FORCE, Oracle会创建一个游标绑定变量，语句会变成”select ename from emp where empno = :b1″。两个用户将共享 相同的游标对象，而不是单独创建两个字典缓存父对象和对应的子对象。
这个值由三种模式:
EXACT: 不尝试修改文字
FORCE: 所有的文字值被替换，语句被共享但不关心文字的值是如何影响执行计划的。SIMILAR: 所有的文字被替换，但是语句只有在有相同执行计划时才会共享。
(11g 没有这个选项了)
CURSOR_SPACE_FOR_TIME
如果设置了改参数，Oracle不会在执行后取消固定的库缓存对象。这意味着随着多个固定的库缓存对象的增加，更多的游标被打开和执行，从而减少了被替换的共享池的内存。设置此参数必须小心，要了解所有应用在内存中的使用情况。没有这方面的知识，这个参数的设置可能会导致ORA-04031错误。光标是会话缓存光标名单上没有自己的SQL区堆寄托。
DB_CACHE_SIZE
在RDA中检查该参数的设置。如果使用SGA_TARGET, 该参数应该默认是0.当使用SGA_TARGET时任何硬编码设置这个参数会在MMAN缩小Buffer Cache时作为最小值。
DB_nK_CACHE_SIZE
这个SGA内存组件不是自动调整，但是这个内存设置会影响MMAN实际可用的内存。
DB_KEEP_CACHE_SIZE
这个SGA内存组件不是自动调整，但是这个内存设置会影响MMAN实际可用的内存
DB_RECYCLE_CACHE_SIZE
这个SGA内存组件不是自动调整，但是这个内存设置会影响MMAN实际可用的内存
JAVA_POOL_SIZE
如果你使用了SGA_TARGET，这将默认值为0.在使用SGA_TARGET下任何硬编码的设置这个参数值，将会作为在缩小Java 池时的最小值。
如果你想知道Java池内存分配的更多细节，你可以执行：
> sqlplus /nolog SQL> connect / as sysdba SQL> alter session set events 'immediate trace name heapdump level 128';
LARGE_POOL_SIZE
并行处理，RMAN操作和顺序的文件IO。如果你没有使用这些特殊功能区，你可以把Large Pool设置为0. Large Pool没有使用LRU算法因此当会话释放分配的larger内存，还会被保留在Large Pool里。如果你想知道Large池内存分配的更多细节，你可以执行：
sqlplus /nolog SQL> connect / as sysdba SQL> alter session set events 'immediate trace name heapdump level 32';
如果使用了SGA_TARGET,这个参数将会显示为0 ，但是你可以设置一个最小值，当MMAN要缩小Larger Pool时不能低于设置的值。
LOG_BUFFER
这个SGA内存组件不是自动调整，但是这个内存设置会影响MMAN实际可用的内存
OPEN_CURSORS
此参数设置一个会话可以有打开的游标的数量的上限。通常情况下，游标通过OCI或通过PL / SQL调用打开打开游标。
如果用户明确的打开游标，会话无法打开一个新的游标（打开游标的数量等于OPEN_CURSORS），一个缓存游标会被关闭，以便明确的打开游标能成功。
参照:
Note 76684.1 Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
Note 208857.1 SCRIPT – to Tune the ‘SESSION_CACHED_CURSORS’ and ‘OPEN_CURSORS’ Parameters
PROCESSES / SESSIONS
这些进程从9.2.x.开始影响共享池的大小。
注：内存结构可以采取32位数据库每个参数多达20个字节和64位数据库每个参数32个字节。在10.2.x中，有超过1300动态参数，所以数据库上用户可以迅速增加。您可以V$RESOURCE_LIMIT 视图查看会话和进程的高水位。如果这些参数的硬编码值低于高水位信息高很多，可以考虑降低参数设置以释放在共享池中一些内存用于其他用途。
SESSION_CACHED_CURSORS
当游标被关闭，oracle分离会话和库高速缓存状态之间的所有关系。如果没有其他会话已经打开相同的游标，库缓存对象及其堆是取消固定和可用于LRU操作。参数SESSION_CACHED_CURSORS控制游标的“软”关闭数量，很像缓存的PL / SQL游标。
SESSION_CACHED_CURSORS不是OPEN_CURSORS的统计数据的一部分;这是一个单独的列表。而不是被真正关闭，oracle则以会话私人LRU列表上的游标，并保持供后续的解析游标。如果用户执行一个新的语句，它会首先搜索会话缓存游标列表，如有发现，使用它。
这个参数设置为高值会增加库高速缓存的内存量（通过查看视图V $ SGASTAT监控）。
参照:
Note 270097.1 ORA-4031 and Very Large Library Cache in Oracle 9.2 with Session_cached_cursors set. Library Cache Pin/Lock Pile Up hangs the application
Note 274496.1 ORA-7445 and ORA-4031 in 9.2.0.5 and 10g if SESSION_CACHED_CURSORS is used
SGA_TARGET
如果这个参数被设置，MMAN进程将试图扩大和缩小自动调整内存组件。有趣的是，10.2，如果您在spfile中指定SGA_TARGET大于SGA_MAX_SIZE明确的值，下次启动时会忽略SGA_MAX_SIZE事先设置，将其设置为新的SGA_TARGET设置。这在11g中不会发生。
SHARED_POOL_SIZE
如果使用SGA_TARGET，这将默认为0，但是当它试图缩小共享池时，此参数的硬编码设置将作为MMAN的最小尺寸。随着9i和10g，SGA固定的内存结构已经移动到共享池。随着9i和更高版本，Oracle还实施了新的子池功能。这可能需要额外的调整分析应用将利用不同的内存。在某些情况下，共享池里面太多的子池，可引起子池中的一个被过度利用，导致ORA-4031的问题。
参照:
Note 270935.1 Shared pool sizing in 10g
如果你想看共享池分配的更多细节，你可以执行
sqlplus /nolog SQL> connect / as sysdba SQL> alter session set events 'immediate trace name heapdump level 2'; 注：数据库活动高峰期间，不建议使用运行堆转储跟踪，跟踪会影响性能。
SHARED_POOL_RESERVED_SIZE
此参数默认设置为5％。当使用SGA_TARGET，共享池组件的增长和缩小会自动有所调整。如果你看到的ORA-4031错误，它们指明内存请求失败超过4000字节的，5％的默认值可能在你的应用环境是不够的。您可以更改隐藏参数，_shared_pool_reserved_pct，为10。这将导致保留区，利用共享池的10％。 例如，
SQL> alter system set "_shared_pool_reserved_pct"=10 scope=spfile; or add in the init file"_shared_pool_reserved_pct"=10
STATISTICS_LEVEL
在10g中附加的内存结构相关的统计跟踪。设置数据库的统计信息收集级别。该参数可以设置为BASIC，TYPICAL或ALL。
默认设置是TYPICAL，共享池将会应变，除非你调整共享池以适应数据库正在进行的分析活动。在某些性能调整的情况下，有必要STATISTICS_LEVEL设置为ALL。这将在共享池中使用更多的内存比其他设置，使用ALL可能导致ORA-4031的问题，如果共享池没有进行调整，以处理额外的内存需求。
在9i和10g的一些已知的bug，解决办法是STATISTICS_LEVEL设置为BASIC。这将使用最少的共享池内存，但你放弃了自我调整功能（内存，顾问，对象统计管理等）。
STREAMS_POOL_SIZE
这是10g中新增的内存池。它的目的是减轻在共享池中内存结构相关的流操作压力。检查大小在RDA的参数进行设置。
如果使用的10g第2版SGA_TARGET，参数会自动调整，并会显示为0。您可以按10g硬编码的最小尺寸和Release2和MMAN不会试图缩小到低于该设置的流池。
如果你想看流池分配的更多细节，你可以执行
sqlplus /nolog SQL> connect / as sysdba SQL> alter session set events 'immediate trace name heapdump level 64';
还审查使用共享服务器（MTS_SERVERS，MTS_DISPATCHERS等）的任何设置。如果这些参数表明，共享服务器配置，你不应该看到的内存结构在共享池中涉及到共享服务器。
注：有些上面列出的参数，只适用使用SGA_TARGET。一定要调查到底有多少内存MMAN可以成长和收缩自动调整的内存组件的工作。
Leave a Reply