Re: COST BASED OPTIMZER INFO ??

In article: <3s1v3p$e0h$1_at_mhadg.production.compuserve.com> Jeff K <102172.2033_at_CompuServe.COM> writes:
# I'm new to the ORACLE world, but have 8 years of DB2. I'm
# currently involved in a project to tune some batch processing
# and from past experience know that when accessing large amounts
# of data sometimes the best access path is not one which you
# might have guessed it would be. i.e. merge scan join, etc.
# The shop I'm in is still doing rule based optimization but I have
# very little info on the V7.0 cost based optimizer. Can anyone
# point me in the right direction or share any experiences they may
# have had with the cost based optimzer in 7.0 ?? Thanks a bunch.
#

From what I have seen and heard it seems that the cost-based optimizer does not work too well until version 7.1.3 or 4.

You may be better off setting your DBs OPTIMIZER_MODE = RULE.

If you want to try using COST you must remember to create STATISTICS for all the tables in your application at regular intervals, say, once a month.

Doing

 ANALYZE TABLE emp COMPUTE STATISTICS;

will be most accurate but takes a long time with large tables

If you use the ESTIMATE STATISTICS clause you should ensure that you sample a good percentage e.g. SAMPLE 33 PERCENT.

HTH Peter

-- 

|~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
| PETER  MOORE  | pt_at_chaff.demon.co.uk       | "Either he's dead, or my watch |
|     DBA       |                            |  has stopped!" - Groucho       |
| MAT Transport | +44 (171) 410 6373         |                                |
|_____________________________________________________________________________|