Tools and Utilities - Expert Oracle RAC Performance Diagnostics and Tuning - page 146

Figure 6-4. Top SQL cluster

Poor performance of queries could be due to several reasons, such as any of the following:

• Stale optimizer statistics : Oracle cost-based optimizer (CBO) uses the statistics collected to
determine the best execution plan. Stale optimizer statistics that do not accurately represent
the current status of the data in objects can easily mislead the optimizer to generate sub-optimal
plans. There is no easy method to determine whether optimizer statistics are up-to-date or
stale causing poor execution plans. Starting with Oracle Database 10g, the optimizer statistics
collection has been automated. A new job called GATHER_STATS_JOB is created that runs
the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. The job is automatically
scheduled to run every night.

• Missing access structures : The absence of appropriate access structures like indexes and
materialized views is a common source of poor SQL performance.

• Sub-optimal execution plan : The CBO can sometimes choose a sub-optimal execution plan for
a SQL statement. This is primarily because of incorrect estimates of some attributes of a SQL
statement such as its cost, cardinality, or predicate selectivity.
• Bad SQL : Queries using Cartesian joins or UNION ALL clauses in a SQL query makes the
execution plan really expensive and time consuming to retrieve the required rows.
As illustrated in Figure 6-4 , the SELECT statement with SQL_ID 19x1189chq3xd has the highest activity. Once the
queries to be tuned have been identified, click Schedule SQL Tuning Advisor and allow Oracle to tune the queries.
The tuning process will perform statistics analysis, access path analysis, SQL profiling, and structure analysis before
tuning the query. Oracle provides the modified query with a comparison of the execution plans before and after
tuning. If the recommended changes are acceptable, the changes can be implemented.

■ Note a more detailed discussion on sQl tuning is forthcoming in Chapter 7.

Another option available under the SQL advisory section is the SQL access advisor. This complements the
SQL tuning advisor functionality focusing on the identification of indexes, materialized views, and indexes on the
materialized views to improve the performance of the entire SQL workload.