Oracle 11g: Index monitoring usage (appunti grezzi)


Ho creato questo script per mettere in monitoring gli indici di determinati schema del vostro db..

SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 150
SET VERIFY OFF
SET ECHO OFF
SET PAGESIZE 1000
SELECT ‘ALTER INDEX “‘ || OWNER ||'”.”‘ || INDEX_NAME || ‘” monitoring usage ;’
FROM ALL_INDEXES
WHERE OWNER=’schema1′
OR OWNER=’schema2′
OR OWNER=’schema3′
ORDER BY OWNER,INDEX_NAME
SPOOL usage_indexes.sql
/
SPOOL OFF
SET VERIFY ON
SET LINESIZE 80
SET HEAD ON
SET FEED ON
SET PAGESIZE 24
SET ECHO ON

questo codice attraverso uno spool genera un altro script (usage_indexes.sql) che mette
in monitor gli indici di determinati schema, in questa maniera successivamente potrete
avere un calcolo del reale uso di questi indici (ed eliminare quelli che non servono..).

I modi per determinare quanto gli indici vengono usati sono per esempio:

SELECT owner, object_name, start_monitoring, end_monitoring, DECODE (flags, 0, ‘NO’, ‘YES’) USED FROM sys.object_usage o, dba_objects d WHERE O.OBJ# = D.OBJECT_ID;

oppure (meglio):

SELECT object_owner, object_name, COUNT(*) use_count FROM dba_hist_sql_plan WHERE object_type LIKE ‘INDEX%’ GROUP BY object_owner, object_name;

bye 😉

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.