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

Home -> Community -> Mailing Lists -> Oracle-L -> About Statistic, any experience using METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'

About Statistic, any experience using METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Wed, 25 Feb 2004 12:07:36 -0400
Message-ID: <003301c3fbb9$857bcaf0$2501a8c0@dazasoftware.com>


Hi, here is the most basic about statistic, I would like to know if some one has experiece about the advangates of using EXEC DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=>'ADM', ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'); instead of
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('ADM','COMPUTE'); Thank you

3.2 Statistics
The optimizer works based on statistics, if they are old, or inaccurate you'll get a wrong execution plan.
You must recalculate statistics every time an important change had happened in it:
· Periodically, based on normal changes in a database
· After importing a big amount of data
· When distinct values in primary columns change
· After creating indexes and table

3.2.1 Test database
If this is the first time you get statistics, you must remember some database has fixed execution plans, or any other consideration can cause a statistics recalculation cause serious problems, as general rule do it first in ha test database, before doing in the production database. 3.2.2 Recalculating statistics
Oracle recommends DON'T USE ANALYZE to gather statistics, the package USE DBMS_STATS, this package get more statistics, specially for new features. Don't execute DBMS_STATS on SYS schema.
This command has dozens of options, like parallel execution, etc. etc. you must read them.
To gather all statistics
EXEC DBMS_STATS.GATHER_DATABASE_STATS(); To gather statistics in a schema:
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('ADM','COMPUTE'); To gather statistics in a schema, more precisely, the one we use, because our database is a small database:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=>'ADM', ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Feb 25 2004 - 10:09:47 CST

Original text of this message

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