[Tibero교육] TPR사례분석

TPR 사례 분석

 

• 비효율적인 SQL 로 인한 CPU 과부하 현상

• INSERT 시 버퍼 캐시 부족 현상

• LITERAL 쿼리로 인한 HARD PARSE

• BUFFER BUCKET SPINLOCK 경합 이슈

• 대량 풀스캔으로 인한 버퍼 캐시 히트율 저하

• Temp Segement 경합에 의한 성능 저하

• 플랜 변경으로 인한 응답속도 저하

• RSB (resource block) 부족 현상

• 핫블록으로 인한 경합

 

 

 

1. 비효율적인 SQL 로 인한 CPU 과부하 현상

• 신규업무 도입 후 CPU 사용량 증가. 평균 75~90% 사용

• 초당 Logical Reads 가 4,200,000 블록으로 과도하게 발생

• Logical Reads 는 메모리 연산 작업으로 CPU 사용율을 높이는 작업

• Instance Activity 항목에서 SELECT 를 처리하는데 대부분 시간을 소요

• Wait Event 에서도 Logical Read 를 처리하는 Event 가 상위 랭크를 차지

• 6.1 SQL Ordered by Elapsed Time 확인 결과 Top SQL 1번이 전체 DB Time의 96.84% 점유

• 6.3 SQL Ordered by Gets 에서도 해당 SQL 이 Total Gets 비율이 97.27% 로 나타남

• SQL 플랜 확인 결과 CRMAIL 테이블의 데이터를 찾는 과정에서 비효율 발생

• 해당 SQL 튜닝 진행 후 CPU 사용률이 75~90% 에서 20~30%로 낮아짐

 

 

 

 

2. INSERT 시 버퍼 캐시 부족 현상

• 부하 테스트 진행 중 목표 TPS 보다 낮은 성능을 보임

• REDO SIZE, BLOCK CHAGNES 항목이 상대적으로 높은것으로 보아 DML 부하로 추정

• INSERT시 부하가 높으며 대부분 인덱스로 인한 부하

• Instance Activity Stats 에서도 INSERT 시 인덱스생성 부하가 높게 나타남

• Wait Event 에서는 버퍼 캐시의 프리 버퍼가 부족한 것으로 파악

• INSERT 대상 테이블의 INDEX 사이즈 30GB

• 버퍼 캐시 2GB 대비 INDEX의 사이즈가 크며 입력되는 데이터가 넓은 범위의 값이기 때문에 대량의 INDEX BLOCK 이 버퍼 캐시를 점유하여 버퍼 캐시 부족 현상 발생

• 버퍼 캐시 2GB 에서 8GB로 증가시켜 해당 현상 해결

 

 

 

 

3. LITERAL 쿼리로 인한 HARD PARSE

• SQL 처리 속도가 전반적으로 느림
• Parse 중 Hard Parese 비중이 96.87 %

• SQL processing time 중 optimizer time 이 높음.

• Hard Parese 로 인하여 PP hit 율이 2.42%

• 다량의 SQL로 인하여 Shared Pool 에서 PP (physical plan) 의 점유율이 높음

 

리터럴 쿼리 확인 방법

select max(sql_id) sql_id,
substr(sql_text, 1, 140) "SQL",
module,
count(*) cnt,
sum(executions) "TotExecs",
min(first_load_time) start_time,
max(first_load_time) end_time
from v$sqlarea
where executions < 5
group by substr(sql_text, 1, 140), module
having count(*) > 10;

• TPR SQL 항목에서는 LITERAL 쿼리의 특성상 나오는 경우가 적음 (적은 수행 횟수)

• SQL Area에 Parsing되어 있는 SQL중 상위 140 character가 동일한 SQL중 그 발생횟수가 10번 이상 발견된 Literal SQL을 찾는다.

• 해당 SQL을 Bind 처리 또는 cursor_sharing=force 로 설정

 

 

 

 

4. 대량 풀스캔으로 인한 버퍼 캐시 히트율 저하

• 특정 시간대에 OLTP 응답속도 저하 발생

• Logical Reads 대비 Physical Reads 가 높음

• 스냅샷 구간의 부하량은 대부분 SELECT 로 인한 부하

• SELECT시 풀스캔이 부하가 많음

• 버퍼 캐시 히트율이 평소의 94.78% 대비 72.92%로 낮음

• SELECT시 풀 스캔으로 인한 multi block disk read time 이 Top Event

• 8.7 SQL Ordered by Reads 를 통해 Physical Read 가 많은 SQL을 찾음

• SQL 튜닝 포인트가 없고 1회 수행 SQL 이라서 버퍼 캐시에서 재사용할 가능성 낮음

• _FSCAN_SMART_FETCH=Y 를 적용하여 풀 스캔시 버퍼 캐시의 일정 부분만 사용하도록 설정

• _FSCAN_SMART_BUFFER_CACHE_MAX_RATIO 로 버퍼 캐시 사용 비율 설정(기본값 3)

 

 

 

 

5. Temp Segement 경합에 의한 성능 저하(TAC만)

• 1번 노드의 SQL 처리 속도가 2번 노드 대비 오래걸림

• Workload Summary 상에서는 특별한 부하가 없음

• SELECT 의 비율이 대부분이며 그중 sort time 점유율이 매우 높음

• PGA 공간이 부족하다고 나타나지만 더 이상 메모리 확보 불가능 상황

• Wait Event 는 Temp Segment 관련 Event 가 상위

• temp granule get wait 는 Temp Segment 를 TAC 노드간 동기화를 위한 대기 시간

• 자신의 노드 Temp Segment 우선 사용. 모두 소진시 다른 노드 Temp 동기화 후 사용.

• SPIN_TEMP_UNIT_POOL_TF 는 Temp 파일별로 Temp extent의 가용 공간을 찾거나 공간할당을 동기화하는 Spinlock

• Temp 파일 수를 늘려 노드간 Temp Segmnet 경합 줄임 (파일수는 노드의 배수 권장)

 

 

 

 

 

6. 플랜 변경으로 인한 응답속도 저하

• 업무 추가 및 변경이 없는 상태에서 배치 업무가 지연되는 현상 발생.

• Workload 를 정상 시점과 지연 시점을 비교시 Logical Read 와 Physical Read 증가 발생

• SQL Processing 비교시 SELECT 의 DB Time 비율이 큰폭으로 상승

• ‘8.4 SQL Ordered by Elapsed Time per Execution’ 확인시 정상시점에 Top SQL 에 나타 나지 않던 SQL이 가장 상위로 올라옴을 확인

• 당일 새벽 통계정보 수집이 있었음을 파악하고 통계정보 원복 후 정상 동장 확인

 

통계정보 복원 방법

 

 

 

 

 

 

7. RSB (resource block) 부족 현상(TAC만)

• 평소 30분 정도 소요되던 인덱스 생성 시간이 3시간 이상 소요됨

• 부하량 지표는 모든 항목이 매우 낮은 수준을 유지하고 있음

• 다른 노드에서 블록을 받아오는 속도가 수십ms 로 매우 느림

• 블록은 다른 노드로 보내는 속도는 정상적이기 때문에 인터커넥트 속도 문제는 아님

• 대기 이벤트에서 spinlock total wait 항목이 DB time 275%을 점유

• 문제의 원인이 되는 스핀락은 ‘7.7 Spinlock(Latch) Statistics’ 에서 파악 가능

• RSB를 회수하는 과정에서 발생하는 SPIN_CCC_RECL_WS 항목이 높음

• RSB 공간 추가 확보를 위해 _CCC_RECL_MAX_RESOURCES_RATIO 값 증가시킴 (300~400)

 

 

 

 

 

 

8. 핫블록으로 인한 경합

• 목표 TPS 대비 약60% 수준의 성능이 나옴

• 초당 트랜잭션이 392회지만 리두 생성량 및 블록 체인지량이 적은 걸로 보아 단건 트랜잭션일 확률이 높음

• SQL 처리중 UPDATE 가 차지하는 비중이 48.17% 로 높음

• 사용할 블록이 다른 세션이 처리중일때 발생하는 blocked by CR/CUR buffer busy 이벤트 가 상위에 올라옴

• BRANCH 테이블을 UPDATE 하는 SQL 이 DB time 의 48.17% 차지

• 해당 테이블은 100건의 로우가 있고 해당 로우를 다수의 세션에서 UPDATE 처리를 하여 핫블록 발생

• 로우의 사이즈가 작아 1개의 블록에 모든 로우가 적재되어 있음

• BRANCH 테이블을 해시 테이블로 만들어 블록 분산 시킴

 

 

 

 

9. SPIN_PPC_BUCKET 경합

• 특별한 부하는 보이지 않지만 다수의 세션이 풀리지 않고 Runnuing 상태

• DB의 부하는 전반적으로 낮은 상태로 유지되고 있었음

• SQL 처리중 SELECT 가 차지하는 비중이 높으나 응답속도가 느린 쿼리는 없음

• 대기 이벤트 상에서는 spinlock total wait 가 가장 상위로 올라옴

• PP 캐시 탐색을 위한 SPIN_PPC_BUCKET 의 MISS율이 51.17%로 높음

• ‘2.1 Workload Stats’ 의 optimizer time 의 수치가 낮은걸로 보아 하드파싱이 아닌 소프 트파싱의 부하로 판단됨

• SQL 의 수행 횟수를 보면 1번 SQL 은 초당 426번 수행, 2번 SQL 은 초당 46회 수행하나 결과값에 UDF 를 사용하고 있음 • 세션에서 PP 를 캐시하는 _SESS_PPC_SIZE 파라미터 적용 (OPEN_CURSORS 보다 큰 값을 적용해야함)