Shulou(Shulou.com)05/31 Report--

How to understand oracle hard resolution, soft resolution, soft resolution, for this problem, this article describes the corresponding analysis and solution in detail, hoping to help more friends who want to solve this problem find a simpler and easier way.

Hard parsing and soft parsing have the same step, while soft parsing is completely different from hard parsing and soft parsing. Let's talk about the theory first, and then let's do an experiment.

Hard resolution process:

1. Syntax, semantics and permissions checking;

2. Query transformation (by applying various transformation techniques, new SQL statements are generated that are semantically equivalent, such as count(1) being converted to count(*));

3. Generate an execution plan based on statistical information (find the least-cost path, which takes time);

4. Save cursor information (execution plan) to library cache.

Soft resolution process:

1. Syntax, semantics and permissions checking;

2. Execute the plan from the library cache after hashing the entire SQL.

Soft parsing saves three steps compared to hard parsing.

Soft parsing process:

To fully understand soft parsing, you must first understand the concept of cursors. When executing SQL, you must first open cursors. After execution, you must close cursors. Cursors can be understood as a handle to SQL statements.

Before executing soft parsing, soft parsing should be performed first. MOS says that SQL statements executed three times will cache the cursor to PGA. This cursor is always open. When there is the same SQL execution, all the procedures of parsing will be skipped and the execution plan will be directly fetched.

SQL> drop table test purge;

SQL> alter system flush shared_pool;

SQL> create table test as select * from dba_objects where 11;

SQL> exec dbms_stats.gather_table_stats(user,'test');

Hard resolution:

SQL> select * from test where object_id=20;

unselected rows

SQL> select * from test where object_id=30;

unselected rows

SQL> select * from test where object_id=40;

unselected rows

SQL> select * from test where object_id=50;

unselected rows

Soft resolution: SQL> var oid number; SQL> exec :oid:=20; SQL> select * from test where object_id=:oid; unselected rows SQL> exec :oid:=30;SQL> select * from test where object_id=:oid; unselected rows SQL> exec :oid:=40;SQL> select * from test where object_id=:oid; unselected rows SQL> exec :oid:=50;SQL> select * from test where object_id=:oid; unselected rows Soft parse: SQL> begin for i in 1.. 4 loopexecute immediate 'select * from test where object_id=:i' using i;end loop;end;/SQL> col sql_text format a40SQL> select sql_text,s.PARSE_CALLS,loads,executions from v$sql swhere sql_text like 'select * from test where object_id%'order by 1,2,3,4;SQL_TEXT PARSE_CALLS LOADS EXECUTIONS---------------------------------------- ----------- ---------- ----------select * from test where object_id=20 1 1 1select * from test where object_id=30 1 1 1select * from test where object_id=40 1 1 1select * from test where object_id=50 1 1 1select * from test where object_id=:i 1 1 4select * from test where object_id=:oid 4 1 4

You can see that soft parsing is only parsed once compared to soft parsing.

Field explanation:

PARSE_CALLS Number of Parses

Number of LOADS hard resolutions

EXECUTIONS Number of executions

SQL execution process diagram is as follows:

About how to understand oracle hard analysis, soft analysis, soft analysis of the answer to the problem shared here, I hope the above content can be of some help to everyone, if you still have a lot of doubts not solved, you can pay attention to the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.