SQL Tuning

SQL Tuning is an approach of optimizing the database performance by various means with more focus on SQL query optimization .

SQL query optimization means the shortest way we can execute the query in the database.

There can be various issues with SQL queries:

  • Poor indexing strategies i.e. too much of indexing
  • Inefficient query resulting in unnecessary large table scans

SQL Queries can be tuned by following methods:

  • Monitor performance of query using Oracle Execution plan
  • Don’t use wild cards in your select queries which does full table scan: E.g. SELECT * FROM TABLE_NAME.
  • Small table full scan can be cached and kept in a database buffer available for rows using ‘KEEP pool’
  • For subqueries, using ‘IF EXIST’ results in stopping of unnecessary table scan after it is found.
  • Avoid using DISTINCT keyword wherever possible as it adds an extra SORT operation which increase I/O count