Databases Reference
In-Depth Information
Oracle advises us to use another method to compute statistics, namely, the
DBMS_STATS
package, which allows deleting statistics, exporting, importing, and gathering statistics in
parallel. The following statement analyses the schema
HR
:
EXEC DBMS_STATS.gather_schema_stats('HR');
ANALYZE
and the use of
DBMS_UTILITY
illustrated earlier
are supported for backward compatibility only; use the package
DBMS_STATS
to collect statistics.
Similarly, we can gather statistics on tables, indexes, or database. Even with
DBMS_STATS
we can use the
ESTIMATE
method, as in the first of the following examples:
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 20);
EXEC DBMS_STATS.gather_table_stats('HR', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('HR', 'EMP_JOB_IX');
Using the
DBMS_STATS
package we can also delete statistics, as shown:
EXEC DBMS_STATS.delete_table_stats('HR', 'EMPLOYEES');
To transfer statistics between different databases, we have to use a statistics table, as
shown in the following steps:
1.
Create the statistics table on the
source
database.
2.
Export the statistics from the data dictionary to the statistics table.
3.
Move the statistics table (Export/Import, Datapump, Copy) to the
target
database.
4.
Import the statistics from the statistics table to the data dictionary.
5.
Drop the statistics table.
The corresponding statements to execute on the
source
database are as follows:
EXEC DBMS_STATS.create_stat_table('DBA_SCHEMA', 'MY_STAT_TABLE');
EXEC DBMS_STATS.export_schema_stats('DBA_SCHEMA', 'MY_STAT_TABLE', NULL,
'APP_SCHEMA');
With these statements we have created the statistics table
MY_STAT_TABLE
in the
DBA_
SCHEMA
and populated it with data from the
APP_SCHEMA
(for example,
HR
).
Then we transfer the
MY_STAT_TABLE
to the
target
database; using the export/import
command line utilities we export the table from source database and then import the table
into the target database, in which we execute the following statements:
EXEC DBMS_STATS.import_schema_stats('APP_SCHEMA', 'MY_STAT_TABLE', NULL,
'DBA_SCHEMA');
EXEC DBMS_STATS.drop_stat_table('DBA_SCHEMA', 'MY_STAT_TABLE');
