Monitoramento de uso de Indices.

Deseja verificar se os índices do Schema estão sendo utilizados ? Siga estes passas

(1) Coloque os indices para ser monitorados :

 Rode a query abaixo,e depois pegue o resultado da mesma
e rode como um script.

SELECT 'ALTER INDEX '||INDEX_NAME||' MONITORING USAGE;' SQL
  FROM USER_INDEXES
 WHERE INDEX_TYPE = 'NORMAL'
 ORDER BY TABLE_NAME, INDEX_NAME;

(2)Deixe por um período de tempo estes indices em monitoramento.Depois disso
você pode executar um select na view V$OBJECT_USAGE e verificar o uso do seu
indice.

select INDEX_NAME, TABLE_NAME, MONITORING, USED, START_MONITORING, END_MONITORING
  from V$OBJECT_USAGE
 

 (3) Como toda monitoria tem custo,depois disso retire os mesmos indices do
 monitoramento.

  Rode a query abaixo,e depois pegue o resultado da mesma
e rode como um script.

SELECT 'ALTER INDEX '||INDEX_NAME||' NOMONITORING USAGE;' SQL
  FROM USER_INDEXES
 WHERE INDEX_TYPE = 'NORMAL'
 ORDER BY TABLE_NAME, INDEX_NAME;

Dependência entre Funções/Procedures

Pessoal Segue uma query que me ajuda muito para identificar a dependência entre funções/procedures no seu schema. SELECT UO.OBJECT_NAME, UD.NAME FROM USER_OBJECTS UO LEFT JOIN USER_DEPENDENCIES UD ON UO.OBJECT_NAME = UD.REFERENCED_NAME WHERE UO.OBJECT_TYPE IN ('FUNCTION','PROCEDURE') AND UD.REFERENCED_OWNER = :pSchema AND UD.REFERENCED_TYPE IN ('FUNCTION','PROCEDURE') ORDER BY UO.OBJECT_NAME

Analise do seu SGDB com o pacote DBMS_STATS

(1) Todo o seu SGDB Oracle (Tabelas e índices)

EXEC DBMS_STATS.gather_database_stats;

Utilizando somente X % das linhas para definição

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

Utilizando somente X % das linhas para definição e cascade nas estatisticas anteriores.

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

(2) Apenas um schema

EXEC DBMS_STATS.gather_schema_stats('SCOTT');
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

(3) Apenas uma tabela do seu schema.

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);


(4) Atualiação das estatisticas do dicionario de dados (data dictionary).

EXEC DBMS_STATS.gather_dictionary_stats;


(5) Somente os indices de um schema.

EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK', estimate_percent => 15);


(6) Atualizar estatistica do System Stats

EXEC DBMS_STATS.gather_system_stats;

(7) Outras formas de atualizar estatística (legado)


ANALYZE TABLE employees COMPUTE STATISTICS;
ANALYZE INDEX employees_pk COMPUTE STATISTICS;

ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;

EXEC DBMS_UTILITY.analyze_schema('SCOTT','COMPUTE');
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_schema('SCOTT','ESTIMATE', estimate_percent => 15);

EXEC DBMS_UTILITY.analyze_database('COMPUTE');
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_rows => 100);
EXEC DBMS_UTILITY.analyze_database('ESTIMATE', estimate_percent => 15);

(8) Exemplo de criação de job para execução continua de análise.

SET SERVEROUTPUT ON
DECLARE
l_jobEst NUMBER;
BEGIN
DBMS_JOB.submit(l_jobEst,
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
SYSDATE,
'SYSDATE + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || l_jobEst);
END;
/

EXEC DBMS_JOB.remove(X);
COMMIT;