RE: Need help

[Jankovic, Djordje]

I have 
done some test before and found out that analyze is faster than dbms_stats for 
smaller tables only (less than 10M) - and the difference was not that big (for 
example very small tables dbms_stats would do a compute instead of 
estimate).  For large tables they are pretty much the same.  And this 
is comparing dbms_stats working in serial mode.  You can do stuff in parallel 
which could change the picture in dbms_stats 
favor. 
 
dbms_stats is more accurate (since its introduction incorrect statistics 
gathering was discovered for ANALYZE), it supports better granularity control 
(partitions, subpartitions).  Using dbms_stats you can also export and 
import statistics: save the old statistics before the run and reuse the old 
statistics in case you don't like the new ones.
 
There 
are a number of other advantages: treating stale tables, automatic estimate 
sample size, collecting histogram info only for skewed columns (at least the 
ones that oracle thinks are skewed).  Using dbms_stats you can also collect 
system stats.
 
And 
analyze is not depreciated in 9i.
 
AFAIK 
the only things dbms_stats does not do are: finding chained/migrated rows, 
computing cluster statistics, and validating the structure.
 
Djordje

  -Original Message-From: Jayaram Keshava Murthy 
  (Cognizant) [mailto:[EMAIL PROTECTED]Sent: Monday, June 
  02, 2003 10:50 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Need help
  Hi 
  all,
   Can 
  anyone tell me which of the following performs better in collecting statistics 
  of a table:
   Analyze 
  command   or
   Dbms_stats.gather_table_stats
   
    I tried my queries 
  with both the options -- but always Analyze is out-performing the 
  dbms_stats.
  But i read in a 
  document that Dbms_stats performs better !!
  Besides I also 
  read that Analyze command will be deprecated from Oracle 
  9i.
   
   So can 
  anyone tell me why dbms_stats is performing poor...
   Are there 
  any paramters that need to be set to increase the performace of dbms_stats 
  ?
   
  Thanks in 
  advance.
   
  Regards
  Kesh
   

RE: dbms_stats broken

[Jankovic, Djordje]

I 
haven't used it, but here is what we are doing: we created a simple package 
which loops through dba_tab_modifications and finds itself (the same way as 
oracle would) which tables are stale: where the sum of inserts, updates and 
deletes is more than 10% of the number of rows, and than run dbms_stats for 
those only.  That way you have a bit more flexibility (you can for example 
exclude some tables), make estimate or calculate decisions on the table 
basis, etc.
 
Djordje

  -Original Message-From: Koivu, Lisa 
  [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 
  2003 10:25 PMTo: Multiple recipients of list 
  ORACLE-LSubject: dbms_stats broken
  
  Hello everyone, 
  
   
  Is anyone using dbms_stats and gather stale or gather auto in 9.2?  I’m trying to use dbms_stats gather schema stats with the stale option and 
  it just isn’t working in 8.1.7.4.  
  This is documented on Metalink.  I’d love to hear from someone else if 
  this is fixed in 9.2 and if it can be reliably used. 
  
   
  Thank 
  you
  Lisa 
  Monkey.