Memory Architecture-SGA-Shared Pool

启动instance:

1、分配内存空间SGA

2、启动后台进程

内存结构：

1、SGA

    2、PGA

    3、UGA

                4、Software code areas

SGA components：

1、Database Buffer Cache

    2、Redo Log Buffer

    3、Shared Pool

                4、Large Pool

                5、Java Pool

                6、Streams Pool

                7、Fixed SGA

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

Shared Pool(共享池):

Shared Pool可以存储多种类型的数据。如：解析过的SQL、PL/SQL代码、系统参数和数据字典信息。

Shared Pool主要包括：

1、Library Cache

2、Data Dictionary Cache

3、Server Result Cache

4、Reserved Pool

1、Library Cache

Library Cache 存储可执行的SQL和PL/SQL代码。

Library Cache包含Shared SQL and PL/SQL areas（Shared SQL Areas）和locks的控制结构。在shared server architecture中，Library Cache还包含private SQL Areas。

当一个SQl语句执行的时候，数据库试图重用之前执行的代码。如果在Library Cache存在此SQL已经解析过的SQL code，并且解析过的SQL可以共享，则数据库重用解析过的SQL code，这个称为soft parse或者Library Cache hit。如果在Library Cache不存在此SQL已经解析过的SQL code，则数据库必须为此SQL build 解析SQL code，这个称为hard parse或者Library Cache miss。

1.1 Shared SQL Areas

Shared SQL Areas:The database uses the shared SQL area to process the first occurrence of a SQL statement。所有用户都可以访问Shared SQL Areas。Shared SQL Areas包含SQL的解析树和执行计划。

Private SQL Areas:Each session issuing a SQL statement has a private SQL area in itsPGA。每个执行相同SQL Statement的用户都有一个指向同一个Shared SQL Areas的Private SQL Areas。

当执行SQL语句时，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 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.

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.

1.2 Program Units and Library Cache

The library cache holds executable forms of PL/SQL programs and Java classes. These items are collectively referred to asprogram units.

The database processes program units similarly to SQL statements.

1.3 Allocation and Reuse of Memory in the Shared Pool

当一个新的SQL被解析的时候数据库分配Shared Pool Memory，分配空间的大小depends on SQL语句的复杂程度。

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 by many 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 of SQL statements.
 

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

• If statistics are gathered for a table,table cluster, or index, 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 later modified by a DDL statement, then the database invalidates the shared SQL area. The optimizer must reparse the statement the next time it is run.

• If you change the global database name, then the database removes all information from the shared pool.

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

This cache holds information about database objects. The cache is also known as the row cache because it holds data as rows instead of buffers.
3、Server Result Cache

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.

SQL query result cache:The database can store the results of queries 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.

PL/SQL function result cache:The PL/SQL function result cache stores function result sets. Without caching, 1000 calls of a function at 1 second per call would take 1000 seconds. With caching, 1000 function calls with the same inputs could take 1 second total. 

4、Reserved Pool

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

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.