Introduction to Oracle PGA

When a user process connects to the database and creates a corresponding session, the Oracle service process sets a PGA zone for the user to store the relevant content of the user session. When the user session ends, the Database System Automatically releases the memory occupied by the PAG zone. This PGA zone has a great impact on database performance, especially for sorting operations. Therefore, properly managing the PGA zone when necessary can greatly improve the database performance.

Three parameters have the greatest impact on PGA.
SORT_AREA_SIZE: total memory used for sorting information
SORT_AREA_RETAINED_SIZE: total memory size for storing sorting information in the memory after sorting.
HASH_AREA_SIZE: memory used to store the hash list.

The following three parameters are described:
1) SORT_AREA_SIZE:
If SORT_AREA_SIZE is set to 512KB and SORT_AREA_RETAINED_SIZE is also 512KB, Oracle uses the memory of 512KB for sorting. After sorting, all data remains in the memory.

2) SORT_AREA_RETAINED_SIZE:
If SORT_AREA_SIZE is set to 512KB and SORT_AREA_RETAINED_SIZE is set to 384KB, Oracle uses the memory of 512KB for sorting and then retains the sorted data of 384KB, in addition, sorted data of 512KB-384KB = KB is written to the temporary tablespace.
If SORT_AREA_RETAINED_SIZE is not set, its value is 0, but the actual reserved sorting data is the same as SORT_AREA_SIZE.

3) HASH_AREA_SIZE:
The HASH_AREA_SIZE parameter is used when a large set is connected with another set. A small table is placed in the memory as the driver table, and then the large table is connected by PROBE. If HASH_AREA_SIZE is too small, the performance of the two sets (tables) is affected.

Note:
1) if the data volume to be sorted is greater than SORT_AREA_SIZE, Oracle will sort the data in batches. Save the sorted data to the temporary tablespace and sort the remaining data. Finally, the sorted data stored in the temporary tablespace is sorted, because the sorted data saved to the temporary tablespace is only sorted by part of the data, the data to be sorted as a whole is partially ordered.
2) * _ AREA_SIZE is only a limitation on an operation. A query may have multiple operations, and each operation has its own memory zone. If SORT_AREA_SIZE is set to 5 MB, a query may have 10 sorting operations, which will occupy 50 MB of sorting memory.
3) 3. * _ AREA_SIZE memory is allocated on demand. If a query requires 5 MB of memory for sorting, even if 1 GB of SORT_AREA_SIZE is allocated, only 5 MB of memory is used.

 

　I. Difference Between PGA and SGA.

The PGA (Program cache) is similar to the SGA (system global zone), which is the region allocated by the Oracle Database System for sessions in the server memory. However, they have different roles and different degrees of sharing. As the name suggests, the global zone of the SGA system shares all processes in the system. When multiple users connect to a routine at the same time, all user processes and service processes can share the SGA zone. Therefore, this SGA is mainly used to provide a platform for communication between processes and service processes of different users. In addition to this role, it also plays an important role in database operations mainly in this SGA region.

The PGA program buffer mainly serves a user process. This memory zone is not shared. Only the service process of this user can access its own PGA zone. As an image metaphor, SGA is like a shared folder on the operating system. Different users can exchange data on this platform. The PGA is like a private folder on the operating system. Only the owner of this folder can access it, and other users cannot access it. Although the program cache area is not open to other users' processes, it still bears some important missions, such as data sorting and permission control.

　　2. Set a proper sorting area size for sorting.

How does the database process data that needs to be sorted? First, the database system saves the data to be sorted to a sorting area in the cache area of the PGA program. Then sort the data in the sorting area. If the data to be sorted is 2 MB, at least 2 MB of space must be available in the sorting area to accommodate the data. In the sorting process, another 2 MB space is required to save the sorted data. Because the system reads data from the memory several thousand times faster than reading data from the hard disk, if the data sorting and reading operations can be completed in the memory, undoubtedly, the database sorting and access performance can be greatly improved. If all the sorting operations can be completed in the memory, this is obviously ideal. But what if the capacity of the sorting area in the PGA is insufficient and it cannot accommodate the sorted data? In this case, the system obtains a space from the hard disk to save the data to be sorted. In this case, the sorting efficiency will be much lower. Therefore, in database management, if you find that many operations require sorting, you can set a large sorting area to improve the efficiency of data access.

In Oracle databases, This sorting area is mainly used to store temporary data generated by sorting operations. Generally, the size of this sorting area occupies most of the space cached by the PGA program, which is the main factor affecting the size of the PGA area. In small applications, the database administrator can directly use the default value. However, in some large applications or database systems that require a large number of record sorting operations, the Administrator may need to manually adjust the size of the sorting area to improve the sorting performance. If the system administrator needs to adjust the size of the sorting area, the system administrator needs to initialize the SORT_AREA_SIZE parameter. In order to improve the performance of data access and sorting, the database system uses the memory to implement the implementation several thousand times faster than the hard disk, and will temporarily store the data to this sorting area, data is sorted in the sorting area. The administrator needs to keep this principle in mind and adjust the size of the sorting area as appropriate to improve the performance of data access and data sorting.

　　3. Important information such as user permissions is stored in the session area.

There is also a session area in the program cache area. In most cases, the Administrator does not want to maintain the session area and can maintain the database system. However, the Administrator still needs to understand the role of this session area. This session zone is directly related to the data security in the database system. The database system is not only a good carrier for storing data, but also provides a platform for unified data management. You can set different access permissions for different users as needed. Simply put, you can control the data that users can access in the database. This improves data security.

When a user's process establishes a session with the database, the system queries the user's related permissions and stores them in the session zone. In this case, when a user process accesses data, the system checks the user permission information in the session area to see if the user has the relevant access permission. Because the system stores the user's permission information in the memory, the system can quickly verify the user's permissions. Because the system does not need to read data from the hard disk, it can directly read data from the memory. The efficiency of reading data from the memory is several thousand times faster than that on the hard disk.

In general, this session area stores the permissions, roles, performance statistics, and other information of the session. This session area is generally self-maintained by the database, and the system administrator does not need to intervene.

4. Store variable information in the stack area.

Sometimes, to improve the reusability of SQL statements, bind variables in the statements. Simply put, an SQL statement can accept the input variables. Therefore, you only need to enter different variable values to meet different query requirements. For example, you need to query the information of all employees. Then, it will query all employees who have worked for more than three years. In fact, they use the same SQL statement, but the variables passed to the system are different. This can greatly reduce the workload of database development. This variable is called the binding variable in the Oracle database system. Variable binding can enhance interaction with users. In addition, important information such as session variables and memory structure during SQL statement running is stored in this stack area.

Generally, this stack zone is the same as the session zone mentioned above. It allows the database system to maintain itself without the Administrator's involvement. The size of these partitions is automatically allocated by the system according to the actual situation. When the user session ends, the system automatically releases the space occupied by these zones.

　　5. cursor area.

The cursor technology is sometimes used in both SQL Server and Oracle databases. When you run a statement that uses a cursor, the Oracle database system allocates a region to it in the middle of the program cache area. This area is called the cursor area. Usually, a cursor is used to complete some special functions. In addition, statements that use cursors are generally less efficient than other statements. Therefore, the Administrator must be cautious when using the cursor.

The cursor area is a dynamic area. When you execute a cursor statement, the system creates a region in the cursor area. When the cursor is closed, the region is released. It takes some time to create and release system resources. Therefore, when using a cursor, if the cursor is frequently opened or closed, the statement execution performance will be reduced. Therefore, the author suggests that when writing a statement, if it is really necessary to use the cursor technology, please note that the cursor should not be opened or closed frequently.

In addition, you can limit the number of cursors to improve database performance in Oracle databases. For example, there is an initialization parameter OPEN_CURSORS in the database system. The administrator can set this parameter as needed to control the number of cursors that can be opened at the same time. However, it should be noted that when a cursor is required, if the hardware resources can be supported, the restriction should be relaxed. This avoids the frequent opening and closing of cursors by user processes. The frequent opening and closing of cursors are detrimental to cursor operations and may affect database performance.

From the above analysis, we can see that the global area of the program mainly contains four parts: the sorting area, the session area, the stack area, and the cursor area. They perform their respective duties, complete the session between the user process and the database. Generally, the system administrator focuses on the sorting area and needs to adjust the size of the sorting area manually when necessary. In addition, the cursor area is a dynamic area, which is created when the cursor is opened and released when it is closed. Therefore, when developing a database, do not open or close the cursor frequently to improve the efficiency of the cursor operation and improve the database performance. The content administrator of other partitions only needs to understand its purpose and hand over the routine maintenance to the database system.