About Statistic, any experience using METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'
- From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Feb 2004 12:07:36 -0400
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » About Statistic, any experience using METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'
