Recommendation Statistics generation for Siebel Database in 10g

Recommendation Statistics generation for Siebel Database in 10g: the optimizer mode is all_rows

1. For all tables that have more than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’,
estimate_percent => 20,
granularity => ‘ALL’,
cascade => TRUE,
degree => 8);

For large tables only take a 20 percent percent sample size, set 8 degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 1’ tells the db not to generate histograms for the indexed columns of these large tables.

2. For all tables that have more than 15 rows and less than 1 million rows use the following:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE);

For smaller tables let the db determine the estimate_percent and degrees of parallelism. The method_opt => ‘FOR ALL INDEXED COLUMNS SIZE 254’ tells the db to generate histograms for the indexed columns of these tables (in addition to other index stats generating mechanisms).

3. For all tables that have 15 rows or less delete the stats from these tables:

EXEC DBMS_STATS.DELETE_TABLE_STATS(
ownname => ‘SIEBEL’,
tabname => ‘THE_TABLE’,
force => TRUE);

The Oracle db optimizer is known to perform inefficiently if stats are generated on empty and very small tables. Therefore the solution is to delete any stats on these tables.

We generated the stats using the above mechanisms using the SQL below, this resulted in much better performance application wide.