Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of Oracle memory structure SGA, PGA, UGA. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. System global area (System Global Area, SGA):

SGA is a set of shared memory structures that are shared by all services and background processes. When the database instance starts, the global local memory of the system is automatically allocated. SGA memory is reclaimed when the database instance is shut down. SGA is not only the largest area of memory, but also an important factor affecting the performance of the database.

To inquire about the SGA area:

SQL > show SGATotal System Global Area 3290345472 bytesFixed Size 2217832 bytesVariable Size 1795164312 bytesDatabase Buffers 1476395008 bytesRedo Buffers 16568320 bytesSQL > select * from vascal VALUE name VALUE-- Fixed Size 2217832 Variable Size 1795164312 Database Buffers 1476395008 Redo Buffers 16568320

Fixed Size represents a fixed area that stores information about each component of the SGA. The size cannot be changed.

Variable Size represents a variable area, such as shared pool, java pool, large pool, and so on.

Database Buffers represents the database cache buffer.

Redo Buffers represents the log buffer.

Fixed SGA (Fixed SGA)

Fixed SGA: as the name implies, it is an immutable memory area that points to other parts of the SGA, through which Oracle finds other areas in the SGA, which can be simply understood as a section of memory used for management.

Block buffer (Database buffer cache)

Data cache (Database Buffer Cache): used to store data blocks read from a data file, determined by the initialization parameter DB_CACHE_SIZE.

How it works and how it works is LRU (the least recently used Least Recently Used). When querying, Oracle will first put the data read from the disk into memory for all users to share, and when querying the relevant data later, you do not have to read the disk again. When inserting and updating, Oracle caches data in the area first, and then writes it in bulk to the hard disk. Through the block buffer, Oracle can improve the performance of disk Imax O through memory cache (Note: the speed of disk Imax O is millimeter, while the rate of memory Imax O is nanosecond).

Data cache blocks consist of many cache blocks of the same size as OS blocks. These cache blocks are divided into three main categories.

Dirty cache block (Dirty buffers): a cache block that is modified when saved in a dirty cache block. That is, when a SQL statement modifies the data in a cache block, the cache block is marked as a dirty cache block. Finally, the dirty cache block is written to the data file of the hard disk by the DBWn process and retained permanently.

Hit cache block (Pinned buffers): the cache block that is recently being accessed is saved in the cache block. It is always kept in the data cache and will not be written to the data file.

Idle cache block (Free buffers): there is no data in the cache block, waiting to be written. After oracle reads the data from the data file, it looks for free cache blocks to write to.

Oracle manages cache blocks through 2 lists (DIRTY, LRU)

1. Save dirty cache blocks in the DIRTY list that have been modified but not written to the data file.

2. Save all cache blocks in the LRU list (dirty cache blocks, free cache blocks, hit cache blocks that have not been moved to the DIRTY list). When a cache block is accessed, the cache block is moved to the head of the LRU list, and the other cache blocks are moved to the end of the LRU list. The cache block placed at the end is removed from the LRU list first.

How data caching works is as follows:

A, ORACLE before copying the data block in the data file to the data cache, find the free cache block in the data cache to accommodate the data block. Oracle will search from the end of the LRU list until the required free cache block is found.

B. if the dirty cache block is searched first, move the dirty cache block to the DIRTY list, and then continue the search. If a free cache block is searched, the data block is written and then moved to the head of the DIRTY list.

C. if enough free cache blocks can be searched, all data blocks are written to the corresponding free cache blocks. The search write process ends.

D. If not enough free cache blocks are found, ORACLE stops the search first, but activates the DBWn process and starts writing dirty cache blocks in the DIRTY list to the data file.

Dirty cache blocks that have been written to the data file will become free cache blocks and will be placed in the LRU list. After performing this work, restart the search until enough free cache blocks are found.

As you can see here, if your high-speed buffer is very small and keep writing, it will cause a lot of overhead.

Block buffers can be configured with 1, 2 or 3 buffer pools, with only one by default

Default Default pool: all data is cached here by default, unless you specify Store (buffer_pool keep) or Store (buffer_pool recycle) when creating the table. Use the LRU algorithm to manage.

Keep pool: cache data that needs to be reused many times and keep it in memory for a long time. The default value is 0.

Recycling pool (Recycle pool): used to cache data that is rarely reused and released after use. The default value is 0.

It turns out that there is only one default pool, where all the data is cached. This creates a problem: a large amount of data that is rarely reused will "squeeze" the data that needs to be reused out of the buffer, resulting in an increase in disk I and O and a decrease in running speed. Later, the retention pool and the recycling pool are separated to cache data separately according to whether they are often reused. These three parts of the memory pool need to be manually sized and are not shared. For example, if the retention pool is full and there is a large amount of free memory in the recovery pool, the memory of the recovery pool is not allocated to the retention pool, which is generally regarded as a very fine low-level tuning device. Use should only be considered after most of all other tuning methods have been used.

Before 9i, the size of the data buffer was determined by DB_BLOCK_BUFFER, and in later versions, it was determined by the parameters DB_CACHE_SIZE and DB_nK_CACHE_SIZE. Different tablespaces can use different block sizes. Add the parameter BLOCKSIZE to the creation tablespace to specify the size of the tablespace data block. If 2k is specified, the corresponding buffer size is the value of the DB_2K_CACHE_SIZE parameter, if 4k is specified, the corresponding buffer size is the value of the DB_4K_CACHE_SIZE parameter, and so on. If BLOCKSIZE is not specified, the default is the value of the parameter DB_BLOCK_SIZE, and the corresponding buffer size is the value of DB_CACHE_SIZE

Redo log buffer (Redo log buffer)

It is used to store log entries, which record changes to the data. When this area runs out, the background process LGWR writes log entries to the online log file on disk. Its size is determined by the initialization parameter log_buffer. By the same token, the log buffer should be slightly larger, especially when there are long-running transactions, which can greatly reduce the number of Ibind O.

The data is cached here before it is written to the redo log file and is triggered in the following cases:

Every 3 seconds

When the cache reaches 3 full at 1MB or 1 beat

When the user submits

Before the data of the buffer is written to disk

Shared Pool (Shared pool)

Used to store SQL statements, PL/SQL code, data dictionaries, resource locks, and other control information. Its size is controlled by the initialization parameter SHARED_POOL_SIZE. It contains the following buffers:

1. Data dictionary cache (data dictionary cache) is used to store frequently used data dictionary information. For example (table definition, user name, password, permissions, database structure, etc.). The cache is often accessed during Oracle running to parse SQL statements to determine whether the object being operated on exists, whether it has permissions, and so on. If it is not in the data dictionary cache, the server process reads it into the data dictionary cache from the data file that holds the data dictionary information. Records are stored in the data dictionary cache (like databases in memory), while block information is stored in other caches.

2. Library buffer (Library Cache): the purpose of library caching is to hold recently parsed SQL statements, PL/SQL procedures, and packages. In this way, before executing a SQL statement, a PL/SQL procedure, and a package, Oracle first searches in the "library cache". If it finds that they have been parsed, it uses the parsed results and execution plan in the "library cache" to execute them without having to re-parse them, significantly improving execution speed and efficiency.

ORACLE decomposes each SQL statement into two parts that can be shared and not shared.

A) shared SQL area: stores recently executed SQL statements, parsed syntax trees, and optimized execution plans. In this way, the same SQL statement is executed later, and the cache information in the shared SQL area is directly utilized. There is no need to repeat syntax parsing. When Oracle executes a new SQL statement, it allocates space in the shared SQL area, depending on the complexity of the SQL statement. If there is no free space in the shared SQL area, the LRU algorithm is used to free the occupied space.

B) Private SQL zone (when in shared mode): stores private information related to each session or user when the SQL statement is executed. Other sessions will not use this information even if they execute the same SQL statement. For example (bind variables, environment, and session parameters).

3. Result cache: the result cache includes SQL query result cache and PL/SQL function result cache. This cache is used to store the results of SQL queries or PL/SQL functions to speed up their future execution.

4. Locks and other control structures: store the information needed for the internal operation of the ORACLE routine. For example (various locks, latches, register values).

Daichi (Large pool)

The size of a large pool is determined by the initialization parameter LARGE_POOL_SIZE. You can use the ALTER SYSTEM statement to dynamically change the size of a large pool, which is optional. DBA can decide whether to create a large pool in the SGA zone according to the actual business needs. If you do not create a large pool, operations that require a lot of memory will occupy the memory of the shared pool, which will have a certain performance impact on SHARED POOL, and LARGE POOL is an area that acts as an isolation function.

The operations that require a lot of memory for ORACLE are:

A. Database backup and recovery, such as RMAN, is used for disk IO buffers in some cases

B. SQL statements with a large number of sort operations

C, parallel database operation to store message buffers between processes

D. UGA is allocated in large pools in shared server mode (if large pools are set)

Java Pool (Java pool)

Used to support running java code in a database, generally controlled by java_pool_size

Stream pool (Stream pool)

Enhanced convection support is generally controlled by stream_pool_size. The stream pool (or, if no stream pool is configured, up to 10% of the space in the shared pool) is used to cache queue messages that the flow process uses to move / replicate data between databases.

2. Process global area (Porcess Global Area, PGA)

A PGA is an exclusive memory area that is used by Oracle processes to store data and control information in a proprietary way. When the Oracle process starts, the PGA is also created by the Oracle database. When the user process connects to the database and creates a corresponding session, the Oracle service process sets up a special PGA area for the user to store the contents of the user's session. When the user session terminates, the system automatically frees the memory occupied by the PGA area. This PGA zone has a great impact on the performance of the database, especially on the performance of sorting operations. Therefore, the reasonable management of the PGA area when necessary can greatly improve the performance of the database.

The global area of the program mainly consists of four parts: sorting area, session area, stack area and cursor area, which perform their own duties and complete the conversation between the user process and the database. In general, system administrators are mainly concerned with the sorting area, which needs to be resized manually if necessary. The main thing to need is that the cursor area is a dynamic area that is created when the cursor is opened and released when the cursor is closed. Therefore, in database development, not frequently opening and closing cursors can improve the efficiency of cursor operation and improve the performance of the database. The content administrator of other partitions only needs to know its purpose, and the daily maintenance can be left to the database system.

1. Set a reasonable sorting area size for sorting.

What does the database do when users need to sort some data? First, the database system saves the data that needs to be sorted to a sort area in the PGA program cache. The data is then sorted in this sort area. If there is 2m of data to be sorted, there must be at least 2m of space in the sorting area to hold the data. Then 2m of space is needed in the sorting process to save the sorted data. Because the speed of reading data from memory is thousands of times faster than that from hard disk, if the operation of data sorting and reading can be completed in memory, it can undoubtedly improve the performance of database sorting and access to a great extent. If this sort of operation can be done in memory, it is obviously ideal. But what if the sort area in the PGA area doesn't have enough capacity to hold the sorted data? At this point, the system will get a space from the hard disk to hold the data that needs to be sorted. At this point, the efficiency of sorting will be much lower. For this reason, in database management, if it is found that sorting is needed for many operations of users, then setting a larger sorting area for users can improve the efficiency of users' access to data.

In Oracle database, this sort area is mainly used to store temporary data generated by sorting operations. Generally speaking, the size of this sort area occupies most of the space in the PGA program cache, which is the main factor affecting the size of the PGA area. In small applications, database administrators can directly adopt their default values. However, in some large applications, or in database systems that require a large number of record sorting operations, administrators may need to manually adjust the size of this sorting area to improve sorting performance. If the system administrator needs to resize the sort area, it needs to be done by initializing the parameter SORT_AREA_SIZE. In order to improve the performance of data access and sorting, the database system uses the fact that the memory is thousands of times faster than the hard disk, and will temporarily store the data to be sorted in this sorting area, and complete the sorting of the data in the sorting area. Administrators need to keep this principle in mind and resize the sort area where appropriate to improve data access and data sorting performance.

2. The session area stores important information such as the user's permissions.

There is also a session area in the program cache. Although in most cases, the administrator does not maintain this session area, you can let the database system maintain. However, the administrator still needs to understand the role of this session area. Because this session area is directly related to the security of the data in the database system. Database system is not only a good carrier for storing data, but also provides a platform for unified management of data, which can set different access rights for different users according to the actual needs. To put it simply, you can control which data users can access in the database, so as to improve the security of the data.

When the user process establishes a session with the database, the system will query the relevant permissions of the user and save them in the session area. In this way, when the user process accesses the data, the system will check the user rights information in the session area to see if it has the relevant access rights. Because the system stores the user's permission information in memory, it checks the user's rights very quickly. Because the system no longer has to read data from the hard disk, it is read directly from memory. Reading data from memory is thousands of times faster than on a hard disk.

Typically, this session area holds information about permissions, roles, performance statistics, and so on. This session area is generally self-maintained by the database, and the system administrator does not need to intervene.

3. The stack area stores variable information.

Sometimes in order to improve the reusability of SQL statements, bind variables are used in statements. To put it simply, the SQL statement can accept variables passed in by the user. Thus, users only need to enter different variable values to meet different query needs. For example, now users need to query the information of all employees. Then it has to inquire about all employees with more than 3 years of service, and so on. In fact, they use the same SQL statement at this time, but the variables passed to the system are different. This can greatly reduce the workload of database development. This variable is called a bound variable in the Oracle database system. Binding variables can be used to enhance interaction with users. In addition, important information such as session variables and memory structure when SQL statements are run are stored in this stack area.

Typically, this stack area, like the session area mentioned above, allows the database system to maintain itself without the administrator being involved. The size of these partitions is also automatically allocated by the system according to the actual situation. When this user session ends, the system automatically releases the space occupied by these areas.

4. Vernier area.

Cursor technology is sometimes needed in both SQLServer and Oracle databases. When you run a statement that uses cursors, the Oracle database system allocates an area in the middle of the program cache. This area is called the Vernier area. In general, cursors are used to perform some special functions. And in general, statements with cursors are a little less efficient than other statements. For this reason, administrators still need to be cautious when using cursors.

The cursor area is a dynamic area. When the user executes the cursor statement, the system creates an area within the cursor area. When the cursor is closed, this area is released. This creation and release requires a certain amount of system resources and takes a certain amount of time. For this reason, when using cursors, if cursors are opened and closed frequently, the performance of the statement will be degraded. Therefore, the author suggests that when writing statements, if it is really necessary to use cursor technology, we should pay attention to not frequently opening and closing cursors.

In addition, in Oracle databases, you can improve the performance of the database by limiting the number of cursors. For example, there is an initialization parameter OPEN_CURSORS in the database system. The administrator can set this parameter according to the actual need to control the number of cursors that the user can open at the same time. It is important to note, however, that in cases where cursors are really needed, this restriction needs to be relaxed if hardware resources can support it. This prevents the user process from opening and closing cursors frequently. Because the frequent opening and closing of cursors is disadvantageous to the operation of cursors and will affect the performance of the database.

3. User global area (User Global Area, UGA)

In dedicated server mode, processes and sessions have an one-to-one relationship, and UGA is included in PGA. In online server mode, processes and sessions have an one-to-many relationship, so UGA no longer belongs to PGA, but is allocated in large pools (Large Pool). However, if allocation fails from a large pool, such as if the large pool is too small, or if no large pool is set up at all, it is allocated from a shared pool (Shared Pool).

On "Oracle memory structure SGA, PGA, UGA example analysis" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.