0

I am having a problem with an application that requires very low latency (less than 100 ms per txn.) Every once in a while we will see spikes where transactions take 20-30 SECONDS. We looked at the ASH report and we are seeing "log file sync" waits, but we are unclear on the exact source of the problem. I want to compare this problem period with a normally operating period. However, the only way I know of to do this is using AWR compare period reports. The problem with this is that the periods are too long (30 mins). I need some way to compare very short periods of time after the problem has occurred since I have now way of knowing when it will happen. Any advice would be appreciated.

  • Why've you added a new question? Your previous one would just need a small edit – Philᵀᴹ Jul 26 '16 at 18:32
  • If it will help narrow it down, you can alter the interval of AWR down to 10 minutes (which is the minimum interval allowed for AWR) via: exec dbms_workload_repository.modify_snapshot_settings(null,10); – Kris Johnston Aug 1 '16 at 20:02
1

Directly query the DBA_HIST_ACTIVE_SESS_HISTORY view if you want a narrower view than an ASH or AWR can provide. Use something like this query:

select distinct user_id, session_id,session_serial#, sql_id, program, machine
from dba_hist_active_sess_history 
where sample_time between 
    to_date('20160819 1830','yyyymmdd hh24mi') and to_date('20160819 1835','yyyymmdd hh24mi')
and event = 'log file sync';

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.