Query not showing in dba_hist_active_sess_history

Posted on

Question :

The application team claims there was a statement that took about 30s to execute at about a certain time.

I tried to find out what queries had been executed in during this time with the following statement:

select a.sql_id, sample_time from dba_hist_active_sess_history a, dba_hist_sqltext b
        where sample_time between to_date('20170330:13:15','yyyymmdd:hh24:mi')
        and to_date('20170330:13:25','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
        order by 2;

However, only statements concerning rman, or the cloud control agent show up and no statement that seems to be from the application.

Could it be that some queries executed do not show up in the dba_hist_active_sess_history table and where else could I look for it?

Answer :

Afer checking that you are indeed on the correct database and if ASH- and AWR reports for both instances don’t show any long running queries it’s time to get in touch with your application developers. Try to find out which statement exactly is slown and why.

I had similiar issues with various applications and application environments and issues I encountered where

  • jdbc:thin behaviour different form jdbc:oci
  • issues with different ojdbc driver versions
  • code changes that split a transaction in multiple small connections resulting in the database doing nothing but opening and closing connections
  • configuration issues with connection pools

So you might have to look in different locations.

Leave a Reply

Your email address will not be published. Required fields are marked *