SQL TUNING

Before going into how to perform SQL Tuning, let us begin with

Thumb Rules of SQL Tuning:

We must Identify the different tables involved in the SQL statement and what are the indexes involved. From the statements we must verify the tables are view names or synonyms or direct tables.

Capture complete SQL text form the V$SQLTXT and other sources.
Analyze the structures of the tables referred in the SQL table.
Definitions of any indexes (Columns, columns order), and if the indexes are unique or non-unique.
Optimizer statistics for all the segments in the table (Table statistics, Index statistics, if these statistics are active or not).
Definitions of any views referred to in SQL statement.
Repeat steps 2, 3, and 4 for all the tables referred in the view definitions found.
Let us see the execution plan of the select statement or any of the SQL statement either using EXPLAIN PLAN, V$SQL_PLAN or the TKPROF output.
Then, we will see any previous optimizer plans for that statement which is the result of the adaptive cursor sharing

Basic SQL Tuning steps:

Identify the high load or top sql statements that are responsible for a large share of application workload and system resources, by reviewing past SQL execution history available in the system.
Verify that the execution plan produced by the query optimizer fro the statements are reasonably performing.
Implement corrective actions to generate better execution plans for poorly performing SQLs.

In general, there are two reasons for the bad performing SQLs.

Bad execution plan – Optimizer can generate if the table doesn’t have related information correct like statistics, Indexes, data, Histograms etc.
Wait events – This can be blocking, IO, Buffer waits etc.,

Hence, we must first check if the bad performing SQLs is because of the bad execution plan or the wait events before we proceed on to the tuning them.

Top 10 oracle Tuning Tips:

Design and develop with performance in mind – Design or develop the code in such a way keeping in mind at the application behavior for next 5 to 10 years but not current day.
- Explicitly identify the performance in mind
- Focus on critical transaction
- Test the SQL for these transactions against simulations of production data.
- Measure performance as early as possible
- Consider prototyping critical portions of the application.
- Consider de-normalization and other performance by design features early on.
Establish a tuning environment.

Using AWR, ADDM, SQL trace, ASH, explain plans etc

Index wisely

Proper index will give us the good performance improvement. For example, If we have a column which is more suitable for the bitmap index and we are trying to create a B-Tree index, then we are unnecessarily increasing weight of the index. So we must know what is the data of the column, if the data is unique or not then decide what index have to be created.

We can consider the advanced indexing option like

Hash clusters
Bit mapped indexes
Index only tables

It is not important to have Index on all the columns of a table. But it is important to have index on the column which is mostly used in the where clauses in the application SQLs.

Reduce Parsing
- Reduce hard parsing
- Use bind variables
- Use cursor cache

Setting SESSION_CACHE_CURSOR can help application that are not re-using the cursors.

Using the Cost based Optimizer
1. Regular collection of the table statistics using the ANALYZE or DBMS_STATS command.
2. Understanding HINTS and how they can be used to influence SQL statement execution.
3. Choose the appropriate optimizer mode: FIRST_ROW is the best for the OLTP applications, ALL_ROWS is best for reporting and OLAP jobs.
Avoid the accidental table scans that can occur unintentionally are a major source of poorly performing SQLs. This may cause by
1. Missing index
2. Using “!=”,”<>” or NOT
3. Use inclusive range conditions or IN lists
4. Looking for values that are NULL
5. Use NOT NULL value with default values
6. Use functions on indexed columns instead of on unindexed columns.
7. Use “functional” indexes
Optimize necessary table scans.
1. Many times, a table scan is the only option. In that case Consider parallel query option.
2. Try to reduce size of table by
  - Adjusting PCTFREE and PCTUSED
  - Relocating infrequently used long columns or BLOBs
Rebuild when necessary to reduce high water mark
Improve caching of the table
1. Use cache hint or table property
2. Implement KEEP and RECYCLE pools.
Partition the table.
Consider the fast-full index scan over the full table scans.

Optimize joins – Think different ways of doing the same
1. Pick the best join method
  - Nested loops joins are the best for indexed joins of subsets.
  - Hash joins are best choice for “big” joins.
2. Pick best join order
3. Optimize special joins when appropriate.
Use array processing – applicable with the large amount of data. Put the data into batches so that we are not losing huge amount of data
Use PL/SQL instead of “tricky” SQL.

Possible SQL Issues:

There are many possible areas that would impact the performance of the SQL. Writing SQL statement for your data can also be reason which results in re-phrasing the SQL without compromising on the output. The optimizer may transform the SQL to the simplest way but there is possibility sometimes it would not be doing because of some factors.

Below is the list of all the problematic areas which lead to bad performance of the SQLs.

Bad SQL statement
Object statistics
System statistics
Proper indexing
Joins
Health of index
Index statistics
SQL parsing
I/O rates
Network throughput
Access path
Data scanning
System load

SQL Tuning methods and Tools:

High-load SQLs are poorly performing, resource intensive SQL statements that impact performance of an Oracle Database. The below tools can be used to identify the high-load SQL statements.