1. 동일 Row 변경에 의한 TX Enqueue : http://www.exemwiki.com/?p=1949
: Enqueue는 lock의 종류이다. Oltp 환경에서 자주 볼 수 있는 Tx Enqueue는 A 사용자가 Dml 후, Commit or Rollback을 하지 않은 상태체서, B 사용자가 Dml 작업 시도시 무한대기 하고 있을때 나타난다.이를 해소하기 위해서는 A 사용자의 Dml Transaction을 Commit or Rollback 처리하여, 종료를 시켜야 한다. 운영Db에 이와 같은 상황이 발생이 잦다면, 문제 모듈을 분석하여 최대한 DML후 다른 작업 없이 Commit or Rollback 이 되도록 수정해야 한다.
2. Sql 파싱( Hard Parsing & Soft Parsing ) : http://www.exemwiki.com/?p=1952
: Soft / Hard parsing의 차이점에 이해시키려는 만화. Oracle에 Sql을 질의하면, Server Process가 맨 처음 하는일이 SGA > Shared pool > Library Cache 영역에 질의한 쿼리의 실행정보(이력)이 있는지 검색한다. 만약 실행정보가 있다면 Soft Parsing, 없다면 Hard Parsing을 하게 되는데 상세한것은 아래와 같다.
우선 소프트냐 하드냐 따지기 전에, 간략하게 Shared Pool의 구조를 아는게 좋다. Shared Pool은 사용자간 공유할 수 는 다양한 구성요소를 저장하는 곳이다. 대표적인것인 4가지 Library Cache, Dictionary Cache, Result Cache, Reversed Cache가 있다. Sql parsing과 관련된것은 Library Cache 와 Dictionary Cache이다. Library Cache에는 Sql Text, Parsing Tree, 실행계획 등이 저장된다. 쿼리 질의시, Server Process가 Libray Cache에 접근하기위해, Library Cache Latch를 얻는다. 이후 Sql실행정보(LCO : Library Cache Object)가 있는지 찾는다. 만약 Sql 실행정보가 있다면, LCO를 생성하지 않고 바로 쿼리를 실행한다. 이것이 소프트 파싱이다. 만약 Libaray Cache에 질의한 Sql의 실행정보가 없다면 Hard Parsing을 해야한다. 실행 정보 저장 공간 확보를 위해 Shared Pool Latch를 획득하고, LCO생성하고, 실행정보데이터를 저장하는 단위작업이 불가피하다. 이것이 흔히 말하는 하드파싱의 줄여라줄여라 하는 이유이다.
* 참고
- 실행정보를 만들기 위해서는 Dictionary Cache를 참조해야한다. Dictionary Cache는 모든 사용자의 정보, 권한을 저장하고 있는 테이블과 뷰, 데이터베이스에 존재하는 테이블과 뷰이름, 테이블 컬럼명, 데이터 타입등을 가지고 있다. 보통 Dictionary Cache에 저장되는 것을 볼수 있는 뷰정보는 다음과 같다. ALL_(사용자가 접근가능한), USER_(사용자가 소유한), DBA_(DBA만 볼수있느), V$(아키텍쳐 성능 및 성능 뷰 ) 이다.
그래서 개발자는 쿼리작성시 대소문자 구분하여, 문장의 동일성을 유지시키는것이 중요하다.
3. Sql의 작성 : http://www.exemwiki.com/?p=1951
4. Enq : SQ - Contention : http://www.exemwiki.com/?p=1949
5. Latch : Cache Buffers Chains : http://www.exemwiki.com/?p=1957
6. DB file scattered read Multi Block I/O : http://www.exemwiki.com/?p=1959
: DataFile에서 Data를 읽는 방법 1
7. DB file sequential read : http://www.exemwiki.com/?p=1961
: DataFile에서 Data를 읽는 방법 2
8. Enq : US - Contention : http://www.exemwiki.com/?p=1963
: Undo Segment 대기
9. Log File Sync : http://www.exemwiki.com/?p=1965
: Redo Log file에 Redo buffer의 내용을 기록하는 것이 끝날때까지 대기
10. Library Cache pin : http://www.exemwiki.com/?p=1967
: 쿼리수행중인데 ,쿼리의 실행계획에 영향을 주는 명령은 Library Cache pin 대기
11. Log Buffer Space : http://www.exemwiki.com/?p=1969
: 많은 dml로 발생하여, Log Buffer 쓰지 못하고 대기
12. Read By Other Session : http://www.exemwiki.com/?p=1971
: Disk에서 Buffer Cache로 적재중인 블록을 읽으려는 세션은 적재가 끝날때까지 기다려야하는 대기
13. Direct Path Read Temp : http://www.exemwiki.com/?p=1973
: Sorting, Hash 관련 작업 시, PGA 공간이 모자라면 Temp TableSpace에 결과값을 저장해놨다가 여러번에
걸쳐 가져옴. 버퍼 캐시를 거치지 않고, Direct로 read ( 버퍼캐시 거치지 않음 )
14. Direct Path Write : http://www.exemwiki.com/?p=1975
: 많은 양을 insert 할시, append 힌트를 사용시 발생. Sga(Buffer Cache)에 거치지 않고, Undo가 생기지 않고, Redo
도 생성되지 않음 (nologging) 실은 최소한의 Redo는 남김.
15. Write Complete Waits : http://www.exemwiki.com/?p=1977
: DBWR 내려적고 있는 버퍼를 액세스 할때 나오는 이벤트, 잦은 체크 포인트로 인해 발생 가능하며, DBWR I/O를 개선하여 잦은 체크 포인트에 대처
16. Latch : cache buffers lru chain : http://www.exemwiki.com/?p=1979
: Disk에서 블록을 Data Buffer Cache로 올릴때, 프리버퍼를 확보하려고 LRU 리스트를 탐색
( COLD 영역에서 => HOT 영역으로 ) FREE BUFFER 확보, 이때 LRU 리스트 접근할때 획득하는 래치.
문제 발생시 쿼리 I/O양 줄이기, 버퍼캐시 크기조정, DBWR I/O 개선등
17. Enq : TM - contention : http://www.exemwiki.com/?p=1981
: index rebuild시 , 테이블에 tm lock 모드 획득. rebuild 동안 테이블에 dml 수행 불가. ( TM Lock 경합 )
18. Latch : shared pool ( Bind mistatch ) : http://www.exemwiki.com/?p=1983
: 바인드 변수 data type의 크기가 달라서, 같은 쿼리지만 옵티마이저가 다르게 인식한다.
이는 Shared pool 에서 sql문을 공유하지 못하고 하드파싱하게 된다. 그러면 Shared Pool에 sql문이
하나더 생기고 그 과정에서 Shared pool 저장해야 되므로 Shared pool latch 경합이 발생할수 밖에 없다.
19. Enq : TX - index contention : http://www.exemwiki.com/?p=1985
: index의 leaf 블록이 꽉 차면, split이라는 작업이 내부에서 일어남
full block의 키 값중 일부가 free block으로 이주하고, 키값 정리 분산하는 것이 split.
이렇게 split이 일어나는 동안에는 key 값이 변경되지 않도록 해당 블록에 tx lock을 사용 함.
split이 발생하는 동안 해당 블록을 변경하려는 세션들은 Enq : TX - index contention 대기를 볼 수 있음.
20. Latch : library cache ( Function call로 인한 수행 횟수 증가 ) : http://www.exemwiki.com/?p=1989
: 쿼리 질의에 출력건수가 100건인데, select 절에 함수가 있다면 해당 함수 또한 100번 호출 된다.
이는 sql 수행시 library cache 영역을 탐색하기 위한 latch 를 101번 획득해야한다.
자주 수행한다면 latch : library cache로 대기 할 수 도 있다. 함수를 스칼라서브쿼리 형태로 변경.
21. Log file switch completion : http://www.exemwiki.com/?p=1989
: 리두 로그 버퍼가 다 사용되면 그 위에 겹쳐서 로그를 기록 함. 변경된 기록이 겹쳐 기록되기 전에 LGWR 백그라운드 프로세스
가 로그기록을 리두로그 파일에 저장함. 이때 리두 로그 파일이 꽉 차서 내려쓰기를 할 수 없으면 스위치가 완료 될때까지 기다
리게 되는데 이때 Log File Switch Completion 이벤트 대기.
로그 파일 스위치가 끝나는 시점에 새로 사용한 리두 로그 파일에 대해 종료 되지 않은 작업이 있다면
log file switch ( checkpoint in complete ),
log file switch ( archiving needed ),
log file switch ( private stand flush incomplete )
22. Enq : HW - contenttion : http://www.exemwiki.com/?p=1991
: 땅은 일종의 Segment에 할당된 extent고, 경계선은 HWM 이다. 개간된 땅은 formatting 된 블록이고, 땅을 다써서 새로
formatting 될때까지 작업을 못하고 enq : HW - contention 대기.
HWM을 여러프로세스가 동시에 변경하는 것을 막기 위한 락을 HW락, 락 획득과정에서 경합 발생시 이벤트 대기
HW락 경합은 세그먼트의 급속한 공간확장이 필요한 경우 매우 보편적으로 나타나며, 극단적인 성능 저하를 야기하는 경우도
발생함.
23. GC Buffer Busy : http://www.exemwiki.com/?p=1993
: 로컬 프로세스가 읽고자 하는 블록이 현재 REMOTE 노드의 요청에 의해 사용중임을 의미하는 대기이벤트.
BUFFER BUSY WAIT or READ BY OTHERS SESSION 이벤트의 global version 으로 이해.
다른 Local Process가 Remote Node에서 전송받고 있는 Block을 동시에 Access하는 과정에서 발생.
Remote Node의 요청에 의해 전송되고 있는 Block을 동시에 Access하는 과정에서 발생.
원격에서 데이터를 가져가거나, 원격에서 DML하는 블록에 대하여 로컬에서 ACCESS 하려고 한다면,
GC(Global Cache) Buffer Busy라고 출력.
24. Free Buffer waits : http://www.exemwiki.com/?p=1995
: Free Buffer(여유 공간)을 찾지 못해 DBWR에 요청신호를 보낸후 Free Buffer waits 대기 이벤트 대기하는 것.
주요 원인 비효율적인 SQL문, 불충분한 DBWR 프로세스 수, 느린 I/O 서브 시스템, 작은 BUFFER 캐시
25. SGA : allocation forcing component growth : http://www.exemwiki.com/?p=1997
: v$parameter에 SGA_TARGET=TRUE이면 SGA 영익이 상황에 맞춰서 늘었다 줄었다함.
일이 많아지면 공간을 늘려야 하고, 그동안 아무일도 못하는 것처럼.
우리 사무실을 SGA라고 생각하면, 지원팀(Shared pool)이 충분히 잘 사용했는데, 신입사원(증가된 일량)이 늘어나서
크기를 늘려야 함. 근데 다른팀(SGA에 다른 자원)으로부터 공간을 할당받고 증가 시키면서
SGA : allocation forcing component growth 이벤트 대기가 발생됨. 이러한 현상을 막기 위해서는 SGA_TARGET이라는
파라메터를 FALSE로 해줘야함. 한진택배 사이트는 SGA_TARGET의 VALUE가 0이므로, FALSE로 되어있음.
26. Enq : TC - contention : http://www.exemwiki.com/?p=1999
: Parallel Query에서 체크포인트가 발생하는 이유는 슬레이브 세션에 의한 "Direct path read" 때문이다.
Direct Path Read는 버퍼캐시를 거치지 않고 데이터 파일을 직업 읽기 때문에 현재 SGA에 있는 블록과 데이터 파일에 있는
블록 사이에 버젼 불일치가 생길수 있어서 코디네이터 세션은 슬레이브 세션을 구동하기전에 Direct Path Read를 수행한
객체 에 대해 세그먼트 레벨의 체크포인트를 요청하게 되고, 체크 포인트 작업이 끝날때까지 enq:TC-contention
이벤트 대기가 발생.
27. GC CR / Current Block 2/3-way http://www.exemwiki.com/?p=2001
: 이해 안됨.
28. GC CR / Current Block congested http://www.exemwiki.com/?p=2003
: 이해 안됨.
29. Db file parallel write http://www.exemwiki.com/?p=2005
: 점심시간 식탁에 사람들은 꽉차있고, 종업원들은 치우느라 바쁘고 손님들도 엄청 대기하고 있음.
꼭 여기 식탁들이 Dirty Buffer이고, 음식을 치워주는 종업원들이 DBWR이다. 여기 식탁에 앉으려고
대기하는 손님들은 Free Buffer Wait를 대기하고, 사람들이 다 먹고 난 자리를 종업원들은 db file parallel write를 대기.
DBWR이 더티블록을 기록하기 위해 I/O 요청을 보낸후, 요청이 끝나기를 기다리는 동안 대기하는 EVENT.
db file parallel write 대기는 기본적으로 i/o 이슈이며, DBWR 프로세스에서 이 대기가 광범위하게 나타난다면, 데이터파일과
관련된 I/O 시스템에 심각한 성능 저하 현상이 발생한 것으로 판단 할 수 있다. I/O 시스템의 성능에 문제가 없는데도 DB FILE
PARALLEL WRITE 대기가 사라지지 않는다면, 그때는 I/O 시스템이 감당할수 없을정도의 많은 쓰기 요청이 발생한 것으로
간주.
30. SQL*NET more data from/to client : http://www.exemwiki.com/?p=2007
: SQL*NET message from client 대기 이벤트는 세션이 클라이언트(사용자)로 부터 메시지를 대기할때 발생.
( 일반적으로 해당 이벤트는 세션이 idle 상태라는 것을 의미 )
SQL*NET message to client 대기 이벤트는 클라이언트로 메시지를 전송할때 발생.
( 클라이언트 프로세스가 메시지를 전송 받을수 없을 만큼 바쁘게 다른일을 하거나,
네트워크 지연으로 인해 메시지 전송 시간이 오래걸릴때 발생 )
SQL*NET more data from/to client는 data의 전송량이 커서 한번에 전송하지 않고 여러번 나누어 한다는 것을 의미
아주 긴 sql문장의 수행을 오라클에게 요청하는 경우, oracle은 os에 전송을 요청하고 응답이 올때까지 SQL*Net more data
from client 대기하게 되고, 거꾸로 아주 큰 data를 client에 보내줘야 하는 경우 (LOB가 대표적) Oracle은 SQL*NET more
data to client 대기 하게 됨.
31. Cursor pin S wait on X : http://www.exemwiki.com/?p=2009
32. GC current split : http://www.exemwiki.com/?p=2011
33. GC cr failure : http://www.exemwiki.com/?p=2013
: CR Block을 전송 받는 과정에서 실패(failure)가 발생했음을 의미하는 Fixed-up Event
gc current retry : Current Block을 전송받는 과정에서 오류가 발생했음을 의미하는 Fixed-up Event.
gc cr/current block lost : Block을 전송받는 과정에서 유실(Loss)이 발생했음을 의미하는 Fixed-up Event.
34. GC cr/current multiblock request : http://www.exemwiki.com/?p=2015
: 로컬 캐시에 존재하지 않는 여러개의 데이터블록을 동시에 읽고자 하는 프로세스는
해당 데이터 블록들을 관리하는 마스터 노드에게 블록전송을 요청하고, 응답을 받을때까지 gc cr/current multiblock request
이벤트 대기한다. FTS와 같은 멀티 블록I/O를 수행하고자 하는 서버 프로세스는 마스터노드에게
DB_FILE_MULTIBLOCK_READ_COUNT 파라미터 값 만큼 블록 전송 요청을 수행하며, 블록을 전송받거나
블록을 읽을 권한을 부여 받는다. gc cr/current multiblock request 이벤트에는 fixed-up 이벤트가 제공 되지 않는다.
35. Enq:ST - contention : http://www.exemwiki.com/?p=2017
36. DFS Lock handle : http://www.exemwiki.com/?p=2019
: 노드간 sequence Lock
37. Enq : TX - allocate ITL entry : http://www.exemwiki.com/?p=2021
38. kksfbc child completion : http://www.exemwiki.com/?p=2023
39. DB file parallel read : http://www.exemwiki.com/?p=2025
40. Control file parallel write : http://www.exemwiki.com/?p=2027
41. Child LCO 생성 : http://www.exemwiki.com/?p=2029
42. gc cr/current grant 2 way : http://www.exemwiki.com/?p=2031
43. log file parallel write : http://www.exemwiki.com/?p=2033
: log file parallel write 대기를 해결하는 방법은 일반적으로 log file sync 대기와 비슷하다. 두 대기 이벤트 모두
LGWR의 성능에 크게 좌우되기 때문 log file parallel 이벤트는 LGWR 프로세스에서만 발생하는 대기이벤트. LGWR는
리두 버퍼의 내용을 리두로그 파일에 기록하기 위해 필요한 I/O 콜을 수행한후 I/O작업이 완료되기를 기다리는 동안
LOG FILE PARALLEL WRITE 이벤트로 대기.
44. Direct Path Read ( Parallel Execution과 Direct Path I/O ) : http://www.exemwiki.com/?p=2035
45. Buffer Busy Waits (동일블록변경에 의한 Data Block에 대한 경합 ) : http://www.exemwiki.com/?p=2037
: 동일 블록 변경에 의한 데이터 블록에 대한 경합
동일한 블록의 서로 다른 로우를 동시에 변경할 경우 버퍼락에 의한 buffer busy waits 이벤트가 발생함.
하나의 로우를 변경을 하기 위해서는 그 로우를 포함하고 있는 블록 전체를 exclusive 모드로 버퍼락을 획득합니다.
46. GC current block busy ( Redo current block busy ) : http://www.exemwiki.com/?p=2039
47. Latch Free ( simlulator lru latch ) : http://www.exemwiki.com/?p=2041
: latch는 특성상 획득할때까지 계속해서 cpu를 점유하면서 스핀하여 latch 획득 시도를 하기 때문에
다수의 세션이 latch 대기를 하게되면 그만큼 cpu 사용율이 증가합니다. 그래서 latch를 해결해야 하는뎅
Simulator lru latch는 쿼리가 수행됐을때, 해당 하는 쿼리에 대해서 일종의 Simulation을 수행해보는 것이라
Latch Free ( Simulator lru latch)를 해소하기 위해서는 DB_CACHE_ADVISOR 기능을 OFF 시키면 이 이벤트
대기현상이 해소됩니다. 그래서 CPU에 민감하면 이기능을 OFF하는 것을 권고합니다.
'ORACLE > SQL Tuning' 카테고리의 다른 글
| 성능 수치 단위 (0) | 2024.10.22 |
|---|---|
| 사용자함수 ( 함수명, 서브쿼리변환, Deterministic, Result Cache ) 호출 결과 분석 (0) | 2024.10.15 |
| LITERAL SQL 찾기 - 심화 (1) | 2024.10.01 |
| like '%'|| :1 ||'%' 성능 개선 방법 고민 (2) | 2024.10.01 |
| [ OZ Report ] Literal SQL 때려잡기 (2) | 2024.09.25 |