Share:|

So what does the Oracle database do when a SQL statement comes in to be processed?

 

A SQL is HARD PARSED the first time it comes into the database:

 

  • SYNTAX CHECK                      Is the SQL grammatically correct?
  • SEMANTIC CHECK                 Do the tables and columns exist? Privileges in place? Any ambiguities (e.g. same column name in two tables not specifying which one the SQL wants)

 

  • Oracle now checks if the SQL statement is found in the database.If so the next steps are skipped

 

  • COST ESTIMATION                      Estimate the Cost of the query. The Plan with the lowest cost is chosen
  • ROW SOURCE GENERATION     Row Source Generator receives the optimal plan from the Optimizer and generates an Execution Plan in a format the SQL engine can use

 

A SOFT PARSE is where the last 2 steps are skipped as the SQL is found in memory.

 

SESSION_CACHED_CURSORS

Soft parses, less expensive than hard parses, nevertheless incur a cost, that of needing the use of Shared Pool and Library Cache latches (these are serial

    operations), that can lead to performance issues in OLTP systems

To minimize the above impact session cursors of reused (used > 3 times) SQL can be stored in the Session Cursor Cache (UGA/PGA)

What is actually stored is a pointer to the location in the Library Cache of where the cursor existed when it was closed

The presence of a cursor in the Session Cursor Cache guarantees the validity of the SQL’s syntax and semantics so the first 2 steps of Parsing are skipped

Instead of searching for the cursor in the Library Cache the Server process follows the pointer and uses the cursor (if present and still valid)

One can use the above feature by setting a value to session_cached_cursors (BMC recommends 100)