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.
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';