Oracle SQL Tuning Advices

1. Find SQL statements with high IO and some wait metrics that can give us more insight into what Oracle is doing behind the scenes to access the object.  

           Solution: Create indexes, force use with hints

1. Find "unused indexes".

      Solution: Enable collecting statistics on indexes

3. Find large table scans.
4. Find small full table scans and counts

      Solution:Pin those hit tables to buffer pool as KEEP

5. Find index range scans and counts   

      Solution:Pin those hit indexes to buffer pool as KEEP

6. Find index unique scans and counts    

      Solution:Pin those hit indexes to buffer pool as KEEP

7. Check for many indexes on data buffer cache 

      Solution: Adjust parameters OPTIMIZER_INDEX_COST_ADJ=20 and

      OPTIMIZER_INDEX_CACHING with the % of indexes on data buffer  

      cache

8. Analyze indexes with compute (or estimate if the you have more than 100,000 rows in your table). If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.
9. Check for skewed indexes which happen when there are lots of DML activities in tables. If  BLEVEL is higher than 3, those indexes should be re-builded.
10. Enable index monitoring feature on the database to monitor which index are rarely used or never used. Accordingly, tune or remove those indexes.
11. Detect fragmentation on database objects.
12. Identify the amount of memory needed to maintain estimated performance. Solution: DB_CACHE_ADVICE = ON
13. Find hard parsed SQL statements frequently and advice programmers to tune them.
14. Find invalidated SQL statements which cause certain hard parses, and advice programmers to tune them.
15. Find top SQL statements which use high amounts of sharable memory. Also monitor invalidation rate of those statements, and advice programmers to find a solution how those SQL statements can release those shared memories unless they are in use.
16. As many users are connected at a time and only a few of them are active (let's say that they are using a thin-thinking application), we have to make sure database is not using static PGA memory size. Setting PGA_Aggregate_Target will save a lot of memory.