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

Why Tune?
Irrespective of having high-performing hardware, a high-performing storage subsystem, or the abundance of
resources on each of the nodes in the cluster, RAC cannot perform magic to fix poor performing queries. Actually,
poor performing queries can be a serious issue when you move from a single instance configuration to a clustered
configuration. In certain cases, a negative impact on the overall performance of the system could be noticed.
A top activity chart (illustrated in Figure 7-3 ) in EM could be a good starting point to determine high resource
intensive queries and candidates for optimization.

Figure 7-3. EM—Top activity

Efficiency of the system depends on the efficiency of the queries that run and access the data. Response time
depends on how well the database is laid out and how well the queries are written to efficiently access the data.
Efficient queries and well-managed database environments provide good response times. So how do you make those
queries more efficient, or how do you tune the queries? Before stepping into this subject, we try to understand how
the optimizer works.

Optimizer
Oracle's cost-based optimizer or CBO, or simply the optimizer, is an Oracle kernel component that helps determine
the most efficient path to execute the SQL statement and access the data sets. The CBO determines the most efficient
path of data excess based on statistics available and scientifically arrives at an access path by creating execution
plans. Such plans are stored in the SGA for reuse by other users who execute the same SQL statement. The optimizer
performs the following operations:

• Evaluates expressions and conditions contained in SQL statements

• Transforms SQL statements to make it more efficient

• Determines the goal of the optimization

• Generates a set of plans for the SQL statement based on the available access paths

• Determines the right join order

For every plan generated, the optimizer will estimate the cost based on statistics available in the data dictionary
for the data distribution, number of rows, and storage characteristics such as the number of tables accessed, indexes
available, and distribution of data into various data partitions.
The cost is an estimated value proportional to the expected resource needed to execute the statement with
a particular plan. The optimizer calculates the cost of the access paths and the join order based on the estimated
computer resources, which includes I/O, CPU, and memory.