The following is the sixth 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.
Know thy database
When writing efficient SQL, it is imperative to know the demographics and shape of the objects your code will bump up against. For most databases, all the information you will ever need can be found in the data dictionary. But when querying the dictionary for object statistics, make sure you are looking at accurate information. While some databases such as SQL Server 2000 have automatic updating of object statistics into the dictionary, other RDBMS engines require you to manually (and periodically) refresh the database catalog with up-to-date object data. Fortunately, most make it pretty easy to accomplish. Oracle, for example, now offers packages to assist with the updating of objects. The dbms_utility package contains several procedures to help database professionals update their schema objects. To update the dictionary for a single schema, you can use the dbms_utility.analyze_schema procedure. Recently introduced is the dbms_utility.analyze_database. Just be careful when executing such a procedure up against a monolithic database like Oracle's applications!
However you choose to update your objects, you should make a practice of keeping data in the dictionary current, especially for databases that are very dynamic in nature. Scheduling the object updates in a nightly maintenance plan for such a database would likely be a good thing to do, especially if you use a cost-based optimizer in your RDBMS. Fresh statistics help the optimizer make more informed choices of what path to follow when routing queries to the data. Obviously, if your database thinks you only have 100 rows in a table that actually contains a million, t
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
he map used by the optimizer might not be the right one and your response times will show it.
So let's say you know (at least now) to keep your object statistics up to date. When tuning SQL, what are some things you should be looking for with respect to the objects used in your query to help make the right coding choices? Although this list is certainly not exhaustive, for tables you can start by eyeballing these items:
Indexes have their own unique set of items that need to occasionally be reviewed. Some of these include:
There are, of course, other items you can review on the table and index statistical front, as well as at the individual column level.
Understanding the current state and shape of the objects being used in the queries that you are trying to tune can unlock clues as to how you may want to restructure your SQL code. For example, you may realize that you have not indexed critical foreign keys that are used over and over again in various sets of join operations. Or, you might spot that your millionrow table is a perfect candidate for a bitmap index given the current WHERE predicate.
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.
