Contente
Como Oracle recolhe amostras e grava todas as sessões ativas na base de dados, uma simples consulta permite obter o histórico de sentenças SQL de um espaço de tempo dado.
Importância
Oracle recolhe automaticamente amostras de todas as sessões que se executam na base de dados a cada segundo e guarda informação do histórico de SQL na vista de dicionário de dados v$active_session_history. Este é um buffer circular, portanto, ao ficar cheio, Oracle arquiva automaticamente a informação dele na vista de dicionário de dados dba_hist_active_sess_history antes de sobregravá-la.
Função
Pode-se obter um histórico das sentenças SQL executadas em um espaço de tempo na base de dados por meio de uma consulta a estas duas vistas. Por exemplo, a seguinte consulta devolve a lista dos primeiros 4000 caracteres das sentenças SQL executadas entre as 9:00 e as 9:05 da manhã, do dia 30 de setembro de 2010:
select a.sql_id,dbms_lob.substr(b.sql_text,4000,1) from dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('20100930:09:00','yyyymmdd:hh24:mi')
and to_date('20100930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
union all
select a.sql_id ,dbms_lob.substr(b.sql_text,4000,1)from v$active_session_history a ,v$sqlarea b
where sample_time between to_date('20100930:09:00','yyyymmdd:hh24:mi') and
to_date('20100930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
Considerações
Além disso, Oracle gera, a cada hora, estatísticas de execução das sentenças SQL no seu Automatic Workload Repository (Repositório automático de carga de trabalho). Você pode ver quais sentenças SQL exigem mais recursos em um momento dado fazendo uma consulta às vistas de dicionário de dados dba_hist_sqlstat e dba_hist_snapshot.
Efeitos
Por exemplo, a seguinte consulta devolve uma lista das sentenças SQL executadas entre as 9 e as 10 da manhã, além do tempo de CPU, tempo decorrido, tempo de io_wait e número de acessos a disco. A saída é ordenada por tempo de CPU.
select a.sql_id,dbms_lob.substr(b.sql_text,1000,1),cpu_time_delta,elapsed_time_delta,iowait_delta,disk_reads_delta from dba_hist_sqlstat a , dba_hist_sqltext b where a.sql_id=b.sql_id and snap_id = (select distinct snap_id from dba_hist_snapshot where to_Char(begin_interval_time,'yyyymmdd:hh24:mi')='20100930:09:00' and to_char(end_interval_time,'yyyymmdd:hh24:mi')='20100930:10:00') order by cpu_time
/