Before starting SQL tuning you must optimize or adjust following parameters otherwise changes to SQL tuning might not work as you expected.
|Server Optimization||Tune disk and network I/O subsystem (RAID, bandwidth, network).|
|Optimize instance||Optimize database instance by adjusting OS parameters (db_file_multiblock_read_count, cpu_count, &etc) and database parameters (db_block_size, db_cache_size) before SQL tuning.|
|Optimizer statistics||Optimizer collects and store statistics and distribution of your data. Enable optimizer statistics to learn more about your data to make intelligent execution plans.|
|Optimizer parameters||Adjust optimizer parameters optimizer_index_cost_adj, optimizer_mode, and optimizer_index_caching|
|Materialized views and indexes||Create missing materialized views and indexes on the recommendations of SQLAccess advisor.|
Once the environment, instance, and objects have been tuned, the you can focus on tuning the individual SQL statements.
Plan for Oracle SQL tuning
You must first select SQL statements by their amount of activity by looking into the Oracle library cache.
Step-1 Identify high-impact SQL
The SQL statements will be ranked according the number of executions and will be tuned in this order. You can identify high-impact SQL by using columns of the v$sqlarea view and the stats $sql_summary or the dba_hist_sql_summary
|Rows processed||Large number of rows will have high I/O|
|Buffer gets||High buffer indicates a resource-intensive query|
|Memory KB||Indicates statements that are using in-memory table joins|
|Disk reads||High disk reads indicate a query that is using excessive I/O|
|CPU secs||SQL statements that use the most processor resources|
|Sorts||Sorts can be a huge slowdown, especially if done on a disk in the TEMP tablespace|
|Executions||The more frequently executed SQL statements should be tuned first|
Step-2 Determine the execution plan for SQL
The common way of get the execution plan for a SQL statement is to use Oracle's explain plan utility and you can ask Oracle to parse the statement and display the execution class path without actually executing the SQL tatement.
Step-3 Tune the SQL statement
SQL statements that possess a non-optimal execution plan, you can tune the SQL statemnt by using one of the following methods:
- Adding SQL “hints” to modify the execution plan
- Re-write SQL with Global Temporary Tables
- Rewriting the SQL in PL/SQL. The SQL can be replaced with a call to a PL/SQL package that contained a stored procedure to perform the query.
Using hints to tune Oracle SQL
A hint is a directive that is added to the SQL statement to modify the access path for a SQL query. Following are the some most common hints to improve tuning:
- Mode hints: first_rows_10, first_rows_100
- Oracle leading and ordered hints Also see how to tune table join order with histograms
- Dynamic sampling: dynamic_sampling
- Oracle SQL undocumented tuning hints - Guru's only
- The cardinality hint
Self-order the table joins - If you find that Oracle is joining the tables together in a sub-optimal order, you can use the ORDERED hint to force the tables to be joined in the order that they appear in the FROM clause.
Tips for writing more efficient SQL
Some general rules for writing efficient SQL in Oracle regardless of the optimizer that is chosen.
Rewrite complex subqueries with temporary tables- Oracle created the global temporary table (GTT) and the SQL WITH operator to help divide-and-conquer complex SQL sub-queries (especially those with with WHERE clause subqueries, SELECT clause scalar subqueries and FROM clause in-line views). Tuning SQL with temporary tables (and materializations in the WITH clause) can result in amazing performance improvements.
Use minus instead of EXISTS subqueries- Using the minus operator instead of NOT IN and NOT Exists will result in a faster execution plan.
Use SQL analytic functions- The Oracle analytic functions can do multiple aggregations (e.g. rollup by cube) with a single pass through the tables, making them very fast for reporting SQL.
Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins- In many cases of NOT queries (but ONLY where a column is defined as NULL), you can re-write the uncorrelated subqueries into outer joins with IS NULL tests. Note that this is a non-correlated sub-query, but it could be re-written as an outer join.
SELECT book_key FROM book
Below we combine the outer join with a NULL test in the WHERE clause without using a sub-query, giving a faster execution plan.
SELECT b.book_key FROM book b, sales s
Index your NULL values- If you have SQL that frequently tests for NULL, consider creating an index on NULL values. To get around the optimization of SQL queries that choose NULL column values (i.e. where emp_name IS NULL), we can create a function-based index using the null value built-in SQL function to index only on the NULL columns.
Leave column names alone- Never do a calculation on an indexed column unless you have a matching function-based index (a.k.a. FBI). Better yet, re-design the schema so that common where clause predicates do not need transformation with a BIF:1
WHERE salary*5 > :myvalue
Avoid the use of NOT IN or HAVING- Instead, a NOT EXISTS subquery may run faster (when appropriate).
Avoid the LIKE predicate- Always replace a "like" with an equality (=), when appropriate.
Never mix data types- If a WHERE clause column predicate is numeric, do not to use quotes. For char index columns, always use quotes. There are mixed data type predicates:
WHERE cust_nbr = "123"
Use decode and case - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected.
Don't fear full-table scans - Not all OLTP queries are optimal when they uses indexes. If your query will return a large percentage of the table rows, a full-table scan may be faster than an index scan. This depends on many factors, including your configuration (values for db_file_multiblock_read_count, db_block_size), query parallelism and the number of table/index blocks in the buffer cache.
Use table aliases - Always use table aliases when referencing columns.