Statistics in 11.2

Collecting Stats Faster

The problem is collecting optimizer statistics with DBMS_STATS.GATHER_SCHEMA_STATS happens serially and takes incredibly long time for huge databases. Particularly when we have LOB tables. Usually I need rebuild statistics after a schema is imported from previous version of the database to the latest one. So I don't care what load it brings to the database - it might be very intense operation but I want it to finish as soon as possible. And usually the load incurred by the usual GATHER_SCHEMA_STATS procedure is minimal so making it running in parallel would be an excellent idea.

One night, when statistics collection already taken a long time, I decided to terminated and go this way:

BEGIN

   DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL', 180); 

   DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','TRUE');

   DBMS_STATS.GATHER_SCHEMA_STATS( 'MYUSER',
        degree  => DBMS_STATS.AUTO_DEGREE,
        options => 'GATHER',
        cascade => TRUE,
        no_invalidate => FALSE
);

END;

/

The effect was terrific. Mainly because it depends on the parameter job_queue_processes and it was set to 1000. So the database was really under a load for some time but the process finished in 30 minutes. I don't think the job_queue_processed is set to a reasonable parameter here for daily operation but at least it worked for this case!
I specified options=>'GATHER' is required when you want to collects statistics for all objects in the schema. With the default 'GATHER AUTO' Oracle implicitly determines which objects need new statistics, and determines how to gather those statistics. When GATHER AUTO is specified, the only additional valid parameters are ownname, stattab, statid, objlist and statown; all other parameter settings are ignored. Returns a list of processed objects.

Checking the current status of concurrent statistics collection:

SELECT DBMS_STATS.GET_PREFS('CONCURRENT') pref FROM dual;

Resources

1. GATHER_SCHEMA_STATS in 11.2
2. Concurrent Statistics Gathering.
3. Understanding Optimizer Statistics (PDF).

Oracle 11g Statistics Collection (AUTOTASK)

      I noticed this subject comes back over and over: no statistics gathering job seems to be activated. Well, usually it is enabled until something unexpected has been done in the database. At 11g gathering optimizer stats it is enabled by default. It doesn’t exist (as in 10g) as a dedicated job anymore. Right now it is maintained using DBMS_AUTOTASK_ADMIN_PACKAGE. When I run a query checking the state of “auto optimizer stats collection” I can see it running every day.

SELECT * FROM DBA_AUTOTASK_JOB_HISTORY
WHERE client_name = 'auto optimizer stats collection'
ORDER BY job_start_time DESC;