-
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
-
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?
-
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
-
what is the db version?
can you run the procedure as sys?
steve
I'm stmontgo and I approve of this message
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|