The shared pool can be said to be a very important power resource in the database, which is related to the performance bottleneck of the database.what is a shared pool? The shared pool is part of the SGA (System global Zone) in the memory structure, which includes: library buffers, data dictionary buffers, server result buffers, reserved pools, and four zones that make up the shared pool, which is the function of the four zones.Library BuffersShared SQL Zone: The execution plan and the parse tree, when the user executes an SQL statement, the shared pool will be based on the complexity of the SQL statement of memory to the user, when the user executes the same SQL statement again, do not have to parse again, improve execution efficiency of executing statements. Similar to the ancient generals in the war, the emperor is the user, the generals as a shared pool, will tie everyone go the way is SQL statement, no longer explore the route. Private SQL Zone: There are several environment settings for SQL statements, such as some of the binding variables of SQL statements, environment and session parameters, as well as local variables, global variables, and package variables for the PL/SQL package. The shared SQL area of an SQL statement in a shared pool can be common to multiple private SQL zones. That is, many people can go this way at the same time.Data Dictionary buffersData dictionary exists in the system table space, the most frequent access in the database is it, there are tables, views and other information, the database will use a data dictionary to parse the SQL statement, it is divided into two parts, a portion of rows (row) in the way the data dictionary buffer, The other part is stored in the library buffer in blocks (block), which together comprise the data dictionary buffers and are shared by all user processes.Server Result BuffersThis is also a point in the shared pool, all the correct SQL queries and function results cached into the server result buffer, when multiple users execute the same SQL statement, as long as it has a parse and execution plan, then the speed is completely out of order with the first time. The server result buffer is different from the database cache, which is buffered into the shared pool without any processing; the latter needs to be read from disk to memory, which is processed.Reserved PoolIf there is a large contiguous memory allocation, the reserved pool will be preferred.shared pools have different architectures in different server modes When the database is in the proprietary server mode, only the shared SQL zone in the library buffer, the private SQL zone and the UGA are divided into the program global area, but in the shared server mode, the library buffer contains the private SQL zone, and the shared pool has UGA (user Global Zone). In general, the shared pool is the exclusive area of the SQL statement, dividing the memory area-->SQL statement parsing and generating the parse tree--making the execution plan--and returning the query results--by all user processes to share the execution plan and results of this SQL statement.What is a database cache buffer area? In order to avoid the database waiting for disk i/o,oracle design the database buffer zone, temporarily hold the user executes the query statement from the data file (disk) read from the data,avoids direct-to-disk I/O, and is shared by other users of the current instance. The shared pool is closely related to the database cache, where the shared pool holds the ins and outs of the SQL statement, and the database cache is the container between the disk and the memory, each of which accesses the shared pool to the container first. The Database Cache area containsKeep pools, recycle pools, and default poolsYou can look at the following figure to understand the database high-speed buffer.
When a SQL query occurs, the result is not found in the server result buffer of the shared pool, then it is looked up in the database cache, if there is a buffer hit (cache hits), read the retention pool, otherwise a buffer error (cache Miss), may read the default pool or recycle pool, In another case, if you look for a long time in the database cache, until the critical point, the DBWN process is notified, the dirty buffers in the Recycle pool are written to the disk, and the free buffers in the default pool are transferred to the recycle pool and become dirty buffers. It is conceivable that the execution of an SQL statement is as follows: Please correct me!
Oracle shared pool and Database cache, which leads to the SQL execution process
