Differential analysis of Oracle hard analytic and soft resolution _oracle

I. Summary

Oracle hard parsing and soft parsing are problems that we often encounter, so we need to consider when to produce soft parsing when a hard resolution is generated and how to judge

SQL Execution Process

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:

Step1. The syntax of the SQL code (syntax correctness) and semantic checking (the existence and permissions of the object).

Step2. Hashes the text of the SQL code to the hash value.

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

Step4. 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, annotations, and so on, and if they are consistent, soft parsing, go to step Step6, without hard parsing again.

Otherwise to step Step5.

Step5. Hard parsing to generate an execution plan.

Step6. Executes the SQL code and returns the result.

Second, soft analysis

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 of the 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.

Third, 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. What I have to mention here is the use of latches in the library cache. The latch is a refinement of the lock, which can be understood to be a lightweight serialization device. When a process requests a latch, the number of latches used to protect shared memory is not modified by more than two processes at the same time. 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 creates a process in which the latch is queued more frequently and performance is low.

1. Here is a demonstration of the above two scenarios

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 5  
    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 executing the previous query, and the resolution count is increased once parse Co
    UNT (Hard) 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 parse count (hard) after the last query is executed
    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 parse count (hard) after the last query is executed 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 parse count (hard) after the last query is executed  573 scott@asmdb> SELECT * from emp where empno=7788; --This is originally empno=7369, caused by a copy error, is now corrected to 7788@20130905 sys@asmdb> select Name,class,value from V$sysstat where statistic#=331
    ;      NAME CLASS value------------------------------------------the hard resolution value is 574 parse count (hard) after the last query is executed

 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 with the same SQL statement, and the number of empty two statements is different, Oracle will also do hard parsing.

Iv. Hard resolution improvements-using dynamic statements

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 parameter cursor_sharing NAME TYPE VALUE------ -----------------------------------------------------------------------cursor_sharing string EXACT s  Ys@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)
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 hard resolved value becomes 866 parse Coun after the last SQL query is executed
T (hard) 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 after the last SQL query was executed or 866 parse count (hard) 866 sys@as Mdb> 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" where sql_text like ' sel
      ECT * FROM dept where deptno% '; Sql_text Child_numbe--------------------------------------------------------------sel    ECT * FROM dept where deptno=: "SYS_B_0" 0 sys@asmdb> alter system set cursor_sharing= ' exact '; --Change the cursor_sharing back to exact--and then look at the Sql_text after executing deptno=40 and queries in Scott's session, and when cursor_sharing is changed to exact, once for each execution-- Also adds a statement to the V$sql sys@asmdb> select Sql_text,child_number from V$sql where Sql_text like ' select * from DEP
      t where deptno% '; Sql_text child_number--------------------------------------------------------------sel       ECT * FROM dept where deptno=50 0 SELECT * FROM dept where deptno=40       0 SELECT * FROM dept where deptno=: "Sys_b_0" 0
 

2. How to use bound variables

Binding variables require variable names, data types, and lengths to be consistent, otherwise you cannot use soft parsing

(1). Binding variable (bind variable) refers to the use of a placeholder in a DML statement, followed by a colon following the variable name, as follows

SELECT * FROM EMP where empno=7788--no binding variable used

SELECT * from emp where Empono=:eno--:eno is the binding variable

In the second query, the value of the variable is provided when the query executes. The query is compiled only once, and then the query plan is stored in a shared pool (the library cache) for later acquisition and reuse of the query plan.

(2). The following uses the binding variable, but the two variables are not the same in nature, for this case, the same use of hard parsing

SELECT * from EMP where Empno=:eno;

SELECT * FROM EMP where empno=:emp_no

Using binding variables requires that the same reply environment be used in different sessions, and the optimizer's rules, etc.

Scott@asmdb> CREATE table tb_test (col int); --Create a table tb_test scott@asmdb> Create or replace procedure Proc1--Create a stored procedure Proc1 use a binding variable to insert a new record as begin for I in 1..10000 loop E
Xecute immediate ' insert INTO tb_test values (: N) ' using I;
End Loop;
End
/Procedure created.
scott@asmdb> Create or Replace procedure PROC2-Creates a stored procedure proc2, does not use binding variables, so each SQL INSERT statement will harden the as begin for I in 1..10000 loop Execute immediate ' INSERT into tb_test values (' | |
i| | ') ';
End Loop;
End
/Procedure created.
scott@asmdb> exec Runstats_pkg.rs_start pl/sql procedure successfully completed.
scott@asmdb> exec Proc1;
Pl/sql procedure successfully completed.
scott@asmdb> exec Runstats_pkg.rs_middle;
Pl/sql procedure successfully completed.
scott@asmdb> exec proc2;
Pl/sql procedure successfully completed.
      Scott@asmdb> exec runstats_pkg.rs_stop (1000);  
      Run1 ran in 1769 Hsecs Run2 ran into 12243 hsecs--run2 the time to run is run1 times run 1/1769≈ in ran of Name Run1  Run2 Diff LATCH. SQL Memory manager Worka 410 2,694 2,284 latch.session allocation 532 8,912 8,380 Latch.simul Ator LRU latch 9,371 9,338 latch.simulator hash latch wuyi 9,398 9,347 STAT ... Enqueue requests 10,030 9,999 STAT ... Enqueue releases 10,030 10,001 STAT ... Parse count (hard) 4 10,011 10,007-The number of hard parsing, the former only four times STAT ... Calls to get snapshot s 10,087 10,032 STAT ... Parse count (total) 10,067 10,034 STAT ... Consistent gets 247 10,353 10,106 STAT ... Consistent gets from CA 247 10,353 10,106 STAT ... Recursive calls 10,474 20,885 10,411 STAT ... DB block gets from Cach 10,408 30,371 19,963 STAT ... DB block gets 10,408 30,371 19,963 latch.enqueues 322 21,820 21,498--Latch queue number comparison Latch.enqu Eue hash Chains 351 21,904 21,553 STAT ... Session logical Reads 10,655 40,724 30,069 latch.library Cache pin 40,348 72,410 32,062--Library cache pin LATCH.KKS Stats 8 40  , 061 40,053 latch.library cache lock 318 61,294 60,976 Latch.cache buffers chains 51,851 118,340 66,489 Latch.row Cache Objects 351 123,512 123,161 latch.library cache 40,710 234,653 193,94
      3 latch.shared Pool 20,357 243,376 223,019 Run1 latches Total versus runs--difference and pct Run1 Run2 Diff Pct 157,159 974,086 816,927 16.13% The number of latches used is far more than--proc2, the ratio is. proc1 13% pl/sql S
 
 Uccessfully completed.

(3). The benefits of using bound variables

The above example shows that in the case where the binding variable is not used, no matter how many times it is resolved, the number of latches used, the queue, the allocated memory, the library cache, the row cache is much higher than the bound

The case of the variable. Therefore, use binding variables as much as possible to avoid hard parsing resulting in additional system resources required.

Advantages of binding variables

Reduce the hard parsing of SQL statements, thereby reducing the extra overhead (cpu,shared Pool,latch) resulting from hard parsing. Second, improve the programming efficiency, reduce the number of database visits.

Disadvantages of binding variables

The optimizer ignores the histogram information and may not be optimized when generating execution plans. SQL optimization is relatively difficult

V. Summary

1. Avoid hard parsing as much as possible, because hard parsing requires more CPU resources, latches, etc.

2.cursor_sharing parameters should weigh the pros and cons, need to consider the impact of using similar and force.

3. Use the binding variables as much as possible to avoid hard parsing.