I have seen many people asking me to tune the SQL query developed by them. I have observed few common mistakes and few frequently asked questions while writing a SQL query. It is always recommended to look at the Explain Plan details and then proceed to tune the query but I believe a certain practice in writing SQL queries needs to be followed so that we can get the Tuned query in one shot. Even with the Explain Plan, many people find it difficult to tune the query. Note: This article is specific to ORACLE APPS SQL Performance Tuning.
Few common mistakes are as follows:
- Usage of views such as org_organization_definitions in a query will degrade the performance because some of the views contain many unnecessary tables present in it. It is always recommended to use the underlying tables present in the view to improve the performance. Keep using views as a last resort when you need the data from all the tables present in the view.
- *_all tables will degrade the performance as it contains data of all operating units. If you want to fetch the data from a particular operating unit then it is always recommended to REMOVE _all and use SYNONYM instead. Note: The responsibility from which you are going to run the program or execute the query needs to have Operating Unit set or you can use mo_global.set_policy_context(‘S’, ORG_ID); to set the operating unit.
- Using EXECUTE IMMEDIATE without BIND VARIABLES will degrade the performance as the query will be parsed multiple times. With the use of BIND VARIABLES, the query will be parsed only ONCE.
- Using functions such as SUBSTR, NVL, UPPER on an indexed column in WHERE clause will degrade the performance of the query as it removes the index from the column. Hence, it is not recommended to use any function on Indexed column in WHERE clause.
- Using DISTINCT, UNION will degrade the performance of the query. Avoid usage of DISTINCT as much as possible and use UNION ALL instead of UNION.
- Use GROUP BY clause whenever you are using AGGREGATE functions. Avoid using GROUP BY clause when AGGREGATE function is not used.
- We can also improve the performance using Materialized View. For more details, Refer Materialized View blog
Few frequently asked questions are as follows:
- Does placing a WHERE clause of a specific data(eg. WHERE id = 1) first and then writing all the other joins of the tables later will help improve the performance? The answer is NO. It doesn’t matter the sequence of joins you write in a query.
- It is time-consuming to get the Explain Plan of each query and then tune it. Is there any other shortest and simplest method? Yes. You can get the TKPROF of the entire program. It will show you not only the Explain Plan of all the queries but also the worst performing query amongst all will be shown at the top. Please click here to see the Steps to take TKPROF blog.
- Is COUNT(1) is better than COUNT(*)? The answer is NO. Both are same. It doesn’t matter which one you write
Please let me know your suggestions on this post.