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

Step 7: Execute the Statement
This step executes the SQL statement and retrieves rows from disk or memory and places the values into the bind
variables. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being
changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked
from use by other users of the database until the next COMMIT , ROLLBACK , or SAVEPOINT for the transaction. This
protects the data from being changed by other sessions while it is being modified. This ensures data integrity.
When data is modified, before and after images describing the changes are written to the redo log buffer and the
undo segments.

Step 8: Fetch Rows
From the bind variables defined in Step 5, the values that are obtained as a result of the statement execution, and
the placement of values in the bind variables rows, are selected and ordered (if requested by the query), and each
successive fetch retrieves another row of the result until the last row has been fetched. These results are returned into
a table format to the calling interface.

Step 9: Close the Cursor
Once the data is returned to the calling interface, the cursor is closed.
Iteration of Steps 1 thru 6 will help generate the execution plan for the statement. The execution plan is then
stored in the library cache and reused when another session executes the same statement.
When tuning an SQL statement, it's this process that we are trying to improve so response time to get the data
back to the user is improved.

Capturing Execution Times
One of the first steps in looking at the performance of a query is to determine its execution time. During this analysis,
it is important to understand where most of the time was spent by the session. What step in the query plan took
the most time? An important parameter that drives keeping the time is the TIMED_STATISTICS and a few other
parameters:

TIMED_STATISTICS •

TIMED_OS_STATISTICS

•

STATISTICS_LEVEL •

OPTIMIZER_USE_PENDING_STATISTICS (New in Oracle Database 12c, Version 1) •

TIMED_STATISTICS
This is a very important parameter; turning this off will disable most of the time-based statistics gathering useful
information for a scientific analysis. For example, when generating a 10046 trace, disabling this parameter will not
show time statistics in the report. Check if the parameter has been enabled. If not, this needs to be turned on. Setting
the STATISTICS_LEVEL parameter to TYPICAL , for example, automatically enables TIMED_STATISTICS to TRUE .