USING DBMS_STAT

posted Jan 31, 2012, 7:23 AM by Sachchida Ojha   [ updated Jan 31, 2012, 7:32 AM ]
DBMS_STAT package collects and manage optimizer statistics. Here are some most common tasks for DBMS_STAT.
1. Collect statistics for a table with default settings.

DBMS_STATS.gather_table_stats
(ownname =>'DBAREF',
tabname =>'EMP');

2. Collect statistics for the entire schema.

DBMS_STATS.gather_schema_stats
(ownname =>'DBAREF');

3. Collect statistics for any table in a schema that are "stale".

DBMS_STATS.gather_schema_stats
(ownname =>'DBAREF',
options =>'GATHER STALE');

4. Create histogram for all indexed columns.

DBMS_STATS.gather_schema_stats
(ownname =>'DBAREF',
method_opt =>'FOR ALL INDEXED COLUMNS SIZE AUTO');

5. Set the default collection to create histograms for indexed columns only if the column has a skewed distribution.

DBMS_STATS.set_database_prefs
(pname =>'METHOD_OPT',
pvalue =>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY');

6. Create statistics table.

DBMS_STATS.create_stat_table
(ownname =>'DBAREF',
stattab =>'mystattab');

7. Export statistics to a statistics table.

DBMS_STATS.export_table_stats
(ownname =>'DBAREF',
tabname =>'EMP'
stattab =>'mystattab'
statid => 'demo1');

8. Import statistics from a statistics table into the current schema.

DBMS_STATS.import_table_stats
(ownname =>'DBAREF',
tabname =>'EMP'
stattab =>'mystattab'
statid => 'demo1');








Comments