Something about running statistics. Got quite a large database of a few TB (datawarehouse), and was curious how fast the statistics are running, and if I could speed it up a little.  Database: 11g release 1.

First of all, the standard gathering of the statistics of a test_user:
exec dbms_stats.gather_schema_stats(‘DWH_USER_TEST’,DBMS_STATS.AUTO_SAMPLE_SIZE);

This lasted about 8 hours.

The machine has got 2 quad core-cpu’s at his disposal, so this must and can be used. It must be said by the way that the ‘degree’ of the tables are 1 (migrated tables from 8i). So that’s why it lasted that long.

I put the degree-preference on the schema (you can put this preference on several levels in 11g nowadays):
– exec dbms_stats.set_schema_prefs(‘DWH_USER_TEST’, ‘DEGREE’,’6′);

Ran the statistics:

– exec dbms_stats.gather_schema_stats(‘DWH_USER_TEST’,DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ‘for all columns size auto’,degree => DBMS_STATS.DEFAULT_DEGREE);

This lasted 1 hour!

Is there a penalty? Yes, a bit: the machine had an average load of 14 ,  which is reasonably busy, so we have to run this definitely during the night, and beware of scheduled batches of the users. Or put the degree lower…. Anyway, it’s important to keep control over the time when it is running.
The number of processes I counted were 14 at the time it ran, which also depends on the parameter ‘parallel_threads_per_cpu’, which is now 2 at our database. So : apparently 6 x 2=12 slaves + his own processes.

Now this was only a test-user. The production user is more as 10 times as big. The statistics of this user has been gathered in 2 days and 15 hours in the old fashioned way.  Put a degree of 5  on the schema, and ran it again: 10 hours and 10 minutes… Still quite a lot, but now I just need a night to accomplish the task.

Nice readable resources to read regarding this subject:

http://www.oracle.com/technology/products/bi/db/11g/pdf/twp_bidw_parallel_execution_11gr1.pdf
http://oracledoug.com/px_slaves.doc