Skip to content
March 11, 2014 / doganay

ALL SCRIPTS ABOUT INDEX MONITORING

--ALL MONITORINGS
-----------------
select o.owner, o.object_name,ou.start_monitoring,ou.end_monitoring,
decode(ou.flags,1,'YES','NO') used
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
and end_monitoring is null;

--MONITOR INDEXES THAT WERE NOT MONITORED BEFORE
------------------------------------------------
select 'ALTER INDEX '||owner||'.'||index_name||' MONITORING USAGE;' from dba_indexes
where
index_name not like '%PK_%'
and index_name not like '%_PK_%'
and index_name not like '%PK_%'
and index_name not like '%$%'
and index_name not like 'SYS_%'
and table_name||'.'||index_name not in
(
select o.owner||'.'||o.object_name
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
);

--MONITOR SOME TABLE(s)' INDEXes
--------------------------------
select 'ALTER INDEX '||owner||'.'||index_name||' MONITORING USAGE;' from dba_indexes
where lower(table_name) in (
'table1',
'table2'
) and index_name not like '%PK_%'
and index_name not like '%_PK_%'
and index_name not like '%PK_%'
and index_name not like '%$%'
and index_name not like 'SYS_%';

--MONITOR INDEXES THAT IS BIGGER THAN ITS TABLE
-----------------------------------------------
select 'alter index '||OWNER||'.'||indexname||' monitoring usage;' from
(
with
tabs as (select owner, segment_name tablename,sum(bytes/1024/1024) tablesize from dba_segments
where segment_type='TABLE' group by owner, segment_name),
inds as (select i.owner, i.index_name indexname, i.table_name tablename,
sum(s.bytes/1024/1024) indexsize from dba_indexes i
join dba_segments s on (i.owner=s.owner and i.index_name=s.segment_name)
group by i.owner, i.index_name, i.table_name)
select * from tabs natural join inds where indexsize > tablesize and indexsize>100
)
where indexname not like '%PK_%'
and indexname not like '%_PK_%'
and indexname not like '%PK_%'
and indexname not like '%$%'
and indexname not like 'SYS_%'
minus
select 'alter index '||owner||'.'||object_name||' monitoring usage;'
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id;

--MONITOR a TABLE's INDEXES WHICH HAVE GREATER THAN n INDEXes
-------------------------------------------------------------
--for ex: 6
select 'alter index '||owner||'.'||index_name||' monitoring usage;' from dba_indexes d
where exists
(
select null from dba_indexes where table_owner=d.table_owner and table_name=d.table_name
and table_owner not in ('XDB','SYS','OWF_MGR')
group by table_owner,table_name
having count(*)>6
)
and index_name not like '%PK_%'
and index_name not like '%_PK_%'
and index_name not like '%PK_%'
and index_name not like '%$%'
and index_name not like 'SYS_%'
and table_name||'.'||index_name not in
(
select o.owner||'.'||o.object_name
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
);

--DISABLE MONITORING WHICH WAS USED
-----------------------------------
select 'alter index '||o.owner||'.'||o.object_name||' nomonitoring usage;'
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
and ou.end_monitoring is null
and ou.flags=1;

--SIZE of ALL INDEXES THAT MONITORED a WHILE, NOT USED and CAN BE DROPPED
-------------------------------------------------------------------------
select sum(bytes)/1024/1024/1024 from dba_segments where segment_name in
(
select o.object_name
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
and ou.flags<>1
and end_monitoring is null
);

--DROP UNUSED INDEXES
---------------------
select 'drop index '||o.owner||'.'||o.object_name||';'
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
and ou.flags<>1
and end_monitoring is null;

--DISABLE ALL MONITORINGS
-------------------------
select 'alter index '||o.owner||'.'||o.object_name||' nomonitoring usage;'
from sys.object_usage ou, dba_objects o
where ou.obj#=o.object_id
and end_monitoring is null;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: