High Volume SQLs

In many respects, high volume SQLs are easier to tune than low volume SQLs because the solution is always the same:

In practice it is more complex. Although the goal is conceptually simple, it can sometimes be hard to achieve.

The first step is to run your SQL through Explain Plan. Check the execution plan for the following:

Did the above steps help? If not, then your SQL should contain only Full Scans and Merge or Hash joins (plus Index scans and Nested Loop joins that are described in the links above as acceptable for High Volume SQL). If performance is still unacceptable, check the following?

Still want more? If you still have Nested Loops and Indexed table accesses, then either go back and try again or start redesigning your schema.

Otherwise, you should have a nice Full Table Scanning, Hash or Merge Joining query. If you still want to squeeze better performance out of it, then you are going to have to reduce IO. This means reading fewer blocks, which is going to be hard because if you have applied all of the principles above then you probably have little or no redundancy in your query (blocks being read twice or more). There are a couple of things you could do:

©Copyright 2003