Tuning the Shared Pool and the Large Pool

一、12c：官档

• Books → Performance Tuning Guide → Part III Tuning Database Memory → 14 Tuning the Shared Pool and the Large Pool

二、关于 Shared Pool

Oracle数据库使用共享池来缓存许多不同类型的数据。缓存的数据包括PL/SQL块和SQL语句、字典缓存数据、结果缓存数据和其他数据的文本和可执行形式。

1 使用 Shared Pool 的好处

适当地使用和调整 Shared Pool 可以减少至少四种方式的资源消耗：

• 如果SQL语句位于 Shared Pool 中，则可以避免解析开销，从而减少系统上的CPU资源和最终用户的运行时间。
• 延迟资源的使用会大大减少，从而导致更大的可伸缩性。
• 共享池内存需求减少，因为所有应用程序都使用相同的SQL语句池和字典资源。
• 减少了I/O，因为共享池中的dictionary元素不需要磁盘访问。

2 Shared Pool 概念

共享池的主要组件包括：

• Library cache
  Library cache 存储最近引用的SQL和PL/SQL代码的可执行(解析或编译)形式。
• Data dictionary cache
  Data dictionary cache 存储从数据字典引用的数据。
• Server result cache (取决于配置)
  Server result cache 是一个可选的缓存，在共享池中存储查询和PL/SQL函数结果。有关服务器结果缓存的信息，请参阅“About the Result Cache”。

Shared Pool 中的许多缓存(包括 Library cache 和 Data dictionary cache)根据需要自动增加或减少大小。当共享池耗尽空间时，旧的条目将过时以适应新条目。
在 Library cache 和 Data dictionary cache 中丢失的缓存比缓冲区缓存中的缺失值要高。出于这个原因，应该对共享池进行大小调整，以确保缓存频繁使用的数据。
一些特性需要在 shared pool 中分配大量内存，比如共享服务器、并行查询或恢复管理器。Oracle建议使用一个单独的内存区域(large pool)来隔离这些特性所使用的 SGA 内存。
从共享池中分配内存的方式是块。这个组块可以使大型对象(超过5kb)加载到缓存中，而不需要单个连续区域。这样，数据库就减少了由于碎片而耗尽连续内存的可能性。
Java、PL/SQL或SQL游标有时可能会从大于5kb的共享池中进行分配。为了更有效地实现这些分配，Oracle数据库将少量共享池隔离。这部分内存(被称为 reserved pool )是在共享池耗尽空间时使用的。

2.1 Library Cache 概念

Library Cache 存储了SQL游标、PL/SQL程序和Java类的可执行形式，它们统称为应用程序代码。本节主要讨论与应用程序代码相关的调优。
当执行应用程序代码时，Oracle数据库尝试重用现有的代码，如果它已经被执行，并且可以被共享。如果SQL语句的解析表示存在于 Library Cache 中，并且可以共享，那么数据库将重用现有的代码。这被称为软解析，或 Library Cache 命中。如果Oracle数据库不能使用现有代码，那么数据库必须构建应用程序代码的新可执行版本。这被称为硬解析，或 Library Cache 缺失。有关SQL和PL/SQL语句何时可以共享的信息，请参见“SQL Sharing Criteria”。
为了执行硬解析，Oracle数据库使用的资源比在软解析期间要多。用于软解析的资源包括CPU和 Library Cache Latch。硬解析所需的资源包括额外的CPU、Library Cache Latch 和 Shared Pool Latch。在处理SQL语句时，解析步骤或执行步骤可能发生硬解析。
当应用程序对SQL语句进行解析调用时，如果该语句的解析表示不存在于 Library Cache 中，那么Oracle数据库将解析该语句并将解析后的表单存储在 Shared Pool 中。为了减少解析调用的 Library Cache，请确保所有可共享的SQL语句都存储在 Shared Pool 中。
当一个应用程序的执行调用SQL语句，如果SQL语句的可执行部分从 Library Cache 中过期(或释放)，为另一个语句腾出空间，那么Oracle数据库隐式地重新解析语句来创建一个新的共享SQL区域，并执行该语句。这也导致了一个硬解析。要在执行调用时减少 Library Cache 遗漏，将更多内存分配给 Library Cache。
有关硬和软解析的更多信息，请参见“SQL Execution Efficiency”。

2.2 Data Dictionary Cache 概念

存储在数据字典缓存中的信息包括:

• 用户名
• 段信息
• 配置文件数据
• 表空间信息
• 序列号

Data Dictionary Cache 还存储关于模式对象的描述性信息或元数据。Oracle数据库在解析SQL游标或编译PL/SQL程序时使用此元数据。

2.3 SQL 共享标准

Oracle 数据库将自动确定发出的SQL语句或 PL/SQL 块是否与当前共享池中的另一个语句相同。
为了将 SQL 语句的文本与共享池中现有的SQL语句进行比较，Oracle 数据库执行以下步骤：

1. SQL 语句的文本 hash 化
   如果没有匹配的hash值，那么SQL语句当前并不存在于共享池中，并且执行了一个硬解析。
2. 如果共享池中的现有SQL语句有匹配的 hash 值，则将匹配语句的文本与 hash 语句的文本进行比较，以验证它们是否相同。
   SQL语句或PL/SQL块的文本必须是相同的字符，包括空格、案例和注释。例如，以下语句不能使用相同的共享SQL区域：
   

   SELECT * FROM employees;
   SELECT * FROM Employees;
   SELECT *  FROM employees;

   而且，仅在文本中不同的SQL语句不能使用相同的共享SQL区域。例如，下面的语句不解析到同一个SQL区域：
   

   SELECT count(1) FROM employees WHERE manager_id = 121;
   SELECT count(1) FROM employees WHERE manager_id = 247;

   这个规则的唯一例外是当 CURSOR_SHARING 参数设置为强制时，在这种情况下，类似的语句可以共享SQL区域。有关游标共享的成本和收益的信息，请参见“Sharing Cursors”。
   
3. 在发布的语句中引用的对象与 Shared Pool 中所有现有语句的引用对象相比较，以确保它们是相同的。
   在SQL语句或PL/SQL块中对 schema 对象的引用必须在相同的 schema 中解析到同一个对象。例如，如果两个用户分别发出以下SQL语句，但是每个用户都有自己的employee表，那么这个语句就不被认为是相同的，因为该语句引用了每个用户的不同表:
   

   SELECT * FROM employees;

4. 在SQL语句中绑定变量必须匹配名称、数据类型和长度。
   例如，下面的语句不能使用相同的共享SQL区域，因为绑定变量名称不同：
   

   SELECT * FROM employees WHERE department_id = :department_id;
   SELECT * FROM employees WHERE department_id = :dept_id;

   许多Oracle产品，如Oracle表单和预编译器，在将语句传递到数据库之前转换SQL。字符被一致地更改为大写，空格被压缩，绑定变量被重命名，这样就生成了一组一致的SQL语句。
   
5. 会话的环境必须是相同的。
   例如，必须使用相同的优化目标优化SQL语句。

See Also:

• 《Oracle Database Reference》了解有关CURSOR_SHARING初始化参数的信息。

三、使用 Shared Pool

共享池的一个重要目的是缓存SQL和PL/SQL语句的可执行版本。这允许在不需要硬解析所需的资源的情况下多次执行执行过的同一SQL或PL/SQL代码，这将导致CPU、内存和 latch 使用的显著减少。
共享池还能够支持数据仓库应用程序中的非共享SQL，这些应用程序执行低并发性、高资源的SQL语句。在这种情况下，建议使用非共享SQL和文字值。使用文字值而不是绑定变量使优化器能够做出良好的列选择性估计，从而提供最佳的数据访问计划。
在高汇率的联机事务处理(OLTP)系统中，有效地使用 Shared Pool 大大降低了与解析相关的应用程序可伸缩性问题的概率。有几种方法可以确保在OLTP系统中有效地使用共享池和相关资源。
See Also:

• 《Oracle Database VLDB and Partitioning Guide》了解有关并行查询执行对共享池的影响的信息。

1 使用共享游标

对运行相同应用程序的多个用户重用共享SQL避免了硬解析。软解析可以显著减少资源的使用，例如共享池和库缓存锁。
使用共享游标

• 如果可能的话，在SQL语句中使用绑定变量代替值
  例如，以下两个SQL语句不能使用相同的共享区域，因为它们不匹配字符：
  

  SELECT employee_id FROM employees WHERE department_id = 10;
  SELECT employee_id FROM employees WHERE department_id = 20;

  用绑定变量替换文本只会导致一个SQL语句，该语句可以执行两次：
  

  SELECT employee_id FROM employees WHERE department_id = :dept_id;

  对于现有应用程序，重写代码以使用绑定变量是不可能的，使用CURSOR_SHARING初始化参数来避免“Sharing Cursors”中描述的一些硬解析开销。
  
• 避免应用程序设计，导致大量用户发出动态的、未共享的SQL语句。
  通常，大多数用户所需的大多数数据都可以使用预先设置的查询来满足。在需要这种功能的地方使用动态SQL。
• 确保应用程序的用户不会更改其单独会话的优化方法和目标。
• 为应用程序开发人员制定以下政策：
  • 为SQL语句和PL/SQL块的绑定变量和间隔约定标准化命名约定。
  • 尽可能考虑使用存储过程。
    发出相同存储过程的多个用户会自动使用相同的共享PL/SQL区域。由于存储过程以解析形式存储，因此它们的使用减少了运行时解析。
• 对于相同但不共享的SQL语句，查询V$SQL_SHARED_CURSOR视图，以确定为什么不共享游标。
  这包括优化器设置和绑定变量不匹配。

See Also:

• 《Oracle Database SQL Tuning Guide》有关游标共享的更多信息。

2 使用单用户登录和合格的表引用

在大型OLTP系统中，用户使用自己的用户登录到数据库，因此显式地对段所有者进行限定，而不是使用公共的同义词，大大减少了字典缓存中的条目数。
对符合资格的表名的另一种选择是通过单个用户登录而不是个人用户登录来连接到数据库。用户级验证可以在中间层本地进行。

3 使用 PL/SQL

使用存储的PL/SQL包可以克服许多系统的可伸缩性问题，这些问题涉及数千个用户，每个用户都有单独的用户登录和公共同义词。这是因为包是作为所有者执行的，而不是调用者，这大大减少了字典缓存负载。
Note:
Oracle鼓励使用definer的权限包来克服可伸缩性问题。减少字典缓存负载的好处与invoker的权限包不一样。

4 避免执行DDL操作

避免在高峰时段在高使用率段执行DDL操作。在这些段上执行DDL操作通常会导致依赖的SQL在稍后的执行中失效和修复。

5 缓存序列号

为频繁更新的序列号分配足够的缓存空间可以显著降低字典缓存锁的频率，从而提高可伸缩性。
为每个序列配置缓存条目的数量：

• 在 CREATE SEQUENCE 或 ALTER SEQUENCE 语句中使用 CACHE 关键字。

6 控制游标访问

根据您的应用程序工具，您可以控制应用程序通过控制游标访问来执行解析调用的频率。
应用程序关闭游标或重用现有游标的频率会影响到会话使用的内存数量，而且通常是由该会话执行的解析量。关闭游标或重新使用游标(针对不同的SQL语句)的应用程序不需要像保持游标打开的应用程序那样多的会话内存。相反，相同的应用程序可能需要执行更多的解析调用，使用更多的CPU和数据库资源。
与不经常执行的SQL语句相关联的游标可以被关闭或用于其他语句，因为重新执行(和重新解析)语句的可能性很低。当包含将被重新执行的SQL语句的游标被关闭或用于另一个语句时，需要额外的解析调用。如果游标保持打开，它可能会在没有发出解析调用的开销的情况下被重用。

7 保持持久连接

使用中间层的大型OLTP应用程序应该维护连接，而不是连接和断开每个数据库请求。维护持久的连接可以节省CPU资源和数据库资源，比如 latch。

四、配置 Shared Pool

1 设置 Shared Pool 大小

在配置新的数据库实例时，很难知道共享池缓存的正确大小。通常，DBA首先对缓存大小进行估计，然后在实例上运行一个有代表性的工作负载，并检查相关的统计信息，以查看缓存是否配置不足或配置过度。
对于大多数OLTP应用程序，共享池大小是应用程序性能的一个重要因素。对于发出非常有限数量的离散SQL语句(如决策支持系统)的应用程序来说，共享池大小并不重要。
如果共享池太小，则使用额外的资源来管理有限的可用空间。这会消耗CPU和锁存资源，并导致争用。理想情况下，共享池应该足够大，可以缓存经常访问的对象。在共享池中拥有大量的空闲内存是浪费内存。在数据库运行后检查统计数据时，确保工作负载中没有出现这些错误。

1.1 使用 Library Cache 统计数据

在设置 shared pool 大小时，目标是缓存在 Library Cache 中多次执行的SQL语句，而不会过度分配内存。为了实现这个目标，请检查以下 Library Cache 统计信息：

• RELOADS
  V$LIBRARYCACHE 视图中的 RELOADS 列显示了从缓存中取出的预先缓存的SQL语句重新加载(或重新解析)的数量。如果应用程序能够有效地重用SQL并在具有最佳共享池大小的系统上运行，那么这个统计值应该具有接近于零的值。
• INVALIDATIONS
  V$LIBRARYCACHE视图中的 INVALIDATIONS 列显示了 Library Cache 数据无效的次数，必须进行修复。这个统计值应该有一个接近于零的值，尤其是在峰值负载期间的OLTP系统。这意味着可以共享的SQL语句被某些操作(比如DDL)无效。
• Library Cache 命中率
  Library Cache 命中率是Library Cache 健康的一个广泛指标。这个值应该与其他统计数据一起考虑，例如硬解析的速率，如果有任何 Shared Pool 或Library Cache latch 争用。
• Shared Pool 中的空闲内存数量
  要查看 Shared Pool 中的空闲内存数量，请查询V$SGASTAT性能视图。理想情况下，空闲内存应该尽可能低，而不会对系统进行任何重新解析。

1.1.1 使用 V$LIBRARYCACHE 视图

使用 V$LIBRARYCACHE 视图来监视反映 Library Cache 活动的统计信息。这些统计数据反映了在最近的数据库实例启动之后的所有库缓存活动。
该视图中的每一行都包含了保存在 Library Cache 中的一种项目的统计信息。每一行描述的项目由 NAMESPACE 列的值标识。具有以下 NAMESPACE 值的行反映了SQL语句和PL/SQL块的 Library Cache 活动：

• SQL AREA
• TABLE/PROCEDURE
• BODY
• TRIGGER

与其他 NAMESPACE 值的行反映了 Library Cache 活动，用于Oracle数据库用于依赖关系维护的对象定义。
示例 1：显示了这个视图的查询，以逐个检查每个名称空间。

SELECT namespace, pins, pinhits, reloads, invalidations
  FROM V$LIBRARYCACHE
 ORDER BY namespace;

该查询的输出可能如下所示：

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

在本例中，输出表示：

• 对于 SQL AREA namespace，有21536,413个执行。
• 这些执行的11,204个导致了一个 Library Cache 缺失，要求数据库隐式地重新解析一个语句或块，或者重新加载一个对象定义，因为它是从 Library Cache 中取出的。
• SQL语句无效两次，再次导致 Library Cache 丢失。

Note:
该查询返回实例启动时的数据。使用在间隔上收集的统计信息可以更好地识别问题。有关在间隔内收集信息的信息，请参见“Automatic Performance Diagnostics”。
See Also:

• 《Oracle Database Reference》有关V$LIBRARYCACHE视图的信息。

1.1.2 收集 Library Cache 命中率

若要计算 Library Cache 命中率，请使用以下公式：

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

将库缓存命中率公式应用到示例 1，结果如下库缓存命中率：

SUM(PINHITS)/SUM(PINS)
----------------------
            .999466248

在本例中，命中率约为99.94%，这意味着只有这样。0.06 %的执行导致了重新解析。

1.1.3 查看 Shared Pool 中的空闲内存数量

Shared Pool 中空闲内存的数量在$SGASTAT视图中报告。
示例 2：显示了此视图的查询。

SELECT *
  FROM V$SGASTAT
 WHERE name = 'free memory'
   AND pool = 'shared pool';

该查询的输出可能如下所示：

POOL        NAME                            BYTES
----------- -------------------------- ----------
shared pool free memory                   4928280

如果空闲内存总是在 Shared Pool 中可用，那么增加它的大小几乎没有好处。然而，仅仅因为 Shared Pool 是满的并不意味着存在问题。它可能是一个配置良好的系统的指示。

1.2 使用 Shared Pool 咨询统计信息

Library Cache 可用的内存数量会极大地影响Oracle数据库的解析率。为了帮助您正确地配置 Library Cache，Oracle数据库提供了以下 Shared Pool 咨询视图：

• V$SHARED_POOL_ADVICE
• V$LIBRARY_CACHE_MEMORY
• V$JAVA_POOL_ADVICE
• V$JAVA_LIBRARY_CACHE_MEMORY

这些 Shared Pool 咨询视图提供了关于 Library Cache 内存的信息，从而使您能够预测 Shared Pool 的大小如何影响到 Shared Pool 中对象的老化。这些视图中的共享池咨询统计信息跟踪 Library Cache 对 Shared Pool 内存的使用，并预测 Library Cache 将如何在不同大小的 Shared Pool 中运行。使用这些视图，您可以确定：

• Library Cache 使用了多少内存?
• 目前有多少内存被固定?
• 在 Shared Pool 最近使用的(LRU)列表上有多少内存?
• 通过改变 Shared Pool 的大小，可以损失多少时间?

当启用 Shared Pool 通知时，这些视图显示 Shared Pool 的咨询统计信息。当禁用通知时，统计信息将被重置。

1.2.1 关于V$SHARED_POOL_ADVICE视图

V$SHARED_POOL_ADVICE 视图显示关于不同池大小的 Shared Pool 中估计解析时间的信息。大小范围从当前 Shared Pool 大小的10%或固定 Library Cache 内存的数量(以当前 Shared Pool 大小的两倍以上)为相等间隔。间隔的值取决于 Shared Pool 的当前大小。
See Also:

• 《Oracle Database Reference》关于V$SHARED_POOL_ADVICE视图的信息

1.2.2 关于 V$LIBRARY_CACHE_MEMORY 视图

V$LIBRARY_CACHE_MEMORY视图显示分配给 Library Cache 内存对象的信息，这些内存对象位于不同的 namespace 中。内存对象是用于高效管理的内存内部分组。 Library Cache 对象可能包含一个或多个内存对象。
See Also:

• 《Oracle Database Reference》关于 V$LIBRARY_CACHE_MEMORY 视图的信息

1.2.3 关于 V$JAVA_POOL_ADVICE 和 V$JAVA_LIBRARY_CACHE_MEMORY 视图

V$JAVA_POOL_ADVICE和V$JAVA_LIBRARY_CACHE_MEMORY视图包含Java池的咨询统计信息，这些统计信息跟踪用于Java的Library Cache 内存的信息，并预测如何更改Java Pool的大小会影响解析速率。
V$JAVA_POOL_ADVICE视图显示关于不同池大小的 Java Pool 中估计解析时间的信息。大小范围从当前Java池大小的10%或固定的Java Library Cache内存的大小(以当前Java Pool 大小的200%为单位)，以相等的间隔进行。间隔的值取决于Java Pool 的当前大小。
See Also:

• 《Oracle Database Reference》关于 V$JAVA_POOL_ADVICE 和 V$JAVA_LIBRARY_CACHE_MEMORY 视图的信息

1.3 使用 Dictionary Cache 统计信息

通常，如果 Shared Pool 的大小适合于 Library Cache，那么它也适合于 Dictionary Cache 数据。
在某些情况下，Dictionary Cache 的遗漏是意料之中的。当数据库实例启动时，Dictionary Cache 不包含任何数据。因此，发布的任何SQL语句都可能导致缓存遗漏。随着更多的数据被读取到缓存中，缓存丢失的可能性会降低。最终，数据库达到了一个稳定的状态，其中最常用的字典数据在缓存中。此时，很少出现缓存遗漏。
V$ROWCACHE视图中的每一行包含单个数据字典项的统计信息。这些统计数据反映了自最近的实例启动以来的所有数据字典活动。

表 1：列出了V$ROWCACHE视图中的列，这些列反映了数据字典缓存的使用和有效性。

列	描述
PARAMETER	标识一个特定的数据字典项。对于每一行，本列中的值是dc_前缀的项。例如，在包含对文件描述的统计信息的行中，该列包含值dc_files。
GETS	显示有关相应项的信息的请求总数。例如，在包含文件描述统计信息的行中，该列包含文件描述数据请求的总数。
GETMISSES	显示缓存不满足的数据请求数，并需要I/O。
MODIFICATIONS	显示 Dictionary Cache 中数据的更新次数。

示例 3：显示了查询该视图在应用程序运行期间的监视数据。派生列PCT_SUCC_GETS可以被视为项目特定的命中率。

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999

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;

该查询的输出可能如下所示：

PARAMETER              SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS      UPDATES
--------------------- ---------- -------------- ------------- ------------
dc_database_links             81              1          98.8            0
dc_free_extents            44876          20301          54.8       40,453
dc_global_oids                42              9          78.6            0
dc_histogram_defs           9419            651          93.1            0
dc_object_ids              29854            239          99.2           52
dc_objects                 33600            590          98.2           53
dc_profiles                19001              1         100.0            0
dc_rollback_segments       47244             16         100.0           19
dc_segments               100467          19042          81.0       40,272
dc_sequence_grants           119             16          86.6            0
dc_sequences               26973             16          99.9       26,811
dc_synonyms                 6617            168          97.5            0
dc_tablespace_quotas         120              7          94.2           51
dc_tablespaces            581248             10         100.0            0
dc_used_extents            51418          20249          60.6       42,811
dc_user_grants             76082             18         100.0            0
dc_usernames              216860             12         100.0            0
dc_users                  376895             22         100.0            0

在本例中，输出表示：

• 对于使用的区段、空闲区段和段，有大量的遗漏和更新。这意味着数据库实例具有相当数量的动态空间扩展。
• 比较成功的百分比和实际得到的数据的百分比，表明 Shared Pool 足够大，可以适当地存储 Dictionary Cache 数据。

您还可以使用以下查询计算整个 Dictionary Cache 命中率；然而，对所有缓存的数据进行汇总将会丢失数据的细粒度：

SELECT (SUM(gets - getmisses - fixed)) / SUM(gets) "row cache"
  FROM V$ROWCACHE;

1.4 增加分配给 Shared Pool 的内存

增加共享池的内存数量会增加库 Library Cache、Dictionary Cache 和 Result Cache的可用内存数量。在此之前，请查看 Shared Pool 统计数据并检查：

• 如果V$LIBRARYCACHE的值。重载列接近于零。
• 如果总V$ROWCACHE.GETMISSES列与总V$ROWCACHE.GETS比率低于10%或15%，对于频繁访问的Dictionary Cache，可以基于应用程序

如果满足了这两个条件，那么共享池就足够大了，并且增加内存可能不会提高性能。另一方面，如果没有满足上述任一条件，应用程序将有效地使用共享池，如“Using the Shared Pool”所描述的那样，那么考虑增加 Shared Pool 的内存。
增加 Shared Pool 的内存：

• 在满足条件之前，增加SHARED_POOL_SIZE初始化参数的值。
  此参数的最大值取决于您的操作系统。该方法减少了对SQL语句和PL/SQL块执行的隐式解析。

IC -需要链接到“Managing Server Result Cache Memory with Init Parameters”

1.5 减少分配给 Shared Pool 的内存

如果V$LIBRARYCACHE.RELOADS列的值接近于零，在共享池中有少量的空闲内存，然后共享池的大小足以存储最频繁访问的数据。如果 Shared Pool 中总是有大量的空闲内存，并且希望在其他地方分配该内存，那么考虑减少共享池的大小。
减少共享池的大小：
减少SHARED_POOL_SIZE初始化参数的值，同时确保保持良好的性能。

2 释放游标

如果没有 Library Cache 缺失，那么可以考虑将CURSOR_SPACE_FOR_TIME初始化参数的值设置为TRUE，以加速执行调用。此参数指定是否可以从 Library Cache 中释放游标，以便为新的SQL语句腾出空间。
如果CURSOR_SPACE_FOR_TIME参数设置为：

• FALSE（默认），然后可以从 Library Cache 中释放游标，而不管与它的SQL语句相关联的应用程序游标是否打开。
  在这种情况下，Oracle数据库必须验证包含SQL语句的游标在 Library Cache 中。
• TRUE，只有当与它的语句关联的所有应用程序游标都关闭时，才可以释放游标。
  在这种情况下，Oracle数据库不需要验证游标是否在 Library Cache 中，因为它不能在与它关联的应用程序游标打开时被释放。

将参数的值设置为TRUE可以节省Oracle数据库的时间，并且可以稍微提高执行调用的性能。这个值还可以阻止游标的释放，直到关联的应用程序游标关闭。
不要将CURSOR_SPACE_FOR_TIME参数的值设置为TRUE：

• 在执行调用中会发现 Library Cache 缺失
  Library Cache 缺失表明 Shared Pool 不够大，不能容纳所有并发开放游标的共享SQL区域。如果 Shared Pool 没有足够的空间用于新的SQL语句，并且该参数的值被设置为TRUE，那么就不能解析语句，而Oracle数据库返回一个错误，表明没有足够的共享内存。
• 对于私有SQL区域的每个用户可用的内存数量很少
  此值还可以防止与打开游标关联的私有SQL区域的释放。如果所有并发开放游标的私有SQL区域都填充了可用内存，这样就没有新的SQL语句空间，那么就不能解析语句，而Oracle数据库返回一个错误，表明内存不足。

如果共享池没有足够的空间用于新的SQL语句，且该参数的值被设置为FALSE，那么Oracle数据库将分配一个现有的游标。尽管释放游标可能会导致后续的 Library Cache 缺失(如果游标被重新执行)，但由于无法解析SQL语句，所以这比暂停应用程序的错误更可取。

3 缓存会话游标

会话游标缓存包含SQL和PL/SQL的封闭会话游标，包括递归SQL。这个缓存对于使用Oracle表单的应用程序非常有用，因为从一个表单切换到另一个表单会关闭与第一个表单相关联的所有会话游标。如果应用程序重复地在同一组SQL语句上执行解析调用，那么重新打开会话游标会降低性能。通过重用游标，数据库减少了解析时间，从而缩短了整个执行时间。

3.1 关于缓存会话游标

会话游标表示一个共享子游标的实例化，它存储在 Shared Pool 中，用于特定的会话。每个会话游标都存储对它已实例化的子游标的引用。
Oracle数据库检查 Library Cache，以确定在给定语句中是否已经发出了超过3个解析请求。如果一个游标已经被关闭了三次，那么Oracle数据库假定与该语句关联的会话游标应该被缓存，并将游标移动到会话游标缓存中。
随后请求解析同一会话的SQL语句，在数组中搜索指向共享游标的指针。如果找到了指针，那么数据库引用指针来判断是否存在共享光标。要从缓存中重用游标，缓存管理器将检查游标的缓存状态是否与当前会话和系统环境相匹配。
Note:
缓存游标的重用仍然是一个解析，尽管它不是一个硬解析。
LRU算法删除会话游标缓存中的条目，以便在需要时为新条目腾出空间。缓存还使用了一个内部的基于时间的算法，使在一定时间内空闲的游标变老。

3.2 启用会话游标缓存

以下初始化参数适用于会话游标缓存：

• SESSION_CACHED_CURSORS
  此参数设置每个会话的缓存关闭游标的最大数量。默认值是50。使用此参数可以从缓存中重用那些在同一会话中重复执行的语句。
  使用此参数可以从缓存中重用那些在同一会话中重复执行的语句。
• OPEN_CURSORS
  此参数指定一个会话可以同时打开的最大游标数。例如，如果它的值设置为1000，那么每次会话最多可以同时打开1000个游标。

这些参数是独立的。例如，您可以将SESSION_CACHED_CURSORS参数的值设置为高于OPEN_CURSORS参数的值，因为会话游标没有缓存在开放状态。
要启用会话游标缓存：

1. 确定保存在缓存中的会话游标的最大数量。
2. 做下列工作之一：
   • 要启用静态缓存，请将SESSION_CACHED_CURSORS参数的值设置为上一步中确定的数字。
   • 要启用动态缓存，请执行以下语句：
     

     ALTER SESSION SET SESSION_CACHED_CURSORS = value;

3.3 调整会话游标缓存

使用V$SESSTAT视图来确定会话游标缓存是否适合于数据库实例。
要增大会话游标缓存的大小：

1. 查询V$SESSTAT视图，以确定当前在特定会话中缓存了多少游标。
2. 查询V$SESSTAT视图，以查找在会话游标缓存中发现游标的解析调用的百分比。
3. 如果以下条件为真，考虑增加SESSION_CACHED_CURSORS参数的值：
   • 会话游标缓存计数接近最大值。
   • 会话游标缓存命中的百分比相对于整个解析器来说是低的。
   • 应用程序反复执行对相同查询的解析调用。

示例 4：显示了此视图的两个查询：
下面的查询发现当前在特定会话中缓存了多少游标：

SELECT a.value curr_cached, p.value max_cached,
       s.username, s.sid, s.serial#
  FROM v$sesstat a, v$statname b, v$session s, v$parameter2 p
 WHERE a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid
   AND p.name='session_cached_cursors'
   AND b.name = 'session cursor cache count';

该查询的输出可能如下所示：

CURR_CACHED MAX_CACHED USERNAME   SID    SERIAL#
----------- ---------- -------- ----- ----------
         49 50         APP         35        263

这个输出显示当前缓存到会话35的游标的数量接近最大值。
以下查询找到在会话游标缓存中发现游标的解析调用的百分比：

SELECT cach.value cache_hits, prs.value all_parses,
       round((cach.value/prs.value)*100,2) as "% found in cache"
  FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
 WHERE cach.statistic# = nm1.statistic#
   AND nm1.name = 'session cursor cache hits'
   AND prs.statistic#=nm2.statistic#
   AND nm2.name= 'parse count (total)'
   AND cach.sid= &sid and prs.sid= cach.sid;

该查询的输出可能如下所示：

CACHE_HITS ALL_PARSES % found in cache
---------- ---------- ----------------
        34        700             4.57

此输出显示会话35的会话游标缓存中的命中次数与解析的总数相比是低的。
在本例中，将SESSION_CACHED_CURSORS参数的值设置为100可能有助于提高性能。

4 共享游标

在SQL解析上下文中，相同的语句是一个SQL语句，其文本与另一个语句、字符(包括空格、case和注释)相同。除了一些文字的值外，类似的语句是相同的。
解析阶段将语句文本与 Shared Pool 中的语句进行比较，以确定该语句是否可以共享。如果CURSOR_SHARING初始化参数的值设置为EXACT (默认值)，如果 Shared Pool 中的语句不相同，则数据库不共享SQL区域。相反，每个SQL语句都有自己的父游标和基于语句中文字的自己的执行计划。

4.1 关于共享游标

当SQL语句使用文字而不是绑定变量时，将CURSOR_SHARING初始化参数的值设置为FORCE，使数据库可以用系统生成的绑定变量替换文本。使用此技术，数据库可以减少共享SQL区域内的父游标数量。
当CURSOR_SHARING参数的值被设置为FORCE时，数据库在解析阶段执行以下步骤：

1. 在 Shared Pool 中搜索相同的语句。
   如果找到了相同的语句，那么数据库将跳过下一步，然后继续执行步骤3。否则，数据库将继续下一步。
2. 在 Shared Pool 中搜索类似的语句。
   如果没有找到类似的语句，则数据库执行一个硬解析。如果找到了类似的语句，那么数据库将继续下一步。
3. 通过解析阶段的其余步骤，确保现有语句的执行计划适用于新语句。
   如果该计划不适用，则数据库执行硬解析。如果该计划是适用的，那么数据库将继续进行下一步。
4. 共享语句的SQL区域

有关数据库执行的各种检查的详细信息，请参见“SQL Sharing Criteria”。

4.2 强制光标共享

最佳实践是编写可共享 SQL并使用CURSOR_SHARING初始化参数的默认值（EXACT ）。默认情况下，Oracle数据库使用自适应游标共享来启用单个SQL语句，其中包含绑定变量以使用多个执行计划。然而，对于具有许多类似语句的应用程序，使用文字而不是绑定变量，将CURSOR_SHARING参数的值设置为 FORCE 可以改善游标共享，从而减少内存使用、更快的解析和减少锁存争用。当共享池中的语句仅在文本值上存在差异时，考虑这种方法，因为当响应时间较低时，会出现大量的 Library Cache 缺失。在这种情况下，将CURSOR_SHARING参数的值设置为强制最大化游标共享，并利用自适应游标共享，基于不同的文本值范围生成多个执行计划。
如果使用CURSOR_SHARING参数设置为EXACT的值生成存储概要，那么数据库就不会使用由文字生成的存储概要。为了避免这个问题，可以设置CURSOR_SHARING为FORCE生成概要文件，并使用 CREATE_STORED_OUTLINES 参数。
将CURSOR_SHARING参数设置为FORCE的值有以下缺点：

• 在软解析期间，数据库必须执行额外的工作，以便在 Shared Pool 中找到类似的语句。
• 在SELECT语句中包含文本的任何选定表达式的最大长度(如返回DESCRIBE)都有增加。但是，返回的数据的实际长度不会改变。
• 不支持星形转换。

当CURSOR_SHARING参数的值被设置为FORCE时，数据库将使用一个父游标和一个子游标用于每个不同的SQL语句。相同的计划用于相同语句的每个执行。例如，考虑以下SQL语句：

SELECT *
  FROM hr.employees
 WHERE employee_id = 101;

如果CURSOR_SHARING参数的值被设置为FORCE，那么数据库就会优化这个语句，就好像它包含一个bind变量，并使用bind peeking来估计基数。
Note:
从Oracle Database 11g Release 2开始，将CURSOR_SHARING参数的值设置为 SIMILAR 已经过时了。请考虑使用自适应游标共享。
See Also:

• 《Oracle Database Reference》有关CURSOR_SHARING初始化参数的信息。
• 《Oracle Database SQL Tuning Guide》有关自适应游标共享的信息。

5 保存大对象以防止老化

在将条目加载到 Shared Pool 后，它不能被移动。有时，随着条目被加载和过时，空闲内存可能会变得支离破碎。通过与数据库缓冲区类似的LRU算法，共享SQL和PL/SQL区域在 Shared Pool 中过时。为了提高性能和避免重新解析，可以防止大型SQL或PL/SQL区域从 Shared Pool 中退出。
DBMS_SHARED_POOL包使您能够将对象保存在共享内存中，这样它们就不会随着正常的LRU机制而过时。通过使用DBMS_SHARED_POOL包在内存碎片发生之前加载SQL和PL/SQL区域，对象可以保存在内存中。这确保了内存的可用性，并防止了在使用了SQL和PL/SQL区域后，用户响应时间的突然减慢。
考虑使用DBMS_SHARED_POOL包：

• 加载大型PL/SQL对象时，例如标准和DIUTIL包。
  当加载大型PL/SQL对象时，如果较小的对象必须从 Shared Pool 中退出来为较大的对象腾出空间，那么用户响应时间可能会受到影响。在某些情况下，可能没有足够的内存来加载大型对象。
• 要在 Shared Pool 中经常使用的表上保存编译触发器。
• 支持序列。
  当一个序列离开 Shared Pool 时，序列号就会丢失。DBMS_SHARED_POOL包将序列保存在共享池中，从而防止了序列号的丢失。

在共享内存中保留SQL或PL/SQL区域：

• 决定要保存在内存中的包或游标。
• 启动数据库。
• 调用DBMS_SHARED_POOL.KEEP 包以钉住对象。
  这个过程确保系统不会在被固定对象加载之前耗尽共享内存。在数据库实例的早期将对象固定在一起可以防止内存碎片，这可能是由于在 Shared Pool 的中间保留了很大一部分内存。

See Also:

• 《Oracle Database PL/SQL Packages and Types Reference》有关使用DBMS_SHARED_POOL过程的信息。

6 配置 Reserved Pool

尽管Oracle数据库将非常大的内存请求分解为较小的块，但是在某些系统上可能需要找到一个连续的内存块(比如超过5kb，默认的最小保留池分配是4400字节)。
如果 Shared Pool 中没有足够的空闲空间，那么Oracle数据库必须搜索并释放足够的内存来满足这个请求。此操作可能占用大量时间的锁存资源，对内存分配的其他并发尝试造成较小的中断。
为了避免这种情况，Oracle数据库默认情况下，如果 Shared Pool 没有足够的空间，数据库可以在 Shared Pool 中保留一个小的内存区域。这个预留的池使大块的分配更有效。数据库可以使用此内存进行操作，例如PL/SQL和触发器编译，或者在装载Java对象时使用临时空间。当从 Reserved Pool 中分配的内存释放后，它将返回到 Reserved Pool。
对于大型分配，Oracle数据库尝试按照以下顺序在 Shared Pool 中分配空间：

1. 从共享池的未保留部分。
2. 从 Reserved Pool。
   如果 Shared Pool 的未保留部分中没有足够的空间，那么Oracle数据库将检查 Reserved Pool 是否有足够的空间。
3. 从内存中。
   如果在 Shared Pool 的未保留部分和保留部分中没有足够的空间，那么Oracle数据库将尝试释放足够的内存以供分配。然后，数据库重试 Shared Pool 中未保留和保留的部分。

6.1 Reserved Pool 大小

通常，不需要更改 Oracle 数据库为 Reserved Pool 预留的空间的默认数量。但是，可能有一些情况需要在 Shared Pool 中留出空间，用于异常大的内存分配。
通过设置 SHARED_POOL_RESERVED_SIZE 初始化参数的值，可以设置 Reserved Pool 大小。SHARED_POOL_RESERVED_SIZE参数的默认值是SHARED_POOL_SIZE参数的5%。
如果将SHARED_POOL_RESERVED_SIZE参数的值设置为SHARED_POOL_SIZE参数的一半以上，那么Oracle数据库将返回一个错误，因为数据库不允许您为 Reserved Pool 保留太多的内存。可用的操作系统内存的数量也可能限制 Shared Pool 的大小。通常，将SHARED_POOL_RESERVED_SIZE参数的值不会设置为超过SHARED_POOL_SIZE参数的10%。在大多数系统中，如果对共享池进行充分的调优，该值就足够了。如果您增加了这个值，那么数据库将从 Shared Pool 中获取额外的内存，并减少用于较小分配的未保留 Shared Pool 内存的数量。
在调优这些参数时，使用V$SHARED_POOL_RESERVED视图的统计信息。在有大量空闲内存的系统中，要增加SGA的大小，REQUEST_MISSES统计值应该等于零。如果系统被操作系统内存限制，那么目标就是让REQUEST_FAILURES 统计值等于零，或者至少防止它的值增加。如果无法实现这些目标值，则增加SHARED_POOL_RESERVED_SIZE参数的值。同样，将SHARED_POOL_SIZE参数的值增加相同的值，因为保留的列表是从 Shared Pool 中提取的。
当SHARED_POOL_SIZE参数的值太小时，V$SHARED_POOL_RESERVED 的固定视图也可以显示。如果REQUEST_FAILURES 统计值大于零且增加，则可能出现这种情况。如果启用了保留列表，则减少SHARED_POOL_RESERVED_SIZE参数的值。如果未启用保留列表，则增加SHARED_POOL_SIZE参数的值，如“Increasing Memory Allocated to the Shared Pool”中所描述的那样。

6.2 增加分配给 Reserved Pool 的内存

如果 REQUEST_FAILURES 统计量的值大于零并不断增加，则 Reserved Pool 太小。在这种情况下，增加 Reserved Pool 可用的内存量。
Note:
增加保留列表上可用的内存量不会影响没有从保留列表中分配内存的用户。
若要增加保留池的大小，请执行以下操作：

• 相应地增加 SHARED_POOL_RESERVED_SIZE 和 SHARED_POOL_SIZE 初始化参数的值。 
  为这些参数选择的值取决于系统的SGA大小约束，如“Sizing the Reserved Pool”中所述。

6.3 减少分配给 Reserved Pool 的内存

如果下列情况下，则 Reserved Pool 太大：

• REQUEST_MISSES 统计量为零或不增加
• FREE_SPACE 统计量大于或等于 SHARED_POOL_RESERVED_SIZE 最小值的50%

如果这些条件中的任何一种为真，则减少 Reserved Pool 可用的内存量。
若要缩小保留池的大小，请执行以下操作：

• 减少 SHARED_POOL_RESERVED_SIZE 初始化参数的值。

五、配置 Large Pool

与 Shared Pool 不同，Large Pool 没有LRU列表。Oracle数据库不尝试将对象从 Large Pool 中老化。如果数据库实例使用以下任何Oracle数据库功能，请考虑配置大型池：

• 共享服务器
  在共享服务器体系结构中，每个客户机进程的会话内存都包含在 Shared Pool 中。
• 并行查询
  并行查询使用 Shared Pool 内存来缓存并行执行消息缓冲区。
• 恢复管理器
  恢复管理器(RMAN)在备份和恢复操作期间使用 Shared Pool 缓存I/O缓冲区。对于I/O服务器进程、备份和还原操作，Oracle数据库分配的缓冲区大小为几百kb。

See Also:

• 《Oracle Database Concepts》关于 large pool 的信息
• 《Oracle Database Backup and Recovery User's Guide 》有关使用恢复管理器确定大池大小的信息。

1 为共享服务器配置 Large Pool

当 Oracle 数据库将 Shared Pool 内存分配给共享服务器会话时，Library Cache 和 Data Dictionary Cache 可用的 Shared Pool 内存数量减少。如果从不同的池分配共享服务器会话内存，则可以保留 Shared Pool 以缓存共享SQL。
Oracle建议使用 Large Pool 来为共享服务器架构分配用户全局区域(UGA)。使用 Large Pool 而不是 Shared Pool 可以减少 Shared Pool 的碎片化，并消除共享的SQL缓存减少的性能开销。
默认情况下，没有配置 Large Pool。如果您不配置 Large Pool，那么Oracle数据库将使用 Shared Pool 来共享服务器用户会话内存。如果您确实配置了 Large Pool，那么在使用共享服务器架构时，Oracle数据库仍然为每个配置的会话分配一个固定的内存(大约10K)。在这两种情况下，都可以考虑相应地增加 Shared Pool 的大小。
Note:
即使使用共享内存增加共享服务器，内存使用总量也会减少。这是因为进程较少；因此，与专用的服务器环境相比，Oracle数据库使用共享服务器的PGA内存更少。
Tip:
要指定数据库允许的并发共享服务器会话的最大数量，请使用 CIRCUITS 初始化参数。
Tip:
对于使用共享服务器排序操作的最佳性能，将 SORT_AREA_SIZE 和 SORT_AREA_RETAINED_SIZE 初始化参数的值设置为相同的值。这样可以将排序结果保存在 Shared Pool 中，而不是将其写入磁盘。

2 为并发查询配置 Large Pool

当 AMM 或 ASMM 管理未启用时，并行查询使用 Shared Pool 内存来缓存并行执行消息缓冲区。在 Shared Pool 中缓存并行执行消息缓冲区会增加其工作负载，并可能导致碎片化。
为了避免可能对性能产生负面影响，Oracle 建议在使用并行查询时不要手动管理 SGA 内存。相反，您应该启用 AMM 或 ASMM，以确保使用 Large Pool 来缓存并行执行内存缓冲区。
See Also:

• 《Automatic Memory Management》
• 《Automatic Shared Memory Management》
• 《Oracle Database VLDB and Partitioning Guide》

3 Large Pool 大小

当在 Large Pool 中存储与服务器相关的UGA时，Oracle数据库使用的UGA的确切数量取决于应用程序。每个应用程序需要对会话信息有不同数量的内存，并且大型池的配置应该反映内存需求。
Oracle数据库收集会话使用的内存统计数据，并将其存储在V$SESSTAT视图中。表 2 列出了反映会话UGA内存的视图的统计信息。

表 2：V$SESSTAT视图中的内存统计信息。

统计	描述
session UGA memory	显示分配给会话的字节数。
session UGA memory max	显示分配给会话的字节的最大内存数量。

有两种方法可以使用此视图来确定 Large Pool 的正确大小。一种方法是根据同时活动的会话数量配置 Large Pool 的大小。要做到这一点，请观察一个典型用户的UGA内存使用情况，并将此数量乘以用户会话的估计数。例如，如果共享服务器需要200K到300K来存储一个典型用户会话的会话信息，您可以同时预测100个活动用户会话，然后将大池配置为30 MB。
另一种方法是计算所有用户会话使用的总内存和最大内存。示例 5 显示了两个查询，分别是 V$SESSTAT 和 V$STATNAME 视图。
示例 5 查询 V$SESSTAT 和 V$STATNAME 视图。
当应用程序运行时，发出以下查询：

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#;

这些查询还从 V$STATNAME 视图中选择，以获得 session memory 和 max session memory 的内部标识符。
这些查询的输出可能如下所示：

TOTAL MEMORY FOR ALL SESSIONS
-----------------------------
157125 BYTES

TOTAL MAX MEM FOR ALL SESSIONS
------------------------------
417381 BYTES

第一个查询的结果显示当前分配给所有会话的内存是157,125字节。该值是具有一个位置的总内存，该位置取决于会话与数据库的连接方式。如果会话连接到专用的服务器进程，那么这个内存就是用户进程的一部分。如果会话连接到共享服务器进程，那么这个内存就是 Shared Pool 的一部分。
第二个查询的结果显示，所有会话的内存的最大大小之和为417,381字节。第二个结果比第一个结果要大，因为一些会话在分配它们的最大数量之后就有了分配内存。
使用两个查询的结果来确定共享池的正确大小。第一个值可能比第二个值更好，除非您期望所有会话同时达到它们的最大分配。
Large Pool 的大小：

1. 验证池(Shared Pool or Large Pool)，其中对象的内存是通过检查V$SGASTAT视图中的列 POOL。
2. 为LARGE_POOL_SIZE初始化参数设置一个值。
   此参数的最小值为300K。

4 限制用户会话的内存使用

要限制来自SGA的每个客户端会话使用的内存，可以使用 PRIVATE_SGA 设置资源限制。
PRIVATE_SGA 通过会话定义从 SGA 中使用的内存字节数。然而，这个参数很少被使用，因为大多数dba不会在用户的基础上限制SGA的消耗。
See Also:

• 《Oracle Database SQL Language Reference》关于设置PRIVATE_SGA资源限制的信息。

5 使用三层连接减少内存使用

如果有大量的连接用户，那么考虑通过实现三层连接来减少内存使用。使用事务处理(TP)监视器只能使用纯事务模型，因为不能在调用之间进行锁和未提交的DML操作。
使用共享服务器环境：

• 与TP监视器相比，应用程序设计的限制要少得多。
• 通过允许用户共享一个服务器池，大大减少了操作系统进程计数和上下文切换。
• 大大降低了总体内存使用量，尽管在共享服务器模式中使用了更多的SGA。