时间：2021-07-01 10:21:17 帮助过：35人阅读
Oracle内存全面分析(4) 1.1.4.3. 共享池的内存管理 通常来说，共享池是根据修正过的LRU算法来 （决定）是否（覆盖或叫刷出）其中的对象 （共享SQL区和数据自动记录行）的，否则这些对象就一直保持在共享池中： 如果共享池需要为一个新对象分配内存，并且共享
通常来说，共享池是根据修正过的LRU算法来（决定）是否（覆盖或叫刷出）其中的对象（共享SQL区和数据自动记录行）的，否则这些对象就一直保持在共享池中：
如果共享池需要为一个新对象分配内存，并且共享池中没有足够内存时，内存中那些不经常使用的对象就被释放掉。一个被许多会话使用过的共享池对象，即使最初创建它的进程已经结束，只要它是有用的，都会被修正过的LRU算法一直保持在共享池中。这样就使一个多用户的Oracle系统对SQL语句的处理和内存消耗最小。
注意，即使一个共享SQL区与一个打开的游标相关，但如果它长时间没有被使用，它还是可能会被从共享池中释放出来。而此时如果打开的游标还需要运行它的相关语句，Oracle就会重新解析语句，并分配新的共享SQL区。
当一条SQL语句被提交给Oracle执行，Oracle会自动执行以下的内存分配步骤：
1． Oracle检查共享池，看是否已经存在关于这条语句的共享SQL区。如果存在，这个共享SQL区就被用于执行这条语句。而如果不存在，Oracle就从共享池中分配一块新的共享SQL区给这条语句。同时，无论共享SQL区存在与否，Oracle都会为用户分配一块私有SQL区以保存这条语句相关信息（如变量值）。
2． Oracle为会话分配一个私有SQL区。私有SQL区的所在（地方）与会话的连接方式相关。
下面是Oracle执行一条语句时共享池内存分配过程的伪代码：
execute_sql(statement){ if ((shared_sql_area = find_shared_sql_area(statement)) == NULL){ if (!allocate_from_shared_pool(&new_buffer)) { if (new_buffer = find_age_area_by_lru(size_of(statement)) == NULL) { raise(4031); return 0; } } shared_sql_area = set_shared_sql_area(new_buffer); parse_sql(statement, shared_sql_area);} private_sql_area = allocate_private_sql_area(statement);run_sql(statement, shared_sql_area, private_sql_area);return 1;}
在以下情况下，Oracle也会将共享SQL区从共享池中释放出来：
· 当使用ANALYZE语句更新或删除表、簇或索引的统计信息时，所有与被分析对象相关的共享SQL区都被从共享池中释放掉。当下一次被释放掉的语句被执行时，又重新在一个新的共享SQL区中根据被更新过的统计信息重新解析。
· 当对象结构被修改过后，与该对象相关的所有共SQL区都被标识为无效（invalid）。在下一次运行语句时再重新解析语句。
· 如果数据库的全局数据库名（Global Database Name）被修改了，共享池中的所有信息都会被清空掉。
· DBA通过手工方式清空共享池：ALTER SYSTEM FLUSH SHARED_POOL;
Shared Pool能被分成几个区域，分别被不同的latch（latch数最大为7，可以通过隐含参数_kghdsidx_count设置）保护。也就是说，shared pool会分为多个区（称为shared pool子池？），分别由不同的LRU链表管理不同的子池，而每个LRU链表由一个latch所保护。
表x$kghlu可以查看shared pool中的LRU列表。
当满足以下两个条件之一时，shared pool会分为多个区（称为shared pool子池？），分别有不同的LRU链表管理：
· 1、 在10g之前版本，如果shared pool大于128M、CPU数量大于4；
· 2、 Oracle数据库版本为10g
这时（即满足以上两个条件之一时），在x$kghlu中就会对应（多个）不同记录（，而不是只有一个记录）。
前面提到，如果Oracle解析一个 PL/SQL程序单元，也需要从共享池中分配内存给这些程序单元对象。由于这些对象本一般比较大（如包），所以分配的内存空间也相对较大。系统经过长时间运行后，共享池可能存在大量内存碎片，导致无法满足对于大块内存段的分配。
为了使有足够空间缓存大程序块，Oracle专门从共享池内置出一块区域来来分配内存保持这些大块。
这个保留共享池的默认大小是共享池的5%。
它的大小也可以通过参数SHARED_POOL_RESERVED_SIZE来调整。
保留区是从共享池中分配，不是直接从SGA中分配的，它是共享池的保留部分，用于存储大块段。
Shared Pool中内存大于5000字节的大段（chunk）就会被存放在共享池的保留部分。而这个大小限制是通过隐含参数_SHARED_POOL_RESERVED_MIN_ALLOC来设定的（如前面所说，隐含参数不要去修改它）。除了在实例启动过程中，所有小于这个数的内存段永远都不会放到保留部分中，而大于这个值的大内存段也永远不会存放到非保留区中，即使共享池的空间不够用的情况下也是如此。
保留区的空闲内存也不会被包含在普通共享池的空闲列表中。
它会维护一个单独的空闲列表。
保留池也不会在它的LRU列表中存放可重建（Recreatable 关于内存段的各种状态我们在后面的内容中再介绍）段（chunk）。
当释放普通共享池空闲列表上的内存时是不会清除这些大段的，同样，在释放保留池的空闲列表上的大内存段时也不会清除普通共享池中内存。
通过视图V$SHARED_POOL_RESERVED可以查到保留池的统计信息。其中字段REQUEST_MISSES记录了没有立即从空闲列表中得到可用的大内存段请求次数。这个值要为0。因为保留区必须要有足够个空闲内存来适应那些短期的内存请求，而无需将那些需要长期cache住的没被pin住的可重建的段清除。否则就需要考虑增大SHARED_POOL_RESERVED_SIZE了。
你可以通过观察视图V$SHARED_POOL_RESERVED的MAX_USED_SPACE字段来判断保留池的大小是否合适。大多数情况下，你会观察到保留池是很少被使用的，也就是说5%的保留池空间可能有些浪费。但这需要经过长期观察来决定是否需要调整保留池大小。
保留区也使用shared pool的LRU链表来管理内存块，但是在做扫描时，相互是不受影响的。例如，内存管理器扫描shared pool的LRU链表，清出空间以分配给一个小于5000字节的内存请求，是不会清出保留区的内存块的，相反亦然。
前面提到，根据LRU算法，一些一段时间没有使用到的内存块会被情况释放。这就可能导致一些重要的对象（如一个含有大量通用算法函数的包、被cache的序列）被从内存中清除掉。这些对象可能只是间歇被使用，但是因为他们的处理过程复杂（不仅包本身重新分配内存、解析，还要检查里面的所有语句--即重建过程为如此。如，游标可以重建，重新解析SQL语句为游标就是所谓重建），要在内存中重建他们的代价非常大。
我们可以通过调用存储过程DBMS_SHARED_POOL.KEEP将这些对象保持在共享池中来降低这种风险。这个存储过程立即将对象及其从属对象载入library cache中，并将他们都标记为保持（Keeping）状态。对于这种对象，我们建议在实例启动时就Keep住，以减少内存碎片的几率。
有一种观点认为那些大对象（如包）是没有必要被Keep住的，因为他们会被保持在共享池的保留区（如前所述，这个区通常使用率很低），所以一般不可能被清出。这个观点是错误滴！因为大多数大对象实际上是被分为多个小的内存段被载入共享池的，因此根本不会因为对象的大小而受到特别的保护。
另外，也不要通过频繁调用某些对象以防止他们被从共享池中清出。如果共享池大小设置合理，在系统运行的高峰时期，LRU链表会相对较短，那些没有被pin住的对象会很快被清出，除非他们被keep住了。
这里再介绍与共享池相关的一些重要参数。
这个参数我们前面已经提到，它指定了Shared Pool的大小。在32位系统中，这个参数的默认值是8M，而64位系统中的默认值位64M。
但是，在SGA中还存在一块叫内部SGA消耗（Internal SGA Overhead）的内存被放置在共享池中。在9i及之前版本，共享池的统计大小（通过v$sgastat视图统计）为SHARED_POOL_SIZE + 内部SGA消耗大小。而10g以后，SHARED_POOL_SIZE就已经包含了这部分内存大小。因此在10g中，共享池的实际使用大小就是SHARED_POOL_SIZE - 内部SGA消耗大小，这在配置共享池大小时需要考虑进去，否则，扶过SHARED_POOL_SIZE设置过小，在实例启动时就会报ORA-00371错误。
看9i中的结果：
SQL> show parameter shared_pool_size NAME TYPE VALUE----------------------------- ----------- --------------shared_pool_size big integer 41943040 SQL> select sum(bytes) from v$sgastat where pool = 'shared pool'; SUM(BYTES)----------58720256 SQL>这个参数前面已经提到，指定了共享池中缓存大内存对象的保留区的大小。这里不再赘述。
这个参数前面也已经介绍，设置了进入保留区的对象大小的阀值。
最后，我们再介绍关于共享池的一些重要视图
这个视图与Oracle的另外一个优化建议器——共享池建议器——相关。我们可以根据这个视图里面oracle所做的预测数据来调整共享池大小。它的预测范围是从当前值的10%到200%之间。视图的结构如下
|
字段 |
数据类型 |
描述 |
|
SHARED_POOL_SIZE_FOR_ESTIMATE |
NUMBER |
估算的共享池大小（M为单位） |
|
SHARED_POOL_SIZE_FACTOR |
NUMBER |
估算的共享池大小与当前大小比 |
|
ESTD_LC_SIZE |
NUMBER |
估算共享池中用于库缓存的大小（M为单位） |
|
ESTD_LC_MEMORY_OBJECTS |
NUMBER |
估算共享池中库缓存的内存对象数 |
|
ESTD_LC_TIME_SAVED |
NUMBER |
估算将可以节省的解析时间。这些节省的时间来自于请求处理一个对象时，重新将它载入共享池的时间消耗和直接从库缓存中读取的时间消耗的差值。 |
|
ESTD_LC_TIME_SAVED_FACTOR |
NUMBER |
估算的节省的解析时间与当前节省解析时间的比。 |
|
ESTD_LC_MEMORY_OBJECT_HITS |
NUMBER |
估算的可以直接从共享池中命中库缓存的内存对象的命中次数。 |
关于如何根据建议器采用合理的共享池大小的方法，和前面提到的缓冲区建议器的使用方法类似，不再赘述。
前面提到了这个视图。这个视图存放了共享池保留区的统计信息。可以根据这些信息来调整保留区。视图结构如下：
|
Column |
Datatype |
Description |
|
以下字段只有当参数SHARED_POOL_RESERVED_SIZE设置了才有效。 |
||
|
FREE_SPACE |
NUMBER |
保留区的（当期）空闲空间数。 |
|
AVG_FREE_SIZE |
NUMBER |
保留区的（自实例启动以来历次）空闲空间平均数。 |
|
FREE_COUNT |
NUMBER |
保留区的空闲内存块数 |
|
MAX_FREE_SIZE |
NUMBER |
最大的保留区空闲空间数。 |
|
USED_SPACE |
NUMBER |
保留区使用空间数。 |
|
AVG_USED_SIZE |
NUMBER |
保留区（自实例启动以来历次）使用空间平均数。 |
|
USED_COUNT |
NUMBER |
保留区使用内存块数。 |
|
MAX_USED_SIZE |
NUMBER |
最大保留区使用空间数 |
|
REQUESTS |
NUMBER |
请求再保留区查找空闲内存块的次数。 |
|
REQUEST_MISSES |
NUMBER |
无法满足查找保留区空闲内存块请求，需要从LRU列表中清出对象的次数。 |
|
LAST_MISS_SIZE |
NUMBER |
请求的内存大小，这次请求是最后一次需要从LRU列表清出对象来满足的请求。 |
|
MAX_MISS_SIZE |
NUMBER |
所有需要从LRU列表清出对象来满足的请求中的内存最大大小 |
|
以下字段无论参数SHARED_POOL_RESERVED_SIZE是否设置了都有效。 |
||
|
REQUEST_FAILURES |
NUMBER |
没有内存能满足的请求次数（导致4031错误的请求） |
|
LAST_FAILURE_SIZE |
NUMBER |
没有内存能满足的请求所需的内存大小（导致4031错误的请求） |
|
ABORTED_REQUEST_THRESHOLD |
NUMBER |
不清出对象的情况下，导致4031错误的最小请求大小。 |
|
ABORTED_REQUESTS |
NUMBER |
不清出对象的情况下，导致4031错误的请求次数。。 |
|
LAST_ABORTED_SIZE |
NUMBER |
不清出对象的情况下，最后一次导致4031错误的请求大小。 |
我们可以根据后面4个字段值来决定如何设置保留区的大小以避免4031错误的发生。
这一视图显示了所有被缓存在library cache中的对象，包括表、索引、簇、同义词、PL/SQL存储过程和包以及触发器（的定义）。
|
字段 |
数据类型 |
说明 |
|
OWNER |
VARCHAR2(64) |
对象所有者 |
|
NAME |
VARCHAR2(1000) |
对象名称 |
|
DB_LINK |
VARCHAR2(64) |
如果对象存在db link的话，db link的名称 |
|
NAMESPACE |
VARCHAR2(28) |
库缓存的对象命名空间，包括： TABLE/PROCEDURE, BODY, TRIGGER, INDEX, CLUSTER, OBJECT, CURSOR, INVALID NAMESPACE, JAVA SHARED DATA, PUB_SUB, RSRC CONSUMER GROUP |
|
TYPE |
VARCHAR2(28) |
对象类型，包括：INDEX, TABLE, CLUSTER, VIEW, SET, SYNONYM, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, CLASS, OBJECT, USER, DBLINK, CURSOR, JAVA CLASS, JAVA SHARED DATA, NON-EXISTENT, NOT LOADED, PUB_SUB, REPLICATION OBJECT GROUP, TYPE |
|
SHARABLE_MEM |
NUMBER |
对象消耗的共享池中的共享内存 |
|
LOADS |
NUMBER |
对象被载入次数。即使对象被置为无效了，这个数字还是会增长。 |
|
EXECUTIONS |
NUMBER |
对象执行次数，但本视图中没有被使用。可以参考视图v$sqlarea中执行次数。 |
|
LOCKS |
NUMBER |
当前锁住这个对象的用户数（如正在调用、执行对象）。 |
|
PINS |
NUMBER |
当前pin住这个对象的用户数（如正在编译、解析对象）。 |
|
KEPT |
VARCHAR2(3) |
对象是否被保持，即调用了DBMS_SHARED_POOL.KEEP来永久将对象pin在内存中。 (YES | NO) |
|
CHILD_LATCH |
NUMBER |
正在保护该对象的子latch的数量。 |
这三个视图都可以用于查询共享池中已经解析过的SQL语句及其相关信息。
V$SQL（对应子游标中的内容）中列出了共享SQL区中所有语句的信息，它不包含GROUP BY字句，并且为每一条SQL语句中单独存放一条记录；
V$SQL lists statistics on shared SQL areas without theGROUP BY clause and contains one row for each child of the original SQL text entered. Statistics
displayed inV$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress.
V$SQLAREA（对应父游标中的内容）中一条记录显示了一条共享SQL区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL语句的统计信息；
V$SQLAREA displays statistics on shared SQL areas and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed,
and ready for execution.
V$SQLTEXT包含了库缓存中所有共享游标对应的SQL语句。它将SQL语句分片显示。
下面介绍一下我常用的V$SQLAREA的结构：
|
字段 |
数据类型 |
说明 |
|
SQL_TEXT |
VARCHAR2(1000) |
游标中SQL语句的前1000个字符。 |
|
SHARABLE_MEM |
NUMBER |
被游标占用的共享内存大小。如果存在多个子游标，则包含所有子游标占用的共享内存大小。 |
|
PERSISTENT_MEM |
NUMBER |
用于一个打开这条语句的游标的生命过程中的固定内存大小。如果存在多个子游标，则包含所有子游标生命过程中的固定内存大小。 |
|
RUNTIME_MEM |
NUMBER |
一个打开这条语句的游标的执行过程中的固定内存大小。如果存在多个子游标，则包含所有子游标执行过程中的固定内存大小。 |
|
SORTS |
NUMBER |
所有子游标执行语句所导致的排序次数。 |
|
VERSION_COUNT |
NUMBER |
缓存中关联这条语句的子游标数。 |
|
LOADED_VERSIONS |
NUMBER |
缓存中载入了这条语句上下文堆（KGL heap 6）的子游标数。 |
|
OPEN_VERSIONS |
NUMBER |
打开语句的子游标数。 |
|
USERS_OPENING |
NUMBER |
打开这些子游标的用户数。 |
|
FETCHES |
NUMBER |
SQL语句的fetch数。 |
|
EXECUTIONS |
NUMBER |
所有子游标的执行这条语句次数。 |
|
USERS_EXECUTING |
NUMBER |
通过子游标执行这条语句的用户数。 |
|
LOADS |
NUMBER |
语句被载入和重载入的次数 |
|
FIRST_LOAD_TIME |
VARCHAR2(19) |
语句被第一次载入的时间戳。 |
|
INVALIDATIONS |
NUMBER |
所以子游标的非法次数。 |
|
PARSE_CALLS |
NUMBER |
所有子游标对这条语句的解析调用次数。 |
|
DISK_READS |
NUMBER |
所有子游标运行这条语句导致的读磁盘次数。 |
|
BUFFER_GETS |
NUMBER |
所有子游标运行这条语句导致的读内存次数。 |
|
ROWS_PROCESSED |
NUMBER |
这条语句处理的总记录行数。 |
|
COMMAND_TYPE |
NUMBER |
Oracle命令类型代号。 |
|
OPTIMIZER_MODE |
VARCHAR2(10) |
执行这条的优化器模型。 |
|
PARSING_USER_ID |
NUMBER |
第一次解析这条语句的用户的ID。 |
|
PARSING_SCHEMA_ID |
NUMBER |
第一次解析这条语句所用的schema的ID。 |
|
KEPT_VERSIONS |
NUMBER |
所有被DBMS_SHARED_POOL包标识为保持（Keep）状态的子游标数。 |
|
ADDRESS |
RAW(4 | 8) |
指向语句的地址（Address of the handle to the parent for this cursor） |
|
HASH_VALUE |
NUMBER |
这条语句在library cache中hash值。 |
|
MODULE |
VARCHAR2(64) |
在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_MODULE设置的模块名称。 |
|
MODULE_HASH |
NUMBER |
模块的Hash值 |
|
ACTION |
VARCHAR2(64) |
在第一次解析这条语句是通过调用DBMS_APPLICATION_INFO.SET_ACTION设置的动作名称。 |
|
ACTION_HASH |
NUMBER |
动作的Hash值 |
|
SERIALIZABLE_ABORTS |
NUMBER |
所有子游标的事务无法序列化的次数，这会导致ORA-08177错误。 |
|
IS_OBSOLETE |
VARCHAR2(1) |
游标是否被废除（Y或N）。当子游标数太多了时可能会发生。 |
|
CHILD_LATCH |
NUMBER |
为了包含此游标的子latch数。 |
查看当前会话所执行的语句以及会话相关信息：
SQL> select a.sid||'.'||a.SERIAL#, a.username, a.TERMINAL, a.program, s.sql_text 2 from v$session a, v$sqlarea s 3 where a.sql_address = s.address(+) 4 and a.sql_hash_value = s.hash_value(+) 5 order by a.username, a.sid; ... ... SQL>视图V$SQL_PLAN包含了library cache中所有游标的执行计划。通过结合v$sqlarea可以查出library cache中所有语句的查询计划。先从v$sqlarea中得到语句的地址，然后在由v$sql_plan查出它的查询计划：
SQL> select lpad(' ', 2*(level-1))||operation "Operation", 2 options "Options", 3 decode(to_char(id), '0', 'Cost='||nvl(to_char(position), 'n/a'), object_name) "Object Name", 4 substr(optimizer, 1, 6) "Optimizer" 5 from v$sql_plan a 6 start with address = 'C0000000FCCDEDA0' 7 and id = 0 8 connect by prior id = a.parent_id 9 and prior a.address = a.address 10 and prior a.hash_value = a.hash_value; Operation Options Object Name Optimizer------------------- -------------------- -------------------- ---------SELECT STATEMENT Cost=0 CHOOSE NESTED LOOPS INDEX RANGE SCAN CSS_BL_CNTR_IDX1 ANALYZ INDEX RANGE SCAN CSS_BKG_BL_ASSN_UQ1 ANALYZ SQL>这个视图包含了关于library cache的性能统计信息，对于共享池的性能调优很有帮助。它是按照命名空间分组统计的，结构如下：
|
字段 |
数据类型 |
说明 |
|
NAMESPACE |
VARCHAR2(15) |
library cache的命名空间 |
|
GETS |
NUMBER |
请求GET该命名空间中对象的次数。 |
|
GETHITS |
NUMBER |
请求GET并在内存中找到了对象句柄的次数（锁定命中，latch命中）。 |
|
GETHITRATIO |
NUMBER |
请求GET的命中率。 |
|
PINS |
NUMBER |
请求pin住该命名中对象的次数。 |
|
PINHITS |
NUMBER |
库对象的所有元数据在内存中被找到的次数（pin命中）。 |
|
PINHITRATIO |
NUMBER |
Pin命中率。 |
|
RELOADS |
NUMBER |
Pin请求需要从磁盘中载入对象的次数。 |
|
INVALIDATIONS |
NUMBER |
命名空间中的非法对象（由于依赖的对象被修改所导致）数。 |
|
DLM_LOCK_REQUESTS |
NUMBER |
GET请求导致的实例锁的数量。 |
|
DLM_PIN_REQUESTS |
NUMBER |
PIN请求导致的实例锁的数量. |
|
DLM_PIN_RELEASES |
NUMBER |
请求释放PIN锁的次数。 |
|
DLM_INVALIDATION_REQUESTS |
NUMBER |
GET请求非法实例锁的次数。 |
|
DLM_INVALIDATIONS |
NUMBER |
从其他实例那的得到的非法pin数。 |
注释：
gets字段是针对找句柄（library cache object handle），而pins字段是针对找library cache object。
library cache object handle和library cache object 两个部分组成了一个完整的库对象。
其中，library cache object 这个部分可以被刷出到磁盘上，需要时再重载（reload）到库缓存中。
只有状态为recreatable的chunk才是可以被刷出的对象（位于shared pool的LRU链表上）。
其中PIN的命中率（或未命中率）是我们系统调优的一个重要依据：
SQL> select sum(pins) "hits", 2 sum(reloads) "misses", 3 sum(pins)/(sum(pins)+sum(reloads)) "Hits Ratio" 4 from v$librarycache; hits misses Hits Ratio---------- ---------- ---------- 84962803 288 0.99999661 SQL> SQL> select sum(pins) "hits", 2 sum(reloads) "misses", 3 ((sum(reloads)/sum(pins))*100) "Reload%" 4 from v$librarycache; hits misses Reload%---------- ---------- ---------- 84963808 288 0.00033896 SQL>当命中率小于99%或未命中率大于1%时，说明系统中硬解析过多（重载（reload）不是意味着将library
cache object 这个部分从磁盘读取到库缓存中？而是通过重新解析SQL语句来重建（recreate）library
cache object 这个部分？），要做系统优化（增加Shared Pool、使用绑定变量、修改cursor_sharing等措施，性能优化不是本文重点，不再赘述）。
这个视图显示了各个命名空间中的库缓存内存对象的内存分配情况。一个内存对象是为了高效管理而组织在一起的一组内部内存。一个库对象可能包含多个内存对象。
|
字段 |
数据类型 |
说明 |
|
LC_NAMESPACE |
VARCHAR2(15) |
Library cache命名空间 |
|
LC_INUSE_MEMORY_OBJECTS |
NUMBER |
属于命名空间并正被在共享池使用的内存对象数。 |
|
LC_INUSE_MEMORY_SIZE |
NUMBER |
正在使用的内存对象的大小总（M未单位）。 |
|
LC_FREEABLE_MEMORY_OBJECTS |
NUMBER |
共享池中空闲的内存对象数。 |
|
LC_FREEABLE_MEMORY_SIZE |
NUMBER |
空闲内存对象的大小总和（M为单位）。 |