Oracle hard parsing and soft parsing __oracle

Oracle hard parsing and soft parsing are the problems we often encounter, what will produce hard parsing, what happens to soft parsing, and how to avoid hard parsing. The following description gives the

The emergence of soft and hard parsing, as well as the drawbacks of hard parsing and how to avoid the production of hard parsing.

The execution process of SQL statements

When a SQL or Pl/sql command is published, Oracle automatically finds that the command exists in the shared pool to determine whether to use hard resolution or soft resolution on the current statement.

Typically, the execution of an SQL statement is as follows:

A.sql The syntax of the code (syntax correctness) and semantic checking (object existence and permissions).

B. Hash the text of the SQL code to get the hash value.

C. If the same hash value exists in the shared pool, the command is further judged for soft resolution, or to the E step.

D. For a new command line with the same hash value, the text is compared to the text of the command line that already exists. These comparisons include case, string consistency, spaces, comments

And so on, if consistent, then soft parse it, go to step f. Otherwise to D step.

E. Hard parsing to generate an execution plan.

F. Execute the SQL code and return the result.

Second, can not use the situation of soft parsing

1. The following three query statements cannot use the same shared SQL area. Although the query's Table object uses case, Oracle generates a different execution plan for it

SELECT * from EMP;

SELECT * from EMP;

SELECT * from EMP;

2. Similarly, in the following query, Oracle also generates a different execution plan for it, although its where clause empno a different value

SELECT * FROM EMP where empno=7369

SELECT * FROM EMP where empno=7788

3. In determining whether to use hard parsing, the referenced object and schema should be the same, and if the objects are the same and the schema is different, you need to use hard parsing to generate different execution plans

Sys@asmdb> Select Owner,table_name from dba_tables where table_name like ' tb_obj% ';

OWNER table_name

------------------------------ ------------------------------

USR1 Tb_obj--two objects with the same name, when the owner is different

SCOTT Tb_obj

Usr1@asmdb> select * from Tb_obj;

Scott@asmdb> select * from Tb_obj; --both require hard parsing and different execution plans at this time.

Iii. The drawbacks of hard analysis

Hard parsing means that the execution of the entire SQL statement needs to be completely parsed to generate an execution plan. For hard parsing, generating execution plans consumes CPU resources and SGA resources. Not here

Not to mention is the use of the latch in the library cache. The latch is a refinement of the lock, which can be understood to be a lightweight serialization device. These latches are used to secure shared memory when the process is applied to the latch

Number at the same time will not be modified by more than two processes. In hard parsing, the use of the latch is required, and the number of latches is limited to wait. The use of a large number of latches thereby

The more frequently queued processes that are required to use the latch, the lower the performance.

Iv. Demonstration of hard parsing

Here's a demo of the two scenarios above

Completed in two different sessions, a session for the SYS account, a session for the Scott account, a different session, and the SQL command line begins with a different account name

"Sys@asmdb>" represents the session of the SYS account when used, and "Scott@asmdb>" represents the session of the Scott account

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

------------------------------------------The current hard resolution value is 569

Parse count (hard) 64 569

Scott@asmdb> select * from EMP;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

------------------------------------------the hard resolution value is 570 after the last query is executed, the number of resolutions increases once

Parse count (hard) 64 570

Scott@asmdb> select * from EMP;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

The hard resolution value is 571 after the last query is executed------------------------------------------

Parse count (hard) 64 571

Scott@asmdb> select * from EMP;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

The hard resolution value is 572 after the last query is executed------------------------------------------

Parse count (hard) 64 572

Scott@asmdb> SELECT * from emp where empno=7369;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

The hard resolution value is 573 after the last query is executed------------------------------------------

Parse count (hard) 64 573

Scott@asmdb> SELECT * from emp where empno=7369;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

The hard resolution value is 574 after the last query is executed------------------------------------------

Parse count (hard) 64 574

As you can see from the example above, although there are subtle differences in the execution of the statements, Oracle makes a hard resolution of them, producing different execution plans. Even the same SQL

statement, and the number of empty two statements is different, Oracle will also do hard parsing.

Five, the improvement method of coding hard analysis

1. Change parameter cursor_sharing

Parameter cursor_sharing determines what type of SQL can use the same SQL area

cursor_sharing = {SIMILAR | EXACT | FORCE}

EXACT-Use an existing execution plan only if the published SQL statement is exactly the same as the statement in the cache.

FORCE--If the SQL statement is literal, Force optimizer to always use an existing execution plan, regardless of whether the existing execution plan is optimal.

SIMILAR--If the SQL statement is literal, use it only if an existing execution plan is optimal, and then restart the SQL if the existing execution plan is not optimal

--statement analysis to develop the best execution plan.

You can set this parameter based on different levels, such as Alter session, ALTER SYSTEM

Sys@asmdb> Show Parameter Cursor_shar--View parameters cursor_sharing

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Cursor_sharing string EXACT

Sys@asmdb> alter system set cursor_sharing= ' similar '; --Change the value of the parameter cursor_sharing to similar

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

------------------------------------------The current hard resolution value is 865

Parse count (hard) 64 865

Scott@asmdb> SELECT * from dept where deptno=10;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

------------------------------------------the last SQL query is executed, the hard resolved value becomes 866

Parse count (hard) 64 866

Scott@asmdb> SELECT * from dept where deptno=20;

Sys@asmdb> Select Name,class,value from V$sysstat where statistic#=331;

NAME CLASS VALUE

------------------------------------------the hard resolved value has not changed or 866 after the last SQL query was executed

Parse count (hard) 64 866

Sys@asmdb> Select Sql_text,child_number from V$sql--you can see in the following results that the binding variable is used in the Sql_text column: "Sys_b_0"

2 where Sql_text like ' select * from dept where deptno% ';

Sql_text Child_number

-------------------------------------------------- ------------

SELECT * FROM dept where deptno=: "Sys_b_0" 0

Sys@asmdb> alter system set cursor_sharing= ' exact '; --Change the cursor_sharing back to exact

--then look at Sql_text after executing deptno=40 and queries in Scott's session, and when Cursor_sharing is exact, every time you perform that

--also adds a statement to the V$sql

S