------------------------------------------------------------------------------------------------------------------------
-- 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;
'ORACLE > 튜닝' 카테고리의 다른 글
| 부하 시 wait event 확인 (v$session 대체해서 실시간 성능 이력 분석 (PLAN 포함됨)) (0) | 2020.11.23 |
|---|---|
| 실행중 SQL 확인 (SORT/GROUPBY/INDEX ) (0) | 2020.11.23 |
| 실행 중 Session Transaction 확인 (0) | 2020.11.23 |
| [TRACE 11g] monitor hint 를 사용한 Plan 보기(v$sql_monitor, v$sql_plan_monitor) (0) | 2020.06.24 |
| [TRACE 10g] gather_plan_statistics hint를 사용한 SQL trace (0) | 2020.06.24 |