Re: ORACLE automatic update statistics better??
- From: "Leyi Zhang (Kamus)" <kamusis@xxxxxxxxx>
- To: mdinh@xxxxxxxxx
- Date: Mon, 16 Aug 2010 15:53:00 +0800
Agree with Michael. Manually gather statistics but replace your
"method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1'" with "method_opt=>
'FOR ALL INDEXED COLUMNS SIZE REPEAT'". (Of course, before this
"REPEAT", you have to gather some columns' histogram by yourself at
first with method_opt=>FOR COLUMNS SIZE AUTO [COLUMN_NAME])
The point is you have to decide which columns should have histogram by
yourself, don't count on Oracle . In my experience, execution plan
sometimes changed because of histogram auto generated when automatic
statistics gathering.
--
Kamus <kamusis@xxxxxxxxx>
Visit my blog for more : http://www.dbform.com
Join ACOUG: http://www.acoug.org
On Sat, Aug 14, 2010 at 12:09 AM, Michael Dinh <mdinh@xxxxxxxxx> wrote:
> Isn't it better to use REPEAT for method_opt?
>
> If auto statistic gathering is using histogram and you gather without
> histogram, next time histogram will be gathered, no?
>
> You can find the default method_opt by executing - select
> dbms_stats.get_param('method_opt') from dual;
>
> exec
> dbms_stats.gather_table_stats(user,tabname=>'&tbl',estimate_percent=>1,cascade=>TRUE,method_opt=>'FOR
> ALL COLUMNS SIZE REPEAT');
>
> Michael Dinh : XIFIN : 858.436.2929
>
> NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
> individual or entity to which it is addressed, and may contain information
> that is privileged, confidential and exempt from disclosure under applicable
> laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH
> INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING,
> AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
> CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT
> NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS
> MESSAGE. If the reader of this email (and attachments) is not the intended
> recipient, you are hereby notified that any dissemination, distribution or
> copying of this communication is strictly prohibited. Please notify the
> sender of the error and delete the e-mail you received. Thank you.
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Powell, Mark
> Sent: Friday, August 13, 2010 8:14 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: ORACLE automatic update statistics better??
>
>
> The Oracle provided job will gather histograms by default and that is pretty
> the main difference from the statistics collection command you posted.
>
> You should not need to manually gather statistics except in those cases where
> you create and load a new table and want statistics now rather than wait on
> the Oracle job task to generate statistics and in those cases where the
> generated statistics do now work well. You may be better off without
> histograms on a specific table, etc....
>
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of dba1 mcc
> Sent: Friday, August 13, 2010 11:09 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: ORACLE automatic update statistics better??
>
> we have ORACLE 10GR2 on LINUX server. Our consultant don't allow us
> "manually" to run "update statistics". Consultant say ORACLE 10GR2 automatic
> update statistics algorithm better than manually command.
>
> The SQL command we used to manually run is:
>
> execute DBMS_STATS.GATHER_SCHEMA_STATS (ownname=> UPPER('&ID'),
> estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FO R ALL
> INDEXED COLUMNS SIZE 1', DEGREE=> DBMS_STATS.DEFAULT_DEGREE, cascade=>TRUE)
>
> I would like listen your opinion.
>
> Thanks.
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
