오라클 19c ash 뷰 데이터 1분마다 백업하는 스크립트

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c v$active_session_history 뷰 데이터 1분마다 백업

오라클 ash 뷰는 일정 시간이 지나면(ash buffer이 차면) 데이터가 샘플링되서 awr로 넘어가고 지난 데이터는 삭제됨

awr 데이터도 유용하지만 ash 데이터는 좀더 상세한 데이터를 볼 수 있음

하지만 장애가 dba가 없을때 발생한 경우 이후 ash 데이터는 없는 경우가 생길 수 있음

이 경우 ash의 백업이 있다면 좋을것 같아서 아래 스크립트를 만들게됨

 

 

ash 데이터를 쌓을 샘플 테이블 생성(BIG_ASH)

일반 테이블
SQL> create table sys.big_ash as select * from v$active_session_history where 1=2;
Table created.
또는
일별 인터벌 파티션 테이블
SQL>
create table sys.big_ash
partition by range (sample_time)
interval (numtodsinterval(1, 'day')) -- 하루 간격으로 파티션 생성
(
  -- 초기 파티션 생성
  partition p_init values less than (to_date('2023/12/08', 'yyyy/mm/dd')) -- 초기 파티션 범위 설정(내일날짜 입력)
)
as select * from v$active_session_history where 1=2
;

파티션 테이블인 경우 추후 오래된 데이터 파티션 drop 가능

 

 

추후 데이터를 빨리 찾을수 있게끔 인덱스 생성

SQL> create index sys.big_ash_ix1 on sys.big_ash(sample_id, session_id);
 
Index created.

인덱스도 자주 조회하는 컬럼이 있는 경우 본인 판단에 따라 추가 생성 가능

 

 

아래 스크립트로 db 스케줄러에 등록(매 1분마다 insert)

SQL> 
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name           =>  'CAPTURE_ASH_DATA',
        job_type           =>  'PLSQL_BLOCK',
        job_action         =>  '
            BEGIN
                INSERT INTO BIG_ASH
                SELECT *
                FROM V$ACTIVE_SESSION_HISTORY VASH
                WHERE NOT EXISTS (SELECT 1 FROM SYS.BIG_ASH BASH WHERE BASH.SAMPLE_ID = VASH.SAMPLE_ID)
                AND SAMPLE_ID > (SELECT NVL(MAX(SAMPLE_ID), 1) FROM SYS.BIG_ASH);
                COMMIT;
            END;',
        start_date         =>  SYSTIMESTAMP,
        repeat_interval    =>  'FREQ=MINUTELY; INTERVAL=1', -- Every minute
        end_date           =>  NULL,
        enabled            =>  TRUE,
        comments           =>  'Capture ASH data every minute'
    );
END;
/

 

 

위 스크립트에서 insert select 문에서 max(sample_id)를 한번더 걸러내는 조건을 작성했는데

이 조건이 있을때와 없을때 성능 차이도 비교해봄

아래 스크립트로 빠르게 조회해봄

오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 ( https://positivemh.tistory.com/960 )

A) 그냥 조회
SELECT *
FROM V$ACTIVE_SESSION_HISTORY VASH
WHERE NOT EXISTS (SELECT 1 FROM SYS.BIG_ASH BASH WHERE BASH.SAMPLE_ID = VASH.SAMPLE_ID)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0         74          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        4      0.04       0.04          0         25          0          31
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6      0.06       0.07          0         99          0          31
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        31         31         31  HASH JOIN ANTI (cr=25 pr=0 pw=0 time=47816 us starts=1 cost=1 size=1448 card=1)
      4002       4002       4002   NESTED LOOPS ANTI (cr=0 pr=0 pw=0 time=42473 us starts=1 cost=1 size=1448 card=1)
      4002       4002       4002    STATISTICS COLLECTOR  (cr=0 pr=0 pw=0 time=42343 us starts=1)
      4002       4002       4002     VIEW  GV$ACTIVE_SESSION_HISTORY (cr=0 pr=0 pw=0 time=42825 us starts=1 cost=0 size=1435 card=1)
      4002       4002       4002      NESTED LOOPS  (cr=0 pr=0 pw=0 time=24558 us starts=1 cost=0 size=1493 card=1)
      3412       3412       3412       FIXED TABLE FULL X$KEWASH (cr=0 pr=0 pw=0 time=3564 us starts=1 cost=0 size=7400 card=100)
      4002       4002       4002       FIXED TABLE FIXED INDEX X$ASH (ind:1) (cr=0 pr=0 pw=0 time=16851 us starts=3412 cost=0 size=1419 card=1)
         0          0          0    INDEX RANGE SCAN BIG_ASH_IX1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=13 card=1)(object id 23340)
      3971       3971       3971   INDEX FAST FULL SCAN BIG_ASH_IX1 (cr=25 pr=0 pw=0 time=209 us starts=1 cost=1 size=13 card=1)(object id 23340)
 
 
B) big_ash 테이블의 max(sample_id) 확인 후 조회
SELECT *
FROM V$ACTIVE_SESSION_HISTORY VASH
WHERE NOT EXISTS (SELECT 1 FROM SYS.BIG_ASH BASH WHERE BASH.SAMPLE_ID = VASH.SAMPLE_ID)
AND SAMPLE_ID > (SELECT NVL(MAX(SAMPLE_ID), 1) FROM SYS.BIG_ASH)
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          4          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          3         42          0          28
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.02       0.02          3         46          0          28
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9
Number of plan statistics captured: 1
 
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        28         28         28  HASH JOIN ANTI (cr=42 pr=3 pw=0 time=2826 us starts=1 cost=2 size=1448 card=1)
        28         28         28   NESTED LOOPS ANTI (cr=2 pr=0 pw=0 time=317 us starts=1 cost=2 size=1448 card=1)
        28         28         28    STATISTICS COLLECTOR  (cr=2 pr=0 pw=0 time=285 us starts=1)
        28         28         28     VIEW  GV$ACTIVE_SESSION_HISTORY (cr=2 pr=0 pw=0 time=639 us starts=1 cost=1 size=1435 card=1)
        28         28         28      NESTED LOOPS  (cr=2 pr=0 pw=0 time=466 us starts=1 cost=0 size=1493 card=1)
        26         26         26       FIXED TABLE FULL X$KEWASH (cr=2 pr=0 pw=0 time=95 us starts=1 cost=0 size=370 card=5)
         1          1          1        SORT AGGREGATE (cr=2 pr=0 pw=0 time=38 us starts=1)
         1          1          1         INDEX FULL SCAN (MIN/MAX) BIG_ASH_IX1 (cr=2 pr=0 pw=0 time=31 us starts=1 cost=1 size=13 card=1)(object id 23340)
        28         28         28       FIXED TABLE FIXED INDEX X$ASH (ind:1) (cr=0 pr=0 pw=0 time=140 us starts=26 cost=0 size=1419 card=1)
         0          0          0    INDEX RANGE SCAN BIG_ASH_IX1 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=53521 card=4117)(object id 23340)
      3971       3971       3971   INDEX FAST FULL SCAN BIG_ASH_IX1 (cr=40 pr=3 pw=0 time=238 us starts=1 cost=1 size=53521 card=4117)(object id 23340)
 

읽는 row 수도 적고, time 부분을 보면 2번째 방법이 훨씬 빠름

 

 

스케줄러 대신 crontab 을 이용해서 job을 등록하는것도 가능함

$ cat bigash.sh
#!/bin/bash
#bigash.sh
 
# 0. apply profile
. ~/.bash_profile
 
# 1. sqlplus를 이용하여 oracle db 접속 후 쿼리 수행
sqlplus / as sysdba <<EOF
set lines 200 pages 1000
INSERT INTO SYS.BIG_ASH
SELECT *
FROM V\$ACTIVE_SESSION_HISTORY VASH
WHERE NOT EXISTS (SELECT 1 FROM SYS.BIG_ASH BASH WHERE BASH.SAMPLE_ID = VASH.SAMPLE_ID)
AND SAMPLE_ID > (SELECT NVL(MAX(SAMPLE_ID), 1) FROM SYS.BIG_ASH);
COMMIT;
EOF

 

 

실행 권한 부여

$ chmod u+x bigash.sh

 

 

crontab 등록

$ crontab -e
* * * * * /home/oracle/bigash.sh 

 

 

추후 오래된 파티션 제거(몇일 보관할지는 사이트별 판단 필요)

(작성필요)

 

 

참조 : 

https://positivemh.tistory.com/960

오라클 19c 10046 trace 및 tkprof 자동수행 스크립트

https://positivemh.tistory.com/601

Active Session History (ASH) Performed An Emergency Flush. This may...

1385872.1