setting stats_bucket_count for a single table

[Former Member]

I have performance problem in ECC7.00 with oracle 10.2.0.2 caused by CBO taking wrong index. I manually captured stats using DBMS_STATS.GATHER_TABLE_STATS using higher number of buckets and CBO changed execution plan and now using better index.

I know that I can set parameter stats_bucket_count to change number of buckets Oracle used to capture stats for Histograms, but I do not want to change it for all tables in the database, but only for one table (the one I have fixed the performance when increased number of buckets). Do you know how to set number of buckets for histograms for a single table in SAP (I can change table dbstatc or init<SID>.sap parmeter file).

[Former Member]

I am running BRTOOLS 7.00 (28) , Oracle database is 10.2.0.2 , ECC 7.00

this is installation that is set already a year ago.

My question is if anyone knows how I can set custom number of buckets for the statistics gathering for a particular table (default value is 75 for all tables and I want to set it to custom value = 200 for a particular table)

[stefan_koehler]

Hello Andrija,

i don't know any parameter / option where this can be specified. But you can lock the statistics for your table with the 200 buckets.

If the execution plan is fine just lock the statistics: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1043993

I have done this for some tables in our system too and it works fine.

Regards

Stefan

[fidel_vales]

Hello,

Unfortunately there is only a "global" parameter stats_bucket_count.

It will affect all tables with histograms, so i do not think it is good here.

What you can do is to execute brconnect only for this table and use the parameter "-b|-bucket <count>"

Syntax should be something like:

brconnect -u / -c -t <table> -f stats -b 200

[JPReyes]

Is this a new installation or an upgrade?...

I remember having a similar issue after an upgrade from Oracle 9 to 10.... basically a problem with the Optimizer parameters.... also i applied some Opatches related to the issue.

Regards

Juan