SQL Tuning

An important fact of database system performance tuning is the tuning of SQL statements. SQL tuning involves three basic steps:

·         Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources.

·         Verifying that the execution plans produced by the query optimizer for these statements perform reasonably.

·         Implementing corrective actions to generate better execution plans for poorly performing SQL statements.

Why Tuning?

The objective of tuning a system is either to reduce the response time for end users of the system, or to reduce the resources used to process the same work. These objectives can be accomplish in several ways:

·         Reduce the Workload

·         Parallelize the Workload

                Approaches For SQL Tuning

• Check the explain plan to identify the bottleneck
• Check and make sure object stats are up to date
• Analyze the query for proper joins
• Check whether indexes created over the joining conditions
• Order of the tables in the from class
• Try to minimize the sorting operation like DISTINCT,UNION,GROUP BY

Explain Plan

• Explain Plan is the first thing you have to check for tuning a sql query.
• EXPLAIN PLAN displays execution plans chosen by the Oracle optimizer.
•  A statement's execution plan is the sequence of operations Oracle performs to run the statement.
• Below are things we can get from explain plan:

·         Ordering of the tables    

·         Access methods

·         Join method        

·         Data operations(filter , sort  etc.)

·         Cost and cardinality      

·         Partitioning & Parallel execution

Why Indexes Aren't Used

The presence of an index on a column does not guarantee it will be used. The following is a small list of factors that will prevent an index from being used.

·         The optimizer decides it would be more efficient not to use the index. If your query is returning the majority of the data in a table, then a full table scan is probably going to be the most efficient way to access the table.

·         You perform mathematical operations on the indexed column

Position of Joins in the WHERE Clause

• ORACLE parser always processes table names from right to left, so the table name you specify last (driving table) is actually the first table processed.
• If you specify more than one table in a FROM clause of a SELECT statement, you must choose the table containing the lowest number of rows as the driving table.
• When ORACLE processes multiple tables, it uses an internal sort/merge procedure to join those tables.
• First, it scans and sorts the first table (the one specified last in the FROM clause).
• Next, it scans the second table (the one prior to the last in the FROM clause) and merges all of the rows retrieved from the second table with those retrieved from the first table.
• If three tables are being joined, select the intersection table as the driving table. The intersection table is the table that has many tables dependent on it.

Tuning Tips

• Using UNION ALL instead of UNION:   The SORT operation is very expensive in terms of CPU consumption.   The UNION operation sorts the result set to eliminate any rows, which are within the sub-queries.   UNION ALL includes duplicate rows and does not require a sort. Unless you require that these duplicate rows be eliminated, use UNION ALL
• Using NOT EXISTS for NOT IN over indexed columns
• Avoid calculations on indexed columns
• Avoid NOT on indexed columns
• Using OR on a indexed column will not use Index and go for a full table scan