Home » RDBMS Server » Performance Tuning » index monitoring (oracle 10g)
index monitoring [message #473184] Thu, 26 August 2010 10:33 Go to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
Can any one tell me is that right way to get all the unused index in the system , if i put this query in batch job and execute it every night upto one months and store its data in a table and after one months i can get all the used indexes and left would be our unused indexes.
select
distinct p.object_name c1
from
dba_hist_sql_plan p,
dba_hist_sqlstat s
where
p.object_owner <> 'SYS'
and
p.operation like '%INDEX%'
and p.sql_id = s.sql_id
Re: index monitoring [message #473185 is a reply to message #473184] Thu, 26 August 2010 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put all indexes in monitoring mode.
Query v$segment_statistics.

Regards
Michel
Re: index monitoring [message #473189 is a reply to message #473185] Thu, 26 August 2010 10:56 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
but after putting all the indexes in monitoring stage we can use this query...
select
index_name
mon,
used
from
v$object_usage;
but my question is is there any issue with the original query which i posted.please advice.
Re: index monitoring [message #473195 is a reply to message #473189] Thu, 26 August 2010 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You have to pay to use DBA_HIST table
2/ They do not trap all queries

Regards
Michel
Re: index monitoring [message #473196 is a reply to message #473189] Thu, 26 August 2010 11:10 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Can any one tell me is that right way to get all the unused index in the system
I would say there is no doubt that the right way is to use index monitoring. But you need to be a bit clever with the query you use, because v$object_usage will show you only indexes in your current schema.

Actually, if you look up the view's creation statement, it is weird: why is a V$ view based on data dictionary tables?
Re: index monitoring [message #473204 is a reply to message #473196] Thu, 26 August 2010 12:14 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
ok. you mean to say if i use these two tables dba_hist_sql_plan p,
dba_hist_sqlstat s to get unused indexes i will not get the actual information.Please advice.
Re: index monitoring [message #473205 is a reply to message #473204] Thu, 26 August 2010 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is what I meant, you may not not have ALL information and so think an index is not used when it is and may be very important.
Imagine a table of a billion of phone calls, an index on (phone number, timestamp) which allows you access very quickly to a specific phone call information but is rarely used, then the plan will be quickly ages out the memory and may not be capture by AWR. If you drop it then you will have to wait quite a long time to get your phone call information.

Regards
Michel
Re: index monitoring [message #473206 is a reply to message #473205] Thu, 26 August 2010 12:22 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
it means that data will not capture in dba_hist_sql_plan history table also.
Re: index monitoring [message #473207 is a reply to message #473205] Thu, 26 August 2010 12:24 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
ok if u do not mind, is there any statement that oracle says that this is not the proper way to capturing the index monitoring.
Re: index monitoring [message #473208 is a reply to message #473207] Thu, 26 August 2010 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://wiki.oracle.com/page/Monitor+Index+Usage
Re: index monitoring [message #473209 is a reply to message #473207] Thu, 26 August 2010 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prashant_ora wrote on Thu, 26 August 2010 19:24
ok if u do not mind, is there any statement that oracle says that this is not the proper way to capturing the index monitoring.


There is no document that enumerates all the ways that are not proper to capture index monitoring.

But there is a document that tells what is the proper way to do it and this is SQL Reference and ALTER INDEX statement.

Regards
Michel

Re: index monitoring [message #473211 is a reply to message #473208] Thu, 26 August 2010 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Thu, 26 August 2010 19:26
http://wiki.oracle.com/page/Monitor+Index+Usage


/forum/fa/2115/0/ Nice! I didn't know wiki.oracle.com; it is bookmarked. /forum/fa/451/0/

Regards
Michel

Re: index monitoring [message #473212 is a reply to message #473209] Thu, 26 August 2010 12:40 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
can u please provide me that link.
Re: index monitoring [message #473213 is a reply to message #473211] Thu, 26 August 2010 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ok if u do not mind, is there any statement that oracle says that this is not the proper way to capturing the index monitoring.
Which came first SQL Standard alter index <index_name> monitoring usage;
or Oracle's DBA_HIST* tables?
Can YOU post URL showing using DBA_HIST* tables is valid way to capture index usage?

[Updated on: Thu, 26 August 2010 12:42]

Report message to a moderator

Re: index monitoring [message #473214 is a reply to message #473213] Thu, 26 August 2010 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>can u please provide me that link.
Are both SEARCH & GOOGLE broken for you?
Re: index monitoring [message #473216 is a reply to message #473212] Thu, 26 August 2010 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
prashant_ora wrote on Thu, 26 August 2010 19:40
can u please provide me that link.


1/ STOP using IM speak (like "u")
2/ It is the same link that I already provided you many times.

Regards
Michel
Re: index monitoring [message #530910 is a reply to message #473216] Fri, 11 November 2011 05:53 Go to previous messageGo to next message
francl
Messages: 8
Registered: November 2011
Location: Slovenia
Junior Member
Here is a link to a script which can be used to monitor indexes.
h t t p : //iziadmin.wordpress.com/2011/10/11/oracle-index-usage
Re: index monitoring [message #530925 is a reply to message #530910] Fri, 11 November 2011 06:18 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
I use this script for index monitoring. You can try it:

-- E. Nossova, Product TuTool : www.tutool.de


/* this script turns on and off index usage
monitoring, additionally it reports information
about index usage for one owner,
input parameters:
        index_owner - index owner, 
                      default: current user for
                      monitoring, all owners for
                      viewing,
        table_owner - table owner,
                      default: all owners,
        table_name - table name, 
                      default: all tables,
        index_name - index name,
                      default: all indexes,
        action - on - turn on index monitoring,
                 off - turn off index monitoring,
                 view - report information about
                        index usage,
                        default: view */

define index_owner='&index_owner'
define table_owner='&table_owner'
define table_name='&table_name'
define index_name='&index_name'
define action='&action'

set verify off
set feedback off
set pagesize 1000
set linesize 1000

col index_owner format a30
col index_name format a30
col table_name format a30
col start_monitoring format a20
col end_monitoring format a20
col used format a4
col monitoring format a10

declare
cursor cur_ind is
select owner, index_name from sys.dba_indexes
where
owner = nvl('&index_owner',user) and
owner not in ('SYS','SYSTEM') and
table_owner = nvl('&table_owner',table_owner) and
table_name = nvl('&table_name',table_name) and
index_name = nvl('&index_name',index_name);
begin
	if upper('&action') = 'ON' then
		for rec_cur_ind in cur_ind loop
			begin
				execute immediate 'alter index '|| rec_cur_ind.owner || '.' || rec_cur_ind.index_name ||' monitoring usage';
			exception when others then
				null;
			end;
		end loop;
	else
		if upper('&action') = 'OFF' then
			for rec_cur_ind in cur_ind loop
				begin
					execute immediate 'alter index '|| rec_cur_ind.owner || '.' || rec_cur_ind.index_name ||' nomonitoring usage';
				exception when others then
					null;
				end;
			end loop;
		end if;
	end if;
end;
/

select u.name index_owner, t.name table_name, io.name index_name,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,
       ou.start_monitoring,
       ou.end_monitoring
from sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where nvl(upper('&action'),'VIEW') in ('VIEW', 'ON', 'OFF')
  and u.name = nvl('&index_owner',u.name)
  and io.owner# = u.user#
  and i.obj# = ou.obj#
  and io.obj# = ou.obj#
  and t.obj# = i.bo#
  and io.name = nvl('&index_name', io.name)
  and t.name = nvl('&table_name', t.name)
order by u.name, t.name, io.name
/



undefine index_owner
undefine table_owner
undefine table_name
undefine index_name
undefine action


Re: index monitoring [message #530932 is a reply to message #530925] Fri, 11 November 2011 06:41 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Who is E. Nossova? /forum/fa/1599/0/
Re: index monitoring [message #530952 is a reply to message #530932] Fri, 11 November 2011 08:06 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
The author. Who else ? Razz
Re: index monitoring [message #530969 is a reply to message #530952] Fri, 11 November 2011 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your brother or sister. Wink

Regards
Michel
Re: index monitoring [message #530972 is a reply to message #530969] Fri, 11 November 2011 10:47 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You did nearly guess. Smile
Regards
Leonid
Re: index monitoring [message #531020 is a reply to message #530972] Fri, 11 November 2011 15:55 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is a female ("Nossova"), so I presume your background is Slavic (Russian, Ukrainian; Macedonian - could be, but not likely). She is related to you, Leonid (such as sister, wife, daughter etc.). I'd put my bet on your wife, if I had to. If I had to guess the name, it would be Elena or Ekaterina /forum/fa/917/0/.

Heh, playing Poirot. Kind of funnier than doing Oracle /forum/fa/451/0/
Re: index monitoring [message #531025 is a reply to message #531020] Fri, 11 November 2011 16:31 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Mr. Poirot,

you are absolutely right. The author is my wife and her name is Elena. Smile

Regards
Leonid
Previous Topic: Merge statement tuning for 100M records in table
Next Topic: Shrinking index
Goto Forum:
  


Current Time: Thu Mar 28 14:16:27 CDT 2024