Oracle Performance Tuning 11g2 (7-1)

最新推荐文章于 2021-06-06 17:53:48 发布

csnkza7163

最新推荐文章于 2021-06-06 17:53:48 发布

阅读量151

点赞数

7 Configuring and Using Memory

This chapter explains how to allocate memory to Oracle Database memory caches, and how to use those caches. Proper sizing and effective use of the Oracle Database memory caches greatly improves database performance. Oracle recommends using automatic memory management to manage the memory on your system. However, you can choose to manually adjust the memory pools on your system, as described in this chapter.

这节解释怎样去分配内存到数据库内存缓存，以及如何使用这些缓存。正确的调整大小和高效使用缓存将会极大的提升系统性能。建议使用自动内存管理。但是你可以像本节中介绍的去手动调整内存池。

This chapter contains the following sections:

Understanding Memory Allocation Issues 理解内存分配问题
Configuring and Using the Buffer Cache 配置和使用缓存
Configuring and Using the Shared Pool and Large Pool 配置使用共享池和大池
Configuring and Using the Redo Log Buffer 配置使用redo池
PGA Memory Management PGA内存管理
Managing the Server and Client Result Caches 管理服务端和客户端的result cache

7.1 Understanding Memory Allocation Issues

Oracle Database stores information in memory caches and on disk. Memory access is much faster than disk access. Disk access (physical I/O) take a significant amount of time, compared with memory access, typically in the order of 10 milliseconds. Physical I/O also increases the CPU resources required, because of the path length in device drivers and operating system event schedulers. For this reason, it is more efficient for data requests of frequently accessed objects to be perform by memory, rather than also requiring disk access.

A performance goal is to reduce the physical I/O overhead as much as possible, either by making it more likely that the required data is in memory, or by making the process of retrieving the required data more efficient.

oracle将信息存储在内存缓存和磁盘上。访问内存要比访问磁盘快的多。与访问内存相比，访问磁盘（IO）会消耗了大量的时间，通常需要在10毫秒左右。物理IO同样也增加了CPU资源的占用，因为设备驱动的路径长度和操作系统事件调度（这里不太理解）。基于这个原因，将频繁访问的数据放在内存中执行将会非常高效，而不是请求磁盘访问。

This section contains the following topics:

Oracle Memory Caches 内存缓存
Automatic Memory Management AMM管理内存
Automatic Shared Memory Management ASMM管理内存
Dynamically Changing Cache Sizes 动态的改变缓存大小
Application Considerations 应用考虑
Operating System Memory Use 操作系统内存使用
Iteration During Configuration 迭代配置

7.1.1 Oracle Memory Caches

The main Oracle Database memory caches that affect performance are: 影响性能的缓存有这个：

Shared pool
Large pool
Java pool
Buffer cache
Streams pool size
Log buffer
Process-private memory, such as memory used for sorting and hash joins

7.1.2 Automatic Memory Management

Oracle strongly recommends the use of automatic memory management to manage the memory on your system. Automatic memory management enables Oracle Database to automatically manage and tune the instance memory. Automatic memory management can be configured using a target memory size initialization parameter (MEMORY_TARGET) and a maximum memory size initialization parameter (MEMORY_MAX_TARGET). The database tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). Before setting any memory pool sizes, consider using the automatic memory management feature of Oracle Database. If you must configure memory allocations, consider using the Memory Advisor for managing memory.

oracle强烈的建议你使用AMM管理内存（自动内存管理）。AMM允许数据库自动管理和调优实例内存。AMM通过memory_target,memory_max_target参数进行配置。数据库调优目标内存大小，在SGA与PGA间按需进行内存分布（通过粒度及MMAP技术实现的）。在设置任何内存部件大小之前，先考虑使用AMM特性（即让数据库自动去调整，而不是自己去设置）。假如你必须要配置内存部件大小，那就考虑使用内存顾问，以便确定需要实际需要的内存大小。

7.1.3 Automatic Shared Memory Management

Automatic Shared Memory Management simplifies the configuration of the SGA. To use Automatic Shared Memory Management, set the SGA_TARGET initialization parameter to a nonzero value and set the STATISTICS_LEVEL initialization parameter to TYPICAL or ALL. Set the value of the SGA_TARGET parameter to the amount of memory that you intend to dedicate for the SGA. In response to the workload on the system, the automatic SGA management distributes the memory appropriately for the following memory pools:

ASMM简化了SGA的配置。为了使用ASMM，需要设置sga_target = xxxx和statistics_level = typical|all。将SGA_TARGET设置成你希望的内存大小。根据系统压力的不同，自动的SGA管理下面的内存部件：

Database buffer cache (default pool)
Shared pool
Large pool
Java pool
Streams pool

If these automatically tuned memory pools had been set to nonzero values, those values are used as minimum levels by Automatic Shared Memory Management. You would set minimum values if an application component needs a minimum amount of memory to function properly.

SGA_TARGET is a dynamic parameter that can be changed by accessing the SGA Size Advisor from the Memory Parameters SGA page in Oracle Enterprise Manager, or by querying the V$SGA_TARGET_ADVICE view and using the ALTER SYSTEM command. SGA_TARGET can be set less than or equal to the value of SGA_MAX_SIZE initialization parameter. Changes in the value of SGA_TARGET automatically resize the automatically tuned memory pools.

If you dynamically disable SGA_TARGET by setting its value to 0 at instance startup, Automatic Shared Memory Management will be disabled and the current auto-tuned sizes will be used for each memory pool. If necessary, you can manually resize each memory pool using the DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE initialization parameters.

假如这些自动优化的内存池已经被设置成非0值，那么这些值就被ASMM作为最小需要保留的值。假如你知道你的应用程序需要占用的最小的部件内存，那就设置一个最小值给它。

SGA_TARGET是一个动态参数，可以通过OEM的SGA SIZE Advisor来实时的调整其大小，或者查询 v$sga_target_advice 视图及使用alter system进行更新。但是sga_target <= sga_max_size参数。当改变SGA_TARGET的时候，SGA中的相关部件也一起随之改变了。

假如你在启动时将SGA_TARGET=0的话，ASMM将会被禁用掉，并且当前各个部件的内存大小将作为其实际大小了（为什么会这样子呢？关闭数据库后，去看看spfile就明白了！oracle将上次系统的各个部件大小都记录下来，以便下次重启的时候直接分配各部件到最佳状态。这就是为什么当ASMM关闭后其他部件会有正常的大小设置）。如果有必要的话，你可以手动的设置下面内存部件的大小：

db_cache_size, shared_pool_size, large_pool_size, java_pool_size, streams_pool_size参数。

The following pools are manually sized components and are not affected by Automatic Shared Memory Management:

下面这些部件是要手工去设置的，不会被ASMM影响到： log buffer, keep, recycle, other non-8k block size, fixed sga

Log buffer
Other buffer caches (such as KEEP, RECYCLE, and other nondefault block size)
Fixed SGA and other internal allocations

To manually size these memory pools, you must set the DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, and LOG_BUFFER initialization parameters. The memory allocated to these pools is deducted from the total available for SGA_TARGET when Automatic Shared Memory Management computes the values of the automatically tuned memory pools.

手动去调整这些内存部件，你必须去设置：db_keep_cache_size, db_recycle_cache_size, db_16k_cache_size, log_buffer参数。虽然这些参数不能被SGA_TARGET自动设置，但是这些参数加起来的内存是包含在SGA_TARGET中的，所以在设置真正需要的内存时，多加上这些内存到SGA_TARGET上。

7.1.4 Dynamically Changing Cache Sizes

If the system is not using Automatic Memory Management or Automatic Shared Memory Management, you can choose to dynamically reconfigure the sizes of the shared pool, the large pool, the buffer cache, and the process-private memory. The following sections contain details on sizing of caches:

假如系统没有使用ASMM或者AMM管理，你可以动态的重新配置这些shared pool,large pool,buffer cache, process-private部件的大小。下面的章节讨论了调整大小的一些细节：

Configuring and Using the Buffer Cache
Configuring and Using the Shared Pool and Large Pool
Configuring and Using the Redo Log Buffer

The size of these memory caches is configurable using initialization configuration parameters, such as DB_CACHE_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE, LOG_BUFFER, and SHARED_POOL_SIZE. The values for these parameters are also dynamically configurable using the ALTER SYSTEM statement except for the log buffer pool and process-private memory, which are static after startup.

Memory for the shared pool, large pool, java pool, and buffer cache is allocated in units of granules. The granule size is 4MB if the SGA size is less than 1GB. If the SGA size is greater than 1GB, the granule size changes to 16MB. The granule size is calculated and fixed when the instance starts up. The size does not change during the lifetime of the instance.

The granule size that is currently being used for SGA can be viewed in the view V$SGA_DYNAMIC_COMPONENTS. The same granule size is used for all dynamic components in the SGA.

You can expand the total SGA size to a value equal to the SGA_MAX_SIZE parameter. If the SGA_MAX_SIZE is not set, you can decrease the size of one cache and reallocate that memory to another cache if necessary. SGA_MAX_SIZE defaults to the aggregate setting of all the components.

这些部件内存大小是由：db_cache_size,java_pool_size,large_pool_size,log_buffer,shared_pool_size参数来配置的。这些参数可以直接通过alter system语句来配置，但是log buffer pool和process-private内存是静态参数，必须重新启动才能应用。

shared pool,large pool, java pool 和buffer cache的内存分配是按照粒度为单位进行分配的。如果SGA分配的大小小于1G的话，那么这个粒度就是4M。假如SGA大于1G，那么粒度就是16M。这个粒度的值是在实例启动时计算出来的，这个值在系统运行期内是不会改变的。

（注：为什么需要这个粒度呢？那考虑一下如果没有这个粒度会怎样？如果没有的话，那么oracle每次分配内存的时候就需要到处去找哪里有free的内存块，然后再确定其大小，最后还要更新自己的“数据库字典”以防止其他进程也从这里分配等等，非常的复杂。

有了粒度之后就好办了，如果一个“粒度块”没有使用，那就把那个4M或者16M的块拿过来使用，用完之后再还回去，用个简单的例子说明就是集装箱的道理，将整个粒度作为一个小的整体，整个SGA就是轮船，轮船里面都是由集装箱组成的，至于你要在集装箱里装什么是自己的事情了。在11G中，oracle使用了MMAP技术，可以在/dev/shm中看到有很多文件，每个都是以4M或者16M大小的，这个就是内存的一个映射，也是粒度的一个直观展示, mmap的意思就是memory map的意思。在10G中，oracle还是使用传统的共享内存技术，启动时分配大量的内存，然后Oracle又将其逻辑的分成许多粒度块使用。而在11G中直接通过操作系统的功能就实现了此方法。下面是我从MAN手册中取的mmap系统调用方法，了解即可。

那是不是只有oracle使用了这个粒度呢？显然不是的，生活中到处都有，操作系统也是一样。假如有16G的内存，那操作系统是如何管理呢？ ------ 它是将内存按4K为一个粒度进行划分的。那为什么又是要以4K为单位划分呢？因为是intel的地址总线这么定的。在32位系统中，注意不是指32位操作系统，而是地址总线只有32位的系统中，比如11111111,11111111,11110000,00000000，我随意的写了32位数字，最后这12位，我用12个0来表示，2¹²=4096=4K，那上面32 – 12 = 20位又是做什么呢？在linux中是按照段页式管理的，页就是这4K的单位的粒度，前面20位就代表一个有2²⁰=1M个页面，每个页面又是4K大小。

理解了这部分之后，就能知道一个程序最小的内存占用了。比如我写了一个简单的printf(“hello world!\n”);程序，那这个程序按理说最多战胜几十个字节，但是实际上操作系统要给它一个页面的，即4K字节使用的。同样如果我想要4M的内存，操作系统就给我们1024个页面（1024*4K = 4M）

）

可以通过 v$sga_dynamic_components 视图来查看粒度大小。SGA中所有的部件都使用相同的粒度。 v$sgainfo中也有的。

你可以将总的SGA大小扩展到SGA_MAX_SIZE大小。假如SGA_MAX_SIZE没有设置，如果需要的话，你可以将一个部件的内存减少，然后重新分配给其他的部件。SGA_MAX_SIZE默认是所有部件的内存总和。

Note:

SGA_MAX_SIZE cannot be dynamically resized. 注意这个SGA_MAX_SIZE是静态参数，修改完一定要加上scope=spfile,否则无法修改成功。

The maximum amount of memory usable by the instance is determined at instance startup by the initialization parameter SGA_MAX_SIZE. You can specify SGA_MAX_SIZE to be larger than the sum of all of the memory components, such as buffer cache and shared pool. Otherwise, SGA_MAX_SIZE defaults to the actual size used by those components. Setting SGA_MAX_SIZE larger than the sum of memory used by all of the components lets you dynamically increase a cache size without needing to decrease the size of another cache.