PERFORMANCE TUNING TIPS AND TECHNIQUES

Below are some of the basic hygiene techniques and concepts we should keep in mind to get the maximum performance out of our query .

1. We should always add adequate predicates to our query. We should always use index columns in our query. And also try to use the index columns in our predicates.
2. If possible, select only the index columns in the SQL query.
3. We should always keep in mind the selectivity of our query. If the selectivity of a table is more than 5%, then there is no certainty if the optimizer would choose the index or not.
4. We should never perform or use arithmetic operations on the indexed columns.
5. We should avoid using the like character in the beginning  like ‘%on’. Instead we should use the reverse index. Eg where reverse(last_name) like ‘on%’.
6. Do not use functions on the indexed columns instead we should always use a query .
7. B-Tree index do not use indexed the null values and this sometimes the suppress the index usage in our queries. To avoid this we can use IS NOT NULL in where clause .Its recommended to use BITMAP Index instead of B-Tree index.
8. We should use Truncate instead of Delete (Truncate does not generate redo). Truncate makes unusable index usable again.
9. We should always check the datatypes of the columns and compare values because if they don’t match the optimizer my skip the index usage.
10. We should avoid using the having by clause because it only restricts or eliminates the rows after they are read from the memory or disc.
11. If you don’t need to use all the table in a view, we should avoid using this view in our queries.
12. We should always use materialized views, partition table .
13. We should create Partition table for queries having low selectivity.
14. Its recommended to use bulk collect as it reduces the context switching. Implicit cursor use bulk collect by default by fetching first 100 rows .
15. Using Bind variables: Bind variables are used when we execute the same query again and again but only the predicate value change. It increase the performance by decreasing the parse count .
16. Bind Variable Peeking : Bind variable peeking is a process when the optimizer waits/peeks for the first variable before deciding on the execution plan . It will generate the plan only after getting the first bind variable.
17. We should use bind variable only when the cardinality  of the values in the column is similar .
18. We should never use the bind variable if the selectivity of each variable is pretty different .
19. After 12c adaptive cursor sharing is used by default.
20. Adaptive cursor sharing automatically detects is the query needs another execution plan or can use the existing plan by using bind aware and bind sensitive cursor .