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.

Advertisements

3 Responses

  1. Hi,

    The above recommendation is quite interesting to me. I have been managing siebel production application and as of now, we were just doing step 3 for sure.
    For most of the tables, we use
    method_opt => ‘FOR ALL COLUMNS SIZE AUTO
    and for few tables, we use
    method_opt => ‘FOR ALL COLUMNS SIZE 1′.

    From greg rahn’s post,
    http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/

    I thought gathering stats with method_opt=>’FOR ALL INDEXED COLUMNS SIZE’ was a bad idea. We’re still working on the options and have found thi link which is very useful to me. Let me try these options to see how it works.

    Thanks anyway.
    Anantha Ramakrishnan

  2. i had tried your recommendation but the performance test that we carried out did not give good results. the above method gave the best performance result. i understand that there are lot many factors which may contribute to the performance of the database.
    thanks for visiting this blog and for your comment.

  3. Thanks a lot for your quick response. I am really excited and definitely planning to implement and test to see the results. I will keep you posted.

    Thanks.
    Anantha

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: