The following is the first 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.
High performing SQL -- where do you start?
Once you know all the things that can be tuned, within the database, where do you start? The answer is, with the code. You can double check all the init.ora parameters to start, monitor hit ratios and use operating system monitoring tools, but most often, the issue is in the code. Both Oracle9i and Oracle10g have added many new features to assist in tuning SQL to make it run faster. We've already covered some of the major Oracle10g new functionality, including ADDM, and the advisors.
Before you can start tuning, you need to understand your goals. Oracle has several types of indexes (b-tree, bitmap) all designed to speed-up data retrieval. Index access can be faster than a full-table scan especially when the desired result set is small and when the rows are on adjacent data blocks (as defined by the clustering_factor column of the dba_indexes view). In certain cases (i.e., a large sorted result set), index access can be more expensive (in terms of logical I/O's) than a full-table scan, but it will start to deliver rows to the requesting program very quickly. Therefore, in order to tune, you need to decide your destination:
- Minimize computing resources: This is Oracle's all_rows optimizer_mode, whereby the CBO strives to deliver the desired rows with a minimum of computing resources. The all_rows optimizer_mode gives a lower cost to parallel full-table scans.
- Minimize response time: When the goal is to minimize response time, Oracle's first_rows optimizer_mode is ideal. The first_rows optimizer_mode gives a lower relative weight to index scans, thereby favoring access plans that start returning rows very quickly, even if it means more logical I/O operations and more computing resources. Being flexible, Oracle provides a wealth of optimizer parameters to allow the senior Oracle DBA to change the default behavior of the CBO.
For example, the optimizer_index_cost_adj parameter is used to control the relative weight assigned to index scans. For most databases, the CBO does an excellent job of always choosing the most appropriate access method for the query.
The characteristics of the data have a huge bearing on the CBO's choice of execution plans. These metadata items include:
- Size of the table, partitioning of table
- Number of distinct values within each table column
- Distribution of values (skew) of table columns (from dba_histogram_cols)
- Availability of parallel query servers (from cpu_count)
- Clustering of data rows on data blocks (clustering_factor column in dba_indexes)
- And many, many more
The CBO only makes intelligent decisions when it is provided with accurate metadata statistics. For years, the CBO has been viewed as 'potentially problematic,' when in fact, it is the lack of accurate and current statistics that have been the real issue.
Go to the main series page.
About the authors
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.
