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

Thread: DBMS_STATS.GENERATE_STATS does not works

  1. #1
    Join Date
    Nov 2002
    Posts
    31

    DBMS_STATS.GENERATE_STATS does not works

    Hi

    I setup DBMS_STATS to gather the stats for table and index but now when i run the PL/SQL batch to generate the stats it gives error

    ERROR at line 1:
    ORA-20000: Unsupported object type for generate_stats
    ORA-06512: at "SYS.DBMS_STATS", line 4835
    ORA-06512: at line 2

    What i am running is

    SQL> begin
    2 DBMS_STATS.GENERATE_STATS ('SCOTT','EMPLOYEE');
    3 end;
    4 /

    Any Idea ? I don't see example for GENERATE_STATS anywhere !!

    You help is appriciated.

    Thanks and Regards
    GC

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    http://otn.oracle.com/docs/products/...s2.htm#1003999
    GENERATE_STATS Procedure
    This procedure generates object statistics from previously collected statistics of related objects. For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.The currently supported objects are b-tree and bitmap indexes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2002
    Posts
    31

    Question RE: DBMS_STATS

    Hi jmodic

    Thanks for looking in. I am following the documentation.

    What i don't understand is what i am doing wrong in GENERATE_STATS statement that causing error.

    I did run

    DBMS_STATS.GATHER_TABLE_STATS for employee table for owner SCOTT

    Am i missing something ?


    Isn't GENERATE_STATS should show some kind of stats for table EMPLOYEE , if it had lots of table scan or something. May be i am wrong. Becoz i don't see any example output for GENERATE_STATS

    thanks
    GC

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    what is the db version?

    can you run the procedure as sys?

    steve
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Nov 2002
    Posts
    31
    Hi Steve,

    DB Version : 8.1.7

    I tried as user sys , system , scott. Same error


    Am i doing something wrong ? All i want to know is all the query running on table i put in GATHER_TABLE_STATS are using indexes or not and any other useful information GENERATE_STATS will give me.

    thanks
    GC

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: RE: DBMS_STATS

    Originally posted by gcxp2001
    What i don't understand is what i am doing wrong in GENERATE_STATS statement that causing error.
    Once again, a quote from the docs:
    The currently supported objects are b-tree and bitmap indexes.
    Isn't that clear enough? I guess SCOTT.EMPLOYEE is neither bitmap nor b-tree index, is it?

    In other words, as far as I understand the docs, this procedure enables you to *generate* artificial statistics for your indexes based on the actual statistics you have gathered for their underlying table.

    Personally I don't see much of a use in this procedure. I would rather gather actual statistics for the indexes to instead of artifically generate it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Nov 2002
    Posts
    31

    RE: DBMS_STATS

    Hi jmodic

    You are correct , EMPLOYEE is table. So according to doc , it will be gather stats for these 2 type of obejcts only. Then what exactly GATHER_TABLE_STATS , is it just used for transferring the stats from one instance to another instance ?

    thanks
    gc

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