Home » SQL & PL/SQL » SQL & PL/SQL » Index stats in oracle 8i
Index stats in oracle 8i [message #275061] Thu, 18 October 2007 03:44 Go to next message
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 #275071 is a reply to message #275061] Thu, 18 October 2007 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already have your answer: if you only want ONE command you can't use GATHER_INDEX but if you only want to gather INDEX statistics, you can't use GATHER_SCHEMA.

Regards
Michel
Re: Index stats in oracle 8i [message #275076 is a reply to message #275061] Thu, 18 October 2007 04:10 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member

In that case how do i gather stats of all the indexes ?
There should be some way ??


Ram
Re: Index stats in oracle 8i [message #275084 is a reply to message #275076] Thu, 18 October 2007 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already know the answer: a GATHER_INDEX per index.

Regards
Michel
Re: Index stats in oracle 8i [message #275087 is a reply to message #275061] Thu, 18 October 2007 04:20 Go to previous messageGo to next message
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 #275103 is a reply to message #275061] Thu, 18 October 2007 05:18 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi Experts,

This is really a big problem as i have over 600 indexes.

Is there any way using Analyze command?

Ram
Re: Index stats in oracle 8i [message #275104 is a reply to message #275103] Thu, 18 October 2007 05:26 Go to previous messageGo to next message
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 #275107 is a reply to message #275061] Thu, 18 October 2007 05:43 Go to previous messageGo to next message
ram_ocp
Messages: 49
Registered: December 2005
Location: Karnataka
Member
Hi,

Thanks for ur reply
Indeeed the procedure executed correctly without any errors but when i query index_stats it does not show any row

Ram
Re: Index stats in oracle 8i [message #275119 is a reply to message #275107] Thu, 18 October 2007 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
index_stats is not populate with this.
Query dba_ind_statistics.

Regards
Michel
Re: Index stats in oracle 8i [message #275141 is a reply to message #275119] Thu, 18 October 2007 08:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or look at the Last_Analyzed column on USER/ALL/DBA_INDEXES
Re: Index stats in oracle 8i [message #275172 is a reply to message #275061] Thu, 18 October 2007 12:24 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Index rebuild error
Next Topic: How Does Temp Table interact with rollback?
Goto Forum:
  


Current Time: Fri Dec 09 09:48:59 CST 2016

Total time taken to generate the page: 0.07763 seconds