Arsh Goel

• Oct 3rd, 2016

 

Please check for the statistics of the tables used in the query..if the tables used are hot tables where data volume varies significantly, the statistics for the tables can play a major role in the execution time of the query.

Prabhat Sahu

• Oct 21st, 2017

 

Troubleshoot SQL Performance or Tuning a SQL query:
The main objective of SQL Tuning is to avoid performing unnecessary work to access rows that do not affect the result. Below are few steps we can follow to tune a SQL Query:
1. As per where condition in select statement, there may not be the index in those column.
2. Check the "Driving Table" for any unnecessary "trigger or constraints" better drop those.
3. Check for Unintentional "cartesian Product".
4. Check for full table on tables, sometime its better to have full table scan in small tables.
5. Try to compose predicates using "AND" and "=" to improve SQL effeciency,
6. Avoid "Transformed Columns" in where clause
[i.e. try not to use like WHERE TO_NUMBER(SUBSTR(a.id,INSTR(b.o_no,.)-1)) = TO_NUMBER(SUBSTR(a.id,INSTR(b.o_no,.)-1)) ]
7. gather "Optimizer Statistics".
8. Check the "Join Orders" (i.e. join fewer rows to the table latter inthe join order.)
9. Restructing the indexes.
a. Remove Non-selective indexes
b. Reordering of columns in index
c. Add columns to the index to improve selectivity
10. Use DML with RETURNING clause to select and modify data with a single call.
[i.e. INSERT / UPDATE / DELETE ... RETURNING], which reduce the number of calls to the database.