SQL Query Tuning Tips

SQL Query Tuning Tips

Here are some very simple yet powerful SQL tips to remember.

• Avoid using the following:
•  Boolean operators >, <, >=, <=, is null,is not null.
•  Not in, !=
•  Like '%pattern', not exists
•  Calculations on unindexed columns or (use union instead)
•  Having (use a WHERE clause instead)
•  Do use the following:

• Enable aliases to prefix all columns

• Place indexed columns higher in the WHERE clause

• Use SQL Joins instead of using sub-queries

• Make the table with the least number of rows the driving table by making it first in the FROM clause

Other important points for SQL Tuning:

• Establish a tuning environment that reflects your production  database.

• Establish performance expectations before you begin

• Always Design and develop with performance in mind

• Create Indexes to support selective WHERE clauses and join conditions.

• Use concatenated indexes where appropriate

• Consider indexing more than you think you should, to avoid table lookups.

• Pick the best join method.

• Nested loops joins are best for indexed joins of subsets.

• Hash joins are usually the best choice for "big" joins.

• Pick the best join order.

• Pick the best "Driving" table.

• Eliminate rows as early as possible in the join order

• Use bind variables. Bind variables are key to application scalability.

• Use Oracle hints where appropriate.

• Compare performance between alternative syntax for your SQL statement.

• Consider utilizing PL/ SQL to overcome difficult SQL tuning issues.

• Consider using third party tools to make the job of SQL tuning easier.

• Never do a calculation on an Indexed column (e.g., WHERE salary*5 > :myvalue).

• Whenever possible, use the UNION statement instead of OR conditions.

• Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause.

• Always specify numeric values in numeric form and character values in character form (e.g., WHERE

emp_number = 565, WHERE emp_name = ‘Jones’).

• Avoid specifying NULL in an indexed column.

• Avoid the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full-table scan.

• Never mix data types in Oracle queries, as it will invalidate the index. If the column is numeric,

remember not to use quotes (e.g., salary = 50000). For char index columns, always use single quotes (e.g., name = ‘BURLESON’).

• Avoid using subqueries when a JOIN will do the job.

• Use the Oracle “decode” function to minimize the number of times a table has to be selected.

• If your query will return more than 20 percent of the rows in the table, a full-table scan may be better than an index scan.

• Always use table aliases when referencing columns.

• To turn off an index you do not want to use (only with a cost-based optimizer), concatenate a null string to the index column name (e.g., name||') or add zero to a numeric column name (e.g., salary+0).

With the rule-based optimizer, this allows you to manually choose the most selective index to service your query. These rules may seem simplistic but following them in a diligent manner will generally relieve more than half of the SQL tuning problems that are experienced:

Remember that Oracle’s rule-based optimizer looks at the order of table names in the FROM clause to determine the driving table. Always make sure that the last table specified in the FROM clause is the table that will return the smallest number of rows. In other words, specify multiple tables with the largest result set table specified first in the FROM clause.

SQL Query Tuning Tips

Efficient SQL statements play a very important role in maintaining the database. They not only improve the performance of the database but also help in reducing the network traffic. The following can be stated as some of the tips for tuning SQL statements used to retrieve data from the database:

1. Know your application and business data well: Before writing our SQL statements, we need to familiarize ourselves with different business data sources. We must be aware of the data volume and its distribution in the database along with thorough understanding of the business data model – such as relationships among business entities.

2. Test your queries with realistic data: After writing an SQL statement, we must test it in an environment which reflects the production database. The behavior of the SQL statement depends a lot on the environment wherein it is tested.

3. Write identical SQL statements in your applications: Use bind variables, stored procedures and packages whenever possible. The benefits of using identical SQL statements include reduced memory use on the database server and faster execution, as parsing is not done.

4. Use indexes carefully on the table: Be sure of creating the necessary indexes on the table used in the SQL statement. But creating too many of them may result in performance degradation of the server. Try to follow the below rules in creating indexes on a table:

1. Create indexes on the columns which are frequently used in the WHERE clause of the application SQL or the queries given by the end users.

2. Index those columns which are frequently used in joining tables in SQL statements.

3. Use only index columns that select small percent of rows with the same value.

4. Do not index the columns which are used with functions and operators in the WHERE clause of the SQL statement.

5. Do not index those columns which get modified frequently as the index maintenance will become an issue during execution of DML statements on the table.

6. Unique indexes are better than non-unique indexes due to better selectivity. Create unique indexes on PK columns and non-unique indexes on FK columns and the columns frequently used in the WHERE clause.

7. Create index so that the column used in the WHERE clause make up a leading portion of the index.

5. Make an indexed path available to SQL: Sometimes even after creating the indexes, they won’t get used in the execution of the query. One of the different ways of making the indexed path available to the optimizer is to use SQL hints.

6. Use Explain Plan and TKPROF wherever possible: These are the tools that come along with Oracle server and help in understanding the execution of our SQL statements. Explain Plan – helps us to know the access path the optimizer opted. TKPROF – shows the actual performance statistics of the SQL statement.

7. Understand the Optimizer: SQL statement can be executed using rule-based optimizer or cost-based optimizer. In older applications, Oracle has used rule-based approach whereas in newer applications, oracle is promoting more of cost-based approach. If you opt to use cost-based approach, then it is necessary to run ANALYZE schema regularly as doing that stores the recent database statistics in the data dictionary views which are used by the cost-based optimizer. The SQL query can tuned only if cost based approach is opted for.

8. Think globally when acting locally: Keep in mind that the modifications you do to improve the

performance of one SQL statement affects other SQL statements used by applications or other users.

9. The WHERE clause is crucial: The following can be said to be some of the cases where the WHERE

clause will not use index even if it is created on a column. The index is created on column COL1 but it

wont be used in the below illustrated examples.

1. COL1 < COL2

2. COL1 > COL2

3. COL1 <= COL2

4. COL1 >= COL2

5. COL1 is null – The index does not store null values hence when queries for null values index won’t get

used

6. COL1 is not null – The index does not store null values hence when queries for null values index won’t

get used.

7. COL1 not in (value1, value2...)

8. COL1 != expression1

9. COL1 like ‘%pattern1’ – index is not used in this case as the leading edge of the index is suppressed;

but whereas if queried as COL1 like ‘pattern1%’, then index is used as they would result in a bounded

range index scan

10. Not exists (subquery)

11. Expression1 = expression2 – All expressions, functions or calculations involving indexes column

would prohibit using the index on that column.

10. Use WHERE clause instead of HAVING clause to filter the records : Using an indexed column in

HAVING clause of the query to filter records will result in full table scan and avoid the usage on the index

on that column. Instead first filter the records using WHERE clause to filter the records which makes use

of the index on the column. Below queries illustrate the usage of index, if an index is created on DEPTID

column of EMP table.

1. Select dept_id, sum(salary) from emp group by dept_id having dept_id = 100;

2. Select dept_id, sum(salary) from emp where  dept_id = 100 group by dept_id;

In case a) Query will not use the index created on dept_id column whereas case b) query makes use of

it.

11. Specify the leading index columns: In case of a compound index, it is used in an SQL query only if the

leading column of it is used. Below queries are used to illustrate the same with an assumption of a

compound index (PART_NUM, PRODUCT_ID) existing on the table PARTS.

1. Select * from parts where part_num = 2;

2. Select * from parts where product_id = 1002;

Case a) would use the index as the column used in the query WHERE clause is a leading column of the

compound index; whereas case b) query would not use the index. If incase of case b), you want to make

use of index we can re-write the query in the following way:

Select * from party where part_num > 0 and product_id = 1002;

12. Evaluate index scan v/s full table scans: If selecting more than 15 percent of the records from a table, it is better to use/force a full table scan on the table for such query. When using an index does more harm than good to the query, we can use techniques the following to suppress the usage of

index.

1. Select /*+ full(a)*/ * from emp where salary = 5000;

A full hint forces the full table scan on the table and avoids the usage of index on salary column.

2. Select * from emp where salary + 0 = 5000;

The salary column in WHERE clause is modified to an expression which results in avoiding the usage of index on the column.

3. Select * from emp where salary = ‘5000’;

An implicit conversion on the indexed column results in avoiding the index usage.

4. Select * from emp where ss# ||’  ‘|| = ‘111-222-333’;

When the percentage of table rows accessed is 15 percent or less, an index scan will work better because it results in multiple logical reads per row accessed, whereas a full table scan can read all the rows in a block in one logical read. Thus, the performance of full table scan is better when accessing a large percentage of rows from a table.

            To illustrate this point, say the ANALYZE command is issued against the EMP table and all its indexes. Oracle generates the following statistics in the data dictionary table USER_TABLES and

USER_INDEXES:

          Table Statistics:

NUM_ROWS = 1000

BLOCKS = 100

Index Statistics:

BLEVEL = 2

AVG_LEAF_BLOCKS_PER_KEY = 1

AVG_DATA_BLOCKS_PER_KEY = 1

            Based on these statistics, the following would be the logical reads (block accessed) for different types of scans:        

  Use of index to return one row = 3

(BLEVEL+(AVG_LEAF_BLOCKS_PER_KEY - 1) + AVG_DATA_PER_KEY)

Use of index to return all rows = 3000

                                        (NUM_ROWS * Blocks accessed to return one row using index)

 Full table scan = 100(BLOCKS)

13. Use an ORDER BY clause for index scan: Oracle optimizer uses an index scan if the ORDER BY clause is used on an indexed column. The query would retrieve ROWID from index and access the table using the ROWID.

14. Know thy data: You need to know the data and its distribution in your database server in order to understand the usage of indexes on the columns and their execution. You need to keep in mind that the SQL query performance varies as the database grows and data distributions changes.

15. Know when to use large-table scans: A full table scan provides better performance when compared to an index scan on a very small table or a very large table. An index scan on a very large table may require scanning of many indexes and table blocks. When these blocks are brought to database buffer cache, they are kept as long as possible. But as these are not needed for other queries, the hit ratio of the database buffer declines resulting in performance degradation. The blocks read by a full table scan are removed from database buffer cache much earlier does not hamper the performance as an index scan on a very large table.

16.   Minimize the table access (passes): Usually by reducing the number of table passes in a SQL statement improves the performance.

     Here’s an example to illustrate the same:

            The STUDENT table has four columns named NAME, STATUS, PARENT_INCOME, and SELF_INCOME. The name is the primary key. The values of the STATUS column are 0 for independent students and 1 for dependent students.

            The following query returns the name and income for dependent as well as independent

students. It results in two passes through the STUDENT table, creates a temporary table for processing, and initiates a sort to eliminate duplicates:

          SELECT NAME, PARENT_INCOME

FROM STUDENT

WHERE STATUS = 1

UNION

SELECT NAME, SELF_INCOME

FROM STUDENT

WHERE STATUS = 0;

The same request can be fulfilled by this query, which results in only one pass through the table:

          SELECT NAME, PARENT_INCOME*STATUS + SELF_INCOME (1-STATUS)

FROM STUDENT;

17. Join tables in the proper order: The order in which the tables are joined in a multiple table join is crucial. Perform the most restrictive search first in order to filter out the maximum number of rows and only a few records are processed in further levels. You need to make sure that the driving table (master table in a master-detail join) returns the least number of rows. For cost-based optimizer, the order in which the tables appear in WHERE clause is irrelevant as it picks the best execution plan on its own. We can use the ORDERED hint to control the order in which

the tables are accessed in the execution plan.

18. Use index-only searches whenever possible: If possible, write the queries that use index-only searches. The optimizer will have to search only index and not the full table to satisfy the SQL resulting in improving the performance. For example, a compound index exists on L_NAME and F_NAME columns

in EMP table.

1. Select f_name from emp where l_name = ‘Smith’;

2. Select f_name, salary from emp where l_name = ‘Smith’;

In Case a) query, the optimizer performs an index-only search whereas in case b) query, the optimizer performs a table search in order to get the data for a column – salary which is not a part of the compound index. Hence we need to observe the column list considered in the SELECT clause as well while writing a query.

19. Redundancy is good: Provide as much information as possible in the WHERE clause of the query. If in

a WHERE clause of a query, there is COL1 = Col2 and Col1 = 10 then the optimizer infers that COL2 = 10.

But at if there is a condition like COL1 = COL2 and COL2 = COL3, then the optimizer does not infer it as

COL1 = COL3.

20. Keep it simple, stupid: Make your SQL statements as simple as possible as simple statements yield better performance than a single complex SQL statement. The lower the Explain Plan Cost of an SQL, the better the performance of the query. Hence we need to try to tune our queries to reduce the cost as much as possible.

21. You can reach the same destination in different ways: In many cases, more than one SQL can provide with the same desired results. Each query uses a different plan and performs differently. For example,

the use of MINUS operator may perform faster than the use of NOT IN or NOT EXISTS predicates. If an

SQL involves an OR in the WHERE clause, we can re-write the same by using a UNION. Hence we need to evaluate all the possible queries for a requirement and choose the best access path query.

22. Use the special columns: Make use of the special columns like ROWID and ROWNUM in your queries. ROWID searches are faster than searches performed on any columns. A ROWID is not a constant in the database; hence do not hard-coded the value in your SQLs and applications.

23. Use explicit cursors over implicit cursors: Implicit cursors are opened by Oracle for DELETE, UPDATE,

SELECT and INSERT and use an extra fetch. Explicit cursors are opened by developers using DECLARE,

PEN, FETCH and CLOSE cursor statements.

24. Explore and take advantage of the oracle parallel query option: Using the parallel query option, you can execute SQL in parallel for faster performance. This parallel query option can be used only in SMP and MPP systems containing multiple disk drivers. The Oracle database has to be configured for this feature to be used and SQLs specific for that feature should be written.

25. Reduce the network traffic and increase throughput: Using array processing and PL/SQL blocks can achieve better performance and reduce the network traffic. Array processing allows a single SQL to process multiple rows. Using arrays in an insert, we can insert 1000 rows in a table. Significance performance gain can be achieved in client/servers and batch systems using this technique. Multiple

SQL statements can cause heavy network traffic. However, if the SQL statements are in a  single PL/SQL block, the entire block can be sent to Oracle server, processed there and results returned to the application running on the client.