In Oracle 11g: The automatic statistics gathering job for Oracle 11g is an internal procedure named GATHER_DATABASE_STATS_JOB_PROC.
Below are the steps in case if we want to delete & gather schema stats on adhoc basis.
Gathering statistics for all objects in a schema
- Ensure that the automatic statistics gathering job is scheduled, using the following SQL.
Output should be 'TRUE'.
Note: GATHER_STATS_PROG is the program that runs the internal procedure GATHER_DATABASE_STATS_JOB_PROC.
- Delete any existing statistics, using the following command from SQLPLUS.
- Change the default "ESTIMATE_PERCENT" to 100% for DBMS_STATS. Use the following procedure from SQLPLUS to change the default. Run this using the Oracle SYSDBA user ID:
- Run GATHER_DATABASE_STATS using the following command from SQLPLUS. Run this command using the Oracle SYSDBA user ID.
Gather Schema statistics
Gathering statistics an individual table
NOTE: For a more cautious approach as outlined in the text above and where column statistics are known not to be beneficial, Replace:
Note that the defaults for statistics gathering on different versions of Oracle are not necessarily the same, for example:
- ESTIMATE_PERCENT: defaults:
- 9i : 100%
- 10g : DBMS_STATS.AUTO_SAMPLE_SIZE (using very small estimate percentage)
- 11g: DBMS_STATS.AUTO_SAMPLE_SIZE (using larger estimate percentage - 100%)
- METHOD_OPT: defaults:
- 9i: "FOR ALL COLUMNS SIZE 1" effectively no detailed column statistics.
- 10g and 11g: "FOR ALL COLUMNS SIZE AUTO" - This setting means that DBMS_STATS decides which columns to add histogram to where it believes that they may help to produce a better plan.
Export and Import schema statistics
Easy way to export and import schema statistics – in case the optimizer is behaving strangely and you want to re-import old statistics when the optimizer behaved properly.
Create the table to store the statistics-
Export schema stats – will be stored in the ‘STATS_TABLE’
If required import these statistics back to TEST schema.
Finally drop the table created to backup the schema stats