ASH to the rescue... for now...

After 4 unexplained crashes and a stalled Sev 1 SR I was pulling my hair out. Particularly vexing was the fact that a user was running a distributed query which was crashing a remote database.

I isolated the 4 crash times for the remote database and I wanted to see what users and SQLs were running on the local database up until the crash time. So for a crash happening around 11:16 I would use the following query. Note that access to these queries requires licensing of Diagnostic and Tuning packs :

SELECT DISTINCT SQL_ID, 
       (SELECT USERNAME FROM DBA_USERS WHERE USER_ID=DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID) AS USERNAME 
  FROM dba_hist_active_sess_history 
 WHERE sample_time between to_date('2017-02-21 11:15','YYYY-MM-DD HH24:MI') 
       AND to_date('2017-02-21 11:16','YYYY-MM-DD HH24:MI') ORDER BY 2;

With a minute range I was guaranteed to get at least a few samples of what was going on before the crash. The DISTINCT filters out any duplicates so I'm left with a list of active SQL_IDs and their users.

After running this and adjusting times to coincide with the 4 crashes I was able to find 1 common user and 3 distinct SQL_IDs across the 4 crashes.

I dove into the three SQL_IDs using this:

select * From dba_hist_sqltext where SQL_ID='sql_id_here';

And discovered they were all a very similar format with a huge "IN" dominating the WHERE clause. At least while we work the SR for a final solution we know what SQL _not_ to run. Reminds me of a Henny Youngman quote: