SQL Engine - Hard Parse (Library cache miss)

Oracle Database Sql Processing


Oracle Database Sql Processing

During the sql parsing (which is a step of the sql processing), if the Database (for instance Oracle) cannot reuse existing code, then it must build a new executable version of the application code (ie a cursor).

This operation is known as a hard parse, or a library cache miss in Oracle.

The database always perform a hard parse of DDL.

During the hard parse, the database accesses the library cache and data dictionary cache numerous times to check the data dictionary.

When the database accesses these areas, it uses a serialization device called a latch on required objects so that their definition does not change. Latch contention increases statement execution time and decreases concurrency.

The database must perform a hard parse at least once for every unique DML statement and performs optimization during this parse. DDL is never optimized unless it includes a DML component such as a subquery that requires optimization.

Discover More
Data System Architecture
Concurrency - Latches (System Lock)

Latches are like semaphores. Latches are used to guarantee physical consistency of data, while locks are used to assure logical consistency of data. Latches are simple, low-level system lock (serialization...
Card Puncher Data Processing
Oracle Database - Statistics - Dynamic Sampling

Optimizer statistics dynamic sampling when the statistics doesn't exist: on the table of for instance on a calculation Dynamic sampling first became available in Oracle9i Database Release 2. It is...
Oracle Database Sql Processing
SQL Engine - SQL Parser

The parser in an SQL engine parse a Sql statement. It's the first stage of SQL processing. This stage involves separating the pieces of a SQL statement into a SQL Tree where each node is a SQL token...
Oracle Database Sql Processing
SQL Engine - Execution

Execution is the last step of sql processing. The SQL engine executes each row source in the tree produced by: the row source generator (within an hard parse) or a soft parse. This step is the...
Oracle Database Sql Processing
SQL Engine - Soft parse (Library cache hit)

A soft parse is any SQL parse that is not a hard parse. If the submitted statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse...

Share this page:
Follow us:
Task Runner