내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
AWR 기본 설명 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26742261
오늘 AWR 분석에서 확인한 내용
대기 이벤트가 발생하는 상황
1. 자신이 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용 중일때
buffer busy waits, latch free, enqueue 관련 대기 이벤트
2. 다른 프로세스에 의해 선행작업이 완료되기를 기다릴때
write complete waits, checkpoint completed, log file sync, log file switch 이벤트
> 책에 설명된 DBWR LGWR 동작은 별도로 파악할 것
3. 할일이 없을때 ( > idle 대기 이벤트 )
SQL*Net message from client, PX Deq: Execution Msg 이벤트
서적 : 오라클 성능 고도화 원리와 해법 1
Wait Event 에서의 gc buffer busy
- 로컬 프로세스가 읽고자 하는 블록이 현재 리모트 노드의 요청에 의해 사용 중임을 의미.
- Placeholder/Fixed-up 분류를 따르지 않는 독립 이벤트.
- p1 : file#, p2 : block#, p3 : id#
- buffer busy wait 이벤트나 read by other session 이벤트의 글로벌 버전
-같은 인스턴스의 다른 프로세스가 해당 블록을 변경 중일 때는 변경이 완료될 때까지 buffer busy wait 이벤트를 대기.
-같은 인스턴스의 다른 프로세스가 해당 블록을 디스크에서 읽어 들이는 중일때는 I/O 작업이 완료될 때까지 read by other session 이벤트를 대기.
-I/O 작업을 수행하는 프로세스는 db file sequential read 이벤트나 db file scattered read 이벤트를 대기
-같은 인스턴스의 다른 프로세스가 해당 블록을 다른 인스턴스로부터 전송 받고 있는 중이라면 전송이 끝날 때까지 gc buffer busy 이벤트 대기.
-다른 인스턴스의 요청에 의해 해당 블록이 전송 중이라면, 전송이 끝나고 사용이 완료될 때까지 gc buffer busy 이벤트를 대기.
- 근본적인 발생 원인은 buffer busy wait 이벤트와 동일하며 해결책도 동일.
-핫 블록이 가장 일반적인 원인. 핫 블록을 분산시킴으로 문제 해결.
-세그먼트 레벨의 파티셔닝, 우편향 인덱스 현상 해소, 시퀀스 캐시 크기의 증가, PCTFREE 증가등.
-FLM을 사용하는 경우에는 세그먼트 헤더 블록이 버퍼 경합의 원인이 되므로 FREELIST GROUPS 속성을 노드 수와 동일하게 설정.
-SQL 튜닝을 통해 불필요하게 많은 블록이 교환되는 것을 방지.
-동일한 세그먼트에 대한 대량의 DML 작업이 여러 노드에서 동시 다발적으로 발생하면 어플리케이션 실행을 적절히 분배.
Operating System Statistics 에서의 vm_in_bytes
얘는 아직 뭔지 모르겠음
Time Model Statistics 에서의 sql execute elapsed time
SQL 문 수행 시간의 총 합. SELECT 문의 경우 패치를 위해 소비된 시간까지 포함됨
Top 5 Timed Events 에서의 db file scattered read
Event 정의
- I/O System 에 Single Block I/O 를 요청 하고 응답이 오기를 기다리는 Event
- P1 = file#
- P2 = Block#
- P3 = 1
- 이 이벤트는 인덱스 스캔, 롤백 또는 언두 세그먼트 읽기, ROWID 에 대한 테이블 액세스,
컨트롤 파일 재구성(rebuild), 데이타 화일 헤더 덤프(dump) 또는 데이터파일 헤더를 읽을 때 발생된다.
SQL > select * from v$bh WHERE file# =<P1> and block# = <P2>
Or
SQL > ALTER SYSTEM DUMP DATAFILE <P1> BLOCK <P2>
- db file sequential read 가 비정상적으로 높다면 아래 부분은 점검 한다.
- OS 의 Disk 구성(Raid, file system or raw device 등등)
- OS I/O 처리방식
- Disk read/write I/O 처리 성능
- Active Session 간의 경합
- DB server 의 I/O 발생량
db file sequential read 발생 사유
- Single Block I/O
- index 를 경유한 Scan : Index Range Scan , Index Unique Scan , Index full Scan
- Chained Row / Migrated Row ( Single Block I/O 시 )
- Multi Block I/O 도중에도 발생 가능
db file sequential read 대기 과다 원인
- 비효율적인 Index Scan
- SQL Tuning
- index 변경
- 지나치게 높은 Clustering Factor
- Table Reorg 를 통해 해결
- 지나치게 깊은 Index 깊이
- Index Rebuild 를 통해 해결
- I/O System 성능 저하
- Disk I/O 성능 개선
- Hot Spot 해소 : Logical Volumn Level Striping, ASM 등
Clustering Factor ( CF )
- 군집도, Table 이 얼마나 Index 와 잘 뭉쳐 있는가를 판단하는 값
- Index Scan 에 따른 Table Block I/O 회수
- DBA_INDEXES.CLUSTERING_FACTOR
Wait Event 에서의 px deq: reap credit
PX Deq Credit : need buffer
: PEC / PES 간, PES / PES 간의 통신은, 프로세스 간 존재하는 테이블 큐(Table Q)를 통해 이루어진다.
가령 PES 가 테이블 큐에 데이터를 집어넣으면, PEC 가 테이블 큐에서 그 데이터를 빼가는 형식이다.
오라클은 두 프로세스 중 한 순간에 오직 하나의 프로세스만이 테이블 큐에 데이터를 집어넣을 수 있도록 보장한다.
테이블 큐에 데이터를 집어넣을 수 있는 자격을 확보할 때까지 기다리는 이벤트다.
-- 이건 얘가 맞는지 확실히 모르겠음
Background Wait Events 에서의 gcs remote message
gcs remote message/ges remote message는 모두 Idle Event입니다. LMS나 LMON같은 RAC Background Process에서 주로 관찰됩니다. Idle Event이기 때문에 특별히 신경쓸 필요는 없다고 봅니다.
Background Wait Evnets 에서의 smon timer, pmon timer
pmon timer, smon timer, px idle 서버 프로세스가 할일이 없을때 발생하는 이벤트.
Instance Activity Stats에서의 call to kcmgas
select * from v$sysstat where name = 'calls to kcmgas';
- SCN은 Sequence에서 발생시키는 것이 아니라 steve adams가 개발한 "kcmgas" function으로 구현
- 조회하면 value 값에 현재 SCN 값을 확인 가능
Instance Activity Stats 에서의 commit txn count during cleanout
얘는 아직 뭔지 모르겠음
Instance Activity Stats 에서의 gc cr block served
gc cr/current block 2-way 이벤트는 gc cr/current request 이벤트에 대한 Fixed-up 이벤트로, 블록을 요청한 프로세스가 마스터 노드로부터 직접 블록 이미지를 전송 받았다는 것을 의미합니다. gc cr/current request 이벤트가 gc cr/current block 2-way 이벤트로 바뀌는(Fixed-up되는) 흐름은 다음과 같습니다.
- 요청 노드의 유저 프로세스가 특정 데이터 블록을 CR 모드 또는 Current 모드로 읽고자 한다.
- 유저 프로세스는 해당 데이터 블록의 적절한 버전이 로컬 버퍼 캐시에 없는 것을 확인하고, 마스터 노드의 LMS(Lock Monitor Services) 프로세스에 블록 전송을 요청한다. 유저 프로세스는 응답을 받을 때까지 gc cr request 이벤트나 gc current request 이벤트를 대기한다.
- 마스터 노드의 LMS(Lock Monitor Services) 프로세스는 자신의 로컬 버퍼 캐시에 요청 받은 블록 이미지가 존재하는 것을 확인하고, 인터커넥트를 통해 해당 블록 이미지를 전송한다. CR 블록을 전송하는 경우에는 gc cr blocks served, gc cr block build time, gc cr block flush time, gc cr block send time 통계 값이 증가하고, Current 블록을 전송하는 경우에는 gc current blocks served, gc current block pin time, gc cr block flush time, gc cr block send time 통계 값이 증가한다.
- 유저 프로세스는 블록 이미지를 전송 받고, gc cr/current request 이벤트를 Fixed-up 이벤트인 gc block 2-way 이벤트나 gc current block 2-way 이벤트로 변경한다. CR 블록을 전송받은 경우에는 gc cr blocks received, gc cr block receive time 통계값이 증가하고, Current 블록을 전송받은 경우에는 gc current blocks received, gc current block receive time 통계 값이 증가한다.
Instance Activity Stats 에서의 GCS / GES Messages Sent
하드웨어 상호 연결을 통해 원격 인스턴스에 전송 된 메시지 수입니다. 이 통계는 일반적으로 RAC 서비스의 작동으로 인한 오버 헤드를 나타냅니다.
이 통계는 다른 두 가지 통계의 합계입니다.
GCS/GES messages sent = gcs messages sent + ges messages sent
gcs messages received - GCS (Global Cache Service)가 수신 한 메시지 수입니다. 이 통계는 v$sysstat.VALUE 'gcs messages sent'에서 확인가능합니다.
ges messages received - GES (Global Enqueue Service)가 수신 한 메시지 수입니다. 이 통계는 v$sysstat.VALUE 'ges messages sent'에서 확인가능합니다.
원문:
GCS/GES Messages Sent - number of messages sent to remote instances over the hardware interconnect. This statistics generally represents overhead caused by functioning of RAC services. This is statistic is a sum of another two statistics:
GCS/GES messages sent = gcs messages sent + ges messages sent
gcs messages received - number of message received by GCS (Global Cache Service). This statistics is derived from v$sysstat.VALUE 'gcs messages sent'.
ges messages received - number of message received by GES (Global Enqueue Service). This statistics is derived from v$sysstat.VALUE 'ges messages sent'.
Instance Activity Stats 에서의 hot buffers moved to head of LRU
Touch count 기반의 LRU 알고리즘은 다음과 같은 방식으로 작동한다.
1. LRU 리스트의 메인 리스트는 크게 핫 영역(Hot Region)과 콜드 영역(Cold Region)으로 나누어진다. 자주 사용되는 블록은 핫 영역에 머무르며, 사용빈도가 낮은 블록은 콜드 영역에 머무른다. 오라클은 개별 버퍼마다 Touch count(접촉 회수)를 관리하며, 프로세스에 의해 스캔이 이루어질 때마다 Touch count를 1씩 증가시킨다.
2. 프리 버퍼를 찾을 때는 우선 LRU 리스트의 보조 리스트에서 미사용된 버퍼를 찾는다. 만일 보조 리스트가 비어 있다면, 메인 리스트의 콜드 영역의 꼬리에서부터 프리 버퍼를 찾는다. 메인 리스트의 꼬리에 있으면서 Touch count가 1이하인 버퍼가 프리 버퍼로 사용된다. 프리 버퍼를 찾는 과정에서 Touch count가 2 이상인 블록을 만나면 핫 영역의 머리(Head of Hot Region)로 옮기고 해당 버퍼의 Touch count를 0으로 초기화시킨다. 핫 영역으로 옮기는 기준이 되는 값은 _DB_AGING_HOT_CRITERIA 히든 파라미터이며 기본값이 2이다.
3. 싱글 블록 I/O에 의해 읽혀진 블록은 Mid-point에 삽입되며 Touch count는 1의 값을 지닌다. Mid-point가 가리키는 위치는 콜드 영역의 머리(Head of Cold Region)이다. 싱글 블록 I/O에 읽혀진 블록은 콜드 영역의 머리에 위치함으로써 버퍼 캐시에 머무를 확률이 높아진다.
4. 멀티 블록 I/O에 의해 읽혀진 블록들은 Mid-point에 삽입된 후 콜드 영역의 제일 뒤(Tail of Cold Region)으로 옮겨진다. 풀테이블스캔(FTS)이나 인덱스풀스캔으로 읽힌 블록들은 콜드 영역의 꼬리에 위치함으로써 버퍼 캐시에 머무를 확률이 낮아진다.
5. Keep 버퍼 풀과 Recycle 버퍼 풀은 Default 풀과는 달리 영역의 구분이 불필요하므로 핫 영역을 가지지 않는다. Recycle 버퍼 풀은 핫 영역을 가지지 않는다는 점을 제외하면 Default 버퍼 풀과 완전히 동일한 방식으로 작동한다. 하지만 Keep 버퍼 풀의 경우에는 FTS로 읽히는 작은 크기의 테이블을 메모리에 상주시키기 위해 고안된 공간이기 때문에 멀티 블록 I/O로 읽은 블록들을 싱글 블록 I/O로 읽은 블록과 동일하게 콜드 영역의 제일 앞에 위치시키도록 구현되었다
Instance Activity Stats 에서의 gc local grants
이러한 데이터 블록은 블록 범위에서 마스터 링됩니다. 예를 들어, 파일 10, 블록 1에서 블록 128까지의 블록 범위는 인스턴스 1, 파일 10의 블록, 블록 129에서 256까지 인스턴스 2에서 마스터 할 수 있습니다. 물론 다양한 버전 10g, 11g 등이지만 여기서 아이디어는 글로벌 캐시 권한이 인스턴스간에 고르게 분산되도록 다양한 인스턴스간에 블록 범위가 균일하게 마스터된다는 것입니다. 흥미롭게도, 블록 범위의 길이는 이후 10g에서 128입니다 (Julian Dyke는 9i에서 1089라고 말했지만, 개인적으로 테스트하지는 않았습니다). 물론, 지원부는 자동으로 128로 조정되는 db_file_multiblock_read_count의 설정을 해제하는 것이 좋습니다. 이는 전체 블록 범위가 더 적은 GC 메시지로 읽을 수 있음을 의미합니다. 나는 빗 나간다.
원문 : These data blocks are mastered in block ranges. For example, range of blocks starting from file 10, block 1 through block 128 may be mastered by instance 1, blocks from file 10, block 129 through 256 are mastered by instance 2 etc. Of course, there are differences between various versions 10g, 11g etc, but Idea here is that block ranges are uniformly mastered between various instances so that Global cache grants are evenly distributed among the instances. Interestingly, length of the block range is 128 from 10g onwards (Julian Dyke mentioned that is 1089 in 9i, but I have not personally tested it). Of course, Support recommends you to unset db_file_multiblock_read_count which will be auto adjusted to 128 which means that Full block range can be read with fewer GC messages, I suppose. I digress.
인스턴스 3을 실행하는 SELECT 문의 세 가지 간단한 경우를 생각해 봅시다.
1. 세션은 블록 파일 1 (블록 10776)에 액세스하려고 시도하지만 블록은 인스턴스 2에 의해 마스터되고 블록은 인스턴스 2 (즉, 인스턴스 2 캐시에 있음)에 의해 소유됩니다. 따라서 인스턴스 3은 해당 블록에 대한 PR (보호 된 읽기) 모드 BL 잠금 요청을 인스턴스 2로 보냅니다. 추가 복잡성을 무시하면 인스턴스 2는 PR 모드 잠금을 인스턴스 3에 부여하고 블록을 인스턴스 3으로 전송합니다. GC 메시지, 승인 및 블록 전송 통계 'gc 원격 부여'도 증가합니다.
2. 세션이 다른 블록 (파일 1, 블록 6375)에 액세스하려고한다고 가정합시다.이 블록은 인스턴스 3에 의해 마스터되고 인스턴스 3이 소유합니다.이 시점에서 추가 GCS / GES 처리가 필요하지 않으며 세션 핀 버퍼링하고 작업을 계속하십시오.
3. 세 번째 사례를 생각해 봅시다. 세션은 파일 1 블록 6374에 액세스하려고 시도하고 있습니다. 해당 블록은 버퍼 캐시에 없지만 인스턴스 3은 블록의 마스터입니다. 따라서 최소한의 GC 메시지로 로컬 선호도 잠금을 획득하고 대기합니다. 그 블록은 디스크에서 버퍼 캐시로 읽혀집니다.
위의 # 2 및 # 3의 경우 인스턴스 요청은 블록 또는 블록 범위의 마스터 노드이기도합니다. 이러한 경우 통계 'gc local grants'가 증가하고 많은 글로벌 캐시 메시지를 피하면서 해당 블록 범위에서보다 저렴한 로컬 선호도 잠금이 획득됩니다.
원문 : Let’s consider three simple cases of SELECT statement running instance 3:
1. A session is trying to access the block file 1, block 10776, but that block is mastered by instance 2 and also that block is owned by instance 2 (meaning, it is in instance 2 cache). So, instance 3 will sent a PR (Protected Read) mode BL lock request on that block to instance 2. Ignoring additional complexities, instance 2 will grant PR mode lock to instance 3 and transfer the block to instance 3. Obviously, this involves multiple GC messages, grants and block transfer. Statistics ‘gc remote grants’ gets incremented too.
2. Let’s consider that session is trying to access another block: file 1, block 6375. That block is mastered by instance 3 and also owned by instance 3. At this point, there is no additional GCS/GES processing is needed and the session pin that buffer and continue the work.
3. Let’s consider a third case. Session is trying to access file 1 block 6374. That block is not in any buffer cache, but instance 3 is master of the block, so local affinity locks are acquired with minimal GC messages and waits. That block is read from the disk in to the buffer cache
In the case #2 and #3 above, requesting instance also is the master node of a block or block range. In these cases, statistics ‘gc local grants’ is incremented and cheaper local affinity locks on those block ranges are acquired avoiding many Global cache messages.
SGA Target Advisory 에서의 SGA Size Factor
현재 설정된 SGA 대비 예상 SGA크기 비율
서적 : 오라클_AWR을_이용한_고성능_데이터베이스_튜닝
Enqueue Activity의 Enqueue Type 에서의 ro-multiple object reuse (fast object reuse)
RO enqueue는 "multi object reuse" enqueue로 알려져 있습니다. 이 Enqueue는 foreground process와 background process 간의 sync 하는데 사용되는 enqueue 입니다. Background는 주로 DBWR나 CKPT를 말합니다.
특별히 object drop이나 table truncate 할 때 많이 사용됩니다.
oracle database내에서 truncate나 drop이 발생하면 다음과 같은 내부 작업들이 수행됩니다.
1. foreground process는 먼저 "RO" enqueue를 "execlusive" mode로 요청합니다.
2. 다음은 instance에 작업을 요청하는 cross instance call이 발생되어, CI enqueue가 할당됩니다.
3. 각 instance의 CKPT는 CI call의 요청에 따라 DBWR에서 dirty buffer를 write하게하고 관련 buffer를
invalidate 합니다.
4. DBWR가 write 작업을 끝내면 foreground process는 "RO" enqueue를 release 합니다.
사실상 이 enqueue 작업은 truncate/drop operation을 순차적으로 수행되기 때문에 자주 drop/truncate가 발생하면 "RO" enqueue contention이 발생할 수 있겠죠.
Enqueue Activity의 Enqueue Type 에서의 TM-DML
세그먼트에 테이블 락 경합이 발생하는 경우이며, 병렬 DML이나 테이블 변경, 인덱스 생성 등 세그먼트 변경 시 발생함
이벤트 발생 원인
기본/외래 키 설정 테이블에서 외래 키에 인덱스가 생성되지 않은 경우
DML을 수행하려고 하는 테이블에 병렬 DML 및 DIRECT INSERT를 수행한 경우
LOCK 명령으로 사용자가 명시적으로 테이블 락을 적용한 경우
인덱스 생성 및 테이블 변경 등과 같은 세그먼트 변경 작업을 수행한 경우
Latch Miss Sources 에서의 kcl gc element parent latch
얘는 아직 뭔지 모르겠음 아래는 Latch Miss Sources 설명
Latch Miss Sources 보고서는 DBA_HISLLATCH_MISSES_SUMMARY 덕셔너리를 참조하여
종료 스냅샷 발생 값에서 시작 스냅샷 발생 값을 뺀 수치이며，레치 이름 칼럼 값 내림차순 정렬，슬립횟수 칼럼 값 오름차순 정렬，획득 실패가 발생한 위치 칼럼 값 내림차순 정렬 순서로 정렬하여 보여준다.
관련 동적 뷰 V$LATCH_MISSES
서적 : 오라클_AWR을_이용한_고성능_데이터베이스_튜닝
Segments by Row Lock Waits
의미
| 항목 | 의미 | 관련 컬럼 |
|---|---|---|
| Row Lock Waits | enq_TX - row lock contention과 enq_TX - index contention 대기 이벤트 관련 행 락 발생 횟수 | SUM(B.ROW_LOCK_WAITS_DELTA) GROUP BY DATAOBJ#, OBJ#, DBID |
| % of Capture | enq_TX - row lock contention과 enq_TX - index contention 대기 이벤트 관련 행 락 중 해당 오브젝트에서 발생한 비율 | 100 * (Row Lock Waits / SUM(B.ROW_LOCK_WAIT_DELTA)) |
A : DBA_HIST_SEG_STAT_OBJ
B : DBA_HIST_SEG_STAT
설명
Segments by Row Lock Waits 단위 보고서는 enq_TX - row lock contention, enq_TX - index contention 대기 이벤트를 발생시키는 행 락 발생 상위 오브젝트를 보여줌
체크포인트
- 발생 비율 : 발생 비율이 특정 오브젝트에 집중되어 있을 때 오브젝트 경합 발생 의심
Global Enqueue Statistics 에서의 broadcast on commit wait time(ms)
얘는 아직 뭔지 모르겠음 아래는 Global Enqueue Statistics 설명
Global Enqueue Statistics 보고서는 RAC 엔큐 락 관련 통계 수치를 보여준다.
DBA_HIST_DLM_MISC 딕셔너리를 참조하여 종료 스냅샷 시 발생 값에서 시작 스냅샷 발생 값을 뺀 수치이며, 통계 이름으로 오름차순 정렬하여 보여준다.
관련 동적뷰 : V$DML_MISC
서적 : 오라클_AWR을_이용한_고성능_데이터베이스_튜닝
RAC Internals-julian Dyke.pdf기타 참고 사이트
참고 서적
RAC Advanced OWI, Internals and Performance in Oracle 10g
오라클_AWR을_이용한_고성능_데이터베이스_튜닝
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| enq UL - contention, PLSQL lock Timer (0) | 2018.12.05 |
|---|---|
| SQL을 변경하지 않고 실행계획 변경 테스트(SQL Plan Management) (0) | 2018.06.28 |
| QMON Coordinator 란 (0) | 2018.05.30 |
| SQL*Net message from client 란? (8) | 2018.05.30 |
| 성능테스트 자료1 (0) | 2018.05.29 |