The STATISTICS_LEVEL parameter was introduced in Oracle version 9 (9.2) to control allmajor statistics collections or advisories in the database. The level of the setting affects a number of statistics and advisories that are enabled. These listed below are for 10g and above The TYPICAL level is recommended for most production databases to enable the […]

We might have a requirement to compare stats for a table between two different systems, two different statid in the user statistics table, user statistics table and dictionary. All these requirements are met by DBMS_STATS new procedure DIFF_TABLE_STATS_IN_STATTAB. In this post, we will see how to use this procedure to compare statistics How to compare

We can transfer statistics between servers allowing consistent execution plans between servers with varying amounts of data. This is possible using the DBMS_STATS package Here are the steps to do for a table Step 1. Create the stat table EXEC DBMS_STATS.CREATE_STAT_TABLE(‘SCOTT,’STATTAB’, ‘TOOLS’); Step 2 Export table statistics to statistics table. EXEC DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>’SCOTT’,TABNAME=>’EMP’, STATTAB=>’STATTAB’,STATID=>’EXP_1′,STATOWN=>’SCOTT); Step 3

We can import statistics from the user statistics table identified by stattab and stored in the data dictionary. Basically, you can import the statistics which you exported sometime before to the use statistics table. To import statistics in Oracle, you can use the DBMS_STATS package. This package provides several procedures and functions for managing database statistics.

To export statistics in Oracle, you can use the DBMS_STATS package. This package provides several procedures and functions for managing database statistics. Here are the basic steps: (1) Connect to your database as a user with the necessary privileges (such as SYS or SYSTEM). (2) create a stat table to hold the statistics EXEC DBMS_STATS.CREATE_STAT_TABLE(‘SCOTT’,’STATTAB’);