Monitorando operações de uma tabela atraves da DBA_TAB_MODIFICATIONS

Alguns dias atrás estava precisando monitorar uma tabela da aplicação do cliente para registrar a quantidade de operações DML que ela estava sofrendo.

Após algumas buscas encontrei a tabela do Oracle DBA_TAB_MODIFICATIONS que conforme documentação registra as modificações sofridas nas tabelas do banco de dados após a ultima coleta de estatisticas, ou seja, a cada nova coleta de estatistica sobre a tabela seus registros na DBA_TAB_MODIFICATIONS são descartados(zerados!).

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

De qualquer forma, minha necessidade era identificar as operações DML sofridas na tabela X no período de 1 hora. Como o Oracle não atualiza estas informações constantemente, podemos utilizar a procedure FLUSH_DATABASE_MONITORING para baixar as informações de monitoramento da memória para as tabelas de dicionário (*_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='PRODUCAO' and TABLE_NAME='X';
 
HORARIO             UPDATES
---------------- ----------
24/09/2012 10:00     127014

Observem que realizei um flush para atualizar as informações e posteriormente obter a quantidade de UPDATES realizados até o momento. Depois de 1 hora realizei o mesmo procedimento, logo, 318558 – 127014 = 191.544 UPDATES registrados!

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
Procedimento PL/SQL concluido com sucesso.
 
HORARIO             UPDATES
---------------- ----------
24/09/2012 11:00     318558

Vamos fazer alguns testes 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 nada 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';
 
n?o ha linhas selecionadas

Vamos baixar os registros de monitoramento da memória e realizar 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
---------------- ---------- ---------- ----------
24/09/2012 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
---------------- ---------- ---------- ----------
24/09/2012 12:03          1          2          0

Agora se executarmos uma coleta de estatisticas na tabela, como já mencionado, 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

Então fica a dica de como podemos monitorar algumas operações sobre uma tabela. Foi muito útil para mim e talvez seja para vocês!

OBS: Para a execução do DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO é necessário o privilégio de ANALYZE ANY 

Referências:

http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4149.htm http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#i1036624