DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Dbms_stats Bug

  1. #1
    Join Date
    Jan 2002
    Posts
    474

    Dbms_stats Bug

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Dbms_stats Bug

    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.?.?.?
    Jeff Hunter

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187

    Re: Dbms_stats Bug

    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
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Jan 2002
    Posts
    474
    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

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I personnaly dont feel to use the proc "gather_database_stats"..

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

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2002
    Posts
    474
    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 ????

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter

  9. #9
    Join Date
    Jan 2002
    Posts
    474
    thanks jeff,

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

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width