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)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
일반 테이블
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 가능
추후 데이터를 빨리 찾을수 있게끔 인덱스 생성
|
1
2
3
|
SQL> create index sys.big_ash_ix1 on sys.big_ash(sample_id, session_id);
Index created.
|
인덱스도 자주 조회하는 컬럼이 있는 경우 본인 판단에 따라 추가 생성 가능
아래 스크립트로 db 스케줄러에 등록(매 1분마다 insert)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 )
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
|
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을 등록하는것도 가능함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
$ 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
|
실행 권한 부여
|
1
|
$ chmod u+x bigash.sh
|
crontab 등록
|
1
2
|
$ crontab -e
* * * * * /home/oracle/bigash.sh
|
추후 오래된 파티션 제거(몇일 보관할지는 사이트별 판단 필요)
(작성필요)
참조 :
https://positivemh.tistory.com/960
오라클 19c 10046 trace 및 tkprof 자동수행 스크립트
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.3.0.0 방법 : 오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 오라클 환경에서 10046 trace 를 수집하려면 아래 게시글 방법처럼 trace를 수동으로 켜
positivemh.tistory.com
https://positivemh.tistory.com/601
Active Session History (ASH) Performed An Emergency Flush. This may...
OS환경 : Oracle Linux 6.8 (64bit) DB 환경 : Oracle Database 11.2.0.4, 19.3.0.0 에러 : Active Session History (ASH) performed an emergency flush. This may ~~sga_target 값 증설 작업 이후 alert log에 발생한 메세지123456789$ vi alert_orcl.l
positivemh.tistory.com
1385872.1
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 10053 trace 자동수행 스크립트 (0) | 2023.12.14 |
|---|---|
| 오라클 19c 재기동시 발생하는 wait event (0) | 2023.12.08 |
| 오라클 19c 10046 trace 및 tkprof 자동수행 스크립트 (0) | 2023.12.07 |
| 오라클 19c not in 절에 null 허용 컬럼 관련 테스트 (0) | 2023.09.21 |
| 오라클 19c 병렬 dml 실행계획 확인 테스트 (0) | 2023.03.05 |