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