Deep analysis and performance tuning of shared pool in Oracle

Summary: This article begins with a detailed introduction to the concept of shared pool in Oracle and the memory structure included. It then provides an in-depth overview of Oracle's management mechanisms for shared pool. Finally, the paper introduces the practical method of buffer cache monitoring and tuning.

1. The concept of shared pool

Oracle databases, as a product of managing data, must be able to recognize the administrative commands (often called SQL statements) submitted by the user to respond. The process of recognizing is called the process of parsing an SQL statement, and the process of responding is called the process of executing an SQL statement. The parsing process is a fairly complex process that takes into account a variety of possible exceptions, such as the absence of an object that is involved in an SQL statement, the lack of permissions on the user who commits it, and so on. Also, you need to consider how to execute SQL statements, how to obtain data, and so on. The final result of parsing is to produce Oracle's own internal execution plans to guide the execution of SQL. As you can see, the process of parsing is a very resource-intensive process. Therefore, Oracle in the process of parsing user-submitted SQL statements, if each occurrence of the new SQL statements in accordance with the standard process complete analysis of the whole, the efficiency is too low, especially with the increase in the number of concurrent users, the increase in data volume, the overall performance of the database will be a straight down.

Oracle summarizes and abstracts the SQL statements, refining the SQL statements into two parts, the static part of the SQL statement, the keywords in the SQL statement itself, the table names involved, and the columns of the table. The other part is the dynamic part of the SQL statement, which is the value in the SQL statement (that is, the data in the table). Obviously, the total number of objects contained in the entire database is limited, and the data contained therein is infinite. And it is this infinite data that leads to the ever-changing SQL statements, which means that in all SQL statements that occur during a database run, the static part can be considered to be finite, while the dynamic part is infinite. In fact, the dynamic part of the analysis of the impact of the static part of the analysis is very small, that is, usually, for the same static part of the SQL statements, the different dynamic parts of the resulting results (execution plan) are basically the same. This also provides a direction for Oracle to improve the efficiency of parsing SQL statements.

Oracle will slow down in memory the SQL statements that are submitted by the user. Each time a new SQL statement is processed, the same SQL statement is first viewed in memory. If the same can reduce the most important parsing work (that is, build the execution plan), which saves a lot of resources, conversely, if you do not find the same SQL statement, you must complete the entire parsing process from beginning to end. This part of the memory that holds the SQL statement is called a shared pool. Of course, the shared pool is not just a SQL statement, but also a memory structure that manages the memory structure of the shared pool, execution planning, control information, and so on.

When Oracle finds the same SQL statement in the shared pool, if the SQL statement uses a binding variable (bind variable), then the static portion of the comparison SQL statement, as we already know, is finite and can easily be cached in memory. , so the probability of finding the same SQL statement is high. If you do not use a binding variable, you are comparing the static and dynamic parts of the SQL statement, and the dynamic part is infinite, so it is difficult to cache such SQL statements in the shared pool. After all, memory is finite, and it is impossible to slow down all the dynamic parts of the shared pool, even if it can be cached, it is impossible to manage such an infinitely large shared pool. The immediate result of not using a binding variable is that the probability of finding the same SQL statement is low, resulting in the need to parse the SQL statement completely, resulting in more resources being consumed. It can be seen from here that only when we use binding variables do we really follow the philosophy of Oracle's introduction of shared pool in order to make the most effective use of shared pool.

The size of the shared pool is determined by the initialization parameter shared_pool_size. 10g can not set this parameter at a later time, and only need to specify Sga_target, so Oracle will automatically determine the size of the shared pool. At a very high level, shared pool can be divided into library cache and data dictionary cache (dictionary cache). The Library cache stores the most recently executed SQL statements, stored procedures, functions, parse trees, and execution plans. The dictionary cache stores the information of the referenced data dictionary during the execution of the SQL statement, including the table names involved in the SQL statements, table columns, permission information, and so on. Dictionary cache is also called row cache, because the information is stored in the form of data rows, not as a block of data stored. For dictionary cache, Oracle tends to cache them in shared pool, without swapping them out of memory, so we don't have to pay too much attention to them. The library cache is the most important part of the shared pool, and the most active part of the shared pool, which requires our careful study. So, when we talk about shared pool, we can actually think of it as referring to the library cache.