Oracle soft and hard parsing of SQL statements

In the forum, some people often propose the direction of SQL optimization: To avoid hard parsing of SQL, so as to improve the efficiency of SQL Execution. Avoiding hard Parsing is indeed an important strategy for efficient use of shared_pool. In general, as developers, we need to remember that in order to make the most efficient use of the Sharing pool, it is best to write SQL statements that can be shared, for example, binding variables is an effective way to avoid hard parsing and thus improve sharing. Let's take a relatively extreme example. Here we use dynamic SQL to simulate hard parsing scenarios:

ChenZw> drop table foo purge; the table has been deleted. Used time: 00: 00: 00.05 ChenZw> create table foo (x int); the table has been created. Used time: 00: 00: 00.07 ChenZw> create or replace procedure proc 2 as 3 begin 4 for I in 1 .. 100000 loop 5 execute immediate 6 'insert into foo values ('| I |') '; 7 end loop; 8 end; 9/the process has been created. The time used: 00: 00: 00.05 ChenZw> exec proc; PL/SQL process has been completed successfully. Used time: 00: 00: 47.75 ChenZw>

 

We can see that the execution time above is 47.75 seconds. We can view the content in a data dictionary shared in the SQL area in the Shared_pool: We found that, the earliest Parsing is to insert 96581 of the data, and the last Parsing is to insert 100000 of the parsing, a total of 6281 data records. Now we use another method to re-execute the preceding statement. We use dynamic SQL to bind variables to write this SQL statement. The code and results we tried are as follows:

ChenZw> drop table foo purge; the table has been deleted. Used time: 00: 00: 00.13 ChenZw> create table foo (x int); the table has been created. Used time: 00: 00: 00.03 ChenZw> drop procedure proc; the process has been deleted. Used time: 00: 00: 00.04 ChenZw> create or replace procedure proc 2 as 3 begin 4 for I in 1 .. 100000 loop 5 execute immediate 6 'insert into foo values (: x) 'using I; 7 end loop; 8 end; 9/the process has been created. Used time: 00: 00: 00.06 ChenZw> alter system flush shared_pool; the system has changed. The time used: 00: 00: 00.92 ChenZw> exec proc; PL/SQL process has been completed successfully. Used time: 00: 00: 04.50 ChenZw>

 

We can view the content saved in v $ SQL and see the following: we can clearly see that the insert statement is parsed once, and 100,000 times are called. Therefore, the execution efficiency changes from 48 seconds of the first time to 5 seconds of the current time. What are the differences between the two? What is the relationship between this example and soft and hard parsing of SQL statements? The figure shown in Oracle Concepts Guide is as follows: Let's make a conclusion first. The first case above is that the execution efficiency is low due to too many times of hard SQL parsing. The second case is, this reduces SQL hard parsing and improves SQL running efficiency. First, when the first insert into foo values (1) is executed, because the variable binding method is not used, in the SGA of the above structure diagram, first, check the judgment syntax of the statement, confirm the permissions, and so on. Then, use hash to form a parsed information and place it in SGA. Then, when insert into foo values (2) is executed, the same work is done. In the second case, when the first insert into foo values (: x) Statement is executed, it is also used to determine the syntax of the statement. After preparation, the parsed information is returned, in SGA, but when the second statement comes, Oracle does not need to do the interest settlement. Just take out the result after the last execution and run it. Therefore, we can see the difference between soft resolution and hard resolution. If Oracle is able to find the information that has previously been parsed from SGA for direct execution during SQL parsing, it is called SQL soft parsing. If Oracle cannot find the SQL statement that can be used when parsing the SQL statement, it must parse the information again, that is, the hard parsing of the SQL statement, the efficiency of SQL soft parsing and hard Parsing is roughly 50-60 times the performance gap (from the test results of an Oracle expert on a forum, the specific address is forgotten ). -- Author Chen ziwen (keen on PM \ ORACLE \ JAVA and so on): ziwen # 163.com deduction: 4 0 9 0 2 0 1 0 0 0 0 0 0 0. The above example shows that, the memory size for storing the resolution results is not very large. For example, in the first example, we parsed 0.1 million times, but only stored 6281 parsed data records. Through the analysis of related statements, we can also know that the memory algorithm of this block should be the least recently used algorithm.