Oracle内存全面分析(5)

作者： fuyuncat
来源： www.HelloDBA.com

1.1.4.7.             共享池的重要视图

最后，我们再介绍关于共享池的一些重要视图

·         v$shared_pool_advice

这个视图与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	估算的可以直接从共享池中命中库缓存的内存对象的命中次数。

关于如何根据建议器采用合理的共享池大小的方法，和前面提到的缓冲区建议器的使用方法类似，不再赘述。

·         V$SHARED_POOL_RESERVED

前面提到了这个视图。这个视图存放了共享池保留区的统计信息。可以根据这些信息来调整保留区。视图结构如下：

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 错误的发生。

·          v$db_object_cache

这一视图显示了所有被缓存在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 的数量。

·         v$sql 、 v$sqlarea 、 v$sqltext

这三个视图都可以用于查询共享池中已经解析过的SQL 语句及其相关信息。

V$SQL 中列出了共享SQL 区中所有语句的信息，它不包含GROUP BY 字句，并且为每一条SQL 语句中单独存放一条记录；

V$SQLAREA 中一条记录显示了一条共享SQL 区中的统计信息。它提供了有在内存中、解析过的和准备运行的SQL 语句的统计信息；

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)	指向语句的地址
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

视图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>

·         v$librarycache

这个视图包含了关于library cache 的性能统计信息，对于共享池的性能调优很有帮助。它是按照命名空间分组统计的，结构如下：

字段	数据类型	说明
NAMESPACE	VARCHAR2(15)	library cache 的命名空间
GETS	NUMBER	请求GET 该命名空间中对象的次数。
GETHITS	NUMBER	请求GET 并在内存中找到了对象句柄的次数（锁定命中）。
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 数。

其中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% 时，说明系统中硬解析过多，要做系统优化（增加Shared Pool 、使用绑定变量、修改cursor_sharing 等措施，性能优化不是本文重点，不再赘述）。

 

 

·           v$library_cache_memory

 

这个视图显示了各个命名空间中的库缓存内存对象的内存分配情况。一个内存对象是为了高效管理而组织在一起的一组内部内存。一个库对象可能包含多个内存对象。

字段	数据类型	说明
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 为单位）。

·         v$sgastat

这个视图前面介绍过，是关于SGA 使用情况的统计。其中，关于Shared Pool 有详细的统计数据。

1.1.5.    重做日志缓存（ Redo Log Buffer ）

Redo Log Buffer 是SGA 中一段保存数据库修改信息的缓存。这些信息被存储在重做条目(Redo Entry) 中. 重做条目中包含了由于INSERT 、UPDATE 、DELETE 、CREATE 、ALTER 或DROP 所做的修改操作而需要对数据库重新组织或重做的必须信息。在必要时，重做条目还可以用于数据库恢复。

重做条目是Oracle 数据库进程从用户内存中拷贝到Redo Log Buffer 中去的。重做条目在内存中是连续相连的。后台进程LGWR 负责将Redo Log Buffer 中的信息写入到磁盘上活动的重做日志文件（Redo Log File ）或文件组中去的。

参数LOG_BUFFER 决定了Redo Log Buffer 的大小。它的默认值是512K （一般这个大小都是足够的），最大可以到4G 。当系统中存在很多的大事务或者事务数量非常多时，可能会导致日志文件IO 增加，降低性能。这时就可以考虑增加LOG_BUFFER 。

但是，Redo Log Buffer 的实际大小并不是LOB_BUFFER 的设定大小。为了保护Redo Log Buffer ，oracle 为它增加了保护页（一般为11K ）：

SQL> select * from v$sgastat where name = 'log_buffer';

POOL         NAME                            BYTES

------------ -------------------------- ----------

             log_buffer                    7139328

1 row selected.

SQL> show parameter log_buffer

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------------

log_buffer                           integer     7028736

SQL>

1.1.6.    大池（ large pool ）

大池是SGA 中的一块可选内存池，根据需要时配置。在以下情况下需要配置大池：

o         用于共享服务（Shared Server MTS 方式中）的会话内存和Oracle 分布式事务处理的Oracle XA 接口

o         使用并行查询（Parallel Query Option PQO ）时

o         IO 服务进程

o         Oracle 备份和恢复操作（启用了RMAN 时）

通过从大池中分配会话内存给共享服务、Oracle XA 或并行查询，oracle 可以使用共享池主要来缓存共享SQL ，以防止由于共享SQL 缓存收缩导致的性能消耗。此外，为Oracle 备份和恢复操作、IO 服务进程和并行查询分配的内存一般都是几百K ，这么大的内存段从大池比从共享池更容易分配得到（所以叫“大”池嘛^_^ ）。

参数LARGE_POOL_SIZE 设置大池的大小。大池是属于SGA 的可变区（Variable Area ）的，它不属于共享池。对于大池的访问，是受到large memory latch 保护的。大池中只有两种内存段：空闲（free ）和可空闲（freeable ）内存段（关于不同类型内存段我们在后面介绍）。它没有可重建（recreatable ）内存段，因此也不用LRU 链表来管理（这和其他内存区的管理不同）。大池最大大小为4G 。

为了防止大池中产生碎片，隐含参数_LARGE_POOL_MIN_ALLOC 设置了大池中内存段的最小大小，默认值是16K （同样，不建议修改隐含参数）。

此外，large pool 是没有LRU 链表的。

1.1.7.    Java 池（ Java Pool ）

Java 池也是SGA 中的一块可选内存区，它也属于SGA 中的可变区。

Java 池的内存是用于存储所有会话中特定Java 代码和JVM 中数据。Java 池的使用方式依赖与Oracle 服务的运行模式。

Java 池的大小由参数JAVA_POOL_SIZE 设置。Java Pool 最大可到1G 。

在Oracle 10g 以后，提供了一个新的建议器——Java 池建议器——来辅助DBA 调整Java 池大小。建议器的统计数据可以通过视图V$JAVA_POOL_ADVICE 来查询。如何借助建议器调整Java 池的方法和使用Buffer Cache 建议器类似，可以参考Buffer Cache 中关于建议器部分。

1.1.8.    流池（ Streams Pool ）

流池是Oracle 10g 中新增加的。是为了增加对流（流复制是Oracle 9iR2 中引入的一个非常吸引人的特性，支持异构数据库之间的复制。10g 中得到了完善）的支持。

流池也是可选内存区，属于SGA 中的可变区。它的大小可以通过参数STREAMS_POOL_SIZE 来指定。如果没有被指定，oracle 会在第一次使用流时自动创建。如果设置了SGA_TARGET 参数，Oracle 会从SGA 中分配内存给流池；如果没有指定SGA_TARGET ，则从buffer cache 中转换一部分内存过来给流池。转换的大小是共享池大小的10% 。

Oracle 同样为流池提供了一个建议器——流池建议器。建议器的统计数据可以通过视图V$STREAMS_POOL_ADVICE 查询。使用方法参看Buffer Cache 中关于优化器部分。