Oracle10g Memory Structure

1.SGA

1.1 query the SGA-related view

1.1.1 GV $ sga_dynamic_components

SQL> select * From GV $ sga_dynamic_components;

PS:

1) The allocation unit of SGA is Granule. Components in SGA are composed of granule, and granule information is provided by Granule entry.ManagementEach entry maintains the type and status of granule. When SGA <= m, granule is 4 m, and when SGA> M, granule is 16 m, in special cases, 32-bit Windows NT is 8 M. From the View query results, we can see that the current granule is 16 m.

2) the main components of SGA are buffer cache, Shared Pool, Java pool, and large pool.

 

1.1.2 v $ sga_current_resize_ops

SQL> DESC v $ sga_current_resize_ops;
Name type nullable
-------------
Component varchar2 (64) y
Oper_type varchar2 (13) y
Oper_mode varchar2 (9) y
Parameter varchar2 (80) y
Initial_size number y
Target_size number y
Current_size number y
Start_time date y
Last_update_time date y

PS:

1)OracleA scoreboard is used to manage the information of each component and its granules in SGA. Scoreboard records the number of granule contained by the component, the modification operation being performed on the component, the target size of the modification, and the modification process, operation Start Time

2) when the number of granule of one component is modified, Oracle immediately records this operation in scoreboard, target size, operation start time. After the modification is complete, Oracle uses target size to replace current size, and clear the target size.

3) Oracle records the last 100 modification operations, that is, scoreboard is a type of buffer used cyclically.

1.1.3 v $ sgainfo

SQL> select * from V $ sgainfo;

Name bytes resizeable
-------------------
Fixed SGA size 2086592 No
Redo buffers 14688256 No
Buffer cache size 3103784960 Yes
Shared pool size 5335154688 Yes
Large pool size 67108864 Yes
Java Pool size 33554432 Yes
Streams pool size 33554432 Yes
Granule size 16777216 No
Maximum SGA size 8589934592 No
Startup overhead in Shared Pool 301989888 No
Free SGA memory available 0

PS:

1) Not all components in SGA can be dynamically modified, as shown in the preceding query results.

1.1.4 v $ sgastat

SQL> select * from V $ sgastat;
Pool name bytes
-----------------
Fixed_sga 2086592
Buffer_cached 3103784960
Log_buffer 14688256
Shared Pool virtual circuits 3867480
Shared Pool transaction 4026704
Shared Pool table definiti 5896
Shared Pool KGL lock hash table state 203280
Shared Pool kwqmncal: allocate buffer 4048
Shared Pool qmn tasks 4128
Shared Pool cursor stats 40715832
Shared Pool enqueue 3096464
Shared Pool x $ skgxpia 2680
Shared Pool sessions 8778128
Shared Pool row cache 7506880
Shared Pool enqueue_hash 160560
Shared Pool processes 12000
Shared Pool db_block_hash_buckets 23592960
Shared Pool x $ ksfqp anchor 80
Shared Pool ASM rollback operations 2592
Shared Pool x $ ksvit table 512
........
Shared Pool kso req alloc 4136
Shared Pool kwqmncini-SLV 240
Shared Pool temporary tables state ob 389736
Shared Pool enqueue_hash_chain_latche 1280
Shared Pool ktpr hist TB 2808.
Large pool free memory 66032864
Large pool px msg pool 1076000
Java pool free memory 33554432
Streams pool free memory 33554432

690 rows selected

You can use the Thomas Kyte method:

SQL> compute sum of bytes on pool
SQL> Break on pool skip 1
SQL> select pool, name, bytes from V $ sgastat order by pool, name;

Pool name bytes
-----------------
Java pool free memory 4194304
************----
Sum 4194304

Large pool px msg pool 206208
Free memory 3988096
************----
Sum 4194304

Shared Pool 1: kngisga 16044
X $ ksmfs table 12
X $ rule_set 16804

......
Xscalc 3528
Xslongops 4040
Xsoqmehift 4168
Xsoqojhift 3272
Xsoqophift 4168
Xsoqsehift 2376
Xssinfo 5532
************----
Sum 113250900

Buffer_cached 46137344
Fixed_sga 1260696
Log_buffer 2932736
************----
Sum 50330776

1.2 SGA features

1) in Unix systems, SGA represents an independent physical entity in the operating system, that is, an independent shared memory segment, you can use IPCS-M | grep Ora to find the shared memory segment.

[Root @ seagull ~] # IPCS-M | grep ora
0 × 5c7384ec 14516224 oracle10 640 169869312 22
0 × 7256e224 22544385 oracle9 640 117440512 35

There may be SGA with no corresponding background process. This indicates that the crash of Oracle is abnormal, but the shared memory segment is not released. In this case, run the ipcrm command to clear the shared memory or restart the machine.

2. in windows, there is only one oracle.exepath, and the private memory allocated to oracle.exe by the system, including SGA and all other Oracle memory (PGA, UGA), so you cannot see the SGA separately.

Composition of 1.3 SGA

Sga_max_size, sga_target

1) Java pool:DatabaseMemory allocated for running JVM, which can be dynamically adjusted in 10 GB

Java_pool_size

2) large pool: Used in sharedServerIn session memory mode, message buffer in parallel execution, and disk I/O buffer in RMAN. This part can be dynamically adjusted in 10 GB and 9ir2.

Large_pool_size

3) Shared Pool: stores shared cursors, stored procedures, dictionaries, etc., which can be dynamically adjusted in 10 GB and 9i

Shared_pool_size

4) streams pool: a dedicated Oracle stream function. If this function is used but the streams pool is not configured, Oracle uses the 10% shared pool as the streams memory.

Streams_pool_size

5) "null" memory: block buffers + fixed SGA + redo Buffer

DB _ * _ cache_size

Log_buffer

Assm in 1.4 10g

In 10g, some SGA components can be automatically adjusted by Oracle, see my previous post: http://blog.chinaunix.net/u1/50863/showart_414883.html

1.5 fixed_sga

1) official explanation of fixed SGA 1: The fixed SGA is a component of the SGA that varies in size from platform. to platform. and from release to release. it is "compiled" into the Oracle binary itself at installation time (hence the name "fixed "). the fixed SGA contains a set of variables that point to the other components of the SGA, and variables that contain the values of various parameters. the size of the fixed SGA is something with which we have no control over, and it is generally very small.
Think of this area as a "Bootstrap" section of the SGA-something Oracle uses internally to find the other bits and pieces of the SGA.

2) fixed SGA official explanation 2: Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA.

1.6 redo Buffer

1) Stores redo entries, and lgwr writes redo entries to online redo log file. The write time is as follows:

A. Every 3 S

B. Commit

C. Log Switch

D.1m or 1/3 full

2) Redo entries contains information about insert, update, delete, create, alter, drop, and other operations, which can be used in the database when necessary.RestoreThey are continuous and serialized in the buffer.

3) In most cases, log_buffer does not need to be large and can be larger than 2 MB:

A. ConcurrencyTransactionsMany. The large log_buffer can ensure that lgwr can write logs to other sessions

B. There are many long-running sessions

3) The redo buffer is specified by the log_buffer parameter. Generally, it must be greater than 128 K and K * CPUs. However, the minimum value is related to the operating system. To know the minimum value, perform the following operations:

SQL> alter system set log_buffer = 1 Scope = spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
Oracle instance shut down.
SQL> startup
Oracle instance started.

Total system global area 171966464 bytes
Fixed size 1260720 bytes
Variable Size 62915408 bytes
Database buffers 104857600 bytes
Redo buffers 2932736 bytes
Database mounted.
Database opened.
SQL> show parameter log_buffer

Name type value

----------

Log_buffer integer 2887168

1.7 block buffer cache

1.7.1 Composition

1) default pool: Default pool

2) Keep pool: blocks with high usage can be cached here to improve performance.

3) recycle pool: blocks with low usage can be cached here

4) db_nk_cache_size is added from 9i to support non-default block size table caching.

All the internal management methods of these buffer caches are the same.

1.7.2 managing blocks in the buffer cache

1) Dirty lists: needed to be written by the dbwr to the disk

2) nodirty lists: This list uses the touch count mechanism to calculate the buffer Access frequency (theory of hotspot block problems ).Basic, X $ BH view tch field), uses the LRU algorithm to control the use of buffer:

2.1) This list includes free buffers (unused), pinned buffers (accessed), and dirty buffers (dirty blocks not moved to the dirty list)

2.2) when the Oracle process accesses a buffer, the buffer will be moved to the MRU (most recently used) end of the list, with more buffers moving to the MRU end, those dirty buffers will be directed to LRU.

2.3) when a process accesses data, it first searches for the data in the database buffer cache (using the hash algorithm). If the data is found, it is called cache hit. If the data is not found, it is called cache miss, in this case, the Oracle process copies the block from the datafile to the buffer.

2.4) when you need to copy the block to the buffer, the process must first find a free buffer. The method is as follows:

A. If the dirty buffer is found on the lru end of the nondirty list, move it to the dirty list and continue to find the free buffer.

B. If the free buffer is found, copy the block to the buffer and move it to the MRU end.

C. If threshold (that is, the maximum number of records not found) is not found, stop searching and notify the dbwr process to write some dirty buffers to the disk.

2.5) if it is full table scan, the buffer will not move to the LRU end, but the MRU end, unless the cache statement is specified during table creation.

1.7.3 multiple blocksize

Omitted

1.8 Shared Pool

1.8.1 Overview

1) includes library cache, Dictionary cache, buffers for parallel execution messages, and control structures.

2) The shared pool size is specified by the shared_pool_size parameter.

1.8.2 library Cache

1) includes sharedsqlareas, private SQL areas (in Shared Server mode), PL/SQL, package, control structure (such as locks and library cache handles)

2) The execution of an SQL statement involves two areas. The shared SQL area can be shared by multiple sessions, and the private SQL area is exclusive to each SQL session.

3) shared SQL area:

3.1) includes the SQL parse tree and execution plan.

3.2) when a new SQL statement is parsed, it is stored in the shared SQL area. The required memory size depends on the SQL complexity. If no remaining memory is available, oracle uses the LRU algorithm to free up the remainder.Space

4) Private SQL area:

4.1) in the Shared Server mode, private SQL area is in the libary cache of SGA, and private SQL area is in the PGA in the private server mode.

4.2) Private SQL area mainly includes bind information and runtime memory structure. Each session that sends an SQL statement has its own private SQL area, however, sessions that issue the same SQL statement share one shared SQL area.

4.3) Private SQL area can be divided into two parts, and the life time is different

A. Persistent area: Contains bind information, which is released when cursor is close.

B. runtime area: it is released when execution is terminated. It is created in the first step when the request is executed. For insert, update, and delete commands, the zone is released after the statement is executed. For query operations, the zone is released only after all rows are extracted or the query is canceled.

4.4) Relationship between cursor and SQL area:

A. cursor Is the handle pointing to the private SQL area. The application can open cursor, at this time, cursor is a resource with a name (for example, the cursor name defined in PL/SQL). Oracle also opens some cursor implicitly, such as recursive cursor.

B. the user process is responsible for managing the private SQL area, and the allocation and deallocation of the private SQL area are largely related to the application tool used. The maximum cursors that a user process can open are specified by the open_cursors parameter.

C. the private SQL area is not completely released until the cursor is disabled. If the statment execution is complete but the cursor is not closed, only the run time area is released and the persistent area is still in the waiting state, therefore, developers should promptly close cursor.

1.8.3 dictionary Cache
1) dictionary is about the Oracle structure and all user-defined information. This information needs to be queried during SQL Execution. dictionary is critical to the database.

2) Oracle has two areas for storing Dictionary data
A. Data Dictionary cache (also called row cache). Here we will explain why it can be called row cache, because row cache stores data in row instead of using buffer (Block ).
B. The other part is in the library cache.

1.8.4 memory allocation and release in Shared Pool
The memory space Calculation Method in the Shared Pool uses the LRU algorithm. When one SQL statement is executed in a session, Oracle performs the following steps to allocate memory in the shared poll:

1) First, check whether one shared SQL area contains the same SQL. If so, use the shared SQL area. Otherwise, oracle allocates a new shared SQL area (uses the LRU algorithm to find free space) to the session.
2) then oracle allocates a private SQL area to the session. The location of the private SQL area depends on whether the Shared Server mode is used. If the private SQL area is used in the Shared Pool, otherwise it is in the PGA.

In addition, Oracle Releases shared SQL areas in the shared pool in the following cases:
1) modifying the analyze Statement of statistics will result in the shared SQL areas of the SQL statement involved in the related object being flused from the Shared Pool
2) The object is changed. For example, if a field is added, the related shared SQL area will also be flused. 3) if the database name is changed, all shared pool information will be flused. 4) alter system flush shared_pool

1.8.5 understanding of Shared Pool in thomos Kyte

1) The size of the Shared Pool is critical. poor use of the shared pool has a significant impact on performance.

2) The shared pool is composed of lots of small chunks (<= 4 K, but not hard limit) of memory. The small chunks are used to reduce fragments, timely is a relatively large package. Due to the use of paging mechanisms, only the executed code will be loaded into some small chunks.

3) The allocation algorithm of Shared Pool uses LRU, but dbms_shared_pool can be used to change the allocation algorithm so that some objects are pin in the Shared Pool. For example, you can use this package to facilitate some frequently used packages or procedure to be installed with shared_pool at database startup.

4) if bind variables is not used, the performance of the shared pool will be greatly reduced, or even the database will be down because:. duplicate parse will consume cpu B. A large amount of resources are used to manage the SQL statements that cannot be reused but occupy space in the Shared Pool. Therefore, we should try to use bind to reuse SQL statements. In most systems, there should be only a few hundred unique queries. Even for super-large systems, I found that unique quries is only in bytes-20000.

5) V $ sgastat and shared_pool_size
A. You can use select sum (bytes) from V $ sgastat where pool = 'shared _ pool 'to query the actual size of the current shared pool.
B. before 10 Gb, the size detected by this method is larger than the shared_pool_size parameter, because in the Shared Pool, besides storing the share pool memory specified by the shared_pool_size parameter, parameters like control_files also consume 264 bytes in the shared pool.
C. At 10 Gb, the two correspond, that is, the shared_pool_size parameter investigated with V $ sgastat should be the same (of course, shared_pool_size does not work if asmm is used)

1.9 large pool

1.9.1 main functions of large pool

1) As session memory in Shared Server Mode

2) Parallel Execution of statements, to allow for the allocation of interprocesses message buffers, which are used to coordinate the parallel query servers

3) backup for RMAN disk I/O buffers in some cases

1.9.2 features of large pool

1) "large" indicates that the allocation unit is much larger than the Shared Pool, mainly for specific tasks.

2) The allocation algorithm uses the heap management mode. Unlike LRU, the allocation algorithm is released when it is used up. Its purpose is to complete a specific task with memory, rather than for cache and reuse as the shared pool.

3) large pool is usually proportional to dbwr_io_slaves and parallel_max_servers parameters.

Java pool 1.10

1.10.1 main functions of Java pool

1) the Java code Code and related data used for caching execution in the database

1.10.2 Java pool features

1) In dedicated server mode, Java pool includes:

A. Only Java class cache is included. Each class has 4 K or 8 K space.

2) in Shared Server mode, Java pool includes: a. The shared part of each Java class

B. Some status information in the UGA of each session

1.11 streams pool

1.11.1 main functions of the streams pool

1) supports the database's streams function, which is a new 10 Gb Function

2) The streams pool (or up to 10 percent of the shared pool if no streams pool is configured) is Used To Buffer Queue messages used by the streams process as it is moving/copying data from one database to another.

2. PGA

2.1 PGA features

1) process global area (PGA) can be understood as process global area or program global area. its memory segment is in the Process private memory area rather than in the shared memory area ). it is a global zone, which means that it contains all the global variables and data structures that code may enter, but it is not shared by all processes. each ORACLE Server process contains its own PGA, which only contains specific information about the process. the structure in PGA does not need to be protected by latches, because other processes cannot access it.
2) Many people do not know the difference between PGA and Uga. In fact, the difference between the two is similar to that between a process and a session. although process and session are generally one-to-one relationships, they are actually more complicated than this. one obvious case is that MTS is configured, and there are usually more sessions than processes. in this configuration, each process has a PGA, and each session has a Uga. the information contained in PGA is not associated with the session, and the information contained in UGA is based on the specific session.
In addition, the location of UGA is determined by the session connection method:
A. if the session is connected to the database through the shared server, there is no doubt that the UGA must be accessible by all processes. Therefore, the UGA is allocated from the SGA. Further, if large pool is set in SGA, UGA is allocated from large pool; otherwise, if large pool is not set, UGA can only be allocated from shared pool.
B. If the session is connected to the database through the dedicated server, the UGA is allocated from the PGA of the process.

3) PGA contains two major areas: Fixed PGA and variable PGA, or PGA heap. the role of fixed PGA is similar to that of fixed SGA. They all contain atomic variables (which cannot be divided), small data structures, and pointers to variable PGA. variable PGA is a heap ). its chunks can be viewed from fixed table x $ ksmpp. PGA heap contains some permanent memory for the fixed table. If the session uses a dedicated connection method, the UGA sub-heap is also contained.
2.2 content of PGA
2.1.1 private SQL Area
1) persistent area: This area stores the cursor information required for multiple executions of the same SQL statement, such as binding variable information and data type conversion information. This part of memory will be released only when the cursor is closed.
2) Run Time area (in fact, the SQL work area in UGA): It stores some information used when the SQL statement is run. The first step in SQL statement processing is to create a runtime region. For DML (insert, update, delete) Statements, the region is released after the SQL statement is executed; for a query statement (select), it is released after all data rows are obtained and passed to the user, or after the query is canceled.
2.1.2 session memory
1) Session memory contains the variable information of sessoin and other session-related information. In Shared Server mode, this structure is shared.

2.3 SQL work area (Run time Area)
1) For sort, hash-join, bitmap-merge, create-bitmap operations

2.4 related views
1) V $ sysstat, V $ sesstat, V $ pgastat, V $ SQL _workarea, V $ SQL _workarea_active
2) V $ process's pga_used_mem, pga_allcated_mem, pga_max_mem Field

2.5 related articles

There are some incisive analysis articles about PGA on the Internet. The attachment is as follows:

3. software code Areas

1) The region used to store Oracle code. The size is usually fixed and will only change when Oracle updated or reinstall is used.
2) this region is subject to the memory area of the user program and is strictly protected.
3) the region can be shared or not shared, and some platforms cannot be shared, such as Windows
4) Oracle tries its best to share the code so that multiple user processes can share the code to save memory.