Oracle Memory Architecture - Oracle 体系结构篇 5

前言：SGA的组件之一 Redo Log Buffer & Shared Pools

               Oracle Memory Architecture - Oracle 体系结构篇 4       http://blog.csdn.net/u010993297/article/details/9617039

1. Redo Log Buffer

 

                                          图 1 Redo log buffer

 

参考上面的图1，理解下面的内容：

<< -- Redo log buffer重做日志缓冲区-- redo entries redo条目包含的信息--- >>

>>Theredo log buffer is a circular buffer in the SGA that storesredo entries describing changes made to the database.Redo entries contain the information necessary toreconstruct, orredo, changes made to the databaseby DML or DDL operations. Database recovery appliesredo entries to data files to reconstruct lost changes.

 

<< -- Redo log buffer重做日志缓冲区—LGWR后台进程--- >>

>>Oracle Database processescopy redo entries from the user memory space to theredo log buffer in the SGA. The redo entries take up continuous, sequential space in the buffer. Thebackground processlog writer (LGWR)writes the redo log buffer to the active online redo log group on disk.

 

<< -- LGWR sequential writes || DBWn scattered writes —--- >>

 >>LGWR writes redo sequentially to disk whileDBWn performsscattered writes of data blocks to disk.Scattered writestend to be much slower thansequential writes. Because LGWR enable users to avoid waiting for DBWn to complete its slow writes, the database delivers better performance.

 

<< --初始化参数LOG_BUFFER--- >>

>>The LOG_BUFFER initialization parameter specifies the amount of memory that Oracle Database uses when buffering redo entries.Unlike other SGA components, the redo log buffer andfixed SGA buffer do not divide memory intogranules.

2. Shared Pools

 

                                                                    图2 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.

参考图2 看下面的有关shared pool的主要部件。

2.1 Library Cache

<< --- library cache || soft parse & hard parse软解析/硬解析 --- >>

The library cache is a shared pool memory structure that storesexecutable SQL and PL/SQL code. This cache contains the shared SQL andPL/SQL areas andcontrol structuressuch as locks andlibrarycache 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 alibrary cache hit. Otherwise, the database must build a new executable version of the application code, known as ahard parse or alibrary cache miss.

 

2.1.1 Shared SQL Areas

                                                        图 3private sql areas and shared sql area

 

图3 ：shows a dedicated server architecture in which two sessions keep a copy of the same SQL statement in their ownPGAs. In a shared server, this copy is in theUGA, which is in the large pool or in the shared poolwhen no large pool exists.

<< ----- SQL areas  ----- >>

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

>>Shared SQL area 共享sql区

The database uses theshared SQL area to process the first occurrence of a SQL statement.

This area is accessible to all users and contains the statementparse tree andexecution plan.

Only one shared SQL area exists for a unique statement.

>>Private SQL area 私有sql区

Each session issuing a SQL statement has aprivate SQL area in itsPGA .

Each user that submits the same statement has a private SQL area pointing to the sameshared SQL area.

Thus, many private SQL areas in separatePGAs can be associated with thesame shared SQL area.

<< ----- SQL statements steps  ----- >>

The database automatically determines when applications submit similar SQL 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 userpoints tothe shared SQL area that contains thestatement andexecution plan.

--其次为会话分配一个私有SQL区

>>Allocates a private SQL area on behalf of the session

The location of the private SQL area depends on theconnection established for the session.

If a session is connected through ashared server, then part of theprivate SQL area is kept in theSGA.

2.1.2 Program Units and the Library Cache

<< ----- what’s program units? ----- >>

The library cache holds executable forms ofPL/SQL programs andJava classes. These items are collectively referred to asprogram units.

<< ----- program units processes ----- >>

The database processes program units similarly to SQL statements.

For example, the database allocates a shared area to hold theparsed,compiled form of aPL/SQL program. The database allocates a private area to hold values specific to the session that runs the program, includinglocal,global, andpackage variables, andbuffers for executing SQL. If multiple users run the same program, then each user maintains a separate copy of his or herprivate SQL area, which holds session-specific values, and accesses asingle shared SQL area.

The database processesindividual 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 theirparsed representations and aprivate area for each session that runs the statement.

2.1.3 Allocation and Reuse of Memory in the Shared Pool 

<< ----- 共享池的内存大小取决于sql语句的复杂度 ----- >>

The database allocates shared pool memory when a new SQL statement is parsed. The memory size depends on the complexity of the statement.

--依据LRU算法

In general, an item in the shared pool stays until it is removed according to an LRU algorithm.

The database allows shared pool items used bymany sessions to remain in memory as long as they are useful, even if the process that created the item terminates. This mechanism minimizes the overhead and processing ofSQL statements.

If space is needed for new items, then the database frees memory for infrequently used items.A shared SQL area can be removed from the shared pool even if the shared SQL area corresponds to anopen cursor that has not been used for some time. If the opencursor is subsequently used to run its statement, then Oracle Databasereparses the statement and allocates a newshared SQL area.

<< ----- 数据库共享池移除共享sql区，在以下几种情况下：统计分析(表、簇表、索引)/对象的DDL语句修改/数据库名称被改变----- >>

The database also removes a shared SQL area from the shared pool in the following circumstances:

>>If statistics are gathered for atable,table cluster, orindex, then by default the database gradually removes all shared SQL areas that contain statements referencing the analyzed object after a period of time. The next time a removed statement is run, the database parses it in a new shared SQL area to reflect the new statistics for the schema object.

>>If a schema object is referenced in a SQL statement, and if this object is latermodified by aDDL statement, then the databaseinvalidates the shared SQL area. The optimizer must reparse the statement the next time it is run.

>>If you change theglobal database name, then the databaseremoves all information from the shared pool.

--可以用命令手动刷新共享池

   Alter system flush shared_pool

>>You can use the  Alter system flush shared_pool statement to manually remove all information in the shared pool to assess the performance that can be expected after an instance restart.

2.2 Data Dictionary Cache

<< --数据字典缓冲区经常被数据库访问的地方-- >>

--数据库字典包含什么？

The data dictionary is a collection of databasetables andviews containing referenceinformation about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently duringSQL 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.

2.3 Server Result Cache

<<--server result cache(sql query result | pl/sql function result cache)—client result cahe (client memory) -->>

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 andPL/SQL function result cache, which share the same infrastructure.

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

 

2.3.1 SQL Query Result Cache

---sql query result cache中存储的是queries和query fragments的结果

The database can store the results ofqueries and query fragments in the sql 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 same select 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_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.

2.3.2 PL/SQL Function Result Cache

The PL/SQL function result cache storesfunction result sets.Without caching, 1000 calls of a function at 1 second per call wouldtake 1000 seconds. With caching, 1000 function calls with the same inputs couldtake 1 second total. Good candidates for result caching are frequently invoked functions that depend on relativelystatic data.

PL/SQL function code can include a request to cache its results. 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 thefunction bodyand adds the result (for these parameter values) to the cache before returning control to the invoker.

2.4 Reserved Pool

The reserved poolis a memory area in the shared pool that Oracle Database can use toallocate large contiguous chunks of memory.

Allocation of memory from the shared pool is performed inchunks. Chunking allowslarge 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, orSQL cursors may make allocations out of the shared pool that are larger than5 KB. To allow these allocations to occur most efficiently, the database segregates a small amount of the shared pool for thereserved pool.

 

今天到这里，明天继续，，发现一个催眠的方法，就是抱一本技术英文书在床头啃，之后就很快就进入入眠的状态。强烈建议大家去买本英文书。