How to improve database performance with Oracle query optimization — Basic concepts

You may not know when you need Oracle query optimization, but you certainly know when you need improvements in your database performance. For example: 

• The trouble tickets in your queues start to pile up.
• That comment on Slack (or Yammer or Teams) reading “I’m never going to get my work done if the system stays this slow all day” starts rolling up dozens of likes.
• The number of abandoned shopping carts on your site suddenly spikes.
• You get voicemail from your boss that starts (and maybe ends) with, “Fix the application.”

Most users and customers won’t know that the problem lies inside an Oracle query, and they don’t care, either. All they know is they can’t be productive or buy your products when pages take forever to load and screens seem stuck.

In this post, I’ll explain a few basic concepts of optimizing queries in Oracle.

The short answer is that you want to modify the query to eliminate the delay in the application and improve database performance.

But there are two different kinds of delay. 

One is that too much time elapses between the user’s/customer’s action and the response from the database. The other is that the query is chewing up too many system resources (memory, CPU, network). The impact on your users and customers is the same, but because those delays can have different causes, you’ll deal with each one differently. 

Another factor in Oracle query optimization is the role that it plays in your job description. Are you going to check database performance regularly and look for ways to write more efficient SQL statements? Or are you going to lurch from crisis to crisis, making time for optimization only when database performance problems arise? 

In either case, you’ll need SQL optimization tools and knowledge of PL/SQL.

How do you improve database performance with Oracle query optimization?

Follow this five-step process that uses SQL optimization tools and PL/SQL know-how.

1. Find high-consuming SQL statements

Whether you optimize reactively or actively, the first thing to look for are SQL statements that are consuming the greatest share of resources. Oracle includes an automated tool called Automatic Database Diagnostic Monitor (ADDM) that continually keeps an eye out for high-load SQL statements. That reduces the manual component of searching for potential problems, but you still need to check it regularly and then manually tune the SQL.

Sometimes, like when a performance problem catches you off-guard, you’ll prefer to look for troublesome SQL statements manually or study them more closely. For that, you can open Enterprise Manager in Oracle and look through the Top SQL section of the Top Activity page.

2. Collect data used by the optimizer

To process SQL, the optimizer in Oracle selects an execution plan based on details about the database and database objects. Here is an example of an execution plan showing a join of the employees and departments tables:

The rows in the table indicate the join order (inner and outer row sources) of the tables, the access path for each table (type of scan) and the join method (nested loops). The predicate information shows any filtering, sorting and aggregation. 

The execution plan depends on constantly changing statistics like number of rows, definition of indexes, available system resources and makeup of columns in tables. SQL performance is only as good as the execution plan, which is only as good as the statistics the optimizer uses. 

Oracle query optimization depends on up-to-date statistics that the database collects automatically. If you prefer to intervene manually, you can also use DBMS_STATS to gather optimizer statistics and system statistics.

3. Figure out where the performance problems lie

Many performance problems arise from poorly designed SQL that is making the database and system resources work harder than necessary. Textbook examples of inefficient SQL include the use of wildcards — SELECT * — and data type conversions in a WHERE clause — WHERE DATE_FORMAT(inv.date, '%Y-%m-%d %T') = '2020-08-02';. 

Is the database using the best execution plan? If Oracle is using stale statistics to select the most efficient way of retrieving data from the database, performance is a likely casualty. For example, a typically costly operation like a full table scan does not impair performance gravely on a small table. But as that table grows, queries should use an index instead of the full table scan for better performance. 

Your goal is to find operations with a high “throwaway” ratio; that is, where the number of rows initially read far exceeds the number of rows ultimately needed. Frequent causes of high throwaway ratios include unselective range scans, late predicate filters and incorrect/inefficient join order. Once you’ve reduced that throwaway ratio, you’ll likely boost performance. 

Of course, no amount of Oracle query optimization will compensate for hardware problems. Be sure that resources like CPU, memory, input/output and network throughput are not bottlenecks. They are not subject to change or deterioration the way software and SQL are, but they should not be completely overlooked, either.

4. Optimize

Now, optimize SQL statements for developers, and indexes for DBAs. Look for problems like these: 

• Is the query processing in parallel? Is parallel processing really necessary? If not, then it’s consuming valuable resources that other queries may need.
• Look for hard-coded hints, which become obsolete and are easily forgotten. If the underlying data changes, the hint becomes invalid.
• Filter as early as possible in the query, for example, by moving WHERE clauses closer to the start of your code.
• Beware of third-party SQL generators such as EMF, LINQ and NHibernate, which often produce sub-optimal code that includes wildcards.
• Avoid data conversions, like between varchar and integer, which can drive CPU cycles far above normal. If you find them, simply use the correct data type.

It’s not so difficult to fix a syntax error or add a new index, but it can take you some time to write more efficient SQL, especially when you’re new to the craft. Good SQL tuning tools include algorithms for quickly finding alternative versions of the original SQL that will run faster in the database.

5. Keep from backsliding

To return to the earlier question, how big a part of your job is Oracle query optimization? How much time can you devote to staying out in front of database performance problems? 

You can achieve low trouble ticket volume (and an empty voicemail box) by keeping an eye on the execution plans. As chokepoints arise and performance drops, choose a better plan. 

The fact is that query optimization is an ongoing process because data is constantly changing. That means updating execution plans with the help of optimizer statistics, SQL profiles and SQL plan baselines, and doing it regularly.

Next step

Find out more about complementing the tools for Oracle query optimization with tools from Quest. Download the technical brief “How Toad for Oracle DBA Edition Complements Oracle Enterprise Manager.”