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

Chapter 7
SQL Tuning
Every application, small or big, online or batch, OLTP or warehouse, all interact with the database using SQL. SQL
could be either in the form of data manipulation language (DML) through INSERT , UPDATE , and DELETE operations;
data definition language (DDL) in the form of CREATE , ALTER , and DROP; or in the form of queries where they SELECT
and retrieve data from the database. SQL operations of any kind should be efficiently written to perform optimally and
give good response times.
Performance of an SQL operation is driven by the optimizer behavior and the plan generated by the optimizer
when the SQL is executed. The behavior of the optimizer is then driven by how well the SQL is written and the
execution path that is available to perform the operation. The execution path determined by the optimizer can be
altered in several ways, such as tuning the query to make it more efficient, by using optimizer hints that will force
the exaction path in a specific way, changing database parameters that could give more resources to perform the
operation, or change the structure of the object by adding features such as database partitions. In this chapter, we look
at the execution paths and phases and identify methods of executing them and consider changing them to be more
efficient. Efficient SQL operations provide good response times, which in turn reduce the total cost of operation and
reduce database (DB )time. Along the way, we also explore some of the important parameters used for this process
and those used by the optimizer.
Before we go into the steps of writing efficient SQL and optimizing the query for performance, we try to
understand the basic SQL execution life cycle.
SQL Execution Life Cycle
Figure 7-1 illustrates the various stages or steps taken beginning with the time that a cursor is opened by the
application. A similar operation (not illustrated) is also followed in a PL/SQL procedure and when the SQL statements
are embedded inside the PL/SQL code. There are nine steps involved in an SQL statement execution. It should be
noted that only the DML statements are required to perform all these steps. DDL statements such as CREATE , ALTER ,
and DROP operations are performed in two steps, namely, CREATE and PARSE .