Database cache for Oracle Performance Optimization

I. Information in the database cache
 
1. What is an execution plan?
 
2. Why share the execution plan?
 
The execution plan generation takes a lot of CPU time, And the optimizer will store the generated execution plan to the shared pool. If you execute many identical statements but do not share the execution plan, the optimizer will search for the shared pool each time to find whether there are any execution plans that can be shared. If not, it will generate it by itself, then, save the generated execution plan to the shared pool. That is to say, if you do not have a sharing plan for the same statement, you will not only consume more CPU to generate the execution plan, but also search the sharing pool and save the newly generated execution plan each time, managing execution plans in a shared pool also has some additional burden. These operations will slow down the execution speed of SQL statements. Oracle does not cancel some administrative work on the execution plan because you do not share the execution plan. For example, if you have a bicycle, you can ride it to speed up, but you not only ride it, but carry it, the result is that the speed is greatly slowed down. Oracle clearly provides a piece of memory called the database cache, hoping that you can share the execution plan in it. Even if you do not share the plan, the database cache will still exist. At this time, you are walking with a bicycle. You do not take advantage of the database cache, but you are under the management burden of the database cache. Therefore, the shared execution plan is the most important aspect to optimize the use of the Shared Pool.
 
 
Ii. Database Cache Optimization
 
The most important aspect of library Cache Optimization is that users can share execution plans. This should be done from both the programmer and DBA perspectives. As a programmer, you should learn to bind variables, which can make similar statements identical, so that they can share the execution plan. Next, let's take a look at the situations in which execution plans can be shared.
 
 
1. Share the execution plan
 
To share an execution plan, the statement text must be identical. For example, the following statement cannot share the execution plan:
 
Statement 1: Select * from tab1 where id = 1;
 
Statement 2: select * from tab1 where id = 1;
 
Why can't Statement 1 and Statement 2 Share the execution plan? The first letter of the first statement is in upper case, and the first letter of the second statement is in lower case. The execution plan cannot be shared even if one space is added.
 
Assume that the text of the preceding two statements is identical, but Statement 1 is issued under user USER1, and Statement 2 is sent in user user2. In addition, if both users have their own TAB1 tables, the two statements cannot share the plan.
 
If the two statements want to share the plan, the text of the two statements should not only be identical, but also the environment for statement execution must be the same. The "environment" mentioned here refers to the value of some initialization parameters. It does not mean different users. Of course, if two users operate on different tables separately, the execution plan will not be shared if the table names are the same. If two users issue statements with the same text and operate on the same table, they can share the execution plan. How can different users operate the same table with the same name? In the preceding example, assume that USER1 has a TAB1 table, but USER2 does not. The query statement of USER1 is as follows:
 
Select * from tab1 where id = 1;
 
USER2 wants to query the table in USER1 in the following format:
 
Select * from user1.tab1 where id = 1;
 
The two statements cannot share the execution plan because the statement text is very different. The USER2 statement has an additional "USER1 .". In this case, we can use a public synonym to allow USER2 to Access Table 1 without adding "USER1." before the table name. See the following example:
 
(For example)
 
That is to say, as long as the statement text is the same and the environment for executing the statement is the same, the two statements can share the execution plan.
 
 
2. bind variables
 
Let's look at the following situation. Assume that a large website requires a large number of users to log on every day. User information is stored in a User_info table. Each user must enter the user ID and password when logging on, the database queries User_info based on the user ID, retrieves the user password and other basic user information, and so on. I will not talk about the subsequent work, that is, when each user logs on, the database must perform a query based on the user ID. Assume that two more users have logged on. One user ID is 1 and the other user ID is 2. The query statements for these two logon users are as follows:
 
Query statement of user 1: select * from user_info where id = 1;
 
User 2's query statement: select * from user_info where id = 2;
 
These two statements do not share the execution plan. We can experiment with it:
 
Step 1: publish the query statement in session 139:
 
SQL> select * from tab1 where id = 1;
 
ID NAME
 
--------------------
 
1 ICOL $
 
1 I _OBJ #
 
This statement is the first time to query the TAB1 table. This will lead to a large number of recursive calls that will be hard parsed multiple times. In the future, when we publish a query with TAB1, there will be no recursive calls. In this step, we will prepare for the next experiment and start the experiment below.
 
 
Step 2: query the resolution of session 139 in another session:
 
SQL> select name, value from v $ sesstat a, v $ statname B where. statistic # = B. statistic # and. sid = 139 and B. name like '% parse % ';
 
NAME VALUE
 
--------------------------------------------------------------------------
 
Parse count (total) 386
 
Parse count (hard) 60
 
Parse count (failures) 0
 
 
Step 3: issue a query statement in session 139:
 
SQL> select * from tab1 where id = 2;
 
ID NAME
 
--------------------
 
2 I _USER1
 
2 PROXY_ROLE
 
 
Step 4: query the resolution of session 139 again in another session:
 
SQL> select name, value from v $ sesstat a, v $ statname B where. statistic # = B. statistic # and. sid = 139 and B. name like '% parse % ';
 
NAME VALUE
 
--------------------------------------------------------------------------
 
Parse count (total) 387
 
Parse count (hard) 61
 
Parse count (failures) 0
 
 
Step 5: Send a similar query statement again in 139:
 
SQL> select * from tab1 where id = 3;
 
ID NAME
 
--------------------
 
3 CON $
 
3 I _IND1
 
 
Step 6: query the resolution in session 139 again:
 
SQL> select name, value from v $ sesstat a, v $ statname B where. statistic # = B. statistic # and. sid = 139 and B. name like '% parse % ';
 
NAME VALUE
 
--------------------------------------------------------------------------
 
Parse count (total) 388
 
Parse count (hard) 62
 
Parse count (failures) 0
 
 
The results of this experiment show that the two statements share different execution plans. Imagine if a large number of users log on every day, every user cannot share the execution plan of similar statements when logging on. How much CPU time will be consumed. This is the time to bind variables. Next, let's look at an example of using the bound variable:
 
Step 1: Define the Bind Variable User_id In the 139 session and assign its value to 4.
 
SQL> var user_id number;
 
SQL> exec: user_id: = 4;
 
The PL/SQL process is successfully completed.
 
 
Step 2: Use the bound variable in the 139 session for query:
 
SQL> select * from tab1 where id =: user_id;
 
ID NAME
 
--------------------
 
4 UNDO $
 
4 I _CDEF2
 
 
Step 3: Observe the 139 session resolution times:
 
SQL> select name, value from v $ sesstat a, v $ statname B where. statistic # = B. statistic # and. sid = 139 and B. name like '% parse % ';
 
NAME VALUE
 
--------------------------------------------------------------------------
 
Parse count (total) 395
 
Parse count (hard) 69
 
Parse count (failures) 0
 
 
Step 4: Change the Bind Variable User_id to 5 and run the query again:
 
SQL> exec: user_id: = 5;
 
The PL/SQL process is successfully completed.
 
Note that this statement must also be parsed. Therefore, we need to check the number of resolutions here:
 
 
Step 5: view the resolution times of the 139 session:
 
SQL> select name, value from v $ sesstat a, v $ statname B where. statistic # = B. statistic # and. sid = 139 and B. name like '% parse % ';
 
NAME VALUE
 
--------------------------------------------------------------------------
 
Parse count (total) 396
 
Parse count (hard) 70
 
Parse count (failures) 0
 
 
Step 6: In the 139 session, use the bound variable to perform another query:
 
SQL> select * from tab1 where id =: user_id;
 
ID NAME
 
--------------------
 
5 C_COBJ #
 
5 I _PROXY_RO
 
The query result is a row with the ID of 5. Here, we use the same statement to query the rows with ID 4 and with ID 5 respectively.
 
 
Step 7: view the resolution times of the 139 session:
 
SQL> select name, value from v $ sesstat a, v $ statname B where. statistic # = B. statistic # and. sid = 139 and B. name like '% parse % ';
 
NAME VALUE
 
--------------------------------------------------------------------------
 
Parse count (total) 397
 
Parse count (hard) 70
 
Parse count (failures) 0
 
After the last query, hard Parsing is not added. This is the function of binding variables.
 
You can only bind variables, but not common variables. The binding variable is a special variable of Oracle. The process of assigning values to it is that after the Oracle optimizer parses the statement, the task of parsing the statement is to determine the statement execution plan. When the variable is bound, the optimizer parses the statement "select * from tab1 where id =: user_id" instead of "select * from tab1 where id = 5; ". The optimizer will determine the statement execution plan according to "select * from tab1 where id =: user_id" instead of "select * from tab1 where id = 5 ". After the statement has been parsed and the statement execution plan has been determined, the optimizer submits the execution plan to the server process for execution and replaces the bound variable with the actual value. If you use a common variable, the Oracle optimizer replaces the variable with the variable value before parsing, so that when parsing statements and generating execution plans, the optimizer sees the statement "select * from tab1 where id = 4" or "select * from tab1 where id = 5", because their texts are different, the optimizer will choose to generate execution plans for them again. Instead of directly executing the execution plan that has been stored in the cache.
 
 
 
 
 
Database cache for Oracle performance optimization [continued] http://www.bkjia.com/database/201202/120552.html
 
 
 
This article is from the "ye shaochen" blog