Olá pessoal, vou apresentar brevemente como habilitar o monitoramento de modificações efetuadas nas tabelas de um banco de dados Oracle.
O principal objetivo desse tipo de monitoramento, ao meu ver, é saber as tabelas mais alteradas, a quantidade de alterações nessas tabelas desde a última coleta de estatísticas. Por que desde a última coleta de estatísticas? Porque toda vez que é coletado estatística de uma tabela essas informações são zeradas.
Sabe aquela conta que o Oracle faz para saber quando as estatísticas estão desatualizadas? Então, você pode fazer a mesma conta consultando a view dba_tab_modifications. Por padrão quando uma tabela sofre 10% de alteração o Oracle considera que suas estatísticas estão desatualizadas, então se você usa a dbms_stats.gather_*_stats com OPTIONS=>’GATHER STALE’ as estatísticas serão coletadas depois dessa quantidade de alterações. Você pode mudar o percentual de Stale Statistics com a procedure dbms_stats.set_*_prefs e o parâmetro STALE_PERCENT.
A versão do meu Oracle é 12.1.0.2, mas tudo isso funciona igual para as versões do Oracle Database 11g e 10g (acredito que funciona também no 9i, mas acho que não existe somente a procedure flush_database_monitoring_info, não cheguei a testar).
Vamos aos exemplos e testes…
Vou criar primeiro uma tabela de testes.
SQL> create table t as select * from hr.jobs; Table created.
Vamos verificar como essa tabela é criada por default.
SQL> SET PAGES 200 LIN 200 SQL> COL OWNER FOR A20 SQL> COL TABLE_NAME FOR A30 SQL> select owner, table_name, monitoring, num_rows, blocks, empty_blocks, avg_space, to_char(last_analyzed, 'dd/mm/yyyy hh24:mi:ss') last_analyzed from dba_tables where owner='SYS' and table_name='T'; OWNER TABLE_NAME MON NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE LAST_ANALYZED -------------------- ------------------------------ --- ---------- ---------- ------------ ---------- ------------------- SYS T YES
Veja que a tabela já é criada com o monitoramento habilitado, isso se deve porque o parâmetro statistics_level do meu banco de dados está com o valor TYPICAL. A mesma coisa acontece se o parâmetro estiver como ALL, o monitoramento só não será habilitado por default se a tabela for criada quando o parâmetro em questão estiver definido como BASIC. (Veja detalhes no tópico 13.3.1.8 do Performance Tuning Guide)
É importante saber como desabilitar e habilitar o monitoramento nas tabelas.
Para desabilitar:
SQL> alter table T nomonitoring; Table altered.
Então verificamos se foi desabilitado:
SQL> select owner, table_name, monitoring from dba_tables where owner='SYS' and table_name='T'; OWNER TABLE_NAME MON -------------------- ------------------------------ --- SYS T NO
Agora vamos habilitar novamente:
SQL> alter table T monitoring; Table altered.
Agora constatamos que a tabela está sendo novamente monitorada:
SQL> select owner, table_name, monitoring from dba_tables where owner='SYS' and table_name='T'; OWNER TABLE_NAME MON -------------------- ------------------------------ --- SYS T YES
Vamos verificar se nossa tabela já possui alguma alteração:
SQL> select table_name,inserts,updates,deletes,truncated,timestamp from dba_tab_modifications where table_name='T'; no rows selected
Vamos inserir alguns registros, realizar algumas alterações e exclusões dessa tabela para observarmos o resultado do monitoramento.
SQL> insert into t values ('OP_MGR', 'Operations Manager', 10000, 20000);
1 row created.
SQL> insert into t values ('BD_MEM', 'Board Member', 100000, 500000);
1 row created.
SQL> insert into t values ('IT_MGR', 'IT Manager', 10000, 30000);
1 row created.
SQL> update t set max_salary=35000 where job_id='IT_MGR';
1 row updated.
SQL> update t set max_salary=50000 where job_id='OP_MGR';
1 row updated.
SQL> update t set max_salary=900000 where job_id='BD_MEM';
1 row updated.
SQL> delete t where job_id='BD_MEM';
1 row deleted.
SQL> commit;
Commit complete.
Vamos verificar se as alterações foram gravadas pelo monitoramento da tabela:
SQL> select table_name,inserts,updates,deletes,truncated,timestamp from dba_tab_modifications where table_name='T'; no rows selected
Ué, onde estão as alterações feitas na tabela T?
Nem sempre essa informação vai imediatamente para as tabelas, então é necessário descarregá-las da área de memória da instância Oracle para as tabelas utilizando a procedure dbms_stats.flush_database_monitoring_info.
SQL> exec dbms_stats.flush_database_monitoring_info; PL/SQL procedure successfully completed.
Agora sim podemos ver as alterações que a tabela T sofreu.
SQL> select table_name,inserts,updates,deletes,truncated,timestamp from dba_tab_modifications where table_name='T'; TABLE_NAME INSERTS UPDATES DELETES TRU TIMESTAMP ------------------------------ ---------- ---------- ---------- --- --------- T 3 3 1 NO 10-SEP-16
Observamos que ao coletar estatísticas da tabela as informações de monitoramento são excluídas.
SQL> exec dbms_stats.gather_table_stats('SYS','T');
PL/SQL procedure successfully completed.
SQL> select table_name,inserts,updates,deletes,truncated,timestamp
from dba_tab_modifications
where table_name='T';
no rows selected
A descarga das informações da memória para as tabelas é feita em diferentes intervalos/momentos dependendo de cada versão. No Oracle 9i li em algum lugar que as informações são descarregadas a cada 15 minutos, no 10g a cada 3 horas, mas acho que nas versões atuais, 11g e 12c, esse intervalo não vale mais, pelo que consegui validar a descarga acontece quando o buffer fica cheio. Quando eu achar a referência atualizo o artigo, porque acredito que li isso em algum livro.
Este é um entendimento bastante simples, mas interessante sobre os comportamentos do banco de dados Oracle.
Já usei este tipo de procedimento apresentado no artigo até como um tipo trace de sessões vindas de um servidor de aplicação com Tomcat para poder saber as tabelas que estavam sendo alteradas por um determinado processo do sistema. Nesse cenário somente um usuário estava trabalhando no sistema, mas como não era possível isolar a sessão foi a única maneira que consegui para identificar algo do que estava sendo feito pelo sistema e isso ajudou o desenvolvedor a corrigir o código.
Espero que seja útil para vocês e se gostaram curtam e compartilhem para difundir o conhecimento na comunidade.
Como dica de teste sugiro você criar uma tabela de exemplo e realizar 11% de alteração nela e então coletar estatísticas com a opção Gather Stale para validar o que falei, você verá que realmente funciona e saberá como o Oracle calcula as estatísticas desatualizadas.
Referências:
http://docs.oracle.com/cd/E25178_01/server.1111/e16638/stats.htm#i41506
Abraços,
Franky