Um erro muito comum em empresas que possuem equipes distintas de DBA, AD e Desenvolvimento é criar as estruturas de banco sem o devido cuidado em indexar as FK’s. Sistemas e melhorias são feitos diariamente e isso pode incluir novos dados, colunas, constraints e demais objetos no banco dedados.

Um script que costumo rodar semanalmente é o que verifica se as FK’s estão indexadas, pois dependendo do uso da tabela, podemso ter um aumento do wait TM CONTENTION. NO meu caso, todos os schemas começam com DB, portanto eu ajustei em 2 lugares para trazer somente os dados referente á esses schemas:

[code language=”sql”]
AND a.owner LIKE ‘DB%’
WHERE c.index_owner LIKE ‘DB%’
[/code]

Basta adaptar o script ás suas necessidades, ou se preferir não filtrar nada, retira esse trecho do código e execute-o.

O script é simples mas de grande utilidade, conforme pode ser visualizado abaixo:

[code language=”sql”]
SELECT CASE
WHEN b.table_name IS NULL THEN ‘unindexed’
ELSE ‘indexed’
END AS status,
a.owner,
a.table_name AS table_name,
a.constraint_name AS fk_name,
a.fk_columns AS fk_columns,
b.index_name AS index_name,
b.index_columns AS index_columns
FROM (SELECT a.owner,
a.table_name,
a.constraint_name,
Listagg(a.column_name, ‘,’)
within GROUP (ORDER BY a.position) fk_columns
FROM dba_cons_columns a,
dba_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type = ‘R’
AND a.owner LIKE ‘DB%’
AND a.owner = b.owner
GROUP BY a.owner,
a.table_name,
a.constraint_name) a,
(SELECT table_name,
index_name,
Listagg(c.column_name, ‘,’)
within GROUP (ORDER BY c.column_position) index_columns
FROM dba_ind_columns c
WHERE c.index_owner LIKE ‘DB%’
GROUP BY table_name,
index_name) b
WHERE a.table_name = b.table_name(+)
AND b.index_columns(+) LIKE a.fk_columns
|| ‘%’
ORDER BY 1 DESC,
2;
[/code]