audit sur index inutile

Bonjour,

je suis sous oracle 10gR2, je voulais savoir si il existait un outils ou un package oracle qui permette de déterminer dans une base les indexes inutiles (soit en doublon ou une colonne indexé deux fois).

Merci d'avance.

Perso, j'y vais avec script et monitoring :
- monitoring usage des index pour vérifier, après un certain temps, s'ils sont utilisés
- select de v$object_usage (dans le schéma propriétaire)

Pour détecter les doublons :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
COLUMN table_owner      FORMAT a10               HEADING  'Table|Owner'
COLUMN table_name       FORMAT a30 word_wrapped  HEADING  'Table Name'
COLUMN index_name       FORMAT a30 word_wrapped  HEADING  'Index Name'
COLUMN index_cols       FORMAT a30 word_wrapped  HEADING  'Index Columns'
column redun_index      FORMAT a30 word_wrapped  HEADING  'Redundant Index'
COLUMN redun_cols       FORMAT a30 word_wrapped  HEADING  'Redundant Columns'
SELECT ai.table_owner  table_owner,
       ai.table_name   table_name,
       ai.index_name   index_name,
       ai.columns      index_cols,
       bi.index_name   redun_index,
       bi.columns      redun_cols
FROM 
( SELECT a.table_owner,
         a.table_name, 
         a.index_name, 
             MAX(DECODE(column_position, 1,
SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 2,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 3,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 4,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 5,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 6,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 7,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 8,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 9,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,10,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,11,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,12,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,13,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,14,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,15,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,16,',
'||SUBSTR(column_name,1,30),NULL)) columns
    FROM dba_ind_columns a
   WHERE a.index_owner not in ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS','PERFSTAT')
   GROUP BY a.table_owner,
            a.table_name,
            a.index_owner,
            a.index_name) ai, 
( SELECT b.table_owner,
         b.table_name,
         b.index_name, 
             MAX(DECODE(column_position, 1,
SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 2,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 3,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 4,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 5,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 6,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 7,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 8,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position, 9,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,10,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,11,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,12,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,13,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,14,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,15,',
'||SUBSTR(column_name,1,30),NULL)) || 
             MAX(DECODE(column_position,16,',
'||SUBSTR(column_name,1,30),NULL)) columns
    FROM dba_ind_columns b
   GROUP BY b.table_owner,
            b.table_name,
            b.index_owner,
            b.index_name ) bi
WHERE ai.table_owner     = bi.table_owner
  AND ai.table_name      = bi.table_name
  AND ai.columns        LIKE bi.columns || ',%'
  AND ai.columns        <> bi.columns
ORDER BY ai.table_owner,
         ai.table_name,
         bi.index_name;

Merci 13thFloor pour ta réponse et ta solution que je vais tester de suite.

Si jamais je trouve une autre piste qui peut être intéressante je la posterai.

Attention au calcul des stats : ils faut les désactiver; car ils vont influencer le monitoring - cas vécu -

Merci pour cette précision,

je vais faire des tests pour voir mais cela me semble étrange car si nous mettons en place un monitoring des indexes durant une période assez longue nous ne pouvons pas rester sans recalculer les stats.

Citation:

Envoyé par Z3phur

Merci pour cette précision,

je vais faire des tests pour voir mais cela me semble étrange car si nous mettons en place un monitoring des indexes durant une période assez longue nous ne pouvons pas rester sans recalculer les stats.

Faites-vous allusion à ce qui PpPool dit? Voici ce que j'ai dans les notes de mon propre étude:

"After starting to monitor index usage, Dbms_Stats.Gather_(Schema|Table)_Stats with cascade=>true
will turn v$object_usage.used to 'YES' as does dbms_Stats.Gather_Index_Stats, unless the index is empty. Analyze doesn't have this problem. (see Bug:4615996 and Bug:4432354)"

de référence:
http://yong321.freeshell.org/oranote...henAnalyze.txt