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

How to analyze the connection and difference of hard parsing, soft parsing and soft parsing in Oracle database? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Oracle database hard parsing and soft parsing have the same step, but soft parsing is completely different from hard parsing and soft parsing.

I. sql parsing

Let's start with a simple sql statement execution step:

Syntax check (syntax check)

Semantic check (symantic check): whether the object exists and has permissions.

Sql parsing (parse): use internal algorithms to parse sql, generate parsing trees and execute plans.

Execute sql and return the result (execute and return)

First, take a look at the memory structure used in sql parsing-- shared pool.

Shared pool is a memory pool, which is divided into many small blocks, each of which has its own function:

Free (idle)

Library cache (library cache, cache sql statements, and execution plan corresponding to sql)

Row cache (dictionary cache-how many tables are in the library, how many users, how many columns, the names of the columns, the data type of the columns, the size of each table, and so on all belong to the database itself.)

A sql statement, after entering the database, server process will take the sql statement to the library cache in shared pool to look for it to see if the sql statement has been executed before. That is, in library cache to see if there is this sql statement and the execution plan corresponding to the sql statement. (this process calculates the hash value by using the HASH function on the passed SQL statement and compares it with the hash value of the existing statement in the shared pool to see if it corresponds one to one. The HASH value of the SQL statement in the existing database can be obtained by accessing the HASH_VALUE column query in the vsql, vsql, vsqlarea, v$sqltext and other data dictionaries.)

II. Concepts involving analysis

1. Hard analysis

Hard parsing (Hard Parse) means that when Oracle executes the target SQL, it cannot find a reusable parsing tree and execution plan in the library cache (Library Cache), but has to parse the target SQL from scratch and generate corresponding parent cursors (Parent Cursor) and child cursors (Child Cursor).

There are actually two types of hard parsing: one is that no matching parent cursor (Parent Cursor) is found in the library cache, and Oracle parses the target SQL from scratch, generates a new parent cursor and a child cursor, and hangs them in the corresponding HashBucket The other is that if a matching parent cursor is found but no matching child cursor is found, Oracle will parse the target SQL from scratch, generate a new child cursor, and hang the child cursor under the corresponding parent cursor.

Hard parsing process:

Syntax, semantics and permission checking

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

Generate an execution plan based on statistics (finding the path with the lowest cost, which is time-consuming)

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

two。 Soft analysis

Soft parsing (Soft Parse) means that when Oracle executes the target SQL, it finds the matching parent cursor (Parent Cursor) and child cursor (Child Cursor) in the Library Cache, and takes the parsing tree and execution plan stored in the child cursor directly to reuse without parsing from scratch.

Soft parsing process:

Syntax, semantics and permission checking

Execute the plan from the repository cache after the entire SQL hash.

Soft parsing saves three steps over hard parsing.

3. Soft and soft analysis

Soft soft parsing (Soft Soft Parse) means that if the value of the parameter SESSION_CACHED_CURSORS is greater than 0 and the target SQL corresponding to the session cursor is parsed and executed more than 3 times, the session cursor will be cached directly in the PGA of the current session. If the SQL is executed again, you only need to parse it, analyze the permission object, and then directly fetch the previously cached matching session cursor from the PGA of the current session. This is called soft parsing.

Soft parsing process:

To fully understand soft parsing, you must first understand the concept of cursors. When you execute SQL, you must first open the cursor, and when the execution is complete, close the cursor, which can be understood as a handle to the SQL statement.

Before performing soft parsing, soft parsing should be performed first. MOS says that a SQL statement executed three times will cache the cursor to PGA, which is always open, and when the same SQL is executed again, all the parsing processes will be skipped to fetch the execution plan directly.

Third, the experiment:

1. Environmental preparation:

Drop table test purge; alter system flush shared_pool; create table test as select * from dba_objects where 11; exec dbms_stats.gather_table_stats (ownname = > 'sys',tabname = >' test')

two。 Hard analysis

Select * from test where object_id=20; select * from test where object_id=30; select * from test where object_id=40; select * from test where object_id=50

3. Soft analysis

Var oid number; exec: oid:=20; select * from test where object_id=:oid; exec: oid:=30; select * from test where object_id=:oid; exec: oid:=40; select * from test where object_id=:oid; exec: oid:=50; select * from test where object_id=:oid

4. Soft and soft analysis

Begin for i in 1.. 4 loop execute immediate 'select * from test where object_id=:i' using i; end loop; end; /

5. Statistics

Select sql_text,s.PARSE_CALLS,loads,executions from v$sql 's where sql_text like 'select * from test where object_id%' order by 1, 2, 2, 3, 4.

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

Field explanation:

Number of times PARSE_CALLS parsed

Number of hard parsing of LOADS

Number of times EXECUTIONS is executed

On how to carry out Oracle database hard parsing, soft parsing, soft parsing connections and differences of the analysis questions shared here, I hope the above content can be of some help to you, if you still have a lot of doubts unsolved, you can follow 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.