Index stats in oracle 8i [message #275061] |
Thu, 18 October 2007 03:44  |
ram_ocp
Messages: 49 Registered: December 2005 Location: Karnataka
|
Member |
|
|
Hi,
I want to collect index stats for an oracle 8.1.7.4 database
The optimizer_mode=choose
Please let me know if i can use dbms_stats to collect the stats.
Also since no of indexes are >200.
Which command should i use GATHER_INDEX_STATS or GATHER_SCHEMA_STATS .As i want to run only one command to collect stats of all the indexes.
Ram
|
|
|
|
|
|
Re: Index stats in oracle 8i [message #275087 is a reply to message #275061] |
Thu, 18 October 2007 04:20   |
Arju
Messages: 1554 Registered: June 2007 Location: Dhaka,Bangladesh. Mobile:...
|
Senior Member |
 
|
|
DBMS.STATS.GATHER_INDEX_STATS(.....);
DBMS.STATS.GATHER_INDEX_STATS(.....);
DBMS.STATS.GATHER_INDEX_STATS(.....);
DBMS.STATS.GATHER_INDEX_STATS(.....);
and ... how many index you want.
You can easily make an scripts by querying from dba_indexes .
[Updated on: Thu, 18 October 2007 04:24] Report message to a moderator
|
|
|
|
Re: Index stats in oracle 8i [message #275104 is a reply to message #275103] |
Thu, 18 October 2007 05:26   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
No, it really isn't a problem. Try this :begin
FOR rec IN (SELECT index_name FROM user_indexes) LOOP
EXECUTE IMMEDIATE 'begin dbms_stats.gather_index_stats(user,:b1); end;' using rec.index_name;
END LOOP;
end;
/
|
|
|
|
|
|
Re: Index stats in oracle 8i [message #275172 is a reply to message #275061] |
Thu, 18 October 2007 12:24   |
ram_ocp
Messages: 49 Registered: December 2005 Location: Karnataka
|
Member |
|
|
Hi,
Michael,There is no dba_ind_statistics table in Oracle 8i.
JRB, can you let me know which table i can find the index height and del leaf rows as the query u gave doesnt populate index_stats table.These two information is sufficient for me using ur procedure.
otherwise i will need to run a complex script which i have right now.
Ram
|
|
|
Re: Index stats in oracle 8i [message #275176 is a reply to message #275172] |
Thu, 18 October 2007 12:33  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | table i can find the index height and del leaf rows
|
If this is what you're searching for, you have to use "analyze index validate structure" and query "index_stats" for each index.
Remember index_stats is (over)loaded each time you use analyze index statement.
Regards
Michel
|
|
|