Re: Migrating to cost based optimizer

hasta_l3_at_hotmail.com wrote:

> BTW, we have exactly zero experience with CBO :-)

Then let me make a recommendation.

Spend a lot of time learning how DBMS_STATS works. Be sure you check out the collection of system statistics, dictionary statistics, and how to fool Oracle into believing things about objects that are not true, today, but may well be true, tomorrow.

For example:
exec dbms_stats.set_table_stats(USER, 'EMP', numrows=>1000000, numblks=>100000);

exec dbms_stats.set_index_stats(USER, 'ix_emp_deptno', numrows=>1000000, numlblks=>10000, numdist=>10000);

exec dbms_stats.set_column_stats(USER, 'emp', 'deptno', distcnt=>10000);

exec dbms_stats.set_table_stats(USER, 'dept', numrows=>100, numblks=>100);

I can easily fool the CBO into believing scott.emp and scott.dept are large tables with creative cardinalities.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group