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,
);
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
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;
