Re: In-depth Oracle Statistics Questions

FrostyT (frostyt_at_aol.com) wrote:
: 1) How can I easily tell if statistics have been gathered on a table,
: index, or cluster? I already know that the dba_tables, dba_indexes, and
: dba_clusters views can be used to view particular columns which "hold" the
: statistics. Would I simply check if these fields have values? There must
: be a better way...

I believe this is the recommended way of telling whether an object has been analyzed... *shrug*

:
: 2) Is there any way to determine when the statistics were collected? I
: assume that the statistics either exist or don't exist and there is no way
: to determine their "age".
:

There is no way to tell when the stats were grabbed. This may change in 7.3

: 3) To decrease the time it takes to collect statistics (via Analyze
: command), I would like to "split" the long list of Analyze commands into
: several separate UNIX shell scripts and simultaneously execute them in the
: background. When working with a 16 CPU system, it seems that 10 separate
: jobs would finish much faster than 1 job. However, can anyone think of
: potential problems doing this? Example: Should the TEMP tablespace be
: temporarily increased in size? This would be performed on an idle
: database instance.
:

Use the ESTIMATE clause.

: 4) Once statistics are collected, they are part of the data dictionary.
: Does this mean that a subsequent export will include the statistics?
: Wouldn't some of these statistics be incorrect after an import (Ex. count
: of leaf nodes)? Is there any reason to perform the statistics collection
: after the import?
:

They are just data items after they are collected. You need to collect them regularly if your tables are in flux. If you've optimized to a particular set of statistics, and you're getting good results, then you might want to skip further updates to particular tables stats. Any new change could destroy performance in an unexpected way.

: Please email any information to frostt_at_ihub.ea.unisys.com
: Thanks!
 

-- 
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I