Oracle 内存的配置和使用 (10g r2) <3>

 

配置和使用Shared pool 和large pool

Oracle使用shared pool来缓存很多不同类型的数据，包含PLSQL块，SQL语句，字典缓存数据等等。
正确的使用和配置shared pool的大小能够减少资源的消耗，至少在下面4个方面：
1.如果SQL语句已经存在shared pool将消除硬解析，节约了终端用户的CPU时间。
2.闩资源使用显著降低，有助于可伸缩性。
3.shared pool 内存需求降低，因为应用程序对SQL和字典数据的重用。
4.I/O需求降低，因为字典数据的缓冲将减少对磁盘的访问。
这个章节将包含：
■ Shared Pool 概念
■ 有效的使用Shared Pool
■ 确定Shared Pool的大小
■ 关于Shared Pool 的统计
■ 使用Large Pool
■ 使用CURSOR_SPACE_FOR_TIME
■ Caching Session Cursors缓存会话游标
■ 配置 Reserved Pool
■ Keeping Large Objects to Prevent Aging保持大对象防止老化
■ CURSOR_SHARING 为现有的系统
■ Maintaining Connections
－Shared Pool 概念
Shared Pool主要是由library cache和dictionary cache组成。library cache存储了最近执行（解析或者编译）的SQL和PLSQL代码。dictionary cache存储了一些书籍字典。Shared Pool里面的大多数cache都根据需求自动增加或者减少，如果shared pool没有空闲空间，将会消除老化的数据实体的空间而供给新的数据。data dictionary cache 或者library cache的命中率丢失的后果比buffer cache更加影响性能。因此，shared pool必须被设置为合适的大小以确保经常使用的数据被缓存。
一些特性使得大内存的分配在shared pool执行，例如：共享服务器，并行查询，恢复管理器。Oracle建议把这些特性的操作在SGA里隔离为一个单独的区域，称为large pool。
Dictionary Cache概念
Dictionary Cache存储了包括：用户名，段信息，表空间信息，元数据等。Oracle使用这些数据用于解析SQL或者编译PSLQL程序。
Library Cache概念
Library Cache保存了执行了的SQL游标、PL/SQL程序、java类。它们的调优很应用程序相关。
当应用程序运行的时，Oracle会先尝试重用已经存在的代码，当然它必须已经执行和解析过而且能够被共享，那么Oracle将重用它。这就是所谓的软解析或library cache命中。如果Oracle不能使用已有的代码，那么一个新版代码会被建立，这就叫硬解析，或library cache丢失。Library cache丢失可能发生在解析也可能发生在运行时期，当程序解析SQL的时候，如果发现它没有存在于cache中，Oracle将重新解析并存储于cache中，这是硬解析。减少library cache丢失的办法，最好就是无论什么时候都保证共享SQL都被存储在shared pool中。如果已经解析过的SQL老化，仍然会发生硬解析，通常解决这个问题是增加library cache的大小。
为了提高硬解析的性能，Oracle分配给硬解析更多的资源。软件解析使用的系统资源包括：CPU和library cache latch 获取。硬解析包括CPU library cache latch 和shared pool latch获得。
共享SQL的标准
Oracle自动的判断某SQL或PL/SQL是否适合于其它语句。
Oracle通过下面的步骤来进行判断：
1.通过SQL文本和已经存储在shared pool里面的SQL进行比较。
2.求出文本的hash值，如果hash值不等，那么将会导致硬解析。
3.如果hash值相等，Oracle会比较语句的文本，包括空格，条件，对象必须完全一致。例如下面的SQL仅仅是空格大小写不一样，但是它们不能使用同样的SQL区域：
SELECT * FROM employees;
SELECT * FROM Employees;
SELECT * FROM employees;
下面的SQL也不会存储于同样的区域：
SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;
但当CURSOR_SHARING设置为SIMILAR 和 FORCE可以解决这些问题，我们将在后面讨论这个参数的好处。
4.比较应用的对象，必须是同一schema里面的同一对象。
SELECT * FROM employees;employees表必须在同一schema里面。
5.绑定变量，必须名称，数据类型和长度一致。
下面的SQL，不能绑定，因为名称不一致：
SELECT * FROM employees WHERE department_id = :department_id;
SELECT * FROM employees WHERE department_id = :dept_id;
很多Oracle的产品，诸如Forms和precompilers,都会在解析前，处理SQL，转化同一的大写，压缩空格，使得SQL尽可能的一致。
－有效的使用Shared Pool
使用Shared Pool的目的，就是为了使得多次使用SQL和PL/SQL避免硬解析，降低CPU,内存,和闩的使用。shared pool在数据仓库环境中也支持不共享SQL的应用，当运行一个发生频率低，资源消费高的SQL时候。这种环境下，建议使用非共享SQL。使用显示值比绑定变量更能允许优化器做出更好的字段选择，提供更好的执行计划。See Also: Oracle Database Data Warehousing Guide
在OLTP系统中，有很多方法来确保有效的使用shared pool和相关资源，如下：
■ Shared Cursors
■ Single-User Logon and Qualified Table Reference
■ Use of PL/SQL
■ Avoid Performing DDL
■ Cache Sequence Numbers
■ Cursor Access and Management
Shared Cursors
为了共享游标，按下面的步骤进行：
*尽可能的使用绑定变量如：
SELECT employee_id FROM employees WHERE department_id = 10;
SELECT employee_id FROM employees WHERE department_id = 20;
改为
SELECT employee_id FROM employees WHERE department_id = :dept_id;
（当然也可以使用CURSOR_SHARING）
*不要在应用程序中设计过多的动态而不能共享的SQL。
*确保应用程序的用户不会为了它自己的会话而改变最优路径和目标。
*建立下面的程序开发习惯：
 在SQL后者PL/SQL中对绑定变量的命名、空格标准化。
 尽可能的使用存储过程。尽力多使用同一过程，那么PL/SQL区域会自动共享，减少运行时解析。
*如果SQL语句一致，当却无法共享，查询V$SQL_SHARED_CURSOR诊断为什么游标不能共享。这可能是由

于优化器设置和绑定变量配置引起的。
Single-User Logon and Qualified Table Reference
在很大的OLTP系统中，每个用户登录都有他自己的ID，使用单独的资格表定义，比使用公共用户要好。将会显著的减少dictionary cache的数量。例如：
SELECT employee_id FROM hr.employees WHERE department_id = :dept_id;
Use of PL/SQL
使用大量并发的情况下使用PL/SQL包，能够提高系统的伸缩性。
Avoid Performing DDL
在系统高峰期，避免使用DDL，它会导致和相关数据对象的所有SQL，PL/SQL失效而重新解析。
Cache Sequence Numbers
增加频繁使用的序列的cache大小。
Cursor Access and Management
Reducing Parse Calls with OCI
Reducing Parse Calls with the Oracle Precompilers
当使用Oracle Precompilers，使用下面三个参数来设置:
■ HOLD_CURSOR = YES
■ RELEASE_CURSOR = NO
■ MAXOPENCURSORS = desired_value
Reducing Parse Calls with SQLJ
Reducing Parse Calls with JDBC
Reducing Parse Calls with Oracle Forms
-确定Shared Pool的大小
Shared Pool: Library Cache 统计
V$LIBRARYCACHE用于监控从实例启动开始library cache的活动。
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
--------------- ---------- ---------- ---------- -------------
BODY 8870 8819 0 0
CLUSTER 393 380 0 0
INDEX 29 0 0 0
OBJECT 0 0 0 0
PIPE 55265 55263 0 0
SQL AREA 21536413 21520516 11204 2
TABLE/PROCEDURE 10775684 10774401 0 0
TRIGGER 1852 1844 0 0
计算命中率的公式
Library Cache Hit Ratio = sum(pinhits) / sum(pins)

SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';
The output will be similar to the following:
POOL NAME BYTES
----------- -------------------------- ----------
shared pool free memory 4928280
shared pool的顾问统计包含：
SELECT * FROM V$SHARED_POOL_ADVICE
SELECT * FROM V$LIBRARY_CACHE_MEMORY
SELECT * FROM V$JAVA_POOL_ADVICE 和V$JAVA_LIBRARY_CACHE_MEMORY
Shared Pool: Dictionary Cache Statistics
命中率统计
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;
公式：SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
关于Shared Pool统计
增加内存分配对library cache 和dictionary cache都有利。
为Library Cache分配额外的内存
确保V$LIBRARYCACHE.RELOADS接近于O。设置SHARED_POOL_SIZE的大小。
为Data Dictionary Cache分配额外的内存
－使用Large Pool
和shared pool不同，large不会消除large pool中的老化对象。如果你正在使用如下特性，那么你应该配置large pool：
*并行查询：Oracle使用shared pool来缓冲并行执行的信息缓存数据。
*恢复管理：恢复管理器使用shared pool来缓存I/O缓冲块。
*共享服务器：每个客户端进程的会话信息。
SELECT SUM(VALUE) || ' BYTES' "TOTAL MEMORY FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
SELECT SUM(VALUE) || ' BYTES' "TOTAL MAX MEM FOR ALL SESSIONS"
FROM V$SESSTAT, V$STATNAME
WHERE NAME = 'session uga memory max'
AND V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC#;
设置PRIVATE_SGA来确定每个用户用到的最大内存。

 

原文：http://valen.blog.ccidnet.com/blog-htm-itemid-153572-do-showone-type-blog-uid-51502.html