특정 시간대 수행 퀴리 확인

------------------------------------------------------------------------------------------------------------------------
-- ASH 버퍼 저장된 세션 히스토리 정보를 이용한 특정 시간대 수행 퀴리 확인하기(v$active_session_history)    
------------------------------------------------------------------------------------------------------------------------
/*
line  2     샘플링이 일어난 시간과 샘플 ID
line  3     세션정보, User 명, 트랜잭션 ID
line  4     수행중 SQL 정보
line  5     현재 세션의 상태 정보, 'ON CPU' 또는 'WAITING'
line  6     병렬 Slave 세션일 때, 쿼리 코디네이터(QC) 정보를 찾을 수 있게 함
line  7, 8  현재 세션의 진행을 막고 있는(blocking) 세션 정보
line  9     현재 발생 중인 대기 이벤트 정보
line 10     현재 발생 중인 대기 이벤트의 파라미터 정보
line 11     해당 세션이 현재 참조하고 있는 오브젝트 정보. v$session 뷰의 컬럼
line 12     애플리케이션 정보
☞ line 7~11의 정보가 매용 유용, 블로킹 세션 정보를 통해 현재 Lock 발생 시킨 세션을 찾음
*/
select 
      sample_id, sample_time
    , session_id, session_serial#, user_id, xid
    , sql_child_number, sql_plan_hash_value
    , session_state
    , qc_instance_id, qc_session_id
    , blocking_session, blocking_session_serial#, blocking_session_status
    , event, event#, seq#, wait_class, wait_time, time_waited
    , p1text, p1, p2text, p2, p3text, p3
    , current_obj#, current_file#, current_block# -- -40016382 
    , program, module, action, client_id
    , sql_text
from v$active_session_history sh, -- dba_hist_active_sess_history  참조
     dba_hist_sqltext hs
where sh.sql_id = hs.sql_id 
and sample_id between 135436538 and 135438470;
--and sample_time 
--between to_timestamp('2020/07/29 04:10:00','yyyy/mm/dd hh24:mi:ss')
--and to_timestamp('2020/07/29 04:25:00','yyyy/mm/dd hh24:mi:ss');

 

======================================================================================
#. 특정 세션의 순차적 수행 분석
-======================================================================================

select *
from (
  select 
    h.session_id as sid,
    to_char(h.sample_time,'mi:ss') as sample_time,
    h.sql_id,
    (select sql_text from v$sqlarea a where a.sql_id = h.sql_id) as sql_text,
    event,
    blocking_session as blocker
  from
    v$active_session_history h
  where
    h.session_id = &sid
  order by h.sample_time desc
) where rownum <= 20
;

 

======================================================================================
#. 10초전~현재까지 ASH 리포트 생성
-======================================================================================

col db_id new_value db_id;
col inst_num new_value inst_num;

select dbid as db_id from v$database;

     DB_ID
----------
3588319577

select instance_number as inst_num from v$instance;

  INST_NUM
----------
         1

select * from table(
  dbms_workload_repository.ash_report_text(
    &db_id,
    &inst_num,
    sysdate - 10/24/60/60,
    sysdate
  ));

 

 

 

======================================================================================
#. Blocking Lock Session 확인
======================================================================================
SELECT    B.BLOCKING_SESSION AS BLOCKING_SESSION_SID
         ,C.SID AS LOCK_SESSION_SID
         ,C.OWNER AS OBJECT_OWNER
         ,C.OBJECT AS OBJECT
         ,B.LOCKWAIT
         ,A.PIECE
         ,A.SQL_TEXT AS SQL
FROM      V$SQLTEXT A
         ,V$SESSION B
         ,V$ACCESS C
WHERE     A.ADDRESS = B.SQL_ADDRESS
AND       A.HASH_VALUE = B.SQL_HASH_VALUE
AND       B.SID = C.SID
AND       B.BLOCKING_SESSION IS NOT NULL
AND       C.OWNER NOT IN ('SYS', 'PUBLIC')
AND       C.OBJECT NOT IN ('TOAD_PLAN_TABLE')
ORDER BY  A.PIECE;

 

--======================================================================================
--#. 현재 세션에서 10초이상 걸리는 쿼리 조회 (SELECT절)
--======================================================================================
 
SELECT    ABS(SYSDATE - A.LAST_LOAD_TIME) * 24 * 60 * 60 AS SEC_TIEM, A.*
FROM      V$SQLAREA A
         ,V$SESSION B
WHERE     A.SQL_TEXT LIKE '%SELECT%'
AND       A.ADDRESS = B.SQL_ADDRESS
AND       B.STATUS = 'ACTIVE'
AND       A.ELAPSED_TIME >= 10 * 1000000 -- 실행계획에서 10초 이상 걸리는 쿼리를 조회(실제 걸리는 시간은 아님.)
AND       A.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM', 'SYSMAN')
AND       B.USERNAME IS NOT NULL;
 
--======================================================================================
--#. 현재 세션에서 PGA, UGA, CPU 사용량 세션별로 조회하는 쿼리
--======================================================================================
 
SELECT    B.USERNAME
         ,A.SID
         ,A.PGA_USAGE
         ,A.UGA_USAGE
         ,A.CPU_USAGE_SECONDS
         ,B.MACHINE
         ,B.PROGRAM
         ,B.MODULE
FROM      (SELECT    B.SID
                    ,MAX(DECODE(C.NAME, 'session pga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS PGA_USAGE
                    ,MAX(DECODE(C.NAME, 'session uga memory', TRUNC(B.VALUE / 1024 / 1024) || 'MB', 0)) AS UGA_USAGE
                    ,MAX(DECODE(C.NAME, 'CPU used by this session', (B.VALUE / 100) || ' Sec', 0)) AS CPU_USAGE_SECONDS
           FROM      V$SESSTAT B
                    ,V$STATNAME C
           WHERE     B.STATISTIC# = C.STATISTIC#
           GROUP BY  B.SID) A
         ,V$SESSION B
WHERE     B.SID = A.SID
AND       B.STATUS = 'ACTIVE'
AND       B.USERNAME IS NOT NULL;