Dbms_stats Bug

[ashley75]

Does anyone encounter the problem with DBMS_STAT??? When we use this packages, the CBO fails to user indexes on large tables, it also errors when you try to compute statistics on the tables that had check constraints. It's only worked if you run estimate on 50% or less.

[anandkl]

Hi,
I have been using this for our production DB's and things r working fine.Can u please email the error ur encountring.

regards
anandkl

[marist89]

Originally posted by ashley75
Does anyone encounter the problem with DBMS_STAT??? When we use this packages, the CBO fails to user indexes on large tables, it also errors when you try to compute statistics on the tables that had check constraints. It's only worked if you run estimate on 50% or less.

Your query may not be using the index on a large table because that is not the "best" plan according to the statistics. I am not aware of any bugs with dbms_stats and check constraints. Maybe if you post the exact errors.?.?.?

[stmontgo]

Originally posted by ashley75
Does anyone encounter the problem with DBMS_STAT??? When we use this packages, the CBO fails to user indexes on large tables, it also errors when you try to compute statistics on the tables that had check constraints. It's only worked if you run estimate on 50% or less.

eastimate at anything more than 50 is the same as compute unless I am mistaken

[ashley75]

Below is error I got when we the dbms_stats, it only happened on the huge tables with more 10million rows. there are so many posted on metalink about this dbms_stats and oracle mentioned it will be fixed on 9.2.0.5.

any thoughts???


BEGIN dbms_stats.gather_database_stats(cascade=>TRUE); END;
*
ERROR at line 1:
ORA-00904: : invalid identifier
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9857
ORA-06512: at "SYS.DBMS_STATS", line 10041
ORA-06512: at "SYS.DBMS_STATS", line 10134
ORA-06512: at "SYS.DBMS_STATS", line 10114
ORA-06512: at line 1

[abhaysk]

I personnaly dont feel to use the proc "gather_database_stats"..

BTW, r u taking stats on SYS tabs as well.. if so, dont

[ashley75]

I thought BEGIN dbms_stats.gather_database_stats doesn't collect statistics of SYS.

In any case, has anyone run dbms_stats on huge tables under 9.2.0.4 ????

[marist89]

I run dbms_stats.gather_table_stats(cascade=>true, ...) on HUGE tables all the time without problems. I run on 8.1.7.2, 8.1.7.4, and 9.2.0.4.

[ashley75]

thanks jeff,

question is "is that true dbms_stats.gather_database_stats will collect stat on SYS???"

[jmodic]

Originally posted by ashley75
thanks jeff,

question is "is that true dbms_stats.gather_database_stats will collect stat on SYS???"

All you need to find this out is to check DBA_TABLES (or DBA_INDESES etc etc) for any of the segments owned by SYS. If you find statistical information for them there (for example number of rows for tables or number of keys for indexes) then it apparently collects statistics for SYS objects....