Oracle SQL Tuning Secret Tips

This chapter has provided an overview of SQL tuning in Oracle10g, with a focus on the new 10g SQL tuning features.  The main points of this chapter include:

§       The goals of SQL tuning involve verifying the best execution plan for any statements.

§       The best execution plan is either the plan that starts returning rows the fastest or the plan that executes the query with the smallest resource consumption.

§       Oracle10g now automatically collects and refreshes schema statistics using the dbms_stats package.

§       Histogram collection can now be easily automated, and some of these databases choose to put histograms on all key columns to improve the accuracy of table join order.

§       One common cause of suboptimal SQL is missing materialized view and indexes, especially function-based indexes.

§       The SQLTuning Advisor and SQLAccess Advisor provide an easy method for identifying and tuning SQL with suboptimal execution plans.

§       Oracle provides a wealth of hints to change the optimizer execution plans.

§       SQL Profiles are a great improvement over the stored outlines of the Optimizer Plan Stability.

§       The new dba_hist tables contain a wealth of historical information about historical SQL execution statistics.

§        Time-series analysis of object usage within SQL can yield important insights into holistic tuning for SQL statements.

The next chapter will introduce the new Automated Session History (ASH) tables and show how ASH can be used to quickly identify and tune resource bottlenecks.  A silver bullet is a SQL tuning technique whereby a single action may have a positive effect on many SQL statements.  The next section will delve into silver bullets as they relate to Oracle SQL tuning and show some techniques that can improve SQL performance.

Using Function-based Indexes (FBI)

In almost all cases, the use of a built-in function like to_char, decode, substr, etc. in an SQL query may cause a full-table scan of the target table. To avoid this problem, many Oracle DBAs will create corresponding indexes that make use of function-based indexes. If a corresponding function-based index matches the built-in function of the query, Oracle will be able to service the query with an index range scan thereby avoiding a potentially expensive full-table scan.

The following is a simple example. Suppose the DBA has identified a SQL statement with hundreds of full-table scans against a large table with a built-in function (BIF) in the WHERE clause of the query.  After examining the SQL, it is simple to see that it is accessing a customer by converting the customer name to uppercase using the upper BIF.

Running the explain plan utility confirms the DBA’s suspicion that the upper BIF is responsible for an unnecessary large-table full-table scan. 

OPTIONS                        OBJECT_NAME                      POSITION

------------------------------ ------------------------------ ----------

SELECT STATEMENT

                                                                       4

  NESTED LOOPS

                                                                       1

    TABLE ACCESS

FULL                           CUSTOMER                                1

    TABLE ACCESS

BY INDEX ROWID                 ORDER                                   2

      INDEX

RANGE SCAN                     CUST_NBR_IDX                            1

The table access full customer option confirms that this BIF not using the existing index on the customer_name column.  Since a matching function-based index may change the execution plan, a function-based index can be added on upper(customer_name).

It can be risky to add indexes to a table because the execution plans of many queries may change as a result. This is not a problem with a function-based index because Oracle will only use this type of index when the query uses a matching BIF.

Now, the SQL can be re-explained to show that the full-table scan has been replaced by a index range scan on the new function-based index. For this query, the execution time has been decreased from 45 seconds to less than two seconds.

OPERATION

------------------------------------------------------------------------------

OPTIONS                        OBJECT_NAME                      POSITION

------------------------------ ------------------------------ ----------

SELECT STATEMENT

                                                                       5

  NESTED LOOPS

                                                                       1

    TABLE ACCESS

BY INDEX ROWID                 CUSTOMER                                1

      INDEX

RANGE SCAN                     CUST_NBR_IDX                            1

    TABLE ACCESS

BY INDEX ROWID                 ORDER                                   2

      INDEX

RANGE SCAN                     UPPER_CUST_NAME_IDX                     1

This simple example serves to illustrate the foremost SQL tuning rule for BIFs. Whenever a BIF is used in a SQL statement, a function-based index must be created.

Using Temporary Tables

The prudent use of temporary tables can dramatically improve Oracle SQL performance. The following example from the DBA world can be used to illustrate this concept. In the query that follows, the goal is to identify all users existing within Oracle who have not been granted a role. The query could be formulated as an anti-join with a noncorrelated subquery as shown here:

This query runs in 18 seconds. These anti-joins can often be replaced with an outer join, but the use of temporary tables offers another option. Now, the same query is rewritten to utilize temporary tables by selecting the distinct values from each table.

With the addition of temporary tables to hold the intermediate results, this query runs in less than three seconds, a 6x performance increase. Again, it is not easy to quantify the reason for this speed increase, since the DBA views do not map directly to Oracle tables; however, it is clear that temporary table show promise for improving the execution speed of certain types of Oracle SQL queries.

Fixing CBO Statistics

A client had just moved their system into production and was experiencing a serious performance problem. The emergency support DBA found that the optimizer_mode =choose, and there was only one table with statistics. The DBA was running cost-based but seemed completely unaware of the necessity to analyze the schema for CBO statistics.

The trouble began when the DBA wanted to know the average row length for a table. After using a Google search to determine that the location of that information was the dba_tables  .avg_row_len column, it was determined that the values were NULL.  The DBA then went to MetaLink and learned that an analyze table command would fill in the avg_row_len column.

CBO will dynamically estimate statistics for all tables with missing statistics, and when using optimizer_mode =choose with only one table analyzed, any SQL that touches the table will be optimized as a cost-based query. In this case, a multi-step silver bullet did the trick:

When the system immediately returned to an acceptable performance level, the DBA realized the importance of providing complete and timely statistics for the CBO using the dbms_stats utility.

Changing CBO SQL Optimizer Parameters

An emergency involving an Oracle 9.0.2 client from Phoenix who was experiencing steadily degrading performance involved a large number of large-table full-table scans which were suspected to being unnecessary.  This suspicious information was found by a quick look into v$sql_plan view using the plan9i.sql script that is found earlier in this chapter.

The top SQL was extracted from v$sql and timed as-is with an index hint.  While it was unclear why the CBO was not choosing the index, the query with the index hint ran almost 20x faster.  After acting fast and running a script against v$bh  and user_indexes, the DBA discovered that approximately 65 percent of the indexes were currently inside the data buffer cache. 

Based on similar systems, the next step was to lower optimizer_index_cost_adj to a value of 20 in hopes of forcing the CBO to lower the relative costs of index access.

optimizer_index_cost_adj =20

optimizer_index_caching =65

Some parameters can be dynamically altered in database versions Oracle9i and newer.

alter system set optimizer_index_cost_adj =20 scope = pfile;

As a result of these actions, the execution plans for over 350 SQL statements were changed, and the overall system response time was cut in half.

Repairing Obsolete CBO Statistics Gathering

A client called and expressed confusion as to why their system was grinding to a halt.  There was a serious degradation in SQL performance after the implementation of partitioned tablespaces in a 16-CPU Solaris 64-bit Oracle 9.0.4 system.  The changes in the development and QA instances had been thoroughly tested.

As it turned out, analyze table and analyze index commands had been used to gather the CBO statistics.  The dbms_stats utility gathers partition-wise statistics. There was not time to pull a deep sample collection, so a dbms_stats was issued with a ten percent sample size. It is parallelized with 15 parallel processes to speed-up the statistics collection:

In less than 30 minutes, the improved CBO statistics tripled the performance of the entire database.

Removing full-table scans with Oracle Text

One serious SQL performance problem occurs when the SQL LIKE operator is used to find a string within a large Oracle table column such as VARCHAR(2000), CLOB, or BLOB:

Since standard Oracle cannot index into a large column, their LIKE queries cause full-table scans, and Oracle must examine every row in the table, even when the result set is very small.  The following problems can be caused by unnecessary full-table scans:

§       Large-table full-table scans increase the load on the disk I/O sub-system

§       Small-table full-table scans(in the data buffer cause high consistent gets and drive up CPU consumption

The Oracle*Text utility, also called Oracle ConText and Oracle Intermedia, allows parsing through a large text column and index on the words within the column.  Unlike ordinary b-tree or bitmap indexes, Oracle context ctxcat and ctxrule indexes are not updated as content is changed.  Since most standard Oracle databases will use the ctxcat index with standard relational tables, the DBA must decide on a refresh interval.

As a result, Oracle Text indexes are only useful for removing full-table scans when the tables are largely read-only and/or the end-users do not mind not having 100% search recall:

§       The target table is relatively static (e.g. nightly batch updates)

§       The end-users would not mind missing the latest row data

Oracle Text works with traditional data columns as well as with MS-Word docs and Adobe PDF files that are stored within Oracle.  Oracle Text has several index types:

§       CTXCAT Indexes: A CTXCAT index is best for smaller text fragments that must be indexed along with other standard relational data (VARCHAR2).

WHERE CATSEARCH(text_column, 'ipod')> 0;

§       CONTEXT Indexes: The CONTEXT index type is used to index large amounts of text such as Word, PDF, XML, HTML or plain text documents.

   WHERE CONTAINS(test_column, 'ipod', 1) > 0

§       CTXRULE Indexes: A CTXRULE index can be used to build document classification applications.

These types of indexes allow users to replace the old-fashioned SQL LIKE syntax with CONTAINS or CATSEARCH SQL syntax.

When the query is executed with the new index, the full-table scan is replaced with a index scan, thereby greatly reducing execution speed and improving hardware stress:

Execution Plan

---------------------------------------------------------

   0      SELECT STATEMENT Optimizer=FIRST_ROWS

   1    0   SORT (ORDER BY)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'BIGTAB'

   3    2       DOMAIN INDEX OF 'TEXT-COLUMN_IDX'

Oracle Text Index Re-synchronization

Since rebuilding an Oracle Text index with CONTEXT, CTXCAT, or CTXRULE requires a full-table scan and lots of internal parsing, it is not practical to use triggers for instantaneous index updates. 

Updating Oracle Text indexes is easy, and they can be scheduled using dbms_job or the Oracle10g dbms_scheduler utility package:  Oracle text provides a CTX_DDL package with the sync_index and optimize_index procedures:

For example, if a nightly dbms_scheduler job is created to call sync_index, the index will be refreshed, but the structure will become suboptimal over time.  Oracle recommends that the optimize_index package be used periodically to rebuild the whole index from scratch.  Index optimization can be performed in three modes: FAST; FULL; or TOKEN.

In sum, the Oracle Text indexes are great for removing unnecessary full-table scans for static Oracle tables, and they can reduce I/O by several orders of magnitude.  This will greatly improving overall SQL performance.

The following conclusion section includes a summary of the most important techniques in time-series SQL tuning.