Database News You Can Use
A monthly newsletter for Oracle users
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Setting optimizer_index_cost_adj
You’re invited to submit your Oracle-related questions to us at email@example.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).
This month’s question comes to us from Jonathan in San Jose, CA: What does the optimizer_index_cost_adj parameter do, and how would I go about changing it in order to make my queries run faster?
Robert Townsend of the Database Specialists team responds: Most of the time, Oracle’s cost-based optimizer does a good job of choosing the most efficient execution plan for your queries. It does this by making a list of possible execution plans for a query, estimating the cost of each plan, and selecting the plan with the lowest cost. However, the optimizer makes it selections based on estimated costs, and sometimes the estimates are not the most accurate. Sometimes Oracle will choose a hash join when you might think a nested loops join would be more efficient. Or perhaps Oracle will choose to perform a full table scan when an index range scan might be more efficient.
Hints and stored outlines may be used to get the optimizer to choose an alternate execution plan for a specific SQL statement. These techniques work well in many situations. However, some applications create SQL queries dynamically as the end user picks codes and conditions on a GUI. This presents a challenge for the DBA because you can’t modify the query that is created on the fly, and trying to create stored outlines for all of the query permutations could drive a DBA crazy. Furthermore, many third-party vendors forbid alterations to their code, making the use of hints off limits.
The optimizer_index_cost_adj instance parameter, introduced way back in Oracle 8.0, may help in these situations. This parameter has a default value of 100, and may be adjusted at the session or instance level to any integer value from 1 to 10000. The cost-based optimizer uses this parameter setting in estimating the cost of accessing a table via an index lookup.
For example, if you set this parameter to a value of 60, then the cost-based optimizer will believe that index lookups are only 60% as expensive as would otherwise have been estimated. Thus, in this case, the optimizer would be more likely to choose execution plans with nested loops joins over hash joins, and index range scans over full table scans. Meanwhile if you set this parameter to 200, then the cost-based optimizer will believe index lookups are twice as expensive as the default. This would lead to more hash joins and full table scans.
You should be careful with the optimizer_index_cost_adj instance parameter because it affects all queries parsed in the session (when set at the session level) or across the database (when set at the instance level). You may be successful in forcing a particular query into using the preferred join but adversely affect 100 other queries at the same time. With care, this parameter can be a useful tool, but caution must be the rule.
Like any other code or configuration change you might contemplate making on a production system, you ought to approach changing the optimizer_index_cost_adj instance parameter in a way that allows you to learn how the change affects the rest of the system. And, of course, you should endeavor to do all of your testing and experimentation in a development environment before deploying the change on production. Be sure to capture a good benchmark in the existing system before a change is deployed so that you will be able to accurately measure the impact of the change after deployment.
Manipulating the optimizer_index_cost_adj instance parameter will not be the best approach for all applications, but it is a good tool to have when you run out of alternatives. Generally speaking, applications have reoccurring code themes and often when one SQL query benefits from an index cost adjustment, other queries in a similar class may as well.
If you are looking for information on the topics of data warehousing or OLAP, take some time to check out Mark Rittman’s Oracle Weblog. You’ll find lots of resources on business intelligence, reporting and data warehousing using Oracle technology. Following are just a few categories listed on the site along with some sample topics in each category. OraSnap: An Oracle Tool with a Twist
- Using data warehouses to calculate customer profitability
- The Oracle Information Architecture explained
- Streamlining Oracle 9i ETL with pipelined table functions
- Creating an OLAP cube using Oracle Database 10g
- Improving the performance of OLAP DML table inserts
- An Oracle DBA’s guide to the OLAP option
- New data warehousing features in Oracle 10g
- The SQL MODEL clause
- Oracle 10g OLAP New Features
OraSnap, which is short for “Oracle Snapshot”, is a utility that gathers performance information from an Oracle database and publishes it in easy-to-use HTML pages. Developed by Stewart McGlaughlin, the OraSnap scripts aid in tuning and optimizing your database by giving you easy access to performance statistics collected by Oracle. McGlaughlin says on his site, “These scripts are the same scripts that most of us already have in our arsenal. The ‘twist’ is the way the information is presented.” All of the statistics are presented in a user-friendly way using HTML—which can then be viewed with a web browser. Here are just a few areas covered by the OraSnap scripts: Celebrating Seven Years of Remote DBA Services
- Tables and indexes
- Session statistics
- Shared pool
OraSnap works on Oracle versions 7.3 through 9i. Check it out at http://www.oracle-books.com/orasnap/index.html.
In September Database Specialists, Inc. held a celebration at its San Francisco offices to mark the seventh anniversary of its remote database administration services. “We’ve provided remote DBA services since 1997, but when we packaged and branded it as DBA Pro several years ago, we also boosted the value proposition for our customers,” said Database Specialists president Roger Schrag. “We are thrilled at how our customer-base has grown as we add more and more features to our DBA Pro offering,” Roger continued. This includes the popular Database Rx tool for monitoring the health of Oracle databases. Share Your Favorite Tips
For more information on the DBA Pro family of services, offering options such as 24/7 comprehensive coverage for mission-critical databases, bundled consulting hours, and proactive daily and monthly reviews for Oracle systems, click here.
Do you have a handy script, a special tip, or a white paper that you would like to share with The Specialist audience? If so, we invite you to send them to Lisa Loper, editor of The Specialist, at firstname.lastname@example.org. Sharing your knowledge is a great way to get your name out there and give something back to the Oracle user community at the same time. We look forward to hearing from you!