1.查询SQL历史执行信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
SET LONG 2000

select sql_id,
 sess_id,
 top_sql_text,
 start_time,
 time_used
 from
 v$sql_history
 where sql_id=&v_sql_id
 order by time_used;

2.查询等待事件的历史信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 0

select THREAD_ID,TRX_ID,WAIT_CLASS,WAIT_OBJECT,WAIT_START,WAIT_TIME from V$WAIT_HISTORY;

3.查询回滚记录不等于0的活动会话信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off


select * from
(SELECT top 50
        T.ID AS TID  ,
        T.SESS_ID    ,
        T.STATUS     ,
        T.READ_ONLY  ,
        T.INS_CNT    ,  --trx insert rows
        T.DEL_CNT    ,  --trx delete rows
        T.UPD_CNT    ,  --trx update rows
        T.UPD_INS_CNT,  --trx update or insert rows
        WAITING,
        user_name,
                substr(clnt_ip,8)
FROM
        V$TRX T,
        V$SESSIONS S
WHERE
        T.ID=S.TRX_ID
                and t.status = 'ACTIVE'
                and (T.INS_CNT+T.DEL_CNT+T.UPD_CNT+T.UPD_INS_CNT) != 0
                order by (T.INS_CNT+T.DEL_CNT+T.UPD_CNT+T.UPD_INS_CNT) desc)
order by (INS_CNT+DEL_CNT+UPD_CNT+UPD_INS_CNT)

4.查询锁信息

set echo off
set feedback off
set timing off
set verify off
set lineshow off
set pagesize 10000
set linesize 1000

prompt
prompt ==================all lock infomation======================

select 
      b.object_name,
      c.sess_id,
      a.* 
from v$lock a, dba_objects b, v$sessions c
where a.table_id=b.object_id
  and ltype='OBJECT'
  and a.trx_id=c.trx_id
  order by LMODE,TRX_ID ;
  
prompt
prompt ==================locked trx infomation======================

with locks as(
  select 
        * from v$trxwait),
res as (   
  select 
        s1.sess_id as wt_sessid,
        s2.sess_id as blk_sessid,
        l.id as wt_trxid,
        l.wait_for_id as blk_trxid,
        l.wait_time,
        s1.sql_text as wt_sql,
        s2.sql_text as blk_sql,
        b.sql_id as wt_sql_id,
        s1.clnt_ip,
        SF_GET_SESSION_SQL(s1.sess_id) fulsql,
        datediff(ss,s1.last_send_time,sysdate) ss
  from locks l left join v$sessions s1 on
  l.id=s1.trx_id
  left join 
  v$sessions s2
    on l.wait_for_id=s2.trx_id
  left join v$sqltext b
  on s1.SQL_TEXT=b.SQL_TEXT
)
select 
      wt_sessid BLOCKED,
      blk_sessid BLOCKER,
      wt_trxid,
      blk_trxid,
      wt_sql_id,
      substr(wt_sql,0,30) as wt_sql,
      clnt_ip,ss
from res order by blocker desc,ss desc;

prompt
prompt ==================lock head trx infomation======================

WITH
        LOCK_HEAD AS
        (
                SELECT DISTINCT
                        WAIT_FOR_ID
                FROM
                        V$TRXWAIT
                WHERE
                        WAIT_FOR_ID NOT IN
                        (
                                SELECT ID FROM V$TRXWAIT
                        )
        )
SELECT
        sess_id AS BLOCKER_SESS,
        TRX_id AS BLOCKER_TRX,
        substr(sql_text, 0, 30) wt_sql          ,
        clnt_ip                                 ,
        datediff(ss, last_send_time, sysdate) ss
from
        LOCK_HEAD L
LEFT JOIN V$SESSIONS S
ON
        L.WAIT_FOR_ID = S.TRX_ID;