Shulou(Shulou.com)06/01 Report--

Shared pool

Shared pools cache various types of program data.

For example, shared pools store parsing SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool involves almost all the operations that occur in the database. For example, if the user executes the SQL statement, the oracle database accesses the shared pool.

The shared pool is divided into several components, the most important of which is shown in the following figure:

These structures include:

Library cache

Data dictionary cache

Server result cache

Reserved pool

Detailed explanation of library cache

Library caching is a shared pool memory structure that stores executable SQL and PL/SQL code.

The cache contains shared SQL and PL/SQL areas and control structures, such as locks and repository cache handles. In the shared server architecture, the library cache also contains private SQL regions.

When the SQL statement is executed, the database attempts to reuse the previously executed code. If the parsing of a SQL statement exists in the library cache and can be shared, the database reuses the code, called soft parsing or cache hit. Otherwise, the database must build a new executable version of the application, called hard parsing or cache omission (cache miss).

Shared SQL area

The database processes every SQL statement that runs in the shared SQL zone and the private SQL zone.

The database processes the first occurrence of SQL statements in the shared SQL zone, which is accessible to all users and contains a statement parsing tree and an execution plan. For each unique SQL statement, there is only one shared SQL zone. A SQL statement issued by each session results in a private SQL zone corresponding to it in the PGA. Even if different sessions submit the same SQL statement, each session has its own SQL zone. Each private SQL zone in the PGA is associated with a shared SQL zone.

When the application submits a similar SQL statement, the database automatically determines. The database considers both SQL statements issued directly by users and applications, as well as recursive SQL statements issued internally.

The database performs the following steps:

1. Check the shared pool to see if there are statements with similar syntax and semantics in the shared SQL area.

If the same statements exist, the database will use them directly, reducing memory consumption.

If the same statement does not exist, the database will allocate a new shared SQL zone in the shared pool. Statements with the same syntax but different meanings use subcursors.

In both cases, the user's private SQL area points to the shared SQL area that contains the statement and execution plan.

2. Assign a private SQL area to the session

The location of the private SQL zone depends on how the session is connected. If the session is connected through a shared server, part of the private SQL zone will remain in the SGA.

The following figure shows a dedicated server architecture in which two sessions keep a copy of the same SQL statement in their own PGA. In the shared server, this copy is located in UGA, it is in the large pool, and when the large pool does not exist, the copy exists in the shared pool.

Program unit and library cache

The library cache contains executable forms of PL/SQL programs and Java classes. These projects are collectively referred to as program units.

The database processor unit is similar to processing SQL statements. For example, the database allocates a shared area to hold the form of a parsed, compiled PL/SQL program. The database allocates a private zone to hold session values specific to running the program, including local, global, and package variables, as well as buffers that execute SQL. If multiple users are running the same program, each user maintains a separate copy of his own private SQL area that contains session-specific values and accesses a single shared SQL area.

The database processes a single SQL statement within the PL/SQL program unit, as described earlier. Although these SQL statements originate from PL/SQL program units, they use a shared area to hold their parsing statements and a private area for each session to execute the statements.

Allocate and reuse memory areas in shared pools

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

In general, an item in a shared pool is held until it is deleted according to the LRU algorithm. The database allows shared pool items used by multiple sessions to stay in memory as long as they are useful, even if the process that created the project is terminated. This mechanism minimizes the overhead and processing of SQL statements.

If a new project requires space, the database can free up space occupied by infrequently used projects. A shared SQL region can be removed from the shared pool, even if the shared SQL region corresponds to an open cursor that is not long used. If you then run its statement using open cursor, the oracle database fixes the statement and allocates a new shared SQL area.

The database removes the shared SQL zone from the shared pool under the following circumstances:

If the database collects statistics for tables, table clusters, or indexes, by default, the database gradually deletes all shared SQL areas that contain statements that reference the analysis object after a period of time. The next time the delete statement is run, the database parses it in a new shared SQL region to reflect the new statistics for the schema object.

If a schema object is referenced in a SQL statement, and if the object is later modified by a DDL statement, the database invalidates the shared SQL region. The optimizer must reparse the statement the next time it runs.

If you change the global database name (global database name = database name + database domain name, such as: oradb.fj.jtyz), the database removes all information from the shared pool.

To assess performance problems that may occur after the database instance is restarted, manually delete all information from the shared pool using the ALTER SYSTEM FLUSH SHARED_POOL statement.

Data dictionary cache

A data dictionary is a collection of database tables and views that contains reference information about the database, its structure, and users.

Oracle databases often access data dictionaries during SQL statement parsing. Access the data dictionary frequently in the oracle database and specify the following special memory location to store the dictionary data:

Data dictionary cache

This cache contains information about database objects. Caching is also called row caching because it saves data as rows rather than buffers.

Library cache

These caches are shared by all server processes to access data dictionary information.

Service result caching

The server result cache is a memory pool in a shared pool. Unlike buffer pools, the server result cache contains result sets, not data blocks.

The server result cache contains the SQL query result cache and the PL/SQL function result cache, which share the same infrastructure.

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

SQL query result cache

The SQL query result cache is a subset of the server's result cache that stores the results of queries and query fragments.

Most applications benefit from this performance improvement. Consider an application that runs the same select statement repeatedly. If the results are cached, the database returns them immediately. In this way, the database avoids the expensive operation of rerunning the block and recalculating the results.

When the query is executed, the database searches memory to determine whether the results exist in the results cache. If the result exists, the database retrieves the result from memory instead of executing the query. If the results are not cached, the database executes the query and returns the results, and then stores the results in the results cache. Whenever a transaction modifies the data or metadata of the database object used to construct cached results, the database automatically invalidates the cached results.

Users can use RESULT_CACHE prompts to annotate queries or query fragments to indicate the results that the database should store in the SQL query results cache. The RESULT_CACHE_MODE initialization parameters determine whether the SQL query result cache is used for all queries or only for annotated queries.

PL/SQL function result cache

The PL/SQL function result cache is a subset of the server result cache that stores the function result set.

Without caching, 1000 function calls (1s/) would take 1000 seconds. With caching, 1000 function calls with the same input take only 1 second. Excellent candidate functions for the result cache are often called, and these functions rely on relatively static data.

The PL/SQL function code can contain a request to cache its results. When this function is called, the system checks the cache. If the cache contains the results of a previous function call with the same parameter value, the system will directly return the cache value to the requestor, and the database will no longer execute the function body. If the cache does not contain results, the function body is executed, the results for these parameter values are added to the cache, and control is returned to the caller.

You can specify the database objects used by the oracle database to calculate the cached results, so if any of them are updated, the cached results will become invalid and must be recalculated.

Caching can accumulate many results-a result is the only combination of each called parameter value. If the database needs more memory, it needs to calculate one or more cached results.

Reserved pool

A reserved pool is an area of memory in a shared pool that an oracle database can use to allocate a large number of contiguous memory blocks.

The database allocates memory from shared pools. Blocks allow large objects (more than 5Kb) to be loaded into the cache without requesting a single adjacent region. In this way, the database reduces the possibility of running out of contiguous memory due to fragmentation.

Unusually, Java, PL/SQL, or SQL cursors may be allocated from shared pools larger than 5kb. In order to make these allocations most efficient, the database isolates a small number of shared pools in the reserved pools.

Daichi

A large pool is an optional memory area for memory allocation, which is larger than the memory allocation of a shared pool.

Large pools can provide large memory allocations for the following:

For shared servers and oracle XA interfaces (using UGA where transactions interact with multiple databases)

Message buffer used to execute statements in parallel

Used for the recovery Manager (RMAN) I ramp O slaves buffer

By allocating session memory in a large pool, the database can avoid memory fragmentation that occurs when allocating memory from a shared pool. When a database allocates large pools of memory to a session, memory cannot be freed unless the session actively frees the memory. Instead, the database manages memory in a shared pool as LRU, which means that part of the memory can be aged.

The following is the structure diagram of Dachi:

A large pool differs from reserved space in a shared pool, which uses the same LRU list as other memory allocated in the shared pool. The big pool does not have a LRU list. Memory block allocation cannot be released until they are used up. Once the memory block is released, it can be used by other processes.

Java Pool

The Java pool is the area of memory used to store all session-specific Java code and data in the Java virtual machine (JVM). This memory includes Java objects that are migrated to the Java session space at the end of the call.

For dedicated server connections, the Java pool contains the shared portion of each Java class, including methods and read-only memory, such as code vectors, rather than the Java state of each session. For shared servers, the pool contains the shared portion of each class and some UGA for each session state. Each UGA grows and shrinks as necessary, but the total size of the UGA must be appropriate to the Java pool space.

Java Pool Advisor statistics provide information about the repository cache memory used for Java and predict how changes in the size of the Java pool affect the parsing rate. When statistics_level is set to typical or higher, Java Pool Advisor is turned on internally, and this information is reset when advisor is closed.

Flow pool

The stream pool stores cache queue information and provides memory for oracle stream capture processes and applications. Stream pools are used only by oracle streams.

Unless it is specifically configured, the size of the flow pool starts at 0. The pool size grows dynamically as required by the oracle flow.

Mixed SGA region

A fixed SGA is an internal administrative area.

For example, a fixed SGA zone includes:

General information about databases and instances that background processes need to access

Information passed between processes, such as information about locks

The fixed SGA size is set by the oracle database and cannot be changed manually. Fixed SGA size can be changed by freeing up space.

Overview of software code area

The software code area is a portion of memory that stores code that is running or can be run. The oracle database code is stored in a software area that is usually more exclusive and protected than the location of the user program.

The software code area is usually static in size and only changes in size when the software is updated or reinstalled. The size required for these areas varies from operating system to operating system.

The software code area is read-only and can be installed shared or non-shared. Some database tools and utilities, such as oracle forms and SQL*PLUS, can be installed with sharing. Where possible, share the database code so that all users can access it without having multiple copies in memory, resulting in overall improvement in primary memory and performance. If you run a database on the same computer, multiple instances of the database can use the same database code area.

The option to install software sharing is not available for all operating systems, for example, on personal computers running the windows operating system.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.