SQL Tuning - Expert Oracle RAC Performance Diagnostics and Tuning - page 203

Soft Parse
As discussed earlier, before parsing the SQL statement, a hash value is generated for the SQL statement and the value
is compared to the hash value with the statements already in the library cache. Once a matching statement is found,
to ensure that nothing has changed between the previous executions and now, Oracle may have to do a simpler parse,
depending on certain conditions. This parse is a soft parse and could be one of these kinds:

• If the user is accessing the SQL statement for the first time and finds it in memory, Oracle has

to ensure that the statement is exactly the same as the one that the user has requested and
ensure that the user has authentication to use the objects referenced in the SQL statement.

• If the user is accessing the SQL statement for the second time and finds it in the library cache,

Oracle still has to validate to ensure that the grants for the objects referenced in the SQL
statement have not changed.

The parse operation consumes memory and CPU cycles to complete the query; however, this reduces the
response time considerably.

Step 3: Describe the Results
This step in the execution life cycle is required to determine the various objects, columns, and characteristics such as
data type, length, and so forth. This step is more relevant when using dynamic queries.

Step 4: Define Query Output
Although the statement is executed and data is retrieved, appropriate memory variables are required to hold the
output information. This step takes care of defining character type, length, and so forth for the variables where data
will be retrieved and stored.

Step 5: Bind Any Variables
If bind variables are used, Oracle is required to bind them. This gives the Oracle server the location (binding by
reference) where bind values will be stored in memory. Usage of bind variables is another important factor for
efficient usage of memory and the overall performance of the system. Bind variables help in repeated execution of the
same statement. Usage of literals (alternative to bind variables) causes statements to be unique and does not allow
sharing of SQL statements. The nonshared statements consume memory and cause excessive parse operation.

Step 6: Parallelize the Statement
This step is performed only when parallelism is required and configured. When parallelism is chosen, the work of
a query is divided among a number of slave processors. If this is required for parsing or if the query is eligible for
parallel execution, it is determined during the parse step and the appropriate execution plan has to be defined.

■ Note Detailed discussion on parallel queries is in Chapter 9.