The following is the eighth part of a 12-part series on Oracle10g CBO internals and SQL tuning optimization. Each tip is excerpted from the not-yet-released Rampant TechPress book, "Oracle SQL and index internals," by Kimberly Floss. Check back to the main series page for upcoming installments.
Try different combinations
When the SQL is valid, your objects are in order, and you have eliminated noticeable flaws, it is now time to begin trying different code combinations in the hopes of improving your query's performance. This is where the use of hints can become a serious timesaver. The SQL language now contains a plethora of hints that you can embed in your query without really touching the actual code structure that exists. Using hints, you can, in effect, accomplish many iterations of rewrites in a short span of time.
Of course, one of the dangers of using hints is not coding them accurately. Unlike typical SQL code that will cough up a syntax error if you make a mistake, an invalid hint will not afford you that luxury. It will not do anything. Therefore, you need to make sure that you code your hints accurately.
Again, this is where a good SQL tuning tool comes in handy. Being able to visually add hints by selecting them from a pop-up list or other GUI dialog is really the way to go. That way, you know that your hint is syntactically correct and will have the intended effect.
What different coding approaches should you try through the use of hints? While there is no way to provide a complete rundown of everything that is open to you, there are a few mainstays to try:
- The Four Horsemen. These include RULE, FIRST ROWS, ALL ROWS, and CHOOSE. I cannot tell you how many times I have improved a query just by going back to the rule base optimizer. Even with all the progress made by cost based approaches, sometimes the old way is the best way.
- Table Order Shuffle. You may want to influence the order in which the optimizer joins the tables used in your query. The ORDERED hint can force the database to use the right tables to drive the join operation and hopefully reduce inefficient I/O.
- Divide and Conquer. When databases introduced parallel operations, they opened up a whole new avenue in potential speed. The PARALLEL hint can be a powerful ally in splitting scans on large tables out into chunks that may be worked on separately in parallel and merged back into a single result set. One thing to ensure is that your database is set up properly with respect to having enough parallel worker bees (or "slaves") to handle the degree of parallelism you specify.
- Index Now. From the EXPLAIN plan, you may discover that the optimizer is not using an available index. This may or may not be a good thing. The only way to really tell is to force an index access plan in place of a table scan with an index hint.
Benchmark your rewrites
Once you have got a suite of new SQL combinations put together for your existing query, it is time to actually send them through the database and see if you have actually accomplished anything. Keep in mind that when you are performing these informal benchmarks, it is important to do them right. For example, do not just execute each query one time and record the measurements. First-time executions typically take longer than subsequent tries due to parse activity and data being read the first time from disk. At a minimum, I would send each new query through three to four times and throw out the high and low readings.
And just what measurements should you be looking at? If Oracle is your database, then there are quite a few excellent statistics that can be used to gauge the success of your rewrite prowess. A few of the more critical ones include:
- Elapsed time. What was the response time experienced?
- Physical I/O. Usually, the more physical I/O, the worse the query.
- Logical I/O. Consistent and block gets. Even in memory, less I/O is better.
- Memory usage. Shared, persistent, and runtime.
- Sort operations. Disk sort activity is usually many times slower than memory sorts.
- CPU time. How much of the server's "brain" did your query actually use?
- Parse time. How long did it take the database kernel to unwrap your query?
It is always exciting to see a rewritten set of SQL complete in a fraction of the time taken by the initial query. What a hero you will be! But what if the benchmarks aren't a no-brainer and you get a set of statistics back that are somewhat close? How do you decide whether or not to implement an altered set of code? There are really no hard-and-fast rules, but most people use a combination of elapsed time and I/O. Does the rewritten query show a somewhat better response time? Does it appear to use less I/O – both logical and physical? Are previously observed disk sorts eliminated? If so, you may have a new candidate to place into production.
About the author
Kimberly Floss is one of the most-respected Oracle database administrators in the U.S., and is president of the International Oracle Users Group (IOUG). With more than a decade of experience, Kimberly specializes in Oracle performance tuning and is a respected expert in SQL tuning techniques. She is an active member of the Chicago Oracle Users Group, and the Midwest Oracle Users Group, in addition to the IOUG. Kimberly Floss has over 15 years of experience in the information technology industry, with specific focus on relational database technology, including Oracle, DB2, Microsoft SQL Server and Sybase. She holds a bachelor's of science degree in computer information systems from Purdue University, specializing in systems analysis and design, and has an MBA with emphasis in management information systems from Loyola University.