Ian Harrison
Ian Harrison DBmarlin technical writer and consultant

Top Oracle Dos and Don’ts for SQL Tuning

Top Oracle Dos and Don’ts for SQL Tuning

I’ve been lucky enough to work alongside some very talented SQL people and the tuning tenets are clear:

  1. Make your query as efficient as possible; and
  2. Ensure your queries will scale with any unexpected increase in load.

My journey through operational database management over the years has been filled with interesting scenarios. This included a slowdown caused by well-intended queries from application support, which joined a 10 million row customer table with a 100 million row fact table (while leaving out some key predicates which would have allowed partition elimination and use of indexes!).

Oracle Top SQL Tips - The Dos

1. Do make sure your database statistics are up to date.

Many poor execution paths are selected by a database because if you don’t update the statistics it has no idea you have crammed it full of extra data and your 500 row table now contains 50,000,000 records.

2. Do look at wait events and times with a tool such as DBmarlin

These expose session and query waits. Not only can you see the waits inside the database, but you can examine the execution path too. This is invaluable to picking up performance issues early.

3. Do ensure you explicitly match the datatypes in your queries to your schema.

This is particularly true of date-time columns, but is equally applicable to text and numbers too. SQL is a very forgiving language and will readily convert a varchar to a number for you if you leave out the quotes. However, this forces Oracle to perform surgery on your query - adding a TO_NUMBER() function for you if required.

In this example, both USER_NAME and EMP_CD are varchar fields and both the queries below will run quite happily:

1
2
3

SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = '1908';

SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = 1908;

The second example, however, will be ‘corrected’ by the database to make it run. In order to do this, it adds a function to the table side of the predicate:

1

... WHERE TO_NUMBER(EMP_CD) = 1908;

The addition of this function will stop the query from hitting the index on EMP_CD!

4. Do use bind variables to pass data into your queries.

If you pass string literals, you not only expose yourself to the security risk of SQL injection, but you can cause performance issues with shared pool contention as it fills with very similar, but not identical, versions. Remember that to be considered the same query, the text must match EXACTLY - it’s case sensitive - so:

Instead of having your SQL area filled with something like this:

1
2
3
4
5

SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = '1908';
SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = '1900';
SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = '1456';
SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = '1892';
SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = '1750';

Use:

1

SELECT USER_NAME, EMP_CD FROM APP_USERS WHERE EMP_CD = :v_emp_cd;

Of course the actual syntax depends on the coding language you use and there is a caveat: beware of skewed data affecting the performance of your query. This is because the optimizer can’t see the actual values being passed in, so assumes uniform data distribution. But the general rule is to use bind variables!

5. Do test your query

Execution plans will continue to work when your business strikes gold and the size of your customer base grows exponentially. Take time to record execution plans and performance statistics, for example the expected number of reads and writes per execution along with execution times. Doing this will allow your queries to stand the test of time and will be invaluable if your key business queries degrade due to growth over time.

Oracle Top SQL Tips - The Don’ts

1. Don’t use column wildcards or fetch any more columns in your cursors than you need

Using wildcards will break your key application when someone adds a column to your table and your code isn’t updated to match. Always select named columns to ensure safety and don’t fetch more data than you need as this can result in poor optimizer choices and unnecessary I/O.

Instead of:

1

SELECT emp.* FROM ...

Use:

1

SELECT emp.empno, emp.ename from ...

2. Don’t use functions on the table side of your predicates

That is unless you have to and they are supported by a function based index. Putting a function in the table side can cause your I/O to skyrocket with unexpected table scans due to indexes being ignored. For example, this query will miss the index on client_name because of the case change on the table data.

1
2
3

SELECT client_name, company_name 
  FROM prospects 
 WHERE UPPER(client_name) = :v_client_name_in;

3. Don’t over index.

While it’s tempting to stick an index on every column you want in your query, you must strike a careful balance between the reduction in I/O aiding speedy queries and the overhead of having yet another index to update every time you insert or update a single row. If you find unexpectedly high logical I/O associated with a query, see if the table has been indexed to within an inch of its life.

4. Don’t use Oracle specific outer join notation

Use ANSI joins - this will make your code more portable to another RDBMS, but also more understandable to a wider audience and therefore easier to support.

Instead of:

1
2
3

SELECT cities.name, countries.name 
   FROM cities, countries
   WHERE cities.country_id = countries.id(+);

Use:

1
2
3

SELECT cities.name, countries.name 
   FROM cities RIGHT outer JOIN countries
     ON cities.country_id = countries.id;

5. Don’t use wrong database!

Let’s face it, we’ve all done it once 🤦‍♂️. We’ve written our query and using good practice, started testing - only to find we have connected to the production database by mistake. Always double check where you are connected to.

In summary

This whistle stop tour of my top tuning tips for Oracle may contain some unexpected entries. However, 30 years of working alongside IT experts has shown me how useful this small amount of extra work and foresight helps throughout the whole product lifecycle. My final tip would be, to always use a database monitoring solution such as DBmarlin, to get a clear and unequivocal evidence of how your SQL is performing.

Ready to try DBmarlin?

If you would like to find out more about DBmarlin and why we think it is special, try one of the links below.

dbmarlin-trial-offer