Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 10g stats
On Nov 12, 12:08 pm, chris b <chris.br..._at_providenthims.co.uk> wrote:
> We are currently migrating to 10.2.0.3 enterprise ( from 8i ) by
> 1) datpumping schema
> 2) Running histogram scripts
> eg DBMS_STATS.GATHER_TABLE_STATS ('LIVCACHE','MI_BATCH',METHOD_OPT=>'
> FOR COLUMNS BATCH_NO SIZE 75');
>
> 3) Gathering stats on all by using method_opt FOR ALL COLUMNS SIZE
> REPEAT
>
> When I check for histograms afterwards I find that step 3 has created
> new histograms ...whereas I thought this option would only build a
> histogram if one already existed / Can anyone advise if my expectation
> of the result is flawed and if so why?
Check the view dba_scheduler_jobs.
The database creation assistant agent (dbca) creates a scheduled job
in the database to gather stats daily.
Perhaps stats were gathered on the tables automatically prior to you
manually running the gather stats job.
The defaults for the parameter "method_opt" changed in 10g in the
procedure dbms_stats.gather_stats.
You may need to first gather stats with method_opt=>'for all columns
size 1' and then gather stats with the histogram columns enumerated.
hth
-bdbafh Received on Mon Nov 12 2007 - 11:25:56 CST