ash
Reminder for me on how useful ASH is
– find snapid where a particular sql occurs
select distinct snap_id from dba_hist_sqlbind where sql_id=’80bd6bb7n33tk’
– find bind variable values for the specified sql
select * from dba_hist_sqlbind
where snap_id = 20637 and trunc(last_captured) >= ‘17-NOV-2011′ and sql_id=’80bd6bb7n33tk’
order by sql_id, position
select snap_id, sample_time, session_id, session_serial#, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event from dba_hist_active_sess_history
where (session_id,session_serial#) in (select session_id,session_serial# from dba_hist_active_sess_history where sql_id=’0trpbjywfsdp6′ and snap_id between 20637 and 20639)
and snap_id between 20637 and 20639 order by snap_id, sample_time
4a2fkw1d4u01u — deletion profile that was blocked
select snap_id, sample_time, session_id, session_serial#, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event from dba_hist_active_sess_history
where (session_id,session_serial#) in (select session_id,session_serial# from dba_hist_active_sess_history where sql_id=’4a2fkw1d4u01u’ and snap_id between 20637 and 20639)
and snap_id between 20637 and 20639 order by snap_id, sample_time
select ‘USER’, sample_time, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event,
(select sql_text from v$sql where sql_id=a.sql_id and rownum=1) xxx from dba_hist_active_sess_history a
where session_id=762 and session_serial#=45940
and snap_id between 20637 and 20642 order by snap_id, sample_time
– blocked ETL Session
select ‘ETL’, sample_time, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event,
(select sql_text from DBA_HIST_SQLtext where sql_id=a.sql_id and rownum=1) xxx from dba_hist_active_sess_history a
where session_id=829 and session_serial#=31419
and snap_id between 20637 and 20642 order by snap_id, sample_time
–USER1@host2( SID=915 ) is blocking ETL_USER@host1( SID=829 )
– blocked by 915, which funnily enough only active 17 Nov from 17:44 till 22:15 - somehow this session did not release the lock at all until killed the following day
select ‘USER’, sample_time, sql_id, session_state, blocking_session, blocking_session_serial#, blocking_session_status, event,
(select sql_text from DBA_HIST_SQLtext where sql_id=a.sql_id and rownum=1) xxx from dba_hist_active_sess_history a
where session_id=915 and session_serial#=2165
select * from dba_hist_active_sess_history a
where session_id=915 and session_serial#=2165
select * from dba_tab_columns where column_name=’HOST_NAME’ and table_name like ‘DBA_HIST%’
select * from DBA_HIST_SQLbind where sql_id=’4a2fkw1d4u01u’ and snap_id between 20637 and 20642