Re: CBO stupid !!!

From: eis_at_mail.otenet.gr >>
Though generaly CBO works ok, at times it makes stupid decisions! << gives example involving unbounded range scan, i.e., >= on indexed value

The other post about setting the init.ora optimizer_goal parameter was good advise for an OLTP environment.

I would like to add that how you analyze your tables makes a big difference. I strongly advice that you never use the default estimate statistics cluase, but rather always add a sample size such as 'analyze table my_table estimate statistics sample 50000 rows;'. The default behavior reads too few rows to generate workable statistics for any but failry small tables.

We also use Forms 3.0 and have found a way to influence the optimizer via stored procedures. I created three otherwise identical procedures that pass an alter session set optimizer_goal command to Oracle via dbms_sql. We can switch the session from choose (which defaults to all_rows), to first_rows, and to rule between statements. This technique only gives us three choices but we find this better than trying to rewrite individual SQL statements or changing the statements to reference views that have built-in hints..

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Sat May 30 1998 - 17:55:33 CDT