Oracle启动时不会按pga_aggregate_target或sga_target把内存预分配，而是在需要时再从系统获取内存，一旦不需要再释放给操作系统
关于SGA与PGA分配：
OLTP：SGA_TARGET = (total_mem * 0.8) * 0.8
PGA_AGGREGATE_TARGET=(total_mem * 0.8) * 0.2
OLAP(DSS)：SGA_TARGET= (total_mem * 0.8) * 0.5
PGA_AGGREGATE_TARGET =(total_mem * 0.8) * 0.5
Oracle内存管理的三种方式：
- Automatic memory management
You specify the target size for the database instance memory. The instance automatically tunes to the target memory size, redistributing memory as needed between the SGA and the instance PGA.
- Automatic shared memory management
This management mode is partially automated. You set a target size for the SGA and then have the option of setting an aggregate target size for the PGA or managing PGA work areas individually.
- Manual memory management
Instead of setting the total memory size, you set many initialization parameters to manage components of the SGA and instance PGA individually.
SGA自动管理也需要配置以下参数(如果使用)：log_buffer, db_keep_cache_size, db_recycle_cache_size与db_nK_cache_size，它们不会自调整
注意下面不同内存区域的视图和参数一般直接查询关键字即可获得，如查看result cache:
show parameter result
Select table_name from dict where table_name like '%RESULT%';
（一）User Global Area(UGA)
UGA用于SHARED SERVER的共享会话，共享会话中的server process还是会使用PGA的，但它处理的相关信息会存于SGA中的UGA，供所有server process共享
In shared server connections, client applications connect over a network to a dispatcher process, not a server process. The dispatcher process receives requests from connected clients and puts them into a request queue in the large pool. The first available shared server process takes the request from the queue and processes it. Afterward, the shared server places the result into the dispatcher response queue. The dispatcher process monitors this queue and transmits the result to the client.
Like a dedicated server process, a shared server process has its own PGA. However, the UGA for a session is in the SGA so that any shared server can access session data.
UGA可以看成PGA包含信息的子集，在Dedicate下UGA可以认为存于PGA中，在SHARED模式下的共享会话的PGA中的部分缓存会放到UGA，它会存于SGA的shared pool，如果设置了LARGE POOL则存于large pool。从下表可知共享会话的location of the run-time for DML and DDL statements还是会存于PGA中的
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session.
（二）Program Global Area (PGA)
Dedicated/shared server process以及background process均需要PGA
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. Background processes also allocate their own PGAs. This discussion focuses on server process PGAs only.
The following figure shows the possible contents of the PGA for a dedicated server session. Not all of the PGA areas will exist in every case.
When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas.
Private SQL Area存放的是某个SQL或PL/SQL某一次的执行相关信息, 所以同一session下可能同时有Multiple private SQL areas
Private SQL Area是不共享的，即使是同一session下执行两次的相同SQL也不共享
Private SQL Area会指向SGA的library cache中相应的执行计划，执行计划是会话可共享的
Do not confuse a private SQL area, which is in the PGA, with the shared 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 of SELECT * FROM sales 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.
CURSOR是某一个private SQL area的同义词
在client端可以认为cursor是一个指针而在server端cursor表示一个状态
A cursor is a name or handle to a specific private SQL area. As shown in the following graphic, you can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
A private SQL area is divided into the following areas:
- The run-time area
Oracle在执行请求前会先创建run-time area
对于DML语句，执行完成后直接释放此区域内存
This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.
Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.
- The persistent area
persistent area只在cursor关闭时才释放而不是执行完某个语句就释放
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.
Private SQL area是由client process来管理的(分配与回收)，但最多分配private SQL areas数量受限于初始参数OPEN_CURSORS
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.
SQL Work Areas用于进行内存密集型操作，包括Sort Area, Hash Area, Bitmap Merge Area
如果此部分空间不够将会把部分数据写于临时表空间
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.
- Session Memory
保存用户访问服务器进程的会话信息和与会话有关的信息
（三）System Global Area (SGA)
除redo log buffer其它SGA组件都是以相同的granule为单位分配与回收的，granule大小是在实例启动时依赖platform及SGA_MAX_SIZE自动决定的，SGA_MAX_SIZE越大granule越大
All SGA components except the redo log buffer allocate and deallocate space in units of contiguous memory called granules. Granule size is platform-specific and is determined by total SGA size. The granule size is determined by the amount of SGA memory requested when the instance stars. Specifically, the granule size is based on the value of the SGA_MAX_SIZE initialization parameter.
The same granule size is used for all components in the SGA
查看粒度：select * from v$sgainfo where name='Granule Size';
Granule的大小会影响你设置内存相关参数的实际值，如你设置了DB_CACHE_SIZE大小为10mb, 而granule为4MB，则实际DB_CACHE_SIZE大小为12MB
buffer cache is the memory area that stores copies of data blocks read from data files.
Buffer cache中的blocks可以为以下状态：
- 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.
Buffer中block被pinned即被锁
Every buffer has an access mode: pinned or free (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.
When a client requests data, Oracle Database retrieves buffers from the database buffer cache in either current mode or consistent mode.
The modes differ as follows:
逻辑读=db block get+consistent read
- Current mode
A current mode get, also called a db 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 use undo 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 a consistent read clone) that does not include the uncommitted updates. Typically, a query retrieves blocks in consistent mode.
To make buffer access efficient, the database must decide which buffers to cache in memory, and which to access from disk.
The database uses the following algorithms:
- LRU-based, block-level replacement algorithm
This sophisticated algorithm, which is the default, uses a least recently used (LRU) list that contains pointers to dirty and non-dirty buffers. The LRU list has a hot end and cold end.
A cold buffer is a buffer that has not been recently used. A hot buffer is frequently accessed and has been recently used. For data concurrency the database actually uses several LRUs.
The database measures the frequency of access of buffers on the LRU list using a touch count. This mechanism enables the database to increment a counter when a buffer is pinned instead of constantly shuffling buffers on the LRU list.
Note: The database does not physically move blocks in memory. The movement is the change in location of a pointer on a list.
When a buffer is pinned, the database determines when its touch count was last incremented. If the count was incremented over three seconds ago, then the count is incremented; otherwise, the count stays the same. The three-second rule prevents a burst of pins on a buffer counting as many touches. For example, a session may insert several rows in a data block, but the database considers these inserts as one touch.
If a buffer is on the cold end of the LRU, but its touch count is high, then the buffer moves to the hot end. If the touch count is low, then the buffer ages out of the cache.
- Temperature-based, object-level replacement algorithm
Starting in Oracle Database 12c Release 1 (12.1.0.2), the automatic big table caching feature enables table scans to use a different algorithm in the following scenarios:
Parallel queries:
In single-instance and Oracle RAC databases, parallel queries can use the big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a nonzero value, and PARALLEL_DEGREE_POLICY is set to auto or adaptive.
Serial queries:
In a single-instance configuration only, serial queries can use the big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a nonzero value.
When a table does not fit in memory, the database decides which buffers to cache based on access patterns. For example, if only 95% of a popular table fits in memory, then the database may choose to leave 5% of the blocks on disk rather than cyclically reading blocks into memory and writing blocks to disk—a phenomenon现象 known as thrashing颠簸. When caching multiple large objects, the database considers more popular tables hotter and less popular tables cooler, which influences which blocks are cached.
The DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter sets the percentage of the buffer cache that uses this algorithm.
See Also: vldb=very large database
Oracle Database VLDB and Partitioning Guide to learn more about the temperature-based algorithm
- Buffer I/O
A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache.
When a requested buffer is not found in memory, the database performs a physical I/O to copy the buffer from either the flash cache or disk into memory. The database then performs a logical I/O to read the cached buffer.
指把buff pool的dirty blocks写入磁盘，DBW writes buffers in the following circumstances:
- A server process cannot find clean buffers for reading new blocks into the buffer cache.
As buffers are dirtied, the number of free buffers decreases. If the number drops below an internal threshold, and if clean buffers are required, then server processes signal DBW to write.
The database uses the LRU to determine which dirty buffers to write. When dirty buffers reach the cold end of the LRU, the database moves them off the LRU to a write queue. DBW writes buffers in the queue to disk, using multiblock writes if possible. This mechanism prevents the end of the LRU from becoming clogged with dirty buffers and allows clean buffers to be found for reuse.
- The database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin.
- Tablespaces are changed to read-only status or taken offline.
- Buffer Reads
Oracle读取blocks时会按以下顺序搜索:
- The server process searches for the whole buffer in the buffer cache.
If the process finds the whole buffer, then the database performs a logical read of this buffer.
- The server process searches for the buffer header in the flash cache LRU list.
If the process finds the buffer header, then the database performs an optimized physical read of the buffer body from the flash cache into the in-memory cache.
- If the process does not find the buffer in memory (a cache miss), then the server process performs the following steps:
- Copies the block from a data file on disk into memory (a physical read)
- Performs a logical read of the buffer that was read into memory
When the number of unused buffers is low, the database must remove buffers from the buffer cache. The algorithm depends on whether the flash cache is enabled:
Flash cache disabled：
The database re-uses each clean buffer as needed, overwriting it.
Flash cache enabled：
DBW can write the body of a clean buffer to the flash cache, enabling reuse of its in-memory buffer. The database keeps the buffer header in an LRU list in main memory to track the state and location of the buffer body in the flash cache. If this buffer is needed later, then the database can read it from the flash cache instead of from magnetic disk.
物理IO后一般会有逻辑IO，但从temp file读取不经过buff cache所以只发生物理IO
The database can perform physical reads from either a data file or a temp file. Reads from a data file are followed by logical I/Os. Reads from a temp file occur when insufficient memory forces the database write data to a temporary table and read it back later. These physical reads bypass the buffer cache and do not incur a logical I/O.
Buffer cache被分为多个buff pools (注意cache与pool区别)
每个pool的aging或caching算法基本相同
A buffer pool is a collection of buffers. The database buffer cache is divided into one or more buffer pools, which manage blocks in mostly the same way. The pools do not have radically different algorithms for aging or caching blocks.
Buffer cache由以下类型pool组成(并非所有pool均必须存在，同一对象只缓存在同一pool):
Default pool(db_cache_size)：
This pool is the location where blocks are normally cached. Unless you manually configure separate pools, the default pool is the only buffer pool. The optional configuration of the other pools has no effect on the default pool.
Starting in Oracle Database 12c Release 1 (12.1.0.2), the big table cache is an optional section of the default pool that uses a temperature-based, object-level replacement algorithm.
In single-instance and Oracle RAC databases, parallel queries can use the big table cache when the DB_BIG_TABLE_CACHE_PERCENT_TARGET initialization parameter is set to a nonzero value, and PARALLEL_DEGREE_POLICY is set to auto or adaptive.
In single-instance configurations only, serial queries can use the big table cache when DB_BIG_TABLE_CACHE_PERCENT_TARGET is set.
Keep pool(db_keep_cache_size)
This pool is intended for blocks that were accessed frequently, but which aged out of the default pool because of lack of space. The purpose of the keep buffer pool is to retain objects in memory, thus avoiding I/O operations.
Note: The keep pool manages buffers in the same way as the other pools: it does not use a special algorithm to pin buffers. The word "keep" is a naming convention.
Recycle pool(db_recycle_cache_size)
This pool is intended for blocks that are used infrequently. A recycle pool prevent objects from consuming unnecessary space in the cache.
DB_nK_CACHE_SIZE(n可为2/4/8/16/32)：
default/keep/recycle pool均只能缓存db_block_size设置的标准块大小，一般OLTP为8K，OLAP可以设置16K或更大。在创建表空间时指定block大小
Each nondefault block size has its own pool. Oracle Database manages the blocks in these pools in the same way as in the default pool.
IM Area是可选的内存区域，用于以列模式缓存数据
The IM column store contains copies of tables, partitions, and materialized views in a columnar format optimized for rapid scans. The IM column store supplements the database buffer cache, which stores data in traditional row format.
相关参数log_buffer，单位bytes，通常小于2MB，合理的值max(0.5M, (128K*cpu个数))
日志刷到磁盘的条件之一是log buffer使用了1/3空间，所以说不能太小
LGWR writes redo sequentially to disk while DBW performs scattered writes of data blocks to disk. Scattered writes tend to be much slower than sequential writes.
Unlike other SGA components, the redo log buffer and fixed SGA buffer do not divide memory into granules.
- Shared Pool
相关参数SHARED_POOL_SIZE
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 a soft parse or a library cache hit.
Otherwise, the database must build a new executable version of the application code, known as a hard parse or a library cache miss.
每个执行的SQL都会有它对应的shared SQL area与private SQL area
在开始执行SQL时会用到shared SQL area, 它包含了statement parse tree以及execution plan.
每个shared sql area只用于唯一的statement; PGA中的private sql area会指定对应的shared sql area
The database represents each SQL statement that it runs in the shared SQL area and private 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 and execution plan. Only one shared SQL area exists for a unique statement.
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:
- 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 a child cursor.
In either case, the private SQL area for the user points to the shared SQL area that contains the statement and execution plan.
- 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.
- Program Units and the Library Cache
The library cache holds executable forms of PL/SQL programs and Java classes. These items are collectively referred to as program units.
The database processes program units similarly to SQL statements. For example, the database allocates a shared area to hold the parsed, compiled form of a PL/SQL program. The database allocates a private area to hold values specific to the session that runs the program, including local, global, and package variables, and buffers for executing SQL. If multiple users run the same program, then each user maintains a separate copy of his or her private SQL area, which holds session-specific values, and accesses a single shared SQL area.
The database processes individual SQL statements within a PL/SQL program unit as previously described. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that runs the statement.
DDL是不会使用shared pool, 不会被共享
The database allocates shared pool memory when a new SQL statement is parsed, unless the statement is DDL, which is not considered sharable. The size of memory allocated depends on the complexity of the statement.
In general, an item in the shared pool stays until the database removes it according to a least recently used (LRU) algorithm. The database allows shared pool items used by many sessions to remain in memory as long as they are useful, even if the database process that created the item terminates. If space is needed for new items, then the database frees memory consumed by infrequently used items.
使用alter system flush shared_pool与更改global database name效果一样
The ALTER SYSTEM FLUSH SHARED_POOL statement removes all information in the shared pool, as does changing the global database name.
- Data Dictionary Cache(row cache)
它是按行记录来缓存而非blocks, 在执行SQL解析时会频繁访问数据字典
The cache is also known as the row cache because it holds data as rows instead of buffers.
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.
- Server Result Cache
The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks.
The server result cache contains the SQL query result cache and PL/SQL function result cache, which share the same infrastructure.
Note: 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.
The SQL query result cache is a subset of the server result cache that stores the results of queries and query fragments.
When a query executes, the database searches memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and then stores the result in the result cache. 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 a RESULT_CACHE hint to indicate that the database should store results in the SQL query result cache.
The RESULT_CACHE_MODE initialization parameter determines whether the SQL query result cache is used for all queries (when possible) or only for annotated queries.
The PL/SQL function result cache is a subset of the server result cache that stores function result sets.
Upon invocation of this function, the system checks the cache. If the cache contains the result from a previous function call with the same parameter values, then the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, then the system executes the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.
The cache can accumulate many results—one result for every unique combination of parameter values with which each result-cached function was invoked. If the database needs more memory, then it ages out one or more cached results.
See Also: Oracle Database Development Guide to learn more about the PL/SQL function result cacheOracle Database PL/SQL Language Reference to learn more about the PL/SQL function result cache
- Reserved Pool
默认Java, PL/SQL, SQL cursor是存于shared SQL area中的，如果这些对象大于5KB(很少会出现这种情况)很容易因为内存碎片无法分配出连接内存使用，这时oracle会把它们存于reserved pool, 它使用chunks分配内存
The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.
The database allocates memory from the shared pool 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.
LAGET POOL没有排序功能，有排序功能的只是在PGA中
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 the 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
Large pool并不会象shared pool会把缓存按LRU age out, 只在会话自己释放后才会移除
By allocating session memory from the large pool, the database avoids the memory fragmentation that can occur when the database allocates memory from the shared pool.
When the database allocates large pool memory to a session, this memory is not eligible to be released unless the session releases it. In contrast, the database manages memory in the shared pool in an LRU fashion, which means that portions of memory can age out.
large pool与reserved pool区别
The large pool is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool. The large pool does not have an LRU list. Pieces of memory are allocated and cannot be freed until they are done being used. As soon as a chunk of memory is freed, other processes can use it.
- 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 when statistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.
The Streams pool stores buffered queue messages and provides memory for Oracle Streams capture processes and apply processes. The Streams pool is used exclusively by Oracle Streams.
Unless you specifically configure it, the size of the Streams pool starts at zero. The pool size grows dynamically as required by Oracle Streams.
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 about 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.
（四）Software Code Areas
用于存放Oracle数据库，该区域内存比用户程序所在的位置具有更多的独占性和受保护性
A software code area is a portion of memory that stores 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.
Note: The option of installing software shared is not available for all operating systems, for example, on PCs operating Microsoft Windows.
108
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
