Dbms_Stats with method_opt

Hi,

Platform: windows server 2003
Database : 10.2.0.4

Recently we have faced an problem with Gathering statistics which leads to
an oracle internal error (Ora-00600).

There is a  production database which has doing some  large transactions.

we usally gather the statistics for this database bettweeen the interval of two weeks .

we will gather the ststistics using the folowing method

exec dbms_stats.gather_schema_stats(ownname=>’user01′,estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);

Last night we have gathered statistics for the a schema .
Suddenly  Only the next day morning Our users reported that some Oracle errors is
flashed  on one of our applications when accessing some particular form .

Then we tried to find the table on which that particular form is been accessing,
and we manually quried a simple statement on tht table:

SQL> select * from Tabular_data;
select * from Tabular_data
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [19004], [], [], [], [], [], [], []

After some search about the argument [19004] we finally understood that it is as a Bug .

This bug is due to the miscalculation of the Oracle’S Automatic Historgram collection on the table
columns .

since we dint mention any method_opt parameter on the above statement , it leads to its default value

method_opt=>’FOR ALL COLUMNS SIZE AUTO’ (Oracle 10g’s Default value)

So we decided to include the method_opt parameter with the size 1 which is the default value in
Oracle 9i.

method_opt=>’FOR ALL COLUMNS SIZE 1′ (Oracle 9i’s Default value)

This method will disable the collection of Histograms.

if we cant loose histograms , Ofcourse the Another way to escape from this error is to lock the

Statistics for that particular table and to proceed with the older approach .
SQL> exec dbms_stats.lock_table_stats(‘USER01′, ‘TABULAR_DATA’);

PL/SQL procedure successfully completed.

SQL> SELECT stattype_locked FROM dba_tab_statistics

WHERE table_name=’TABULAR_DATA’;

STATTYPE_LOCKED
——————–
ALL

So you cant able to gather statistics on the locked table.