2

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?

  • Are you connected to the right instance? Your tags suggest you are on a RAC, but you are quering the dba* views that to my knowledge are instance bound (like the v$-views). Does the statement show up in a clusterwide report (e.g. with ashrpti) – jmk Apr 4 '17 at 11:43
  • Yes, I am on a RAC and I have tried the query on both instances with the same results. An AWR report also shows no queries taking such a long time. However, I created the AWR report as the system user, should I do so as sys user? – jera Apr 4 '17 at 11:47
  • If AWR and ASH don't show any long running queries chances are that it's time to get in touch with your application team and investigate which statement runs slowly and where. running ASH/AWR as sys does not reveal more data. – jmk Apr 4 '17 at 11:56
  • Thank you, that's what I was looking for. Do you want to post this as an answer so I can accept it? – jera Apr 4 '17 at 12:04
  • 1
    Not everything you run is eligible to be shown in DHASH (or V$ASH). When Oracle samples sessions, the session must be on CPU or must be waiting for a non-idle wait event. If your sessions do really really small transactions really quickly, it is very likely they will not be picked up. Other than using colored_sql there isn't much you can do. – Raj Apr 4 '17 at 19:12
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.