OCP课程54：管理II之管理内存

最新推荐文章于 2023-03-30 10:59:43 发布

cuishan5183

最新推荐文章于 2023-03-30 10:59:43 发布

阅读量182

点赞数

课程目标：

SGA中的内存组件
自动内存管理
手动配置SGA
配置自动PGA内存管理

1、内存管理：概览

因为数据库服务器上可用内存有限，因此，必须关注数据库实例的内存分配。如果不必要的区域分配了太多的内存，那么其他需要内存的区域就有可能性能不佳。使用内存的自动分配和维护功能可以大大减轻内存管理工作。但即使是自动管理的内存也需要监控和优化，并可能需要进行手动配置。

2、回顾Oracle数据库内存结构

数据库创建和使用各种用途的内存结构。例如，存储正在运行的程序代码，用户之间共享的数据以及每个连接用户的私有数据区。

实例2个基本内存结构：

系统全局区（SGA）：一组共享内存结构，称为SGA，包含一个数据库实例的数据和控制信息。SGA由所有服务器和后台进程共享。
程序全局区（PGA）：包含一个服务器或者后台进程的数据和控制信息的内存区域。PGA是数据库在服务器或者后台进程启动时创建的非共享内存。每个服务器进程和后台进程都有它自己的PGA。

SGA是包含实例的数据和控制信息的内存区域，包含以下数据结构：

共享池（Shared pool）：缓存各种结构，可以在用户之间共享
数据库缓冲区缓存（Database buffer cache）：缓存从数据库中检索的数据块
保持缓冲池（KEEP buffer pool）：是数据库缓冲区缓存的一种特别类型，可以长时间保留内存中的数据块。
循环缓冲池（Recycle buffer pool）：是数据库缓冲区缓存的一种特别类型，可以快速回收内存。
nK缓冲区缓存（nK buffer cache）：是数据库缓冲区缓存的一种特别类型，存放与默认的块大小不同的块。
重做日志缓冲（Redo log buffer）：缓存重做信息（用于实例恢复），直到它可以被写在到存储在磁盘上的物理重做日志文件中。
大池（Large pool）：是可选择的区域，为某些大进程提供大量的内存分配，例如：数据库备份和恢复操作，以及I/O服务器进程
Java池（Java pool）：用于所有会话指定的Java代码和在Java虚拟机（JVM）中的数据。
流池（Streams pool）：用于Oracle Streams。

当使用EM或者SQL*Plus启动实例，会显示分配给SGA的内存总量。

程序全局区（PGA）是一个包含每一个服务器进程数据和控制信息的内存区域。一个服务器进程处理一个客户请求。每个服务器进程都有自己的私有PGA。PGA主要分为两部分：堆栈空间和用户全局区（UGA）。

用动态SGA，可以改变数据库缓冲区缓存，共享池，大池，Java池和流池的大小而不需要关闭实例。

数据库使用初始化参数来创建和管理内存结构。管理内存的最简单的方法是让数据库自动管理和调整，只需要设置一个目标内存大小初始化参数（memory_target）和最大内存大小初始化参数（memory_max_target）。

3、缓冲区缓存

可以通过指定DB_CACHE_SIZE参数配置缓冲区缓存。缓冲区缓存存放大小为DB_BLOCK_SIZE参数指定的数据文件数据块。缓冲区缓存是SGA的一部分，所以所有的用户都可以共享这些块。服务器进程从数据文件读取数据到缓冲区缓存。为了提高性能，在一个单一的读操作中，服务器进程有时会读取多个块。DBWn进程将数据从缓冲区缓存写入到数据文件，为了提高性能，DBWn在一个写操作写入多个块。

在任何给定的时间，缓冲区缓存可以保存一个数据库块的多个拷贝。只有一个当前块拷贝存在，但为了满足查询，服务器进程可能需要从过去的映像信息构建读一致的副本。这被称为一致读（CR）块。

最近最少使用（LRU）清单反映了缓冲区的使用。综合考虑最近使用情况以及最常使用情况，对缓冲区进行排序。也就是说，最经常和最近使用的缓冲区在最多最近使用端。刚刚拷贝进行来的块位于最少最近使用端，位于清单的中间，作为一个起始点，从这里，根据使用情况向上或者向下移动。

缓冲区缓存有如下四种状态：

Pinned：要么正在将块读入到缓存，要么正在将块写入到缓存。其他会话等待访问该块。
Clean：unpinned状态，如果其当前内容不再需要，则可以被覆盖使用。其内容要么与磁盘一致，要么包含块的读一致快照。
Free/unused：实例刚刚启动时缓冲区为空，与clean状态相似，只是这些缓冲区还没有被使用。
Dirty：不再pinned，内容（数据块）发生了改变，被覆盖之前需要由DBWn写入到磁盘。

服务器进程使用缓冲区缓存，DBWn进程将修改的缓冲区缓存写回到数据文件以使这些缓存可再被使用。检查点队列列出了将要写入到磁盘的缓冲区。

在同一数据库中Oracle支持多种块大小。标准块大小用于SYSTEM表空间。通过设置DB_BLOCK_SIZE初始化参数指定标准块大小。可以从2KB到32KB，默认是8KB。非标准块缓冲区缓存大小由以下参数指定：

db_2k_cache_size
db_4k_cache_size
db_8k_cache_size
db_16k_cache_size
db_32k_cache_size

DB_nK_CACHE_SIZE参数不能用于设置标准块的缓存大小。如果DB_BLOCK_SIZE的值为8K，则不能设置DB_8K_CACHE_SIZE。对于标准块的缓存大小总是由db_cache_size值确定。

每一个缓冲区缓存的空间都是有限的，不能将磁盘上所有的数据都放入到缓冲区缓存。当缓冲区缓存满了，后续的缓存命中失败会导致数据库将脏块写入到磁盘以便腾出空间给新的数据（如果一个缓冲区没有脏块，则在读入新块到缓冲区之前不需要将其写入到磁盘）。后续对刚写入到磁盘的数据访问又会导致新的缓存命中失败。

缓存的大小会影响一个数据请求缓存命中的可能性。如果缓存大，则更可能包含被请求的数据。增加缓存的大小会增加缓存命中率的百分比。

例子：查看数据库缓冲区缓存的大小

SQL> select name,bytes/1024/1024 from v$sgainfo where name='Buffer Cache Size';

NAME BYTES/1024/1024

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

Buffer Cache Size 336

4、使用多个缓冲池

数据库管理员可以通过创建多个缓冲池提高数据库缓冲区缓存的性能。根据对象的访问方式分配不同的缓冲池。有三种类型缓冲池：

Keep：这个池是用来保存很可能被重用对象。在内存中保存这些对象可以减少I/O操作。其大小要比放入该池的段的总大小要大。这样就不需要换出。Keep池由db_keep_cache_size参数配置大小。
Recycle：这个池是用来保存很少有机会被重复使用的块。其大小要比放入该池的段的总大小要小。这意味着块读入到池中就会很快被换出。Recycle池是由db_recycle_cache_size参数配置大小。
Default：这个池始终存在。相当于一个没有Keep池和Recycle池的实例的缓冲区缓存，由db_cache_size参数配置大小。

注意：Keep池和Recycle池不是Default池的一部分。

SQL> show parameter db_keep_cache_size;

NAME TYPE VALUE

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

db_keep_cache_size big integer 0

SQL> show parameter db_recycle_cache_size;

NAME TYPE VALUE

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

db_recycle_cache_size big integer 0

SQL> show parameter db_cache_size;

NAME TYPE VALUE

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

db_cache_size big integer 0

5、使用多个缓冲池

可以在CREATE和ALTER table，cluster和index语句的STORAGE子句使用BUFFER_POOL子句定义对象的默认缓冲池。如果不显式设置缓冲池则使用默认的Default池。

语法为：BUFFER_POOL [KEEP | RECYCLE | DEFAULT].

当使用ALTER语句修改对象的默认缓冲池，已经读入的块仍然保留在当前的缓冲池，直到被正常的缓存管理活动刷出。从磁盘读入的该对象的块才会被放入到新指定的缓冲池。

由于缓冲池被分配给段，多段的对象可以有多个缓冲池中的块。例如，索引组织表可以在索引和溢出段上定义不同的池。

SQL> create index emp_id_idx on emp(employee_id) storage(buffer_pool keep);

Index created.

SQL> alter table emp storage(buffer_pool recycle);

Table altered.

6、共享池

使用shared_pool_size初始化参数指定共享池的大小。共享池是存储多个会话共享信息的存储区。包含了不同类型的数据，如上图所示。

库缓存（Library cache）：库缓存包含共享SQL和PL/SQL区（PL/SQL块和SQL语句的编译和解析）PL/SQL块包括：

存储过程和函数
包
触发器
匿名PL/SQL块

数据字典缓存（Data dictionary cache）：数据字典缓存存放字典对象的定义。

结果缓存（Result cache）：包括SQL查询结果缓存和PL/SQL函数结果缓存。这个缓存用来存储SQL查询或PL/SQL函数结果以加快其未来执行。

用户全局区（User Global Area）：Oracle共享服务器的会话信息。当使用共享服务器模式，如果没有配置大池，则UGA位于共享池。

例子：查看共享池大小

SQL> select name,bytes/1024/1024 from v$sgainfo where name='Shared Pool Size';

NAME BYTES/1024/1024

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

Shared Pool Size 160

7、大池

大池是SGA中的一块可选内存区域，用于大量内存分配：

用于共享服务（Shared Server MTS方式中）的会话内存和Oracle分布式事务处理的Oracle XA接口
IO服务进程
RMAN的I/O操作缓冲
使用并行查询（Parallel Query Option PQO）时的消息缓冲
高级队列内存表存储

大池为以上这些分配内存可以减少共享池的碎片，更高效的使用共享池。

大池可以通过AMM和ASMM自动管理。也可以用large_pool_size参数配置大小。

例子：查看大池大小

SQL> select name,bytes/1024/1024 from v$sgainfo where name='Large Pool Size';

NAME BYTES/1024/1024

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

Large Pool Size 8

8、Java池和流池

Java池的内存是用于存储所有会话中特定的Java代码和JVM中数据。Java池的使用方式依赖于Oracle服务的运行模式。不要认为使用Java开发的程序就要用Java池，只是说在数据库里面使用Java去编写了存储过程才会用到Java池，但是目前很少有人用Java去编写存储过程了。Java池的大小可以通过参数JAVA_POOL_SIZE来设置。

Java池顾问的统计资料提供了有关库缓存内存的信息，并预测了Java池的大小变化会如何影响解析率。当statistics_level设置为typical或更高时才会打开Java池顾问。当顾问被关闭时，统计数据会被重置。

流池是Oracle 10g中新增加的。是为了增加对流（流复制是Oracle 9iR2中引入的一个非常吸引人的特性，支持异构数据库之间的复制。10g中得到了完善）的支持。但是现在Oracle的流复制技术用得很少了，因为Oracle收购了Oracle golden gate，可以跨平台，跨数据库，Oracle现在主推这个，流复制就用得越来越少了。流池也是可选内存区，属于SGA中的可变区。它的大小可以通过参数STREAMS_POOL_SIZE来指定。如果没有被指定，流池大小默认为0，Oracle会在第一次使用流时自动创建，根据需要动态增加。

例子：查看Java池和流池大小

SQL> select name,bytes/1024/1024 from v$sgainfo where name='Java Pool Size';

NAME BYTES/1024/1024

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

Java Pool Size 4

SQL> select name,bytes/1024/1024 from v$sgainfo where name='Streams Pool Size';

NAME BYTES/1024/1024

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

Streams Pool Size 4

9、重做日志缓冲区

Oracle服务器进程为每个DML或DDL语句从用户内存空间复制重做条目到重做日志缓冲区中。重做条目包含必要的由DDL和DML操作对数据库进行改变的重建或重做信息。这些信息用于数据库恢复，在缓冲区中占用连续的空间。

重做日志缓冲区是一个循环缓冲区；在重做日志缓冲区中，服务器进程可以在已被写入磁盘的日志缓冲区中复制新的条目。这需要LGWR进程快速写出以确保空间可以用于新的重做条目。LGWR进程将重做日志缓冲区写入磁盘的联机重做日志文件，拷贝自上次LGWR写磁盘后进入到缓冲区的所有重做条目。

触发LGWR写的情形：

当用户进程提交事务时。
每隔三秒，或当重做日志缓冲区三分之一满。
当DBWn进程写修改缓冲区到磁盘，如果对应的重做日志数据尚未写入磁盘。

例子：查看重做日志缓冲区大小

SQL> select name,bytes/1024 from v$sgainfo where name='Redo Buffers';

NAME BYTES/1024

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

Redo Buffers 2316

10、自动内存管理：概览

自动内存管理（AMM）允许Oracle数据库自动调整SGA和PGA大小。在大多数平台上，只需要设置目标内存大小初始化参数（memory_target）和最大内存大小初始化参数（memory_max_target），数据库根据需要在SGA和PGA间动态调整内存。可以使EM启用AMM：服务器>内存顾问（数据库配置部分）然后单击“启用”按钮。

使用AMM，数据库也会动态调整各个SGA组件和单个PGA的大小。

因为目标内存初始化参数是动态的，可以随时更改目标内存大小而不需要重新启动数据库。最大内存作为目标内存的上限，防止目标内存设置太大。由于某些SGA组件不能轻易收缩或必须保持在一个最小值，数据库还可以防止设定的目标内存太小。

这种间接的内存转移依赖于操作系统释放共享内存的机制。在内存被释放到操作系统后，其他的组件可以通过从操作系统请求内存来分配内存。目前，自动内存管理支持Linux，Solaris，HPUX、AIX和Windows平台。

例子：查看目标内存和最大内存初始化参数

SQL> show parameter memory_target

NAME TYPE VALUE

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

memory_target big integer 800M

SQL> show parameter memory_max_target

NAME TYPE VALUE

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

memory_max_target big integer 800M

11、Oracle数据库内存参数

上图显示了内存初始化参数层次结构。虽然可以只设置memory_target参数启用自动内存管理，但仍然可以设置各种缓存的最小值。

如果sga_target和pga_aggregate_target设置为非零值，则分别是SGA和PGA的最小值。memory_target的值可以是从sga_target + pga_aggregate_target到memory_max_size。
如果设置了sga_target，数据库只自动调整SGA各组件的尺寸。PGA自动调整不依赖于是否对其进行了显式设置。然而，整个SGA（sga_target）和PGA（pga_aggregate_target）不会进行自动调整，即不自动增大或收缩。

12、监控自动内存管理

从EM主页（相关链接部分），导航到顾问中心>内存顾问。

自动内存管理启用后，在内存顾问页面的分配历史区域可以看到内存组件大小的图形化表示。在第一个直方图的顶部是PGA，下部是SGA。第二个直方图中的顶部是共享池大小，下部对应缓冲区缓存。

在这个页面上，还可以通过单击“建议”按钮访问内存目标顾问。此顾问提供了各种总内存大小对应的数据库时间改善。

注意：也可以使用V$MEMORY_TARGET_ADVISOR视图查看内存目标顾问。

13、监控自动内存管理

动态性能视图V$memory_dynamic_components显示所有动态调整内存组件的当前大小，包括SGA和PGA的大小。V$memory_target_advice视图为memory_target初始化参数提供调优建议。

V$memory_target_advice视图中memory_size_factor为1的行表示由memory_target设置的当前值以及完成当前工作负载所需的DB time。前面和后面的行，显示另外的memory_target大小及其对应的大小因子以及对应的DB time。

SQL> select * from v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION

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

400 .5 698 1.0279 0

600 .75 679 1.0005 0

800 1 679 1 0

1000 1.25 679 1 0

1200 1.5 679 1 0

1400 1.75 679 1 0

1600 2 679 1 0

7 rows selected.

SQL> select component,current_size,min_size,max_size,user_specified_size from v$memory_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE

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

shared pool 209715200 138412032 209715200 0

large pool 8388608 8388608 71303168 0

java pool 4194304 4194304 4194304 0

streams pool 4194304 0 4194304 0

SGA Target 545259520 545259520 545259520 0

DEFAULT buffer cache 310378496 310378496 381681664 0

KEEP buffer cache 0 0 0 0

RECYCLE buffer cache 0 0 0 0

DEFAULT 2K buffer cache 0 0 0 0

DEFAULT 4K buffer cache 0 0 0 0

DEFAULT 8K buffer cache 0 0 0 0

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE

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

DEFAULT 16K buffer cache 0 0 0 0

DEFAULT 32K buffer cache 0 0 0 0

Shared IO Pool 0 0 0 0

PGA Target 293601280 293601280 293601280 0

ASM Buffer Cache 0 0 0 0

16 rows selected.

SQL> select component,parameter,initial_size,target_size,final_size from v$memory_resize_ops where initial_size<>final_size;

COMPONENT PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

DEFAULT buffer cache db_cache_size 0 322961408 322961408

SGA Target sga_target 0 545259520 545259520

PGA Target pga_aggregate_target 0 293601280 293601280

java pool java_pool_size 0 4194304 4194304

large pool large_pool_size 0 71303168 71303168

shared pool shared_pool_size 0 138412032 138412032

streams pool streams_pool_size 0 4194304 4194304

DEFAULT buffer cache db_cache_size 322961408 318767104 318767104

DEFAULT buffer cache db_cache_size 318767104 381681664 381681664

large pool large_pool_size 71303168 8388608 8388608

shared pool shared_pool_size 138412032 142606336 142606336

COMPONENT PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

DEFAULT buffer cache db_cache_size 381681664 377487360 377487360

shared pool shared_pool_size 142606336 146800640 146800640

DEFAULT buffer cache db_cache_size 377487360 373293056 373293056

DEFAULT buffer cache db_cache_size 373293056 369098752 369098752

shared pool shared_pool_size 146800640 150994944 150994944

DEFAULT buffer cache db_cache_size 369098752 364904448 364904448

shared pool shared_pool_size 150994944 155189248 155189248

shared pool shared_pool_size 155189248 159383552 159383552

DEFAULT buffer cache db_cache_size 364904448 360710144 360710144

DEFAULT buffer cache db_cache_size 360710144 356515840 356515840

shared pool shared_pool_size 159383552 163577856 163577856

COMPONENT PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

DEFAULT buffer cache db_cache_size 356515840 352321536 352321536

shared pool shared_pool_size 163577856 167772160 167772160

DEFAULT buffer cache db_cache_size 352321536 343932928 343932928

shared pool shared_pool_size 167772160 176160768 176160768

shared pool shared_pool_size 176160768 184549376 184549376

DEFAULT buffer cache db_cache_size 343932928 335544320 335544320

shared pool shared_pool_size 184549376 192937984 192937984

DEFAULT buffer cache db_cache_size 335544320 327155712 327155712

shared pool shared_pool_size 192937984 201326592 201326592

DEFAULT buffer cache db_cache_size 327155712 318767104 318767104

shared pool shared_pool_size 201326592 209715200 209715200

COMPONENT PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE

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

DEFAULT buffer cache db_cache_size 318767104 310378496 310378496

34 rows selected.

14、高效内存使用：指导

如果可能的话，最好是将SGA纳入物理内存，因为物理内存提供了最快的访问。操作系统可以提供额外的虚拟内存，但虚拟内存会经常被换出到磁盘。在一些平台上，可以使用初始化参数lock_sga锁定SGA到物理内存。但此参数不可与AMM或ASMM一起使用。

执行SQL语句时，数据块请求读或写，或两者有之。这被认为是一个逻辑I/O，当块被请求时，会检查该块是否已经存在于内存中。如果不在内存中，从磁盘读取，这就是所谓的物理I/O。在内存中找到块的次数与逻辑I/O总数之比为缓冲区缓存命中率。一个较高的比例意味着更多的块在内存中找到，而不需要磁盘I/O。

缓冲高速缓存命中率在99%以上很常见，但这并不总是意味着系统调优良好。如果经常执行一些不必要的查询，不断请求相同的块，则命中率自然会提高。

此外，大全表扫描可以降低这个比例，因为可能会从磁盘读取整个表；扫描可能不会使用已经在缓冲区缓存中的一些块。所以，如果应用程序中有一些必要的大全表扫描，数据库可能总是有一个低的缓冲区缓存命中率。

使用EM的内存顾问根据数据库活动进行SGA大小的调整。

15、库缓存调优指导

库缓存作为共享池的一部分，存储所有SQL，Java代码，PL/SQL存储过程和包以及控制结构如锁和库缓存斗柄。代码进入共享池以便可以在所有用户之间共享，都可以利用已经处理的SQL数据。因此，无论语句执行多少次，有多少用户去执行，只需要执行一次诸如解析SQL语句和确定数据访问路径（也被称为“执行计划”）的任务。如果库缓存太小，不能容纳所有的执行语句，则不能利用共享SQL数据的优势。如果库缓存太大则会加重系统管理其内容的负担。

库缓存可能会有很多看起来不一样但实际上是同一条语句的拷贝，一个常见的原因是这些语句只是有一些格式上的不同，字符串没有完全匹配。另一个原因是使用了字变量而没有使用绑定变量。如果两个语句只是字变量的值不一样，则在大多数情况下，使用绑定变量替换字变量会提高效率。

CURSOR_SHARING初始化参数可以设置让系统自动使用绑定变量替换字变量。通常情况下，应该利用这个设置作为临时措施，直到应用程序在适当的时候使用绑定变量。但使用这个参数可能会导致其他的问题。

相比在应用程序中从不同地方发出相同的SQL语句，使用PL/SQL将语句放入到存储过程，然后调用，这样就只在一个位置了，确保了SQL语句的共享，且编译该存储过程时该SQL语句就进行了解析并有了执行计划。

可以缓存序列值，如果经常用到一些序列，建议为其设置缓存值。

可以使用dbms_shared_pool包将对象固定到库缓存中，可以减少对象的重新加载和编译。

16、自动共享内存管理：概览

由于需要固定PGA而不能使用AMM，可以考虑使用自动共享内存管理（ASMM）简化SGA的内存管理。使用SGA_TARGET初始化参数指定实例的SGA可用内存，数据库自动调整SGA各组件。

例如，在一个系统中，白天运行联机事务处理（OLTP）需要大的缓冲区缓存，晚上执行并行批处理任务需要大的大池，需要同时配置缓冲区缓存和大池以满足其峰值要求。使用ASMM，OLTP工作运行时，缓冲区缓存使用大部分内存以获取良好的I/O性能。当数据分析和批处理工作启动后，内存会自动迁移到大池中，这样就可以使用并行查询操作而不产生内存溢出错误。

通过使用服务器参数文件SPFILE，数据库可以跨实例关闭和启动来记录自动调整组件的大小。但需要在实例启动后去获取工作负载特性。可以从过去的信息开始，并继续评估在最后一次关机时留下的工作量。

17、ASMM如何工作

自动共享内存管理是由两个后台进程来实现：Manageability Monitor（MMON）和Memory Manager（MMAN）。MMON定期捕获统计数据和内存顾问数据。MMAN根据MMON调整各内存组件的大小。

基于工作负载信息，自动共享内存管理：

后台定期捕获统计数据。
使用内存顾问。
执行假设分析以确定最佳的内存分配。
将内存移到最需要的地方。
保存各组件大小到SPFILE。

18、启用自动共享内存管理

从手动的共享内存管理修改为ASMM的步骤：

（1）运行以下查询获取SGA_TARGET的值：

SELECT ((SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)) “SGA_TARGET” FROM DUAL;

（2）设置SGA_TARGET：

ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]

其中value是上一步计算的值或者是所有SGA组件的和到SGA_MAX_SIZE之间的值。

（3）设置自动调整的SGA组件的值为0，可以通过编辑文本初始化参数文件或者使用ALTER SYSTEM语句执行，根据需要重启实例。

从自动内存管理修改为ASMM的步骤：

（1）设置MEMORY_TARGET初始化参数0。

ALTER SYSTEM SET MEMORY_TARGET = 0;

基于当前SGA内存分配设置SGA_TARGET。

（2）设置自动调整的SGA组件的值为0。完成后重启实例。

例子：从自动内存管理修改为ASMM

SQL> select component,current_size/1024/1024 from v$memory_dynamic_components where component='SGA Target';

COMPONENT CURRENT_SIZE/1024/1024

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

SGA Target 520

SQL> alter system set memory_target=0;

System altered.

SQL> alter system set sga_target=520m;

System altered.

SQL> show parameter db_cache_size

NAME TYPE VALUE

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

db_cache_size big integer 0

SQL> show parameter pool_size

NAME TYPE VALUE

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

global_context_pool_size string

java_pool_size big integer 0

large_pool_size big integer 0

olap_page_pool_size big integer 0

shared_pool_size big integer 0

streams_pool_size big integer 0

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 542814208 bytes

Fixed Size 2255032 bytes

Variable Size 213911368 bytes

Database Buffers 322961408 bytes

Redo Buffers 3686400 bytes

Database mounted.

Database opened.

19、禁用ASMM

可以设置SGA_TARGET为0禁用自动共享内存管理。在这种情况下，所有自动优化的参数的值设置为对应组件的当前值，即使用户之前为自动优化的参数指定了一个非零值。

在上图中，SGA_TARGET的值为8GB和SHARED_POOL_SIZE的值为1GB。如果系统内部调整共享池组件的大小为2GB，然后设置SGA_TARGET为0，结果SHARED_POOL_SIZE被设置为2 GB，覆盖原来用户指定的值。

例子：禁用ASMM

SQL> select component,current_size/1024/1024 from v$memory_dynamic_components;

COMPONENT CURRENT_SIZE/1024/1024

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

shared pool 140

large pool 8

java pool 4

streams pool 4

SGA Target 520

DEFAULT buffer cache 356

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

COMPONENT CURRENT_SIZE/1024/1024

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

DEFAULT 16K buffer cache 0

DEFAULT 32K buffer cache 0

Shared IO Pool 0

PGA Target 280

ASM Buffer Cache 0

16 rows selected.

SQL> show parameter pool_size

NAME TYPE VALUE

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

global_context_pool_size string

java_pool_size big integer 0

large_pool_size big integer 0

olap_page_pool_size big integer 0

shared_pool_size big integer 0

streams_pool_size big integer 0

SQL> show parameter db_cache_size

NAME TYPE VALUE

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

db_cache_size big integer 0

SQL> alter system set sga_target=0;

System altered.

SQL> show parameter pool_size

NAME TYPE VALUE

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

_shared_io_pool_size big integer 0

global_context_pool_size string

java_pool_size big integer 4M

large_pool_size big integer 8M

olap_page_pool_size big integer 0

shared_pool_size big integer 140M

streams_pool_size big integer 4M

SQL> show parameter db_cache_size

NAME TYPE VALUE

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

db_cache_size big integer 356M

20、PGA

程序全局区（PGA）是一个内存区，包含服务器进程的数据和控制信息，是在服务器进程启动时服务器创建的非共享内存。为实例所有服务器进程分配的总的PGA内存称为aggregated PGA。

如果使用共享服务器模式，一部分PGA位于SGA中。

PGA内存通常包含以下几部分：

（1）私有SQL区

私有SQL区包含诸如绑定信息和运行时内存结构的数据。这些数据对应于每个会话调用的SQL语句；绑定变量持有不同的值，游标状态也不一样。执行SQL语句的每一个会话都有一私有SQL区，每个提交了相同SQL语句的用户都有其自己的私有SQL区，使用一个共享SQL区。因此，许多私有SQL区可以对应一个相同的共享SQL区。私有SQL区的位置取决于建立会话连接的类型。如果会话是通过专有服务器建立连接，私有SQL区位于服务器进程的PGA。如果会话是通过共享服务器建立连接，私有SQL区的一部分被位于在SGA。

（2）游标和SQL区

一个Oracle Pro*C程序或Oracle调用接口（OCI）程序的应用开发者可以显式打开游标或处理特定的私有SQL区，在程序执行期间使用她们作为资源命名。数据库为一些SQL语句隐式使用的递归游标也使用共享SQL区。

（3）工作区域（Work Area）

对于复杂的查询（例如决策支持查询），PGA的很大一部分是专门用于内存密集型操作的工作区。如：

基于排序的操作，如ORDER BY，GROUP BY，ROLLUP和窗口函数
Hash-join
位图合并（Bitmap merge）
位图创建（Bitmap create）
大负载操作的写缓冲区

排序运算符使用工作区（排序区）来执行内存排序。类似地，一个哈希连接运算符使用工作区（哈希区）从左端构建哈希表。

可以控制和调整工作区的大小。一般情况下，较大的工作区可以显着提高消耗大量内存的特定操作的性能。

（4）会话内存（Session Memory）

会话内存是分配给会话变量（登录信息）和与会话相关的其他信息的内存。对于一个共享服务器，会话内存是共享的。

自动PGA内存管理

默认情况下，Oracle数据库自动管理分配给实例PGA的总量。可以通过设置初始化参数PGA_AGGREGATE_TARGET来控制总量大小。Oracle数据库确保PGA内存分配给所有数据库服务器进程和后台进程的总量不超过这个值。

21、使用V$PARAMETER参数

当为SGA_TARGET指定一个非零值，且不指定自动调优的SGA组件的值，则在V$PARAMETER视图中这些组件的值都为0，ISDEFAULT字段的值都是TRUE。

如果为自动调优的SGA组件的指定了值，则在V$PARAMETER视图中显示指定的值。

SQL> select name,value,isdefault from v$parameter where name like '%pool_size';

NAME VALUE ISDEFAULT

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

shared_pool_size 146800640 TRUE

large_pool_size 8388608 TRUE

java_pool_size 4194304 TRUE

streams_pool_size 4194304 TRUE

_shared_io_pool_size 0 TRUE

global_context_pool_size TRUE

olap_page_pool_size 0 TRUE

7 rows selected.

相关习题：

（1）Which two initialization parameters would you set to enable Automatic Shared Memory Management? (Choose two.)

A.set SHARED_POOL_SIZE to zero

B.set STATISTICS_LEVEL to BASIC

C.set SGA_TARGET to a non-zero value

D.set DB_CACHE_SIZE to a non-zero value

E.set STATISTICS_LEVEL to TYPICAL or ALL

答案：AC

（2）Your database interface is running. A user SCOTT starts a SQL *Plus session, and issues the following query:

SQL> SELECT * FROM sales;

Which process would retrieve the result from the database and return it to the client program?

A.User process

B.Server process

C.System Monitor (SMON)

D.Process Monitor (PMON)

E.Checkpoint process (CKPT)

答案：B

（3）Exhibit: View the Exhibit to examine the parameter values. You are planning to set the value for

the MEMORY_TARGET parameter of your database instance. What value would you assign?

此主题相关图片如下：

A. 1440 MB

B. 90 MB

C. 362 MB

D. 272 MB

答案：C

（4）Which statements about the MEMORY_TARGET initialization parameter are true? (Choose all that apply.)

A. MEMORY_TARGET can be increased up to the value of MEMORY_MAX_TARGET, if MEMORY_MAX_TARGET is set to a value greater than zero

B. MEMORY_MAX_TARGET defaults to a value of zero if MEMORY_TARGET is not set

C. MEMORY_TARGET represents the total amount of memory that can be allocated to SGA and PGA memory structures.

D. MEMORY_TARGET is static and cannot be modified without shutting down the instance

答案：ABC

（5）此主题相关图片如下：

View the Exhibit to examine the output produced by the following query at three different times since the database instance started and has undergone workloads of different capacities:

SQL> SELECT substr(component, 0, 10) COMP, current_size CS,

user_specified_size US

FROM v$memory_dynamic_components

WHERE current_size!=0;

What do you infer from this?

A. All sessions are connected to the database instance in dedicated mode, and no RMAN or parallel query operations have been performed.

B. The database instance is running with manual shared memory management.

C. The database instance is running with manual PGA management.

D. The database instance has the MEMORY_TARGET value set to a nonzero value.

答案：D

（6）View the Exhibit to observe the error.

此主题相关图片如下：

You receive this error regularly and have to shut down the database instance to overcome the error. What can the solution be to reduce the chance of this error in future, when implemented?

A. setting the PRE_PAGE_SGA parameter to TRUE

B. locking the SGA in memory

C. increasing the value of SGA_MAX_SIZE

D. automatic memory management

答案：D

（7）What Oracle process runs when the database is in ARCHIVELOG mode but not when it is in NOARCHIVELOG mode?

A. MMON

B. LGWR

C. ARCH

D. ARWR

E. COPY

答案：C

（8）You specify a nonzero value for the MEMORY_TARGET initialization parameter, but do not set the PGA_AGGREGATE_TARGET or the SGA_TARGET parameters. You restart your database instance. Which statement about the result is true?

A. The database instance starts, and Oracle sets the default value of SGA_TARGET to the same value as SGA_MAX_SIZE.

B. The database instance starts, and Oracle automatically tunes memory and allocates 60 percent to the SGA and 40 percent to the PGA.

C. The database instance starts, but Automatic Memory Management is disabled.

D. The database instance will not start because you did not specify the PGA_AGGREGATE_TARGET or SGA_TARGET parameter.

答案：B

（9）Note the following parameters settings in your database:

SGA_MAX_SIZE = 1024M

SGA_TARGET = 700M

DB_8K_CACHE_SIZE = 124M

LOG_BUFFER = 200M

You issued the following command to increase the value of DB_8K_CACHE_SIZE:

SQL> ALTER SYSTEM SET DB_8K_CACHE_SIZE=140M;

What would happen?

A. It will fail because DB_8K_CACHE_SIZE parameter cannot be changed dynamically

B. It will be successful only if the memory is available from the auto tuned components

C. It will fail because an increase in DB_8K_CACHE_SIZE cannot be accommodated within SGA_TARGET

D. It will fail because an increase in the DB_8K_CACHE_SIZE cannot be accommodated within SGA_MAX_SIZE

答案：D

（10）You set the following parameters in the parameter file and restarted the database:

MEMORY_MAX_TARGET=0

MEMORY_TARGET=500M

PGA_AGGREGATE_TARGET=90M

SGA_TARGET=270M

Which two statements are true regarding these parameters after the database instance is restarted? (Choose two.)

A. The MEMORY_MAX_TARGET parameter is automatically set to 500 MB.

B. The value of the MEMORY_MAX_TARGET parameter remains zero till it is changed manually.

C. The PGA_AGGREGATE_TARGET and SGA_TARGET parameters are automatically set to zero.

D. The lower bounds of PGA_AGGREGATE_TARGET and SGA_TARGET parameters are set to 90 MB and 270 MB, respectively.

答案：AD

（11）Which of the following Oracle features is enabled by setting a nonzero value for the MEMORY_TARGET initialization parameter?

A. Automatic PGA Memory Management

B. Automatic SGA Memory Management

C. Automatic Shared Memory Management

D. Automatic Memory Management

E. Manual SGA Memory Management

F. None of the above

答案：D

（12）By setting the value of MEMORY_TARGET to zero and setting the value of SGA_TARGET to a nonzero value, you will enable which of the following memory-management options?

A. Automatic PGA Memory Management

B. Automatic SGA Memory Management

C. Automatic Shared Memory Management

D. Automatic Memory Management

E. Manual SGA Memory Management

F. None of the above

答案：C

（13）For Oracle 11g, Oracle strongly recommends that you configure your database to use which of the following memory-management features?

A. Automatic PGA Memory Management

B. Automatic SGA Memory Management

C. Automatic Shared Memory Management

D. Automatic Memory Management

E. Manual SGA Memory Management

F. None of the above

答案：D

（14）To manually configure the SGA components using Oracle Enterprise Manager Memory Advisor, you can set values for which of the following initialization parameters? (Choose all that apply.)

A. DB_CACHE_SIZE

B. SHARED_POOL_SIZE

C. LARGE_POOL_SIZE

D. JAVA_POOL_SIZE

E. SGA_MAX_SIZE

F. SORT_AREA_SIZE

答案：ABCD

（15）When manually configuring the SGA, which of the following parameter changes requires an instance restart to take effect?

A. DB_CACHE_SIZE

B. SHARED_POOL_SIZE

C. LARGE_POOL_SIZE

D. JAVA_POOL_SIZE

E. SGA_MAX_SIZE

F. SORT_AREA_SIZE

答案：E

（16）Using Oracle Enterprise Manager to set SGA pool values manually, for which of the following pools does Oracle EM offer advice to set the value appropriately? (Choose all that apply.)

A. DB_CACHE_SIZE

B. SHARED_POOL_SIZE

C. LARGE_POOL_SIZE

D. JAVA_POOL_SIZE

E. SGA_MAX_SIZE

F. SORT_AREA_SIZE

答案：AB

（17）In Oracle 11g, by default which one of the following conditions implicitly enables Automatic PGA Memory Management?

A. Setting a nonzero value for SGA_TARGET

B. Configuring Automatic Shared Memory Management

C. Configuring Automatic Memory Management

D. Setting a nonzero value for SGA_MAX_SIZE and PGA_AGGREGATE_TARGET

E. None of the above

答案：B

（18）Automatic PGA Memory Management eliminates the need to manually configure which of the following initialization parameters? (Choose all that apply.)

A. SORT_AREA_SIZE

B. HASH_AREA_SIZE

C. BITMAP_MERGE_AREA_SIZE

D. CREATE_BITMAP_AREA_SIZE

E. PGA_AGGREGATE_TARGET

答案：ABCD

（19）When tuning Automatic PGA Memory Management, which of the following views will provide the information specified?

A. The V$PGA_TARGET_ADVICE view shows the predicted cache hit-ratio improvement if you increase PGA_AGGREGATE_TARGET.

B. The V$PGA_TARGET_ADVICE view shows how the V$SQL_WORKAREA histogram will change if you change the value of PGA_AGGREGATE_TARGET.

C. The V$PGA_TARGET_ADVICE_HISTOGRAM view shows how the

V$SQL_WORKAREA_HISTOGRAM will change if you switch between Manual and Automatic PGA Memory Management.

D. The V$PGA_TARGET_ADVICE view shows how performance will improve for the different work areas if you switch from Manual to Automatic PGA Memory Management.

答案：A

（20）Examine the exhibit to view the parameters set in your parameter file. (Click the Exhibit(s) button.)

You restart the instance.

To what value will the MEMORY_MAX_TARGET parameter be set by default?

A. 120M

B. 320M

C. 480M

D. 600M

答案：D

（21）Which statement about Automatic Memory Management with Oracle 11g is true?

A. You cannot specify MEMORY_TARGET if you explicitly specify SGA_TARGET or PGA_AGGREGATE_ TARGET values that are greater than zero in your parameter file.

B. Oracle can reallocate memory between the SGA and PGA automatically as needed.

C. To use Automatic Memory Management, you must explicitly set both the MEMORY_TARGET and MEMORY_MAX_TARGET parameters in your parameter file.

D. You can set the MEMORY_TARGET parameter to a maximum value of the current SGA size plus the current PGA size.

答案：B

（22）The DBA has chosen to manage SGA and PGA memory separately in an OLTP database because of his unique knowledge of the application. Which of these are good starting points to use when configuring the maximum values for SGA and PGA, based on the amount of memory available on the system?

A. 20% SGA, 80% PGA

B. 25% SGA, 75% PGA

C. 50% SGA, 50% PGA

D. 75% SGA, 25% PGA

E. 80% SGA, 20% PGA

答案：E

（23）Note the following parameter settings:

此主题相关图片如下：

Which setting is NOT allowed?

A. ALTER SYSTEM SET DB_CACHE_SIZE=50M;

B. ALTER SYSTEM SET DB_8K_CACHE_SIZE=10M;

C. ALTER SYSTEM SET DB_4K_CACHE_SIZE=10M;

D. ALTER SYSTEM SET DB_16K_CACHE_SIZE=10M;

答案：B

（24）You want to enable automatic PGA memory management in your database. Which setting is required to achieve this?

A. Set MEMORY_TARGET to zero

B. Set the STATISTICS_LEVEL parameter to BASIC

C. Set the WORKAREA_SIZE_POLICY parameter to MANUAL

D. Set the PGA_AGGREGATE_TARGET parameter to nonzero value

答案：D

（25）Examine the parameter setting in your database:

此主题相关图片如下：

Which statement is correct about the database?

A. Automatic memory management is disabled because PGA_AGGREGATE_TARGET and SGA_TARGET are not set

B. The instance is started but the database will not be opened until PGA_AGGREGATE_TARGET and SGA_TARGET are set

C. The database is opened but users cannot perform transactions until PGA_AGGREGATE_TARGET and SGA_TARGET are set

D. Automatic memory management is enabled and, as per policy, 60% of the memory for System Global Area (SGA) and 40% of the memory for Program Global Area (PGA) will be distributed at startup

答案：D

（26）View the Exhibit to observe the error.

此主题相关图片如下：

You receive this error regularly and have to shutdown the database instance to overcome the error. Automatic Shared Memory Management is configured for the instance. What can you do to reduce the chance of this error in the future?

A. Increase the value of SGA_MAX_SIZE

B. Enable automatic memory management

C. Set the PRE_PAGE_SGA parameter to true

D. Lock the System Global Area (SGA) in memory

答案：B

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2120680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28536251/viewspace-2120680/