32 Tips for Oracle SQL Query Writing and Performance Tuning

I am working with Oracle SQL from day when I joined IT industry and I have been fascinated by number of things I have learned about SQL, different databases and its usage over the years.

In early phase of my career, I learned that writing SQL queries was not just about writing but writing so it can run efficiently. Performance of SQL queries are critical to any application that is built to use Oracle and I say Oracle because this blog is specifically for Oracle SQL.

The following is a list of some tips which can be used as guideline to write and review custom SQL queries. This is by no means an exhaustive list to get the best tuning results but can serve as a ready reference to avoid the common pitfalls while working with Oracle SQL:

1. Do not use the set operator UNION if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.

2. Select ONLY those columns in a query which are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.

3. Do not use the keyword DISTINCT if the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.

4. If it is required to use a composite index, try to use the “Leading” column in the “WHERE” clause. Though Index skip scan is possible, it incurs extra cost in creating virtual indexes and may not be always possible depending on the cardinality of the leading columns.

5. There should not be any Cartesian product in the query unless there is a definite requirement to do so. I know this is a silly point but we all have done this mistake at one point 🙂

6. Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.

7. SQL statements should be formatted consistently (e.g the keywords should be in CAPS only) to aid readability. Now, this is not a performance tip really. However, it’s important and part of the practices.

8. If possible use bind variables instead of constant/literal values in the predicate filter conditions to reduce repeated parsing of the same statement.

9. Use meaningful aliases for tables/views

10. When writing sub-queries make use of the EXISTS operator where possible as Oracle knows that once a match has been found it can stop and avoid a full table scan (it does a SEMI JOIN).

11. If the selective predicate is in the sub query, then use IN.

12. If the selective predicate is in the parent query, then use EXISTS.

13. Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function (e.g NVL), consider creating a function based index.

14. Try to use an index if less than 5% of the data needs to be accessed from a data set. The exception is a small table (a few hundred rows) which is usually best accessed through a FULL table scan irrespective of the percentage of data required.

15. Use equi-joins whenever possible, they improve SQL efficiency

16. Avoid the following kinds of complex expressions:

    • NVL (col1,-999) = ….
    • TO_DATE(), TO_NUMBER(), and so on

These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method

17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator

18. Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data

19. Querying from a view requires all tables from the view to be accessed for the data to be returned. If that is not required, then do not use the view. Instead, use the base table(s), or if necessary, define a new view.

20. While querying on a partitioned table try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning.

21. Consider using the PARALLEL hint (only when additional resources can be allocated) while accessing large data sets.

22. Avoid doing an ORDER BY on a large data set especially if the response time is important.

23. Consider changing the OPTIMIZER MODE to FIRST_ROWS(n) if the response time is important. The default is ALL_ROWS which gives better throughput.

24. Use CASE statements instead of DECODE (especially where nested DECODEs are involved) because they increase the readability of the query immensely.

25. Do not use HINTS unless the performance gains clear.

26. Check if the statistics for the objects used in the query are up to date. If not, use the DBMS_STATS package to collect the same.

27. It is always good to understand the data both functionally and it’s diversity and volume in order to tune the query. Selectivity (predicate) and Cardinality (skew) factors have a big impact on query plan. Use of Statistics and Histograms can drive the query towards a better plan.

28. Read explain plan and try to make largest restriction (filter) as the driving site for the query, followed by the next largest, this will minimize the time spent on I/O and execution in subsequent phases of the plan.

29. If Query requires quick response rather than good throughput is the objective, try to avoid sorts (group by, order by, etc.). For good throughput, optimizer mode should be set to ALL ROWS.

30. Queries tend to perform worse as they age due to volume increase, structural changes in the database and application, upgrades etc. Use Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) to better understand change in execution plan and throughput of top queries over a period of time.

31. SQL Tuning Advisor and SQL Access Advisor can be used for system advice on tuning specific SQL and their join and access paths, however, advice generated by these tools may not be always applicable (point 28).

32. SQL Access paths for joins are an component determining query execution time. Hash Joins are preferable when 2 large tables need to be joined. Nested loops make work better when a large table is joined with a small table.

Disclaimer: Points listed above are only pointers and may not work under every circumstance. This check list can be used as a reference while fixing performance problems in the Oracle Database.

Suggested further readings

  1. Materialized Views
  2. Advanced Replication
  3. Change Data Capture (Asynchronous)
  4. Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM).
  5. Partitioning strategies.

Now it’s turn if have any more tips which you have used then do add them in comment section… Your feedback is very valuable and it would be useful for other viewers too.

By |2016-10-31T14:21:27+00:00May 8th, 2015|Oracle|32 Comments

About the Author:

32 Comments

  1. Tomaz July 21, 2015 at 1:22 pm - Reply

    This is a really helpful article! For SQL optimization I would also recommend to use script sql tuning.

  2. Arjun August 31, 2015 at 3:58 pm - Reply

    Very helpful information Ranjan. This saves lot of time for developers by checking these points.

  3. Arun October 13, 2015 at 9:46 am - Reply

    thanks rajan

  4. vsn November 3, 2015 at 5:40 pm - Reply

    could you please provide test cases of tuning different sql statements on a monthly basis atleast……….#

  5. Varun February 5, 2016 at 9:24 am - Reply

    Thanks a lot for this.This helped me tune my query from 30 mins to 10 mins.!

  6. tiffany jewelry February 16, 2016 at 12:12 pm - Reply

    Everyone loves genuinely when folks get together because well share ideas. immensely important siteand even keep it up-

  7. Anubhav March 17, 2016 at 2:20 pm - Reply

    6. Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.

    I don’t get this.. Please can anyone explain?

    • Kumar Gauraw March 17, 2016 at 10:18 pm - Reply

      Anubhav,

      To understand that, here is an example:

      Consider Table A and B both joined with a common key while they have their own independent fields (Say Name, City in table A and Age, Salary in B).

      Now, the select query could be written like this:

      select Name, City, Age, Salary from A JOIN B on A.ID=B.ID

      However, it is recommended that you write the query like this:

      select A.Name, A.City, B.Age, B.Salary from A JOIN B on A.ID=B.ID

      Using the later approach, we stop Oracle from doing guess work. Instead, it knows instantly which column belongs to which table cutting short the time it needs to take care to figure things out by itself.

      Hope this helps?

      Thanks,
      Kumar

      • Nihal kumar October 24, 2017 at 5:18 pm - Reply

        Hi Kumar ,
        Now I am working as Informatica developer.but I want next job in Hadoop .can you tell me how I start it

  8. Prasanth April 26, 2016 at 12:39 pm - Reply

    Very Useful Tips ***

  9. Raghu May 27, 2016 at 6:46 pm - Reply

    Information is helpful when we are facing an performance issues.

  10. Hosting May 29, 2016 at 6:57 pm - Reply

    Because this type of index creation always involves reading the old index structure and performing a large sort operation, Oracle is able to dedicate numerous independent processes to simultaneously read the base index and collect the keys for the new index structure. Now, we have saved the best for last. The final approach parallelized Oracle queries can be used by all e-commerce databases to improve performance.

  11. Ujwala July 4, 2016 at 7:02 am - Reply

    Very nice and helpful article

  12. Vinayaga July 12, 2016 at 4:51 pm - Reply

    Very Helpful for these tips. Thanks for sharing

  13. shivani September 1, 2016 at 7:36 pm - Reply

    very helpful article provided in a very interesting way..Thanks!

  14. Mahesh Pandey September 30, 2016 at 10:35 pm - Reply

    A helpful guide, would have been better if you can share an example for each statement.

    Kindly share example for each and every statement.

  15. Ramasubramani October 10, 2016 at 9:29 am - Reply

    Excellent post. Very Useful. Thanks.

  16. Shan December 27, 2016 at 2:02 pm - Reply

    Is there any visual tool, which will suggest the required tuning like indexing, join conditions etc

  17. Shamed H February 8, 2017 at 1:07 pm - Reply

    Good tips ….Thanks for sharing

    But , The below point seems to have more clarity.I believe, Oracle provide better performance for single query.

    “17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator”

  18. ilteris mutlu April 13, 2017 at 11:25 am - Reply

    Nice articicle, thank you. Would you mind to explain 11 and 12 with one each. I couldnt distinguish.
    I prefer EXISTS as soon as it is possible.

  19. Selva May 30, 2017 at 11:56 am - Reply

    Nice Tips. Please share whatever you know and the latest tuning tips.

    Thank you so much

  20. Amit July 25, 2017 at 11:25 am - Reply

    Use driving_site hnts while accession data over DB link.

  21. basant August 14, 2017 at 7:40 pm - Reply

    thanks bro….

  22. Madhavi November 7, 2017 at 12:04 pm - Reply

    Nice Article . Very useful. thank you.
    sometimes, Oracle Autotrace option can be used for single query tuning purpose.
    check out blog post , which has information on oracle autotrace feature
    http://www.dbvidya.com/oracle-performance-tuning/

  23. venu December 1, 2017 at 12:20 pm - Reply

    table A has billions of data and table B have very less amount of records. we are fetching union all of A and B using UNION ALL. when we do sort by on top of view query, it took a long time to fetch the records do u have any idea how we can improve the performance?

  24. rituraj raman April 10, 2018 at 4:36 am - Reply

    Can any one suggest me when we have to use union operator ,any minimum records count is their.
    If query fetch 50,000 rows can we use union operator.
    Suggest me if any record count restrictions.

  25. shweta April 13, 2018 at 6:12 am - Reply

    hello, This article clear lots of confusion. Bitmap gives a lot of flexibility. B-trees don’t have this luxury. we can’t just plonk one on top of the other to find what you’re looking for. While Oracle Database can combine B-trees (via a “bitmap conversion”), this is relatively expensive. In general to get the same performance as the three bitmaps, we need to place all three columns in a single index. This affects how reusable an index is, which we’ll come to later.

  26. […] 32 Tips for Oracle SQL Query Writing and Performance Tuning […]

  27. Monari Denis May 4, 2018 at 9:24 pm - Reply

    great list!
    33. (additional to 17) you may want to use a GTT as temporary storage to create a multi step set of query, running against partial data sets. From 12c you can benefit from non shared statistics on GTT and temporary undo to achieve really high concurrency and performance.
    34. in your ETL you may want to exploit dynamic sampling 11 to avoid run dbms_stats on temporary objects and save time.

  28. Ranvir Thakor June 1, 2018 at 4:53 pm - Reply

    with respect to performance improvement try to identified as base table from multiple table in join where set of records are low in number . make this table as base table for further joining which can reduced the query execution timings.

  29. Jairo Suarez July 26, 2018 at 1:59 am - Reply

    Hi Ranjan. I wonder if I could authorize me to write this information in Spanish and put in my Spanish Blog. Thanks so much.

  30. J Chui August 8, 2018 at 12:53 pm - Reply

    Quite complete!

Leave A Comment