Oracle SQL Execution Steps

All Oracle SQL statements must be processed the first time that they execute (unless they are cached in the library cache). and SQL execution steps include:

  1. A syntax check – Are all keywords present "select . . . from", etc . .

  2. A semantic check against the dictionary – Are all table names spelled correctly, etc.

  3. The creation of the cost-based decision tree of possible plans

  4. The generation of the lowest cost execution plan

  5. Binding the execution plan – This is where the table–> tablespace –> datafile translation occurs.

  6. Executing the query and fetching the rows. 

Once the execution plan is created, it is stored in the library cache (part of the shared_pool_size) to facilitate re-execution.  There are two types of parses:

  • Hard parse – A new SQL statement must be parsed from scratch.  (See hard parse ratio, comparing hard parses to executes).  If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (see cursor_sharing=force).
  • Soft parse – A reentrant SQL statement where the only unique feature are host variables. (See soft parse ratio, comparing soft parses to executes).  The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that “parses SQL once and executes many times” (also see your setting for session_cached_cursors, as this effects the reentrancy of an SQL statement).  

Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.  See the cursor_sharing parameter for an easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.

For more details, read this SQL Execution Steps on BURLESON.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: