Imagine the following situation.
You designed and coded your application, everything is working perfectly, your end customer is pleased, everything is firing on all cylinders… As time passes and your business starts growing, all of a sudden, the monthly report starts to arriving late – information that was needed in the morning starts arriving in the afternoon, your batch processes become slower and slower.
Your application is now required to function on an enterprise level. The amounts of data being processed and transformed every day are huge, so everything is much slower than before. By now it’s obvious that it’s time for query optimization because the performance of SQL queries is critical to any application that is built to use Oracle according to XsPDF.
So, this blog is meant specifically for Oracle SQL
TIP#1 – Use only what you need
It sounds very logical when you read it, but I’m sure we all had times when it was just easier to write SELECT s.* FROM sales s; then to list all fields we need from some Sales table. Don’t be like that. Unused columns that are needlessly fetched increase I/O on your database and add to network traffic.
TIP#2 – Avoid the Cartesian product
It’s obvious we’re still warming up, because this one is self-evident, but throughout many years in development, I the had urge to stress this one just on the safe side – we all get this. So here it comes one more time, avoid using the Cartesian product. Except when one of your two tables that you join consist of one row 😊. I know it sounds silly, but in everyday development you see everything.
TIP#3 – Avoid modifying indexed columns with functions
Do not modify indexed columns using Oracle built-in functions like TO_DATE, TRIM, LOWER etc. Using those functions on an indexed column will disable the optimizer from identifying the index. Try to use a function on the other side of condition and in the end, if it is inevitable, try to use a column through a function (e.g. NVL), consider creating a function-based index.
TIP#4 – Use bind variables
The optimizer spends a lot of time parsing your query. As stated in Dave's Technical Notes, the use of literal values will cause many unique statements to be cached because each literal value is treated as different. Furthermore, Dave's Technical Notes mentioned that this will result in the usage of more space in the Share Pool. With the use of bind variables, the statement remains the same, there is only one statement cached as opposed to many. So, to avoid multiple parsing of the same query and to s(h)ave some time (off), use the bind variable.
TIP#5 – Avoid using OR in join conditions
It’s hard to explain why, but some people have a habit of joining two tables one way OR another. This results in a decrease of the queries’ speed for at least a factor of 2. It’s a far better solution to use UNION ALL operator and join two tables.
TIP#6 – Use UNION ALL instead UNION
Well, since we have already mentioned the UNION ALL operator, try using it instead of the UNION operator. UNION ALL is much faster than UNION because UNION ALL does not care about duplicates while UNION checks for duplicate rows.
TIP#7 – IN VS. EXIST
„If the selective predicate is in the sub query, then use IN.“ Oracle Boss
„If the selective predicate is in the parent query, then use EXISTS.“ Data Integration Ninjas
If you remember the times before Oracle 10g, you know how crucial it was to determine when to use EXIST instead of IN. Inappropriate usage of either one of those resulted in wildly wrong execution plans. If you are stuck on a legacy enterprise piece of application that uses Oracle DB older than 10g, then I’m sorry, but nowadays Oracle DB got “smarter” and it doesn’t care what you use.
TIP#8 – Consider using the PARALLEL hint (on a large amount of data)
Even your home desktop PC nowadays has a processor that contains multiple cores, why don’t you try to utilize it for shaving some time off our queries. Before applying this strategy, consult your DBA.
These are some of the tips and tricks that I wish someone had told me when I was starting out or had issues with misbehaving SQLs. Furthermore, the main thing to remember is to have your DB in order, use partitions, monitor DB performance, isolate high-consuming SQLs and then, when you figure out where the problem is, solve it (preferably using some of the tips and tricks from this list 😊).
Of course, if you need some extra help just let us know.
