Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 10g stats

Re: 10g stats

From: bdbafh <bdbafh_at_gmail.com>
Date: Mon, 12 Nov 2007 17:25:56 -0000
Message-ID: <1194888356.604016.284510@o38g2000hse.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US