Monitorando operações de uma tabela através da DBA_TAB_MODIFICATIONS

Muitas vezes somos questionados sobre quantas operações DML (Data Manipulation Language) ocorreram sobre determinadas tabelas. Pois bem, no Oracle database existe a view DBA_TAB_MODIFICATIONS que demonstra a quantidade de modificações sofridas nas tabelas do banco de dados desde a ultima coleta de estatísticas sofrida em cada objeto.

SQL> desc DBA_TAB_MODIFICATIONS
 Nome                                      Nulo?    Tipo
 ----------------------------------------- -------- ----------------------------
 TABLE_OWNER                                        VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_NAME                                  VARCHAR2(30)
 INSERTS                                            NUMBER
 UPDATES                                            NUMBER
 DELETES                                            NUMBER
 TIMESTAMP                                          DATE
 TRUNCATED                                          VARCHAR2(3)
 DROP_SEGMENTS                                      NUMBER

É importante destacar que estas informações serão computadas apenas para as tabelas com o atributo MONITORING.

Até a versão 10g, era especificado a palavra-chave MONITORING na instrução CREATE (ou ALTER) TABLE para ativar o monitoramento das estatísticas do objeto.

A partir da versão 11g as palavras-chave MONITORING e NOMONITORING foram depreciadas e as estatísticas passaram a sere coletadas automaticamente. Se você especificar essas palavras-chave, elas serão ignoradas. Para desabilitar o monitoramento de uma tabela a partir do 11g, defina o parâmetro de inicialização STATISTICS_LEVEL como BASIC. Seu padrão é TYPICAL, que permite a coleta automática de estatísticas.

Como o Oracle não atualiza estas informações constantemente, podemos utilizar a procedure FLUSH_DATABASE_MONITORING do pacote DBMS_STATS para baixar as informações de monitoramento da memória para as tabelas de dicionário acessíveis pelas views (*_TAB_MODIFICATIONS, *_TAB_STATISTICS e *_IND_STATISTICS).

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  
Procedimento PL/SQL concluido com sucesso.
  
SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, UPDATES from DBA_TAB_MODIFICATIONS where TABLE_OWNER='TESTE' and TABLE_NAME='EXEMPLO';
  
HORARIO             UPDATES
---------------- ----------
13/07/2019 10:00     127014

Depois de 1 hora realizei o mesmo procedimento de flush de dados, logo, 318.558 – 127.014 = 191.544 UPDATES registrados!

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  
Procedimento PL/SQL concluido com sucesso.

SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, UPDATES from DBA_TAB_MODIFICATIONS where TABLE_OWNER='TESTE' and TABLE_NAME='EXEMPLO';
  
HORARIO             UPDATES
---------------- ----------
13/07/2019 11:00     318558

Mais um exemplo para melhor evidenciar o monitoramento registrado pelo Oracle na DBA_TAB_MODIFICATIONS:

SQL> create table teste.teste (cod number);
  
Tabela criada.
  
SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_OWNER='TESTE' and TABLE_NAME='TESTE';
  
n?o ha linhas selecionadas

Observem que ainda não temos qualquer registro coletado para a tabela TESTE. Vamos então adicionar um registro:

SQL> insert into teste.teste values (1);
  
1 linha criada.
  
SQL> commit;
  
Commit concluido.
  
SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_OWNER='TESTE' and TABLE_NAME='TESTE';

Baixando os registros de monitoramento da memória e realizando novamente a consulta:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  
Procedimento PL/SQL concluido com sucesso.
  
SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_OWNER='TESTE' and TABLE_NAME='TESTE';
  
HORARIO             INSERTS    UPDATES    DELETES
---------------- ---------- ---------- ----------
13/07/2019 12:02          1          0          0

Um teste com updates:

SQL> update teste.teste set cod=2 where cod=1;
  
1 linha atualizada.
  
SQL>  update teste.teste set cod=1 where cod=2;
  
1 linha atualizada.
  
SQL> commit;
  
Commit concluido.
  
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
  
Procedimento PL/SQL concluido com sucesso.
  
SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_OWNER='TESTE' and TABLE_NAME='TESTE';
  
HORARIO             INSERTS    UPDATES    DELETES
---------------- ---------- ---------- ----------
13/07/2019 12:03          1          2          0

Agora se executarmos uma coleta de estatísticas na tabela, todos os registros são zerados!

SQL> execute DBMS_STATS.GATHER_TABLE_STATS (ownname => 'TESTE',TABNAME =>'TESTE',estimate_percent => 100,method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => null,granularity => 'ALL', cascade => TRUE);
  
Procedimento PL/SQL concluido com sucesso.
  
SQL> select to_char(TIMESTAMP,'dd/mm/yyyy hh24:mi') HORARIO, INSERTS, UPDATES, DELETES from dba_tab_modifications where TABLE_OWNER='TESTE' and TABLE_NAME='TESTE';
  
n?o ha linhas selecionadas