Troubleshoot SQL Performance

You have a SQL that the performance is not reliable, some days it run fast, other days, it does not, how do you troubleshoot the performance of such SQL

Showing Answers 1 - 2 of 2 Answers

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.

  Was this answer useful?  Yes

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(,INSTR(b.o_no,.)-1)) = TO_NUMBER(SUBSTR(,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.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions