Oracle programming Art Study Notes (10)-SGA, PGA and UGA

Oracle has three main memory structures:
The System Global Area (SGA) is a large shared memory segment. Almost all Oracle processes need to access a certain point in this zone.
The Process Global Area (PGA) is the memory dedicated to a Process or thread. Other processes/Threads cannot access it.
User Global Area (UGA): This memory Area is associated with a specific session to save the session status. If a Shared Server is used, UGA will be allocated in SGA; if a dedicated server is used, UGA will be in PGA (I .e. process memory zone.
There are two ways to manage memory in Oracle. One is automatic management, and the other is manual management.
Automatic Management has two levels,
At the first level, you must set two parameters (sga_target and pga_aggregate_target) to determine the size of SGA and PGA;
For the second level (after 11 GB), you only need to set the memory_target parameter. Oracle automatically determines the size of all memory zones.
Memory_target parameter->: http://www.bkjia.com/database/201110/108939.html
PGA
PGA memory management is controlled by the database initialization parameter WORKAREA_SIZE_POLICY and can be modified at the session level. In Oracle9i Release 2 and later versions, this initialization parameter defaults to AUTO, indicating automatic PGA memory management.

If the WORKAREA_SIZE_POLICY parameter is MANUAL, manual pga memory management is started. The following parameters have the greatest impact on the PGA size:
• SORT_AREA_SIZE: the total amount of RAM used to sort information before information is swapped out to the disk.
• SORT_AREA_RETAINED_SIZE: total memory size used to save sorted data after sorting is completed. This part of memory is generally allocated from UGA.
That is to say, if SORT_AREA_SIZE is 512 KB and SORT_AREA_RETAINED_SIZE is 256 KB, the server process uses 512 KB of memory to sort data during initial query processing. When sorting is completed, the sorting area will "contract" to 256 KB, which will write the uneconomical sorted data in the 256 KB memory into the temporary tablespace.
• HASH_AREA_SIZE: memory used by server processes to store the hash list in memory. The hash structure is used for hashed joins. These structures are usually used when a large set is joined with another set. A smaller portion of the Two sets will be hashed to the memory, and parts that cannot be stored in the hash area will be stored in the temporary tablespace through the join key.

If WORKAREA_SIZE_POLICY is set to AUTO and PGA_AGGREGATE_TARGET has a non-0 value, automatic PGA memory management is used.

For OLTP applications, automatic PGA memory management is preferred. Manual memory management applies to large batch processing jobs (they run during special time periods, which are the only activity in the database ).

SGA
You can use
Select pool, name, bytes from v $ sgastat order by pool, name;
Statement to understand the SGA details.
The main SGA components include:
1) fixed SGA (fixed SGA)
The size of the fixed SGA cannot be controlled, but the fixed SGA is usually very small. You can think of this zone as the "self-starting" zone in SGA. Oracle should use this zone internally to find other zones in SGA.

2) redo buffer)
Online redo logs must be temporarily cached in the redo buffer before being written to the disk.
Since memory-to-memory transmission is much faster than memory-to-disk transmission, the use of redo log buffer can speed up database operations.

3) block buffer cache)
After Oracle reads database blocks from the disk and writes the database blocks to the disk, these database blocks are stored in the block buffer cache. This is a very important area in SGA.
The cached blocks of each segment in SGA are placed in three locations:
• Default pool: All block segments are usually cached in this pool.
• Keep pool: Normally, segments with frequent access will be placed in this candidate buffer pool.
• Recycle pool: By convention, large random access segments can be placed in this candidate buffer pool.
In fact, these three pools manage blocks in the same way as the large body; there is no fundamental difference between the blocks aging or caching algorithms. The goal is to enable DBAs to aggregate segments to the hot and warm zones) and do not care to cache ).
Theoretically, the objects in the default pool should be hot enough (that is to say, enough) to stay in the cache. The cache keeps them in memory because they are very popular blocks. Some sections may be quite popular, but not too hot; these blocks serve as warm blocks. To keep the blocks of these warm segments cached, you can allocate these segments to the retention pool, in an attempt to keep the warm blocks in the buffer cache for a longer time. Allocate the "unsuitable cache" segment to the recycle pool, so that the recycle pool is quite small, so that the block can quickly enter the cache and leave the cache (reducing management overhead ).
The default pool, retention pool, and recycle pool only cache blocks of the default size (the block size used when the database was initially created ).
If you want to use a non-default block size in the database, you need to configure a buffer pool to save these blocks.
For example, the default block size of the current database is 8 K.
Create tablespace ts_16k datafile 'C: \ ts_16k.dbf' size 2 m blocksize 16 k;
An attempt to create a 16 K block size tablespace will get an error of "ORA-29339: tablespace block size 16384 does not match the configured block size. This indicates that a 16 K block size block buffer must be created.
You can set the DB_16K_CACHE_SIZE parameter and restart the database.
You can also narrow down another SGA component to free up space in the existing SGA to create a 16 KB cache.
Alternatively, if the SGA_MAX_SIZE parameter is greater than the current SGA size, you can directly allocate a 16 KB cache.
Then, the tablespace of the 16 K block size can be created successfully.

4) shared pool)
The sharing pool is where Oracle caches "program" data, system parameters, data dictionary cache, resolved query plans, and so on.
The memory in the shared pool is managed based on the LRU (least recently used) principle, similar to the block buffer cache.

Oracle stores parsed and compiled SQL statements together with other content in the shared pool.
This requires developers to use bind variable in most cases ). For example:
Select * from emp where empno =: empno;
This query is compiled only once, and then the query plan is stored in a shared pool (database cache) for future retrieval and reuse of the query plan.
If you really want Oracle to run slowly or even stop, you can do it without using the Bind Variable:
Select * from emp where empno = 123;
If the direct quantity (constant) is used in the query, each query will be a brand new query. It seems that the database has never seen it before and must parse and limit the query (name resolution) security check and optimization. To put it simply, each different statement you execute must be compiled during execution.
Compared with reusing a parsed query plan (called soft parsing or soft parse), parsing statements containing hard-coded variables (called hard parsing and hard parse) takes longer, in addition, more resources are required.
The more important factor is the latching mechanism used by the database cache. During hard parsing of a query, the database will occupy a low-level serial device for a longer period of time, which is called a latch ), these latches protect the data structure in the Oracle shared memory from being modified by two processes at the same time (otherwise, Oracle will eventually get the corrupted data structure), and if someone is modifying the data structure, other users are not allowed to read the data again. The longer and more frequent the data structure is to be latched, the more processes wait in queue for the latch, and the longer the waiting queue. You may start to monopolize precious resources. As long as one application in the database is not performing well, it will seriously affect the performance of all other applications. If only one small application does not use the Bind Variable, even if other applications are well designed, resolved SQL statements can be properly placed in the shared pool for reuse, however, because of the existence of this small application, stored SQL statements will be deleted from the shared pool in a period of time. This makes these well-designed applications hard to parse SQL again.
Not binding variables is a major cause of performance problems and an important factor hindering scalability.

Another influence of not binding variables is security, which is vulnerable to "SQL injection" attacks.

A new parameter CURSOR_SHARING = FORCE is added to Oracle 8.1.6. If you want to, this feature will implement an auto-binder ). If a query is written as SELECT * from empwhere empno = 1234, the auto-binding server quietly changes it to SELECT * from emp where empno =: x. This can indeed substantially reduce the number of hard resolutions and reduce the latch latencies discussed earlier-but it may have some side effects. In the long run, we should try to bind variables and use constants only when necessary. This is the correct method.

5) large pool)
A large pool is used to allocate large blocks of memory. The shared pool does not process such large blocks of memory.
The Shared Pool manages memory based on LRU, which is suitable for caching and reusing data. A large block of memory is allocated to obtain a block of memory and then use it. Therefore, there is no need to cache this memory.
The memory allocated in the large pool is managed on the stack, which is similar to the memory managed by the C language through malloc () and free. Once a piece of memory is released, it can be used by other processes. In the shared pool, there is no concept of releasing memory blocks. It only allocates memory and then uses it. After a while, if you need to reuse that memory, Oracle will aging your memory block.

Large pools are used in the following scenarios:
• Shared Server connection, used to allocate a UGA zone in SGA.
• Parallel execution of statements allows allocation of message buffers between processes. These buffers are used to coordinate parallel query servers.
• Backup, used in some cases for the RMAN disk I/O buffer.

6) Java pool)
It is used to support running Java in the database.

7) stream pool)
Used to support streaming (a database sharing/replication tool ).

The parameters that have the greatest impact on the overall size of SGA are as follows:
• JAVA_POOL_SIZE: controls the size of the Java pool.
• SHARED_POOL_SIZE: controls the size of the Shared Pool to some extent.
• LARGE_POOL_SIZE: controls the size of a large pool.
• DB _ * _ CACHE_SIZE: controls the cache size of each available buffer.
• LOG_BUFFER: controls the size of the redo buffer to some extent.
• SGA_TARGET: used for automatic SGA memory management in Oracle 10 GB and later versions.
• SGA_MAX_SIZE: used to control the maximum size that SGA can achieve when the database is started and running.
You can query the v $ sgainfo view to check the size of each SGA component and whether it can be adjusted (RESIZEABLE ).

If you use manual SGA memory management, you need to set the above parameters;
If Automatic SGA memory management is adopted, you only need to set the SGA_TARGET parameter to the desired SGA size. The database instance allocates and revokes (releases) Each SGA component at runtime according to the workload conditions.

When the database is shut down, the database uses some header underlined parameters (_ DB_CACHE_SIZE, _ JAVA_POOL_SIZE, _ LARGE_POOL_SIZE, and _ SHARED_POOL_SIZE) for the size of each SGA component) record to the storage parameter file (SPFILE), and use these values at startup to set the default size of each zone.
In addition, if you know the minimum value of a zone in the above four zones, you can set this parameter in addition to SGA_TARGET. The instance uses your settings as the lower bound (that is, the minimum possible size of this zone ).

After 11 GB, Oracle provides automatic memory management. You only need to set the memory_target parameter. Oracle automatically determines the size of all memory zones. In this case, the SGA_TARGET and PGA_AGGREGATE_TARGET parameters are used to set the lower bound.
Oracle will store the optimal settings of SGA and PGA to the two parameters _ sga_target and _ pga_aggregate_target of spfile.

Whether using automatic memory management or manual memory management, the memory of each pool is allocated in a unit called granule.
You can query V $ SGA_DYNAMIC_COMPONENTS to view the particle size of each pool. For example:
Select component, granule_size from v $ sga_dynamic_components;