Hard parse & soft parse

Each DDL execution requires hard parsing.

SQL parsing process

Oracle will take the following steps to process this SQL statement:

1. syntax check: Check whether the spelling of this SQL statement is correct.

2. semantic check: such as checking whether the access object in the SQL statement exists and whether the user has the corresponding permissions.

3. parsing SQL statements (prase): parses SQL statements using internal algorithms to generate parse tree and execution plan ).

4. Execute the SQL statement and return the result (execute and return)

Five steps:

1: syntax analysis

2: Permission and object check

3: Check whether there are identical items in the Shared Pool. If yes, skip step 4 and Step 5 and run the SQL statement. Then, calculate soft parse.

4: select an execution plan

5. generate an execution plan

3. Explanation:

Oracle will calculate the hash value of the passed SQL statement using the hash function, and compare it with the hash value of the existing statement in the shared pool to see if it is one-to-one. The hash value of SQL statements in the existing database can be obtained by accessing the hash_value column in the data dictionary such as V $ SQL, V $ sqlarea, and V $ sqltext.

If the hash values of SQL statements are consistent, Oracle needs to re-check the semantics of SQL statements to determine whether the values are consistent. So why does Oracle need to check the statement text again? Which of the following statements does not correspond to the hash value of the SQL statement? In fact, even if the hash value of the SQL statement has been matched, it cannot be said that these two SQL statements can be shared.

Dictionary Cache

The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. oracle accesses the data dictionary frequently during SQL statement parsing. this access is essential to the continuing operation of oracle.

The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold Dictionary data. one area is called the data dictionary cache, also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data ). the other area in memory to hold Dictionary data is the library cache. all Oracle user processes share these two caches for access to data dictionary information.

Parsing

Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle:

• Checks the statement for syntactic and semantic Validity

• Determines whether the process issuing the statement has privileges to run it

• Allocates a private SQL area for the statement

Oracle also determines whether there is an existing shared SQL area containing the parsed representation of the statement in the library cache. if so, the user process uses this parsed representation and runs the statement immediately. if not, Oracle generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.

Note the difference between an application making a parse call for a SQL statement and Oracle actually parsing the statement. A parse call by theapplication associates a SQL statement with a private SQL area. after a statement has been associated with a private SQL area, it can be run repeatedly without your application making a parse call. A parse operation by Oracle allocates a shared SQL area for a SQL statement. once a shared SQL area has been allocated for a statement, it can be run repeatedly without being reparsed.

Both parse CILS and parsing can be expensive relative to execution, so perform them as seldom as possible.