-- Top 10 best practices for SQL/ PL-SQL tuning
1. Avoid full table scan unless it return large number of rows. Do,nt specify hint if it is returning large number of rows It will deteriorate the performance. Oracle 10g onwards hint always high CPU resource. You can use hints wherever necessary but at the same time need to check the cost.
2. Keep the index rebuild up to date if your table is getting frequently deleted and records inserted. Ask DBA about your table DML operations. Avoid indexes on that column where total no. of uniqueness is less than 30%. Don't keep more than 5 to 6 indexes on heavily transaction tables. Remove unnecessary indexes which have not been used. Place the indexed column higher in the where clause.
Note: Index on numeric column is better way than index on char or varchar column while searching / filtering. Don't use index on XML or Clob datatypes.
3. Analyze table to get latest statistics that help CBO to estimate the cost of execution plan.
4. Avoid operators like <> , not in , like as this consumes a lot resource of server.
5. Try to use merge statement instead of multiple updates as merge is really faster.
6. Avoid LEFT OUTER / RIGHT OUTER JOINs, UNION. Use INNER JOIN and UNION ALL ( if duplicates allowed, otherwise filter via UI code).
7. If the tables are big use partitions by table. Partitions by range or partition by hash.
8. If the report contains call to the various objects such as procedure and functions club all the oracle objects into the package and give call to package. Execution is faster.
9. Use GTT ( Global temporary table) if big / huge table is used no. of times in JOINs. Same GTT can be used if any table is temporary purpose. GTT statistics is internally managed by Oracle.
10. Use DECODE and CASE - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected
1. Avoid full table scan unless it return large number of rows. Do,nt specify hint if it is returning large number of rows It will deteriorate the performance. Oracle 10g onwards hint always high CPU resource. You can use hints wherever necessary but at the same time need to check the cost.
2. Keep the index rebuild up to date if your table is getting frequently deleted and records inserted. Ask DBA about your table DML operations. Avoid indexes on that column where total no. of uniqueness is less than 30%. Don't keep more than 5 to 6 indexes on heavily transaction tables. Remove unnecessary indexes which have not been used. Place the indexed column higher in the where clause.
Note: Index on numeric column is better way than index on char or varchar column while searching / filtering. Don't use index on XML or Clob datatypes.
3. Analyze table to get latest statistics that help CBO to estimate the cost of execution plan.
4. Avoid operators like <> , not in , like as this consumes a lot resource of server.
5. Try to use merge statement instead of multiple updates as merge is really faster.
6. Avoid LEFT OUTER / RIGHT OUTER JOINs, UNION. Use INNER JOIN and UNION ALL ( if duplicates allowed, otherwise filter via UI code).
7. If the tables are big use partitions by table. Partitions by range or partition by hash.
8. If the report contains call to the various objects such as procedure and functions club all the oracle objects into the package and give call to package. Execution is faster.
9. Use GTT ( Global temporary table) if big / huge table is used no. of times in JOINs. Same GTT can be used if any table is temporary purpose. GTT statistics is internally managed by Oracle.
10. Use DECODE and CASE - Performing complex aggregations with the “decode” or "case" functions can minimize the number of times a table has to be selected
No comments:
Post a Comment