a.應(yīng)用場(chǎng)景：開(kāi)發(fā)反應(yīng)2023-03-02 00:22至00:35，數(shù)據(jù)落盤慢，根據(jù)情況查看此時(shí)間段的主要活動(dòng)事件，數(shù)量，與sql_id（全局）
select count(*), sql_id, event, blocking_session
  from gv$active_session_history
 where sample_time between
       to_date('2023-03-02 00:22:00', 'yyyy-mm-dd hh24:mi:ss') and
       to_date('2023-03-02 00:35:00', 'yyyy-mm-dd hh24:mi:ss')
 group by sql_id, event, blocking_session
 order by 1;
（非全局）BLOCKING_INST_ID--被阻塞者, blocking_session--阻塞者
select count(*), sql_id, event, BLOCKING_INST_ID, blocking_session
  from v$active_session_history
 where sample_time between
       to_date('2023-03-02 00:20:00', 'yyyy-mm-dd hh24:mi:ss') and
       to_date('2023-03-02 00:35:00', 'yyyy-mm-dd hh24:mi:ss')
 group by sql_id, event, BLOCKING_INST_ID, blocking_session
 order by 1;
b.現(xiàn)在我們已經(jīng)得到兩個(gè)關(guān)鍵信息：sql_id與阻塞事件,首先根據(jù)sql_id我們可以再進(jìn)一步使用此視圖，實(shí)際中可以多調(diào)整幾個(gè)較小的時(shí)間段，以突出最有代表的信息
select count(*),
       session_id,
       session_serial#,
       sql_id,
       event,
       BLOCKING_INST_ID,
       blocking_session
  from v$active_session_history
 where sample_time between
       to_date('2023-03-02 00:24:00', 'yyyy-mm-dd hh24:mi:ss') and
       to_date('2023-03-02 00:25:00', 'yyyy-mm-dd hh24:mi:ss')
   and sql_id = '1xfbtdvu3xb67'
 group by session_id,
          session_serial#,
          sql_id,
          event,
          BLOCKING_INST_ID,
          blocking_session
 order by 3;
c.加入等待事件后更清晰
select count(*),
       session_id,
       sql_id,
       event,
       BLOCKING_INST_ID,
       blocking_session
  from v$active_session_history
 where sample_time between
       to_date('2023-03-02 00:25:00', 'yyyy-mm-dd hh24:mi:ss') and
       to_date('2023-03-02 00:35:00', 'yyyy-mm-dd hh24:mi:ss')
   and event = 'library cache lock'
   and sql_id = '1j47z0mc6k02b'
 group by session_id, sql_id, event, BLOCKING_INST_ID, blocking_session
 order by 1;
結(jié)論：可以看出大量并發(fā)等待，最終是發(fā)現(xiàn)有什么阻塞了此SQL語(yǔ)句

#根據(jù)dba_objects確定創(chuàng)建時(shí)間是否匹配
select owner,
       object_name,
       object_type,
       to_char(created, 'yyyy-mm-dd hh24:mi:ss')
  from dba_objects
 where object_name = 'AA_BBB_CCCC_DDDD_OUT'
   and created > to_date('2023-03-01', 'yyyy-mm-dd')
 order by 4;
 select owner,
       object_name,
       object_type,
       to_char(created, 'yyyy-mm-dd hh24:mi:ss')
  from dba_objects
 where object_name = 'AA_BBB_CCCC_DDDD_IN'
   and created > to_date('2023-03-01', 'yyyy-mm-dd')
 order by 4;