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:
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. Fo
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
r 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:
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.
Go to the main series page.
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.
