Difference analysis between Oracle hard parsing and soft parsing
- 2021-12-19 07:14:15
- OfStack
1. Summary
Oracle hard parsing and soft parsing are problems we often encounter, so we need to consider when to produce soft parsing and when to produce hard parsing, and how to judge
Execution of SQL
When an SQL or PL/SQL command is issued, Oracle will automatically find out if the command exists in the shared pool to decide whether to use hard parsing or soft parsing for the current statement.
Typically, the SQL statement is executed as follows:
Step1. SQL code syntax (syntax correctness) and semantic check (object existence and permissions).
Step2. Hash the text of the SQL code to get a hash value.
Step3. If there is the same hash value in the shared pool, go one step to this command to determine whether to perform soft parsing, otherwise go to e step.
Step4. For a new command line with the same hash value, the text is compared individually with the text of an existing command line.
These comparisons include case, whether the string is 1, spaces, comments, etc. If 1 is, soft parse it and go to step Step6 without hard parsing again.
Otherwise go to step Step5.
Step5. Hard parsing, generating execution plan.
Step6. Execute the SQL code and return the result.
2. Soft parsing
1. The following 3 query statements cannot use the same shared SQL area. Although the queried 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 generates a different execution plan for its where clause, empno, despite its different values
select * from emp where empno=7369
select * from emp where empno=7788
3. When judging whether to use hard parsing, the referenced object and schema should be the same. If the object is the same but schema is different, it is necessary 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 have the same name, and when the owners are different,
SCOTT TB_OBJ
usr1@ASMDB> select * from tb_obj;
scott@ASMDB> select * from tb_obj; -- At this time, both need to use hard parsing and go through different execution plans
3. Hard parsing
Hard parsing means that the execution of the whole SQL statement needs to be completely parsed to generate an execution plan. Hard parsing and generation of execution plans consume CPU resources and SGA resources. What has to be mentioned here is the use of latch in library cache. Latch is a refinement of lock, which can be understood as a lightweight serialization device. When a process applies for a latch, the latch is used to protect the number of shared memory from being modified by more than two processes at the same time. In hard parsing, it is necessary to apply for the use of bolts, and the number of bolts needs to wait in limited cases. A large number of bolts are used. As a result, the more frequently the processes that need to use bolts are queued, the lower the performance will be.
1. The following is a demonstration of the above two situations
Completed in two different session, one session for the sys account, one session for the scott account, and a different session whose SQL command line begins with a different account name
Such as "sys @ ASMDB > "Represents the session of the sys account at the time of use," scott @ ASMDB > "session representing an scott account
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- -- The current hard-resolved 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
-------------------- ---------- ---------- -- On execution 1 The hard-parsed value after queries is 570 The number of parses increased 1 Times
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
-------------------- ---------- ---------- -- On execution 1 The hard-parsed value after queries is 571
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
-------------------- ---------- ---------- -- On execution 1 The hard-parsed value after queries is 572
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
-------------------- ---------- ---------- -- On execution 1 The hard-parsed value after queries is 573
parse count (hard) 64 573
scott@ASMDB> select * from emp where empno=7788; -- Originally here empno=7369 Was caused by a copy error and has now been corrected to 7788@20130905
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- -- On execution 1 The hard-parsed value after queries is 574
parse count (hard) 64 574
As you can see from the above example, Oracle hard-parsed the executed statements, generating different execution plans, despite the subtle differences. Even with the same SQL statement, and the number of spaces in the two statements is different, Oracle will also be hard parsed.
4. Hard parsing improvements-using dynamic statements
1. Change the parameter cursor_sharing
The parameter cursor_sharing determines which 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 identical to the statement in the cache.
FORCE--If the SQL statement is literal, it forces Optimizer to always use an existing execution plan, regardless of whether the existing execution plan is optimal or not.
SIMILAR--If the SQL statement is literal, use it only if the existing execution plan is optimal, and re-align the SQL if the existing execution plan is not
Statement to develop the best execution plan.
This parameter can be set 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'; -- Set the parameter cursor_sharing Change the value of to similar
sys@ASMDB> select name,class,value from v$sysstat where statistic#=331;
NAME CLASS VALUE
-------------------- ---------- ---------- -- The current hard-resolved 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
-------------------- ---------- ---------- -- On execution 1 Article SQL After querying, the hard-parsed 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
-------------------- ---------- ---------- -- On execution 1 Article SQL After the query, the hard-parsed value does not change or 866
parse count (hard) 64 866
sys@ASMDB> select sql_text,child_number from v$sql -- You can see in the following results SQL_TEXT Binding variables are used in columns :"SYS_B_0"
where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBE
-------------------------------------------------- ------------
select * from dept where deptno=:"SYS_B_0" 0
sys@ASMDB> alter system set cursor_sharing='exact'; -- Will cursor_sharing Change back to exact
-- Next in scott Adj. session Execute in deptno=40 And and then view the query sql_text , when cursor_sharing Replace with exact After that, every time you execute that 1 Times
-- Will also be in v$sql Increase in 1 Bar statement
sys@ASMDB> select sql_text,child_number from v$sql
where sql_text like 'select * from dept where deptno%';
SQL_TEXT CHILD_NUMBER
-------------------------------------------------- ------------
select * 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 variable requires variable name, data type and length to be 1, otherwise soft parsing cannot be used
(1). The binding variable (bind variable) is the use of a placeholder in an DML statement, using a colon followed by 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, variable values are provided when the query is executed. The query is compiled only once, and the query plan is then stored in a shared pool (library cache) for later retrieval and reuse.
(2). Binding variables are used below, but the two variables are essentially different. In this case, hard parsing is also used
select * from emp where empno=:eno;
select * from emp where empno=:emp_no
When using binding variables, it is required that different sessions use the same reply environment, and the rules of the optimizer, 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 Insert a new record using a binding variable
as
begin
for i in 1..10000
loop
execute immediate 'insert into tb_test values(:n)' using i;
end loop;
end;
/
Procedure created.
scott@ASMDB> create or replace procedure proc2 -- Create a stored procedure proc2 No binding variables are used, so every 1 A SQL Insert statements are hard parsed
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 in 12243 hsecs --run2 The running time is run1 Adj. /1769 Approximate times
run 1 ran in 14.45% of the time
Name Run1 Run2 Diff
LATCH.SQL memory manager worka 410 2,694 2,284
LATCH.session allocation 532 8,912 8,380
LATCH.simulator lru latch 33 9,371 9,338
LATCH.simulator hash latch 51 9,398 9,347
STAT...enqueue requests 31 10,030 9,999
STAT...enqueue releases 29 10,030 10,001
STAT...parse count (hard) 4 10,011 10,007 -- The number of hard parses, the former only 4 Times
STAT...calls to get snapshot s 55 10,087 10,032
STAT...parse count (total) 33 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 -- Queue number comparison of latch
LATCH.enqueue 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,943
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% --proc2 The number of bolts used is also far more than proc1 And the ratio is .13%
PL/SQL procedure successfully completed.
(3). Benefits of using bound variables
From the above example, we can see that in the case of not using binding variables, whether it is the number of parses, the number of bars used, queues, allocated memory, library cache, row cache is much higher than binding
Variable. Therefore, bind variables are used as much as possible to avoid the extra system resources required by hard parsing.
Benefits of binding variables
Reduces hard parsing of SQL statements, thereby reducing the overhead incurred by hard parsing (CPU, Shared, pool, latch). Secondly, improve programming efficiency and reduce the number of database visits.
Disadvantages of binding variables
The optimizer will ignore the histogram information, which may not be optimized enough when generating the execution plan. SQL optimization is relatively difficult
STEP 5 Summarize
1. Avoid hard parsing as much as possible, because hard parsing requires more CPU resources, bolts, etc.
2. The cursor_sharing parameter should be weighed, and the impact of using similar and force should be considered.
3. Use bound variables as much as possible to avoid hard parsing.