oracle 内存体系

Oracle内存体系 Basic Memory Structures The basic memory structures associated with Oracle Database include: System global area (SGA) The SGA is a group of shared memory structures, known as SGA components , that contain data and control in

Oracle内存体系

Basic Memory Structures

The basic memory structures associated with Oracle Database include:

• System global area (SGA)

  The SGA is a group of shared memory structures, known asSGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.

• Program global area (PGA)

  A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.

  One PGA exists for eachserver process and background process. The collection of individual PGAs is thetotal instance PGA, orinstance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.

• User Global Area (UGA)

  The UGA is memory associated with a user session.

• Software code areas

  Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.

UGA概述

UGA是会话内存（session memory），保存会话变量，比如登陆信息等。基本上UGA都是存储关于会话状态的一些信息。

UGA的分配:shared_server  UGA在SGA中分配；dedicated_server UGA在PGA中分配。

PGA概述

The PGA is memory specific to an operating process or thread that is not shared by other processes or threads on the system. Because the PGA is process-specific, it is never allocated in the SGA.

The PGA is a memory heap that contains session-dependent variables required by a dedicated or shared server process. The server process allocates memory structures that it requires in the PGA.

PGA包含的内容

PGA分为了一些子工作区，每个子工作区有不同的作用，下面展示PGA可能有的子工作区，并不是每一个都存在于任何一个PGA内

Private SQL Area

A private SQL area holds information about a parsed SQL statement and other session-specific information for processing. When a server process executes SQL or PL/SQL code, the process uses the private SQL area to storebind variable values, query execution state information, and query execution work areas.

Do not confuse a private SQL area, which is in the UGA, with theshared SQL area, which stores execution plans in the SGA. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA. For example, 20 executions ofSELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data.

A private SQL area is divided into the following areas:

• The run-time area

  This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in afull table scan.

  Oracle Database creates the run-time area as the first step of an execute request. ForDML statements, the run-time area is freed when the SQL statement is closed.

• The persistent area

  This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.

The client process is responsible for managing private SQL areas. The allocation and deallocation of private SQL areas depends largely on the application, although the number of private SQL areas that a client process can allocate is limited by the initialization parameter OPEN_CURSORS.

Although most users rely on the automatic cursor handling of database utilities, the Oracle Database programmatic interfaces offer developers more control over cursors. In general, applications should close all open cursors that will not be used again to free the persistent area and to minimize the memory required for application users.

--不良好的应用设计，在使用使用游标后，不释放游标，会导致ora-10000错误，此时可以暂时增大open_corsors参数缓解。但是还需修改应用代码，解决根源问题

SQL Work Areas

A work area is a private allocation of PGA memory used for memory-intensive operations. For example, a sort operator uses thesort area to sort a set of rows. Similarly, ahash join operator uses ahash area to build ahash table from its left input, whereas abitmap merge uses thebitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

sort_aera 用于排序的内存区域。hash_area 两个表发生hash_join时，用到hash_area。bitmap merge uses the bitmap merge area to merge data retrieved from scans of multiple bitmap indexes.

If the amount of data to be processed by the operators does not fit into a work area, then Oracle Database divides the input data into smaller pieces. In this way, the database processes some data pieces in memory while writing the rest to temporary disk storage for processing later.

--如果由于工作区不适合操作结果的存放，此时oracle会写入一部分到磁盘。如果排序过大，可能会用到tempfile。

Generally, larger work areas can significantly improve performance of an operator at the cost of higher memory consumption. Optimally, the size of a work area is sufficient to accommodate the input data and auxiliary memory structures allocated by its associated SQL operator. If not, response time increases because part of the input data must be cached on disk. In the extreme case, if the size of a work area is too small compared to input data size, then the database must perform multiple passes over the data pieces, dramatically increasing response time.

PGA   和UGA

PGA是进程专用的内存，这是oracle专用或共享服务器进程需要的一组独立于会话的变量，pga是一个“内存堆”，其中还可以分配其他结构（sort，hash，bitmap  merge等等）

UGA也是一个内存堆 ，其中定义会话特有的机构。在shared server mode，UGA从SGA中分配，在dedicated server中，在PGA中分配。因此，在设定共享服务器（shared server mode）的时候，必须设定large pool的大小，以便有足够的空间来适应可能的并发访问数据库。

SGA概述

The SGA is a read/write memory area that, along with the Oracle background processes, make up a database instance. All server processes that execute on behalf of users can read information in the instance SGA. Several processes write to the SGA during database operation.

SGA组件

• database buffer cache
• fixed sga
• redo log buffers
• share pool
• large pool
• java pool
  
• stream pool

Database Buffer Cache

Thedatabase buffer cache, also called thebuffer cache, is the memory area that stores copies of data blocks read from data files. Abuffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache.

Oracle Database uses the buffer cache to achieve the following goals:

• Optimize physical I/O

  The database updates data blocks in the cache and stores metadata about the changes in the redo log buffer. After aCOMMIT, the database writes the redo buffers to disk but does not immediately write data blocks to disk. Instead,database writer (DBW) performslazy writes in the background.

• Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk

  When Database Smart Flash Cache (flash cache) is enabled, part of the buffer cache can reside in the flash cache. This buffer cache extension is stored on aflash disk device, which is a solid state storage device that uses flash memory. The database can improve performance by caching buffers in flash memory instead of reading from magnetic disk.

Buffer States

The database uses internal algorithms to manage buffers in the cache. A buffer can be in any of the following mutually exclusive states:

• Unused

  The buffer is available for use because it has never been used or is currently unused. This type of buffer is the easiest for the database to use.

• Clean

  This buffer was used earlier and now contains a read-consistent version of a block as of a point in time. The block contains data but is "clean" so it does not need to be checkpointed. The database can pin the block and reuse it.

• Dirty

  The buffer contain modified data that has not yet been written to disk. The database must checkpoint the block before reusing it.

Every buffer has an access mode:pinned orfree (unpinned). A buffer is "pinned" in the cache so that it does not age out of memory while a user session accesses it. Multiple sessions cannot modify a pinned buffer at the same time.

  The database uses a sophisticated algorithm to make buffer access efficient. Pointers to dirty and nondirty buffers exist on the sameleast recently used (LRU) list, which has a hot end and cold end. Acold buffer is one that has not been recently used. Ahot buffer is frequently accessed and has been recently used.

--可以指定将一部分对象“钉”在缓冲区内，这样这部分对象不会移除

Buffer Modes

When a client requests data, Oracle Database retrieves buffers from the database buffer cache in either of the following modes:

• Current mode

  A current mode get, also called adb block get, is a retrieval of a block as it currently appears in the buffer cache. For example, if an uncommitted transaction has updated two rows in a block, then a current mode get retrieves the block with these uncommitted rows. The database uses db block gets most frequently during modification statements, which must update only the current version of the block.

• Consistent mode

  A consistent read get is a retrieval of a read-consistent version of a block. This retrieval may useundo data. For example, if an uncommitted transaction has updated two rows in a block, and if a query in a separate session requests the block, then the database uses undo data to create a read-consistent version of this block (called aconsistent read clone) that does not include the uncommitted updates. Typically, a query retrieves blocks in consistent mode.

Buffers and Full Table Scans

When buffers must be read from disk, the database inserts the buffers into the middle of the LRU list. In this way, hot blocks can remain in the cache so that they do not need to be read from disk again.

A problem is posed by a full table scan, which sequentially reads all rows under the tablehigh water mark (see "Segment Space and the High Water Mark"). Suppose that the total size of the blocks in a table segment is greater than the size of the buffer cache. A full scan of this table could clean out the buffer cache, preventing the database from maintaining a cache of frequently accessed blocks.

Blocks read into the database cache as the result of a full scan of a large table are treated differently from other types of reads. The blocks are immediately available for reuse to prevent the scan from effectively cleaning out the buffer cache.

In the rare case where the default behavior is not desired, you can change theCACHE attribute of the table. In this case, the database does not force or pin the blocks in the buffer cache, but ages them out of the cache in the same way as any other block. Use care when exercising this option because a full scan of a large table may clean most of the other blocks out of the cache.

--一般的块在进入到缓冲区的时候，进入到LRU的中部，这样会保证热快不被移除。如果在全表扫描（table full scan ）的时候，情况会不一样。全表扫描的时候，进入到缓冲区的块进入到 LRU的尾部。缓冲区和大表全表扫描的关系。

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database.Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.

Oracle Database processes copy redo entries from the user memory space to the redo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. The background processlog writer (LGWR) writes the redo log buffer to the active online redo log group on disk.Figure 14-8 shows this redo buffer activity.

--redo log buffer 是一个循环的缓冲区，用来存储从用户内存copt来的redo条目，顺序存储。在SGA中，redo log buffer  和fixed SGA的非配不是按照granule size分配。

关于SGA的粒度大小，一般存在这样的关系：

SGA Memory Amount	Granule Size
Less than or equal to 1 GB	4 MB
Greater than 1 GB and less than or equal to 8 GB	16 MB
Greater than 8 GB and less than or equal to 16 GB	32 MB
Greater than 16 GB and less than or equal to 32 GB	64 MB
Greater than 32 GB and less than or equal to 64 GB	128 MB
Greater than 64 GB and less than or equal to 128 GB	256 MB
Greater than 128 GB	512 MB

或者可以在动态性能视图v$sgainfo 中查看Granule Size 。

内存的分配按照粒度来分配。例如，granule size=4M 但是你想要一个6M的池，那么oracle会为你分配8M（大于等于granule size的 最小倍数）。

可以通过v$memory_dynamic_components 和v$sga_dynamic_components查看各个池的粒度。

SQL> desc v$sga_dynamic_components;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COMPONENT                                          VARCHAR2(64)
 CURRENT_SIZE                                       NUMBER
 MIN_SIZE                                           NUMBER
 MAX_SIZE                                           NUMBER
 USER_SPECIFIED_SIZE                                NUMBER
 OPER_COUNT                                         NUMBER
 LAST_OPER_TYPE                                     VARCHAR2(13)
 LAST_OPER_MODE                                     VARCHAR2(9)
 LAST_OPER_TIME                                     DATE
 GRANULE_SIZE                                       NUMBER

Shared Pool

The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, anddata dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.

share pool 组件

• data dictionary cache
• library cache
• server result cache
• reserved pool

库缓存(Library Cache)

   The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles.In a shared server architecture, the library cache also contains private SQL areas.

When a SQL statement is executed, the database attempts to reuse previously executed code. If a parsed representation of a SQL statement exists in the library cache and can be shared, then the database reuses the code, known as asoft parse or a library cache hit. Otherwise, the database must build a new executable version of the application code, known as ahard parse or alibrary cache miss.

--库缓存主要用于缓存可执行sql和pl/sql代码。一个语句需要执行时，会先在库缓存寻找是否以前执行过改语句。如果找到，则库缓存命中，软解析。未命中，库缓存miss，硬解析。

Shared SQL Areas

The database represents each SQL statement that it runs in the following SQL areas:

• Shared SQL area

  The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree andexecution plan. Only one shared SQL area exists for a unique statement.

• Private SQL area

  Each session issuing a SQL statement has a private SQL area in its PGA . Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.

The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements.

The database performs the following steps:

1. Checks the shared pool to see if a shared SQL area exists for a syntactically and semantically identical statement:

   • If an identical statement exists, then the database uses the shared SQL area for the execution of the subsequent new instances of the statement, thereby reducing memory consumption.

   • If an identical statement does not exist, then the database allocates a new shared SQL area in the shared pool. A statement with the same syntax but different semantics uses achild cursor.

   In either case, the private SQL area for the user points to the shared SQL area that contains the statement and execution plan.

2. Allocates a private SQL area on behalf of the session

   The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.

Server Result Cache

Unlike the buffer pools, the server result cache holds result sets and not data blocks. The server result cache contains theSQL query result cache andPL/SQL function result cache, which share the same infrastructure.

A client result cache differs from the server result cache. A client cache is configured at the application level and is located in client memory, not in database memory.

SQL Query Result Cache

The database can store the results of queries and query fragments in theSQL query result cache, using the cached results for future queries and query fragments. Most applications benefit from this performance improvement.

For example, suppose an application runs the sameSELECT statement repeatedly. If the results are cached, then the database returns them immediately. In this way, the database avoids the expensive operation of rereading blocks and recomputing results. The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of database objects used to construct that cached result.

Users can annotate a query or query fragment with aRESULT_CACHEhint to indicate that the database should store results in the SQL query result cache. TheRESULT_CACHE_MODE initialization parameter determines whether the SQL query result cache is used for all queries (when possible) or only for annotated queries.

Reserved Pool

The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.

Allocation of memory from the shared pool is performed in chunks. Chunking allows large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of contiguous memory because of fragmentation.

Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5 KB. To allow these allocations to occur most efficiently, the database segregates a small amount of the shared pool for the reserved pool.

Data Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing.

The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data:

• Data dictionary cache

  This cache holds information about database objects. The cache is also known as therow cache because it holds data as rows instead of buffers.

• Library cache

All server processes share these caches for access to data dictionary information.

dictionary  cache ==> 也叫做row cache ， row cache字典缓存 是oracle 中 真正意义上 以 行 row 形式呈现的数据结构。
buffer cache 中当然会有 数据字典基表 ，但是  buffer cache中的信息 总是以 block的形式存放的， 而字典缓存的 存在是为了 方便 oracle 内部函数操作、和解析操作能够 高效的运行的， 如果直接访问block中的字典信息 那么显然是很低效的。
另一方面 对字典的并发访问和修改  不能直接依赖于 block lock ， 只有  row cache的形式才能实现。但是 需要注意 本质上row cache的数据来源于 buffer cache中的 字典基表 例如tab$ 、 obj$等， 同时字典信息也最终 会以buffer cache的形式被保存到磁盘上。

Large Pool

The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. The large pool can provide large memory allocations for the following:

• UGA for the shared server and he Oracle XA interface (used where transactions interact with multiple databases)

• Message buffers used in the parallel execution of statements

• Buffers for Recovery Manager (RMAN) I/O slaves

By allocating session memory from the large pool for shared SQL, the database avoids performance overhead caused by shrinking the shared SQL cache. By allocating memory in large buffers for RMAN operations, I/O server processes, and parallel buffers, the large pool can satisfy large memory requests better than the shared pool.

--large pool，可选组件，可以在shared server模式分配uga，缓存并行执行的语句，和RMAN操作缓存。

Java Pool

The Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM). This memory includes Java objects that are migrated to the Java session space at end-of-call.

For dedicated server connections, the Java pool includes the shared part of each Java class, including methods and read-only memory such as code vectors, but not the per-session Java state of each session. For shared server, the pool includes the shared part of each class and some UGA used for the state of each session. Each UGA grows and shrinks as necessary, but the total UGA size must fit in the Java pool space.

The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on whenstatistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.

--共享服务器使用large pool作为会话内存；并行执行特性使用large pool作为信息缓冲区；RMAN 备份使用large pool作为磁盘I/O缓存。large pool可以动态调整大小

Fixed SGA

The fixed SGA is an internal housekeeping area. For example, the fixed SGA contains:

• General information about the state of the database and the instance, which the background processes need to access

• Information communicated between processes, such as information aboutlocks (see"Overview of Automatic Locks")

The size of the fixed SGA is set by Oracle Database and cannot be altered manually. The fixed SGA size can change from release to release.

Overview of Software Code Areas

Software code areas are portions of memory that store code that is being run or can be run. Oracle Database code is stored in a software area that is typically more exclusive and protected than the location of user programs.

Software areas are usually static in size, changing only when software is updated or reinstalled. The required size of these areas varies by operating system.

Software areas are read-only and can be installed shared or nonshared. Some database tools and utilities, such as Oracle Forms and SQL*Plus, can be installed shared, but some cannot. When possible, database code is shared so that all users can access it without having multiple copies in memory, resulting in reduced main memory and overall improvement in performance. Multiple instances of a database can use the same database code area with different databases if running on the same computer.