Tuning SQL Queries

Effective optimization of SQL queries must start from overall optimization of Oracle system (if it has not been done before). Otherwise, it may force you to rewrite tuned execution plans later. You can find some hints on system-level Oracle tuning in this article:

https://www.convert-in.com/docs/oracle/system_optimization.htm

Oracle SQL tuning is a very complicated procedure having primary goals to improve the execution plan to fetch the rows with the minimal number of reads from the database. Digging deeper into details, those goals can be reached through the following steps:

• Validate index usage. Sometimes there is a choice of indexes, so the database specialist must ensure that Oracle is using the proper index.
• Identify and remove unnecessary full-table scans. Unnecessary full-table scans result in excessive I/O operations, potentially hampering the overall performance of your database. Adding indexes is the most common workaround for unnecessary full-table scans. You can apply standard b-tree indexes to tables, while bitmapped and function-based indexes offer additional options for eliminating such scans. Also, it possible to force unnecessary full-table scan to utilize index by adding an index hint to the SQL statement.
• Optimize caching for small-table full-table scans. When a full-table scan proves to be the most efficient access method, it is extremely important for the database administrator to guarantee the availability of a dedicated data buffer for the rows. For small tables this can be achieved by forcing it into the KEEP pool.
• Add missing indexes and materialized views. Oracle diagnostic tool SQL Access Advisor can identify missing indexes and materialized views. It is a good practice to constantly monitor the database workload using SQL Access Advisor and to follow its recommendations by adding suggested indexes and materialized views.

Oracle SQL Optimizer

The optimizer determines the best way to execute a SQL query based on the statement structure, statistical information about the involved database objects and all the relevant execution features.

The Oracle optimizer must make three major decisions during the optimization of a SQL statement: method to access the data, join method (nested loops, hash joins, etc) and tables join order.

The cost-based optimizer relies on statistics gathered from the table through the 'analyze table' command. These statistics provide Oracle with valuable information about the tables, enabling it to make intelligent decisions about the most efficient approach to handle SQL queries. However, it's important to understand that the cost-based optimizer may not always choose the optimal path for query speed. Although ongoing enhancements are being made to the cost-based optimizer, there are still situations where the rule-based optimizer can produce faster Oracle queries.

By default, Oracle optimizer uses 'all_rows' mode that is tailored to minimize usage of computational resource and dur to this fact it prioritizes full-table scans over index access. On the other hand, index access ('first_rows_n' optimizer mode) may cause extra I/O overhead, but it allows the originating query to return rows faster.

If not all tables involved in the query contain CBO statistics, Oracle estimates statistics at runtime that may essentially decrease performance of the query.

Since SQL statements typically fetch only a small subset of rows from a table, Oracle optimizers are designed to identify required indexes and use to reduce excessive I/O whenever it is possible. Nevertheless, if a query's statement is inefficient, the cost-based optimizer may become uncertain about the optimal data access path and occasionally choose a full-table scan. Consequently, the best practice for Oracle database administrators to investigate SQL statements and actively search for opportunities to avoid full-table scans.

Oracle SQL Tuning Plan

Step 1. Identify high-impact SQL. All SQL statements must be sorted number of executions descending and tuned in this order. The frequency of query usage can be located in stats$sql_summary.EXECUTIONS. Table stats$sql_summary and view v$sqlarea contain the following important information about SQL statements:

• v$sqlarea.ROWS_PROCESSED - number of rows processed on behalf of this SQL statement. The more rows are processed, the high I/O and impact on the TEMP tablespace.
• stats$sql_summary.BUFFER_GETS - big number of buffer gets indicates a high resource demanding query.
• stats$sql_summary.DISK_READS - big number of disk reads indicates a query that is causing excessive I/O.
• v$sqlarea.CPU_TIME - helps to find SQL queries that requires the most processor resources.
• stats$sql_summary.SORTS - sorts may dramatically decrease performance of query, especially when done on a disk.

Step 2. Determine the execution plan. After each SQL statement is identified, next step is to 'explain' it in order to determine the execution plan. A lot of third-party tools can show the execution plan for SQL statements. The most straightforward option to determine the execution plan for SQL query is using explain plan utility provided by Oracle. It allows Oracle DBA to parse the statement and explore the execution class path without actually executing the query.

The result is arranged as a special 'plan' table containing the following fields:

• operation - type of the performed access (table access, table merge, sort or index operation)
• options - operation modifiers specifying a full table, a range table, or a join
• object_name - table name used by the query component
• Process ID - identifier of the query component
• Parent_ID - parent of the query component (multiple components may have the same parent)

Although plan table helps to determine the access path to the data, this information may be not enough. The SQL optimizer knows number of rows and presence of indexes on fields for each table, but it is not aware about important factors of data distribution (for example, the number of rows that are expected from each query component).

Step 3. Tune the SQL statement. For SQL statements with less-than-optimal execution plans, tuning can be performed through one of the following methods:

• Adding SQL 'hints' to modify the execution plan. Hint is an instruction for the optimizer telling information about the data it does not know.
• Re-write SQL with Global Temporary Tables used to store some temporary data that is too complicated to complete in a single pass.
• Replace SQL with a call to PL/SQL package containing a stored procedure that runs the SQL statement. For some queries this adjustment can result up to 20x performance improvement.