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.

 

Fix Missing CBO Statistics The call came in from a client in Florida who had just moved their system into production and was experiencing a serious performance problem. Upon inspection, we found optimizer_mode=choose and only one table with statistics. The DBA told me that she was running cost-based and she seemed totally unaware of the requirement to analyze the schema for CBO statistics. Here is how I recall the conversation: DB: “How are you collecting statistics?” DBA: “We have BMC Patrol” DB: “No, No, how are you getting SQL optimizer statistics?” DBA: “The Oracle sales rep said that the CBO was intelligent, so I assumed it was getting its own statistics.” You know, in a way, she was right. The problem started when she wanted to know the average row length for a table. She did a Google search and discovered that it was in the dba_tables.avg_row_len column. When she found it null, she went to MetaLink and learned that an analyze table command would fill-in the avg_row_len column. As we know, when using optimizer_mode=choose with only one table analyzed, any SQL that touches the table will be optimized as a cost-based query, and the CBO will dynamically estimate statistics for all tables with missing statistics. In this case, a multi-step silver bullet did the trick: alter table customer delete statistics;            exec dbms_stats (…); The system immediately returned to an acceptable performance level, and the DBA learned about the importance of providing complete and timely statistics for the CBO using the dbms_stats utility. 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