Differences between Oracle hard parsing and soft parsing: oracle and soft

Differences between Oracle hard parsing and soft parsing: oracle and soft

I. Summary

Oracle hard parsing and soft parsing are common problems, so we need to consider when soft Parsing is generated, when hard Parsing is generated, and how to determine

SQL Execution Process

When an SQL or PL/SQL command is published, Oracle automatically finds whether the command exists in the shared pool to determine whether to use hard or soft parsing for the current statement.

Generally, the SQL statement execution process is as follows:

Step 1. SQL code syntax (syntax correctness) and semantic check (Object existence and permission ).

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

Step if the same hash value exists in the shared pool, the command is further used to determine whether soft resolution is performed. Otherwise, the command is sent to Step e.

Step 4. For new command lines with the same hash value, the text will be compared one by one with the existing command line text.

These comparisons include case sensitivity, whether the strings are consistent, spaces, and comments. If they are consistent, perform soft parsing and go to Step 6. You do not need to perform hard parsing again.

Otherwise, go to Step 5.

Step 5. generate an execution plan through hard parsing.

Step6. execute the SQL code and return the result.

Ii. Soft Parsing

1. The following three query statements cannot use the same shared SQL zone. Although the queried table objects are case-sensitive, Oracle generates different execution plans for them.

select * from emp;select * from Emp;select * from EMP;

2. Similarly, in the following query, Oracle generates different execution plans even though the values of the where clause empno are different.

select * from emp where empno=7369select * from emp where empno=7788

3. When determining whether hard Parsing is used, the objects and schemas referenced should be the same. If the objects are the same, but the schemas are different, hard parsing should be used 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 -- the names of the two objects, when the owner has different SCOTT TB_OBJusr1 @ ASMDB> select * from tb_obj; scott @ ASMDB> select * from tb_obj; -- both require hard parsing and different execution plans.

Iii. Hard Parsing

Hard parsing means that the execution of the entire SQL statement must be completely parsed to generate an execution plan. However, hard parsing requires CPU resources and SGA resources to generate execution plans. Here we have to mention the use of latches in the database cache. Latches are refined locks and can be understood as lightweight serialized devices. After a process applies for a latch, these latches are used to protect the number of shared memory instances from being modified by more than two processes at the same time. During hard parsing, you need to apply for the use of latches, and the number of latches needs to wait for a limited number of cases. The use of a large number of latches leads to more frequent queuing of processes that require latches, resulting in over-low performance.

1. the above two cases are demonstrated below

In two different sessions, one is the sys account session, the other is the scott account session, and the SQL command line of different sessions starts with different account names

For example, "sys @ ASMDB>" indicates the session of the sys account in use, "scott @ ASMDB>" indicates 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 finally Scott @ ASMDB> select * from emp; sys @ ASMDB> select name, class, value from v $ sysstat where statistic # = 331; name class value -------------------- ---------- -- after the last query is executed, the hard resolution VALUE is 570, and the number of resolutions increases by parse count (hard) 64 570scott @ ASMDB> select * from Emp; sys @ ASMDB> select name, class, value from v $ sysstat where statistic # = 331; name class value ------------------ ---------- -- after the last query is executed, the hard resolution VALUE is 571 parse count (hard) 64 571scott @ ASMDB> select * from EMP; sys @ ASMDB> select name, class, value from v $ sysstat where statistic # = 331; name class value -------------------- ---------- -- after executing the previous query, the hard resolution value 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 ------------------ ---------- -- after the last query is executed, the hard resolution VALUE is 573 parse count (hard) 64 573scott @ ASMDB> select * from emp where empno = 7788; -- the original empno = 7369 is caused by a replication error. It has been corrected to 7788 @ 20130905 sys @ ASMDB> select name, class, value from v $ sysstat where statistic # = 331; name class value -------------------- ---------- -- after the last query is executed, the hard resolution VALUE is 574 parse count (hard) 64 574.

From the above example, we can see that despite the slight differences in the executed statements, Oracle still performs hard parsing for them and generates different execution plans. Even for the same SQL statement and the number of spaces in the two statements is different, Oracle performs hard parsing.

Iv. Hard parsing improvement-use dynamic statements

1. Change the cursor_sharing parameter.

The cursor_sharing parameter determines what type of SQL can use the same SQL area.

CURSOR_SHARING = {SIMILAR | EXACT | FORCE}

EXACT-the execution plan is used only when the released SQL statement is exactly the same as the statement in the cache.

FORCE -- if the SQL statement is literal, it forces Optimizer to always use the existing execution plan, regardless of whether the existing execution plan is the best.

SIMILAR -- if the SQL statement is literal, it is used only when the existing execution plan is the best. If the existing execution plan is not the best, re-execute the SQL statement.

-- Analyze the statement to make the best execution plan.

You can set this parameter based on different levels, such as alter session and alter system.

Sys @ ASMDB> show parameter cursor_shar -- view the cursor_sharing name type value parameter ---------------------------------------- cursor_sharing string EXACTsys @ ASMDB> alter system set cursor_sharing = 'similar '; -- change the value of the cursor_sharing parameter to similarsys @ 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 865scott @ ASMDB> select * from dept where deptno = 10; sys @ ASMDB> select name, class, value from v $ sysstat where statistic # = 331; name class value -------------------- ---------- -- after the last SQL query is executed, the value of hard parsing changes to 866 parse count (hard) 64 866scott @ ASMDB> select * from dept where deptno = 20; sys @ ASMDB> select name, class, value from v $ sysstat where statistic #= 331; name class value -------------------- ---------- -- after the last SQL query is executed, the VALUE of hard parsing remains unchanged or 866 parse count (hard) 64 866sys @ ASMDB> select SQL _text, child_number from v $ SQL -- in the following results, we can see that the binding variable is used in the SQL _TEXT column: "SYS_ B _0" where SQL _text like 'select * from dept where deptno % '; SQL _TEXT CHILD_NUMBE should ------------ select * from dept where deptno =: "SYS_ B _0" 0sys @ ASMDB> alter system set cursor_sharing = 'exist '; -- change cursor_sharing back to exact -- execute the deptno = 40 and query SQL _text in scott's session. When cursor_sharing is changed to exact, at each execution time -- a statement sys @ ASMDB> select SQL _text, child_number from v $ SQL where SQL _text like 'select * from dept where deptno % 'will also be added to v $ SQL '; SQL _TEXT CHILD_NUMBER limit ------------ select * from dept where deptno = 50 0 select * from dept where deptno = 40 0 select * from dept where deptno =: "SYS_ B _0" 0

 
2. bind variables

To bind a variable, the variable name, data type, and length must be consistent. Otherwise, soft resolution cannot be used.

(1) bind variable refers to using a placeholder in a DML statement, that is, using a colon followed by the variable name, as follows:

Select * from emp where empno = 7788 -- Bind Variable not used

Select * from emp where empono =: eno --: eno is the bound variable.

In the second query, the variable value is provided during query execution. The query is compiled only once, and then the query plan is stored in a shared pool (database cache) for future retrieval and reuse of the query plan.

(2) The Bind Variable is used below, but the two variables are different in essence. In this case, hard Parsing is also used.

Select * from emp where empno =: eno;

Select * from emp where empno =: emp_no

When binding variables, the same session environment and optimizer rules must be used in different sessions.

Scott @ ASMDB> create table tb_test (col int ); -- create Table tb_testscott @ ASMDB> create or replace procedure proc1 -- create Stored procedure proc1 use Bind Variable to insert new record asbeginfor I in 1 .. 10000 loopexecute immediate 'insert into tb_test values (: n) 'using I; end loop; end;/Procedure created. scott @ ASMDB> create or replace procedure proc2 -- create the Stored procedure proc2 without binding variables. Therefore, every SQL insert statement will be hard parsed by asbeginfor I in 1 .. 10000 loopexecute immediate 'insert into tb_test values ('| I |') '; end loop; end;/Procedure created. scott @ ASMDB> exec runstats_pkg.rs_startPL/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 & gt; exec runstats_pkg.rs_stop (1000 ); run1 ran in 1769 hsecs Run2 ran in 12243 hsecs -- run2 runs at/1769 ≈ times of run1 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 -- number of hard resolution times, the former only has four STAT... callto 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 cballs 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 -- the number of LATCH queues is compared. 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% -- the number of latches used by proc2 is much greater than that of proc1, with a ratio. 13% PL/SQL procedure successfully completed.

(3). Benefits of using Bound variables

The preceding example shows that no bound variable is used, no matter the number of resolutions, the number of latches used, the queue, the allocated memory, the library cache, And the row cache are much higher than the bound variables.

Variable. Therefore, bind variables as much as possible to avoid hard parsing to generate additional system resources.

Advantages of variable binding

Reduce the hard parsing of SQL statements, thus reducing the extra overhead (CPU, Shared pool, latch) caused by hard parsing ). Second, improve programming efficiency and reduce the number of database accesses.

Disadvantages of variable binding

The optimizer ignores the histogram information and may not optimize the execution plan when generating it. SQL optimization is relatively difficult

V. Summary

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

2. The cursor_sharing parameter should weigh the pros and cons and consider the impact of using similar and force.

3. Try to use bind variables to avoid hard parsing.

What is the difference between: = and = in oracle?

Variable binding refers to using variables rather than constants in the conditions of SQL statements. For example, there are two SQL statements in the shared pool,
Select * from tab1 where col1 = 1;
Select * from tab1 where col1 = 2;
For oracle databases, this is two completely different SQL statements, which both require hard parse. Because oracle calculates the hash value of each character in the memory based on the SQL statement text, although the preceding two SQL statements have only one character, oracle uses the hash algorithm to obtain different hash addresses in the memory. Therefore, oracle considers this statement to be two completely different statements. If you rewrite the preceding SQL statement to select * from tab1 where col1 =: var1; and then query the variable var1 by assigning a value, oracle will perform hard parse for this statement for the first time, in the future, only soft parse will be performed. Assuming that a statement has been repeatedly executed for several 100,000 times, the benefits of using bind var are enormous. If bind var is not fully used for an application, it will be accompanied by severe performance problems.

Variable binding is relative to text variables. The so-called text variables refer to the SQL statement that directly writes query conditions. Such SQL statements need to be parsed repeatedly under different conditions, to bind a variable is to use a variable to replace the direct writing condition, query the bind value and pass it at runtime, and then bind it for execution. The advantage is that hard Parsing is reduced, CPU contention is reduced, and shared_pool is saved. The disadvantage is that histogram cannot be used, which is difficult for SQL optimization.

Differences between hard solution and Soft Solution

To learn more about this problem, you must first understand the soft and hard solutions. I understand it in this way. Well, this is my personal understanding, not an official one. Blackberry is a machine customized by communication companies abroad, such as Vodafone. Such a machine cannot be used directly when it comes to China. It seems that it has any network lock. To use it in China, you must unlock it first. There are two ways to unlock: soft and hard. The soft solution is to use some software to unlock the machine. This method has no effect on the machine and can be used normally. There is also a hard solution, it seems that the hardware is modified after forced dismounting (the argument is not official but does not affect understanding, huh, huh). It is a very violent means, but it is said that the hard Solution of a skilled master has little impact on the machine, however, I do not think that hard decoding experts in China have such skills. Hard decoding may cause all damage to the machine, such as innocent crashes. Therefore, there is a gap between the price of soft and hard solutions. This is like a new receng or spam plane you bought when you bought a mobile phone. There will be many unexpected problems. If you haven't bought one yet, I can recommend a seller for you. My 81 was bought from him. It took a long time and I haven't found any problems yet, the quality is quite good.
If you say so much, there may be no focus, but I hope it will be helpful for your purchase. Good luck to the landlord.