PGA and SGA

When a user process connects to a database and creates a corresponding session, the Oracle service process specifically sets up a PGA zone for that user to store relevant content for that user session. When this user session is terminated, the database system automatically frees the memory occupied by the Pag area. This PGA zone has a significant impact on the performance of the database, especially with regard to the performance of the sequencing operation. Therefore, the reasonable management of the PGA zone when necessary can greatly improve the performance of the database.

One, the difference between the PGA and the SGA.

The PGA (program buffer) is similar to the SGA (System global Zone), which is the area allocated by the Oracle database system for the session in server memory. But the two have different roles and different degrees of sharing. The SGA system global zone, as its name implies, is shared across all processes within the system. When multiple users are connected to a routine at the same time, all user processes and service processes can share the SGA area. The main purpose of this SGA is to provide an exchange platform for processes and services between different users. In addition to this role, another important function is that the operation of various databases is mainly done in this SGA area.



The PGA program buffer is primarily intended for use by a user process. This memory area is not shared and only the user's service process itself can access its own PGA zone. As an image metaphor, the SGA is like a shared folder on the operating system, where different users can communicate data on the platform. The PGA, like a private folder on the operating system, is accessible only to the owner of the folder and not to other users. Although the program buffers are not as open as other users ' processes, the memory area still carries some important missions, such as data sorting, permission control and so on, which are inseparable from this memory area.

Second, set a reasonable sort area size for the sort (sort areas).

When a user needs to sort some data, how does the database handle it? First, the database system saves the data that needs to be sorted into a sort area in the PGA program cache. The data is then sorted in this sort area. If the data to be sorted is 2M, there must be at least 2M of space in the sorting area to accommodate the data. Then the sorting process requires 2M of space to hold the sorted data. Since it is thousands of times times faster to read data from memory than to read from the hard disk, it is undoubtedly possible to improve the performance of database sorting and access to a large extent if the data sorting and reading operations can be done in memory. If this sort of operation can be done in memory, obviously this is ideal. But what if the sort area in the PGA zone is not enough to hold the sorted data? At this point, the system takes a space from the hard disk to hold the data that needs to be sorted. At this point, the efficiency of sorting will be reduced a lot. In database management, if you find that a lot of user operations need to use sorting, then users will set a larger sorting area, you can improve the efficiency of user access to data.



In an Oracle database, this sort area is primarily used to hold temporary data generated by a sort operation. In general, the size of this sorting area occupies most of the space that the PGA program caches, which is the main factor affecting the size of the PGA area. In small applications, the database administrator can take their default values directly. However, in some large applications, or in a database system that requires a large number of records to be sorted, the administrator may need to manually adjust the size of the sorting area to improve the performance of the sorting. If the system administrator needs to adjust the sorting size, it needs to be implemented by initializing the parameter sort_area_size. In order to improve the performance of data access and sequencing, the database system uses memory thousands of times times faster than the hard disk implementation, will be ready to sort the data temporarily deposited into the sorting area, and in the sorting area to complete the sorting of the data. Administrators need to keep this principle in mind and, where appropriate, adjust the size of the sorting area to improve the performance of data access and data sequencing.


Third, the session area holds the user's privileges and other important information (user Sesson DATA).

A session area is also included within the program buffer. Although the majority of cases, the administrator does not maintain this session area, can let the database system for maintenance. However, the administrator still needs to understand the role of this session area. Because this session area is directly related to the security of data in the database system. Database system is not only a good carrier of data storage, but also provides a unified management of data platform, according to the actual needs, for different users to set different access rights. Simply put, you can control what data the user can access in the database. thereby improving the security of the data.



When the user process establishes a session with the database, the user's relevant permissions are queried and saved in the session area. In this case, when the user process accesses the data, the system checks the user rights information in the session area to see if it has relevant access rights. Because the system will store the user's permission information in memory, so it checks the user's permissions very quickly. Because the system does not have to go to the hard disk to read the data, read directly from memory. The efficiency of reading data from memory is thousands of times times faster than on the hard disk.



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


Four, stack space to save the variable information (stack spaces).

Sometimes in order to improve the reusability of SQL statements, binding variables are used in statements. Simply put, the SQL statement can accept the variables that the user passed in. So that users only need to enter a different value of the variable, you can meet the different query requirements. For example, users are now required to query all employee information. Then it also inquires all employees who have worked for more than 3 years and so on. In fact, they are using the same SQL statement, but the variables passed to the system are different. This can reduce the workload of database development to a great extent. This variable is called a bound variable in an Oracle database system. Binding variables can be used to enhance interactivity with the user. In addition, this stack area also holds important information such as session variables, the memory structure of SQL statements at runtime, and so on.



Typically, this stack area, like the conversation area mentioned above, allows the database system to self-maintain, and administrators are not involved. The size of these partitions is also automatically assigned by the system according to the actual situation. When this user session ends, the system automatically frees up the space occupied by these areas.

Five, cursor state.

In both SQL Server databases and Oracle databases, cursor technology is sometimes required. When you run a statement that uses a cursor, the Oracle database system assigns it an area in the middle of the program buffer. This area is called the cursor area. Typically, cursors are used to accomplish some of the more special functions. In general, the use of cursors is less efficient than the execution of other statements. For this reason, administrators need to be cautious when using cursors.



The cursor area is a dynamic region. When a user executes a cursor sentence, the system creates an area within the wiper area. When the cursor is closed, the area is freed. This creates and frees up, requires a certain amount of system resources, and takes a certain amount of time. For this reason, when cursors are used, the execution performance of the statement is reduced if the cursors are opened and closed frequently. So I suggest that when you write a statement, if you really need to use cursor technology, you should be aware that cursors do not open and close frequently.



In addition, in an Oracle database, you can increase the performance of your database by limiting the number of cursors. If there is an initialization parameter Open_cursors in the database system. The administrator can set this parameter according to the actual needs, controlling the number of cursors that the user can open at the same time. It is important to note, however, that if a cursor is really needed, it needs to be relaxed if the hardware resources are able to support it. This prevents the user process from opening and closing cursors frequently. Because of the frequent opening and closing of cursors this is detrimental to the operation of the cursor, which can affect the performance of the database.



As can be seen from the above analysis, the program Global Region (PGA) mainly contains the sorting, session, stack and cursor area Four parts of the content, they do their own work, the user process to complete the session between the database. Typically, the system administrator focuses on the sorting area, which is required to manually adjust the size of the sorting area. In addition, the cursor area is a dynamic region that is created when the cursor is opened and released when it is closed. Therefore, in the database development, do not open and close the cursor frequently can improve the efficiency of the cursor operation, improve the performance of the database. Content managers in other partitions only need to know what their purpose is, and routine maintenance is given to the database system to complete.

PGA and SGA