SQL Process Flow

From Robs_Wiki
Jump to: navigation, search

Step 1 Statement is parsed

The user issues a statement and the statement is parsed by the database. When an application issues a SQL statement, the application makes a parse call to the database to prepare the statement for execution. The parse call opens or creates a cursor, which is a handle for the session-specific private SQL area that holds a parsed SQL statement and other processing information. The cursor and private SQL area are in the program global area (PGA).
The parse call involves 3 steps:

  • syntax check (no errors in SQL statement)
  • semantics check (do the objects actually exist)
  • shared pool check (to determine if the statement is already in the shared pool so it can skip resource-intensive steps of statement processing.) SQL statements are hashed and this hash (SQL_ID) is checked.
    • If the statement is not in the shared pool, a hard parse occurs: 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.
    • A soft parse is any 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 of code is also called a library cache hit.

Step 2 Statement is executed

During execution, the SQL engine executes each row source in the tree produced by the row source generator. This step is the only mandatory step in DML processing.