Note: Burleson Oracle Consulting is the leader in Oracle Emergency Support and is available to assist with any Oracle database problem, anywhere in the world, anytime.

 

Changing CBO Optimizer Parameters Another emergency situation involved an Oracle 9.0.2 client from Phoenix who called complaining about steadily degrading performance. A quick look into v$sql_plan view using my plan9i.sql script revealed loads of suspected unnecessary large-table, full-table scans. In this case, the top SQL was extracted from v$sql and timed as-is and with an index hint. The query with the index hint ran almost 20x faster, but it was unclear why the CBO was not choosing the index. This was a production emergency, and I did not have the luxury of investigating the root cause of the CBO issue. I had to act fast, so I ran a script against v$bh and user_indexes and discovered that approximately 65 percent of the indexes were currently inside the data buffer cache.  Based on similar systems, I decided to lower optimizer_index_cost_adj to a value of 20, hopefully forcing the CBO to lower the relative costs of index access. optimizer_index_cost_adj=20 optimizer_index_caching=65 If you are Oracle9i and beyond, you can dynamically alter some parameters: alter system set optimizer_index_cost_adj=20 scope = pfile; This quick fix changed the execution plans for over 350 SQL statements and cut by half overall system response time. The client was elated, and I was then able to take my time and investigate the root cause of the problem. If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress. It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: http://www.rampant-books.com/book_ 2005_1_awr_proactive_tuning.htm