About DATABASE

아직도 Shared pool 설정에 어려움을 느끼십니까?

작성자 : 한민호(blubeard@nate.com)

필자가 Oracle Internal에 관심을 가지게 된 것은 얼마 되지 않지만 공부를 하면서 이렇게 자료를 만들어 정리를 해보는 것도 좋은 경험이 되고 여러 사람들과 지식을 공유하고 싶었기 때문에 OKM에 기고하게 되었다.

SGA에 서 shared pool만큼 사이즈를 설정하기 어려운 Parameter가 없다. 그리고 이 shared pool의 크기는 너무 커도, 너무 작아도 문제가 된다. 즉, 얼마나 적절히 설정하느냐에 따라 DB의 성능을 좌우하게 된다. Parameter만 잘 설정해도 성능이 좋아진다니 이렇게 편하고 좋은 튜닝 방법이 어디 있을까 하는 생각도 든다. 하지만 shared pool의 크기를 아무 지식과 진단 없이 적절히 설정하기란 여간 까다로운 일이 아닐 수 없다. 특히 Row cache와 Library cache는 크기를 각각 설정하는 것이 불가능하기 때문에 초보자에겐 이런 것들이 어려움으로 다가올 수 있다. Shared pool을 자세히 알아 봄으로써 그러한 걱정들을 이번 기회에 덜 수 있다면 훌륭한 DBA가 되는데 도움이 되리라 생각된다. 이에 Shared Pool을 설정함에 있어 진단의 지표로 OWI를 사용할 것이다.

- Wait Event의 중요성

 Programming을 해본 사람이라면 동기화 문제에 대해 매우 잘 알고 있을 것이다. Oracle역시 수많은 Transaction에 의해 작업이 되기 때문에 이 때 발생하는 동기화 문제들을 해결하기 위해 수 많은 Latch, Lock, Pin을 사용하고 있다. 이 동기화란 것은 Serial한 작업이기 때문에 성능에 막대한 영향을 주게 된다. Wait Event는 이러한 동기화 작업에 있어서 Critical Section에 들어가지고 못하고 대기하는 작업들의 대기시간 동안 발생하는 이벤트이다. 이 때문에 Wait Event 발생을 줄이는 것은 중요한 일이고 이를 잘 분석하여 Tuning하는 것은 매우 효과적인 방법인 것이다. 그럼 이제 Shared Pool을 Wait Event의 관점에서 진단하고 분석해보기로 하겠다.

- Shared pool의 목적

Shared pool에 대해 간략히 설명을 해보자면 shared pool의 목적은 실행된 Cursor를 공유하여 CPU나 memory를 효율적으로 사용하는 데 있다. Cursor란 SQL의 경우 실행할 때 필요한 실행계획 같은 실행 정보를 담고 있는 SGA상에 할당된 Heap Memory를 말한다. 물론 공유 할 수 있는 것들은 다양하다. 공유할 수 있는 정보들을 나열하자면 SQL구 문, 실행계획, PL/SQL소스와 그것들의 실행정보, table, view 같은 object 등이 있다. 이것들을 공유한다면 동일한 PL/SQL이 나 SQL을 실행함에 있어 매번 실행계획을 만들며 hard parsing이 일어나는 부하를 예방할 수 있다.

- Shared pool의 구성요소

shared pool을 구성하고 있는 구성요소에 대해 알아보겠다. 우선 shared pool의 구성요소는 4가지로 나뉜다. Process목록, Session목 록, Enqueue목록, Transaction목 록 등이 할당된 Permanent Area와 SQL문 을 수행하는데 필요한 모든 객체 정보를 관리하는 Library cache, dictionary 정보를 관리하는 Row Cache, 그리고 마지막으로 동적 메모리 할당을 위한 공간인 Reserved Area로 나눌 수 있다.

- Heap Manager를 통한 메모리 관리

메모리에 대한 할당 및 해제 작업은 Heap Manager를 통해 동적으로 관리가 된다. 이 Heap Manager에 대해 간략히 알아보면 Top-level의 Heap과 그 하위에 여러 개의 Sub-Heap을 포함하는 구조를 이루고 있다. 이 Heap은 또한 linked list구 조의 Extent들로 구성이 되어 있으며 Extent는 여러 개의 chunk로 구성되어있다. 실제적으로 chunk의 사용 현황에 대해 알고 싶다면 X$KSMSP라는 View를 통해 관찰 할 수 있을 것이다.

- Chunk의 관리

Chunk는 4가지 상태로 관리가 된다. 그 4가지 상태는 Free, Recreatable, Freeable, Permanent다. 이러한 chunk들의 상태에 따라 linked list가 구성되는 것이다. 상태의 이름만으로도 그것이 어떤 상태인지 알 수 있을 것이다. 더 정확히 설명 하자면 Free는 즉시 사용 가능한 상태를 말한다. 이 Free 상태의 chunk들로 묶여 있는 linked list가 free list인 것이다. 구체적으로 설명하면 이것은 255개의 bucket이 있고 각 bucket당 free chunk들 이 linked list구조로 연결되어있다. 이때 bucket은 각각의 정해진 기준의 크기 이하의 chunk들로만 구성되어 있다. 이러한 이유로 bucket이 아래로 갈수록 chunk들의 크기가 크다. Recreatable은 재생성 가능한 상태이다. 뒤에서 다시 설명하겠지만 이것은 unpinned(현 재 사용되고 있지 않은)일 때 재사용이 가능하다. 쉽게 말하자면 이것은 사용이 되었었지만 다시 사용될 확률이 낮아져서 재사용이 가능한 상태가 된 것이며, 현재 사용 중이 아니라면 chunk를 재사용할 수 있도록 이러한 상태의 chunk를 묶어 LRU list로 관리한다. (관련 뷰 : X$KGHLU) 그리고 Freeable은 session이나 call 동안에만 필요한 객체를 저장하고 있는 상태이며 이는 session등이 금방 끊길 수도 있기 때문에 chunk가 필요할 때 할당의 대상이 되지는 못한다. Permanent는 말 그대로 영구적인 객체를 저장하고 있는 상태이며 이것 역시 사용할 수 없는 chunk다.

실제 Heap Dump를 이용하면 R이 앞에 붙어서 상태가 정의 되어 있는 것을 볼 수 있는데 이것은 SHARED_POOL_RESERVED_SIZE란 Parameter를 통해 발생한 chunk들이다. 이 chunk도 적절히 사용하면 매우 중요한 튜닝 요소가 될 수 있다. 이것에 대해 oracle 매뉴얼에서는 PL/SQL block이 많이 사용되는 경우를 예로 들고 있다. 즉, 이것은 large chunk를 위해 할당된 공간인 것이다. Parameter를 정해 주지 않는다면 설정된 shared_pool_size의 5%가 default value로 분류된다. Steve Adams의 Oracle Internal이나 매뉴얼에서도 5000byte 이상의 큰 object를 위한 공간이라고 설명한다. 이는 large chunk가 요구되지 않는다면 굳이 설정할 필요가 없다는 말도 되는 것이다. 이러한 튜닝 요소에 초점을 맞추어 설정하면 되는 parameter인 것이다. 이것들 역시 linked list로 관리되며 명칭은 Reserved Free list라고 부른다.

(아 래 그림 1을 참조한다면 이해하는 데에 도움이 될 것이다.)

[그림 1] Shared Pool Heap 구조(출처 : Advanced OWI in Oracle 10g / ㈜엑셈)

정리를 해보자면 chunk는 사용할 수 있는 것과 없는 것으로 나눌 수 있다. 그 중에 실제 chunk 할당 과정에서 latch경 합을 벌여 Wait Event가 발생하기 때문에 free list와 LRU list에서 관리되고 있는 chunk들에 주목을 할 필요가 있을 것이다. 왜냐하면 운영 시 peak time때의 할당된 chunk의 총 크기가 곧 shared pool의 size를 적절히 결정하는 데에 중요한 역할을 하기 때문이다. 이것에 대한 설명은 Wait Event의 발생과 연관 지어 이야기 해 보겠다.

- Shared Pool의 관리

Chunk를 할당하는 과정에서 반드시 필요한 것이 shared pool latch의 획득이다. 하지만 이러한 shared pool latch는 아쉽게도 shared pool당 단 1개 밖에 존재하지 않는다. 이것은 즉, chunk를 동시에 할당 받아야 할 상황이라면 이것을 획득하는 과정에서 경합을 벌이게 된다는 의미이다. 무엇 때문에 이렇게 shared pool latch 수를 적게 만들어 놓았는지 정확히 알 수는 없지만 여러 개를 만들어 놓았다면 역시나 동기화 문제를 관리하는데 있어 어려움이 있기 때문이 아닐까 싶다. 다행히도 Oracle 9i부터는 Hidden Parameter인 _KGHDSIDX_COUNT를 이용하여 하나의 shared Pool을 7개 까지의 Sub-Pool로 나누어 관리하는 것이 가능해졌다. 그리고 더 반가운 소식은 각각의 Sub-pool당 독자적인 free list, LRU list, shared pool latch를 가짐으로 인해 부족했던 자원에 대한 경합을 그나마 감소시킬 수 있게 되었다. 그러나 경합은 감소했을지 몰라도 CPU의 개수나 SHARED_POOL_SIZE가 충분히 크지 않다면 ORA-4031에러의 발생위험이 1개의 shared pool로 관리됐을 때보다 더 높다는 것이다. 그래서 권장하는 방법이 CPU 4개 에 SHARED_POOL_SIZE가 250m이 상일 때 Sub-pool을 사용하는 것이다. 한 때는 하나의 Sub-pool에 할당 가능한 chunk공 간이 없으면 다른 Sub-pool에 사용 가능한 free chunk가 있음에도 찾지 못했던 버그도 있었다. shared pool latch에 대해 좀 더 알아보자면 실제적으로 shared pool latch를 소유하는 시간이 shared pool latch를 대기하게 하는 중요한 이유이다. 때문에 latch를 획득한 후에 일어나는 작업들을 알면 경합의 포인트를 알 수 있을 것이다.

 - Chunk의 할당과정

Shared pool latch를 획득하게 되면 우선 free chunk를 찾기 위해 free list를 탐색한다. 그리고 적절한 free chunk가 있다면 할당을 받지만 없다면 LRU list를 탐색하게 된다. 이것마저도 찾지 못한다면 Reserved Free List를 탐색하고 이것 역시 실패하면 Spare Free Memory를 탐색하게 된다. 이 모든 과정이 실패가 되었을 때 ORA-4031에러가 발생하게 되는 것이다. 이 과정에서 할당을 받게 된다면 딱 필요한 크기만 할당을 받고(split) 나머지는 다시 free list에 등록이 되기 때문에 free list가 할당이 된다고 해서 반드시 짧아지는 것은 아니다. 그리고 적절한 chunk를 찾기 위해 위에서와 같이 여러 과정은 거치지만 이 과정은 생각보다 매우 빠른 시간 안에 이루어진다. 하지만 이것들이 다수의 작업이 된다면 경합에 대한 wait time은 피부로 느껴질 것이다. 여기서 이제껏 언급이 없었던 Spare Free Memory에 대해 궁금해하는 분이 많을 꺼 같아 간단하게 설명하자면(이 내용은 Steve Adams의 Oracle Internal에 내용이 있다) instance가 start up 되었을 때 shared pool size에 정해진 크기의 절반만이 shared pool에 할당된다. 이것은 성능을 극대화 하는데 에도 연관이 있으리란 생각이 든다. Chunk의 수가 줄면 그 만큼 free list가 짧아지기 때문에 그에 대한 탐색시간도 짧아지고 shared pool latch의 소유 시간 역시 짧아지기 때문에 메모리를 숨겨놓지 않았을까 생각된다. 위의 과정들을 미루어 짐작해볼 때 shared pool latch의 소유시간은 free list의 길이와 얼마나 빨리 적절한 chunk를 찾느냐에 따라서 결정된 다는 것을 알 수 있을 것이다. 그럼 free list의 길이가 길어지는 것은 어떠한 경우 일까? 바로 그것은 chunk split가 다량으로 발생하여 단편화(fragmentation) 되었을 때이다. 이러한 경우 free list의 길이가 길어지게 되는 것이다. 단편화는 hard parsing에서 일어나는 것인데 hard parsing에 대해 모르는 독자들을 위하여 간단히 설명하면 처음 실행하는 SQL문이 있다면 이것에 대한 실행정보를 저장하고 있는 Heap Memory 할당이 필요한데 이 Heap이 바로 chunk인 것이다. 이러한 실행계획을 만들고 chunk에 할당하는 과정은 매우 부하가 있기 때문에 hard parsing이라고 이름 붙여진 것이다. 이런 과도한 단편화로 인해 shared pool latch의 경합만 가중 시키는 것이 아니다. 큰 chunk 할당이 요구되는 hard parsing이 이루어 질 때 적절한 free chunk를 찾지 못하여 ORA-4031에 러를 유발하게 된다.

- Shared Pool Size 설정

지금까지 설명했던 것들을 가지고 shared pool size에 대해 결론을 내 보면, 첫째로, memory가 무조건 크다는 생각으로 shared pool을 늘리면 안 된다고 볼 수 있다. 이것은 오히려 free list의 길이만 늘어나게 되기 때문이다. 그리고 V$SGASTAT를 통해 확인한 shared pool의 free memory가 작다고 해서 SHARED_POOL_SIZE를 늘려서는 안 된다. Free memory는 단지 free chunk의 합이기 때문이다. 이는 즉 LRU list, reserved list, spare memory도 있기 때문에 크게 문제가 되는 것은 아니라는 말이다. 지금까지 설명한 것을 고려해 본다면 적절한 크기를 정하는 데는 매우 도움이 될 것이다. 만약 초보 DBA라면 Oracle에 서 제공하는 advice를 이용하는 것도 괜찮은 방법일 듯싶다. OEM구성을 하여 Enterprise Manager를 보면 memory tab에서 shared pool 부분의 advice버튼만 클릭하면 적절한 shared pool 크기에 대한 지침을 제공하고 있다.(그 림 2를 참조하시오) 이것이 아주 정확한 척도가 되지는 못할지라도 초보 DBA에게는 매우 매력 있는 기능임에는 틀림이 없다. 이 지침은 되도록이면 peak time 이후에 이용하는 것을 권장한다.

[그림 2] Enterprise Manager의 Shared pool advice

Shared Pool Size를 적절히 줄이게 되면 free list 탐색시간의 감소로 인해 hard parsing에 의한shared pool latch의 경합을 줄이는 효과를 볼 수 있지만 ORA-4031에 러의 위험이나 상주할 수 있는 공유 객체의 수가 줄어들어 LRU list를 자주 이용하기 때문에 오히려 부가적인 hard parsing을 발생시킬 수 있음에 유의해야 한다. 이때 오르내리는 객체가 프로시저나 패키지라면 그 부하는 상당할 것이다. 이에 대비하여 DBMS_SHARED_POOL.KEEP을 이용하여 메모리에 고정시키는 방법도 매우 유용하다. Shared pool을 flush해도 내려가지 않기 때문이다. Shared pool latch가 발생하는 것을 가장 줄일 수 있는 방법은 bind변 수의 사용이나 CURSOR_SHARING Parameter를 설정하는 것이다. CURSOR_SHARING parameter는 SQL문 장을 자동으로 bind변수치환을 해주는 변수이다. 위에서 언급했던 Flush Shared pool에 대하여 잠시 설명을 하고 가면 alter system flush shared_pool;이란 명령을 통해 shared pool을 flush 시킬 수 있다. 이 작업은 단편화된 free chunk에 대해 coalesce 작업이 이루어 지기 때문에 유용하지만 NOCACHE 옵션이 없는 sequence가 있다면 예상치 못한 gap이 생길 수도 있기 때문에 유의해야 한다.

 - Library Cache Latch와 Shared Pool Latch의 관계

Shared pool을 튜닝 하는데 있어 반드시 shared pool latch 획득만이 문제가 되는 것은 아니다.  바로 shared pool latch 획득 이전에 library cache latch의 획득이 먼저 있기 때문이다. 이것을 비롯한 parsing에 대해 좀더 이해를 돕기 위해 아래의 그림 3을 참조하기 바란다.

[그림 3]  parsing시 shared pool latch와 library cache latch

 그림 3를 보면 알 수 있듯이 hard parsing이 soft parsing 보단 부하가 큰 작업 임을 알 수 있다. 또한 library cache latch와 shared pool latch 획득 시점을 미루어 보아 동시에 많은 세션이 library cache latch를 획득하려고 하게 되면 이것에 대한 병목 현상으로 shared pool latch에 대한 경합은 상대적으로 줄어들 수 있을 것이란 예상도 가능하다. 그렇다면 이렇게 shared pool latch에 영향을 주는 library cache latch에 대해서도 자세히 알아볼 것이다.

- Library Cache의 구조와 관리

Library cache에 할당 받는 Heap memory는 shared pool latch를 걸고 할당 받은 free chunk이다. 이때 Library Cache Manager(KGL)에 의해 관리되는데 이는 내부적으로 Heap Manager(KGH)를 이용하는 것이다. 이때 할당된 free chunk는 LCO(Library Cache Object)와 handle을 저장하는데 사용된다. Library Cache Memory는 크게 hash function, bucket, handle list, LCO로 구성되어 있다. 하나씩 설명을 해보면 hash function은 bucket을 결정하기 위한 연산을 수행하는 함수로 보면 된다. 객체에 따라 SQL의 경우 SQL TEXT를 그대로 numeric 값으로 변환하여 bucket을 결정하고 SQL외의 객체들은 schema, object name, DB link를 numeric 값으로 변환하여 bucket을 결정한다. Bucket의 성장은 LCO의 크기가 매우 많아져서 성장이 불가피할 때 성장하게 되는데 이때 대략 2배 크기의 소수로 확장하게 된다. 그리고 bucket의 초기 값은 _KGL_BUCKET_COUNT로 설정이 가능하다.

아래 그림 4, 5는 필자가 그린 handle과 LCO의 구조, 그리고 그것에 대한 간략한 설명이다.

[그림 4] Handle의 구조

[그림 5] LCO의 구조

[그림 6] Library cache 구조 (출처 : Advanced OWI in Oracle 10g / ㈜엑셈)

위의 그림 4, 5를 숙지하였다면 handle이 무엇이고 LCO가 무엇인지, 또 이것이 저장하는 정보에 대하여 알 수 있었을 것이라고 예상 된다. 이제 그림 6을 보면 대략적인 구조가 머리 속에 들어 올 것이다. 여기서 특징적인 것은 SQL의 경우 child table을 갖는 다는 점이고 그 child table이 저장하고 있는 실제적 자식 handle과 LCO는 익명 리스트로 관리되고 있다는 점이다. 물론 PL/SQL의 경우는 조금 다르다. 이것의 구조는 그림 7,8을 보면 좀더 명확히 알 수 있다.

[그림 7] SQL의 LCO구조

[그림 8] PL/SQL의 LCO구조

PL/SQL과 일반 SQL은 Heap Memory를 사용하는데 있어서도 차이점이 있다는 것을 금방 알 수 있을 것이다.

- Oracle의 놀라운 메커니즘 Invalidation 자동화

Oracle의 장점 중 하나인 invalidation 자동화에 대해 잠시 설명을 해 보겠다. 이에 대한 내용이 자세히 언급된 곳은 많지 않았을 것이다. 우선 그림 7을 보면 SQL LCO의 구조에 대해 잘 보여주고 있다. Parent LCO와 두 개의 child LCO가 보일 것이다. 보통 이런 방식으로 저장되는 경우는 각각 다른 schema에 같은 이름의 table을 가지고 있을 때 동일한 SQL을 query할 수 있을 것이다. 쉽게 예를 들면 scott1과 scott2란 두 user가 있는데 이들이 각각 emp라 는 table을 가지고 있고 select * from emp;라는 동일한 query를 두 user가 전송했을 때 이러한 구조로 LCO가 생성되게 되는 것이다.

이때 child LCO의 Dependency Table에 는 scott1의 경우 scott1.emp table의 LCO를 참조하고 cott2의 경우 scott2.emp table의 LCO를 참조하게 되는 것이다.

이 참조 과정에서 handle의 lock hold/wait list, pin hold/wait list를 이용하게 된다. 이때 중요한 것이 바로 lock이다. 참조하는 LCO의 lock을 shared mode로 잡아 사용하고 해제하는 것이 아니라 null mode로 유지하는 것이다. 이것이 바로 나중에 참조하는 table이 변경되었을 때 이 lock list를 없애 버림으로 인해 SQL Cursor(select * from emp;)를 따로 어떤 프로세스를 통해 invalidation한 상태로 만들지 않고 자동으로 invalidation하게 하는 것이다.

- Library cache에서 발생하는 Wait Event 소개

그럼 이제는 library cache에서 발생하는 주요 Wait Event들에 대해 알아보자. library cache에서 일어나는 자주 발생하는 Wait Event에는 3가지가 있다. 그것은 latch:library cache, library cache lock event, library cache pin event이다. 이것은 명칭에서도 알 수 있듯이 latch, lock, pin을 소유하기 위해 대기하는 event 이다. 이 동기화 자원들에 대하여 자세히 알아보자.

- Library Cache Latch

 우선 library cache latch에 대해 알아보면 이 latch는 library cache 영역을 탐색하고 관리하는 모든 작업을 보호하는 데에 그 목적이 있다. Latch의 수는 일반적으로 shared pool latch의 수 보다는 많다. 왜냐하면 CPU개수보다 큰 소수 중 가장 작은 소수로 설정되어 있기 때문이다. 이 때문에 library cache latch를 획득하려는 프로세스가 CPU개수 보다 적다면 library cache latch 자원은 손쉽게 획득하는 대신에 shared pool latch를 가지고 경합할 확률이 높을 것이고 library cache latch의 개수보다 많은 프로세스가 획득하려 한다면 library cache latch를 가지고 경합을 하느라 shared pool latch의 경합은 상대적으로 줄어들 수 있다. 그렇다면 이러한 library cache latch 경합을 가중시키는 작업엔 어떤 것이 있을까? 바로 hard parsing이 나 soft parsing이 과다한 경우와 자식 LCO가 많아 anonymous list의 탐색시간이 증가하는 경우이다. 그리고 SGA영역이 page out되는 극히 드문 경우를 예로 들어 볼 수 있다. 이에 대한 해결 책으로 PL/SQL block 내에서 자주 실행되는 SQL에 대해서는 Static SQL을 사용하면 된다.(Dynamic SQL은 안됨) LCO를 pin하여 soft parsing 없이도 cursor를 계속 재사용 할 수 있는 효과를 볼 수 있다.

그리고 SESSION_CACHED_CURSORS Parameter를 이용하여 3회 이상 수행된 SQL에 대해서는 PGA영역에 cursor의 주소 값과 SQL text를 저장하여 cursor 탐색 시 성능향상을 기대할 수 있다.(library cache latch도 획득해야 하고 soft parsing도 발생하지만 library 탐색시간이 매우 짧기 때문에 성능향상이 된다.) 하지만 application에 서 SQL 수행 시 마다 log on/off하 는 경우 이 parameter는 세션이 끊어지면 소용이 없기 때문에 성능 향상을 기대하기 어렵다. 때문에 connection pool을 함께 이용하는 것이 현명한 방법이다. 그리고 마지막으로 SGA영역의 page out의 경우는 잘 발생하지 않지만 만약을 대비해서 LOCK_SGA값을 TRUE로 하여 고정시켜 놓는 것이 좋다.

- Library Cache Lock

 Library cache lock(관련 뷰 : X$KGLLK, 10g-DBA_DDL_LOCKS, DBA_KGLLOCK)에 대해 설명하면 이것은 handle에 대해 획득하는 lock이라 볼 수 있다. 이것의 목적은 동일 object의 접근 및 수정에 대해 다른 client들로부터 예방하는 것이다. Lock은 세가지 모드를 갖게 되는데 shared, exclusive, null mode가 있다. Shared로 획득하는 경우는 parsing과 실행단계이고 exclusive는 procedure 생성이나 변경의 경우, recompile시와 table 변경의 경우가 있다. 보통 참조하는 LCO에 대해 exclusive모드와 shared모 드로 각각 획득하려는 경합으로 인해 waiting이 발생하게 된다. null mode는 보통 실행 후에 참조하는 객체에 대해 null mode로 lock을 소유하게 된다. 

- Library Cache Pin

 마지 막으로 library cache pin(관련 뷰 : X$KGLPN, 10g-DBA_KGLLOCK)에 대해 설명하면 Heap data에 pin을 꽂아 변경되지 않도록 보장하는데 그 목적이 있다. 이것은 반드시 library cache lock을 획득한 후에 획득해야 한다. 이것은 shared와 exclusive 두 mode가 지원되며 이렇게 획득하는 경우를 살펴보면, shared mode로 획득하는 경우는 Heap Data를 읽을 때 pin을 걸어 object들의 변경을 예방하며 exclusive mode로 획득하는 경우는 Heap Data를 수정할 때이다. Heap data를 수정하는 경우는 procedure recompile이 나 hard parsing 발생 시 execution plan을 세우는 과정에서 참조하는 LCO가 변경되면 안되기 때문에 pin을 걸어 보호한다.

 이때 발생하는 library cache lock이나 library cache pin은 V$SESSION_WAIT의 P1, P2, P3 column과 X$KGLOB View를 이용하여 object 정보를 구할 수 있다. P1=handle address, P2=lock address, P3=mode*100+namespace (lock mode : 1=null, 2=shared, 3=exclusive)이기 때문에 V$SESSION_WAIT을 조회하여 P1값을 구한 후 P1과 X$KGLOB의 kglhdadr column과 비교하여 kglnaobj column을 query해 서 object의 이름을 구할 수 있다.

- 맺음말

위의 library caches에 관한 내용들을 종합적으로 정리해서 결론을 내려보면 Wait Event를 통해 그 Event가 왜 발생하였는가를 인지할 수 있다면 정확한 진단 역시 가능함을 알 수 있다. 그리고 덧붙여 내부적인 Event의 발생과정을 아는 것이 튜닝을 하게 될 때 넓은 시야를 가질 수 있도록 도와주고 좀더 효율적이고 정확한 튜닝을 할 수 있는 계기가 되리라 확신한다.

참고자료

OWI를 활용한 오라클 진단 & 튜닝 / ㈜엑셈 역

Advanced Oracle Wait Interface in 10g / 저자 조동욱 / ㈜엑셈

Oracle 8i Internal Services for Waits, Latches, Locks, and Memory / Steve Adams / O’Reilly

Manual

Oracle Database Concepts 10g R2

Oracle Database Reference 10g R2

Oracle Database Performance Tuning Guide 10g R2

mutex나 semaphore는 시스템에서 동기화를 위해서 제공하는 자원(리소스)이고, latch와 lock은 db에서 사

용하는 동기화 기법으로 정리됩니다. latch와 lock을 mutex 또는 semaphore로 구현을 할 수 있습니다.


첫번째 mutex를 보면, 상호배제를 구현하기 위한 동시성 제어 단위이다.

mutex는 단지 어떤 리소스에 대한 권한을 획득/비획득 이라는 2개의 상태로 나뉘어 진다.

가장 쉬운 예로 pthread_mutex_lock / unlock 함수를 들 수 있다.

둘째로 latch라는 개념은 (물론 운영체제와 같은 다른 C/S field에서는 다른 개념으로 사용할 수 있다)

이렇게 표현된다.

"물리적인 대상의 리소스에 대해 짧은 시간동안 권한(읽기/쓰기)를 획득하여 작업하고자 할 경우 사용되는 동시성 제어 단위"

DBMS 입장에서 설명을 하자면, 물리적이라는 의미는 대상이 특정 버퍼 블럭이나, 낮은 수준의 객체에 대한 것이라고 해석될 수 있다.

또한, 짧은 시간동안이라는 것은 상대적인 개념으로서 뒤에 설명될 lock보다 짧다는 것인데, 이 latch의 목적이 가능한한 빠른 연산을 목표로 하고 있기 때문에 짧다라는 표현을 쓴다.

그리고, latch는 mutex와는 달리 모드를 가질 수 있다.

대표적으로 읽기 모드와 쓰기 모드인데, 본인이 알고 있는 범위내에서는 DBMS에서의 latch는 이 두가지의 모드밖에 보지 못했다.

마지막으로 가장 중요한 또 한가지 점은 이 latch는 dead lock이 발생하지 않는다고 가정한다는 것이다.

물론 잘못된 순서로 객체에 latch를 잡는 경우에는 dead lock이 발생할 수 있지만, 이러한 상황은 개발자에 의한 버그이지 원래 latch의 목적과는 상반된다는 것이다.

따라서, latch에서는 dead lock 상황에 대한 해결책도 가지지 않는다. 일반적으로 이러한 목적을 DBMS내부에서 부합하기 위해서 latch의 대상이 되는 객체는 엄격한 순서로 연산이 이루어 지게 되어 있다.

특히, 인덱스나 레코드 페이지에 대한 순서가 어긋나면 DBMS가 정지하는 상황이 발생하고, 개발자는 혹독한 (기초도 모르는!!!) 시련을 겪게 된다.

세번째로, lock은 DBMS 에서 이렇게 표현된다.

"논리적인 대상에 대한 작업시 사용되는 동시성 제어 단위로서 비교적 긴 시간동안 발생하는 연산" 정도로 말할 수 있겠다.

우선 이 논리적인 대상이라는 의미는 단순이 메모리 대상 객체라기 보다는 테이블 혹은 레코드라는 보다 추상적인 의미가 강하고, 또한 lock이 잡히는 길이도 사용자에 의해서 상당히 긴 시간동안 발생할 수 있다.

또한, mutex는 1개, latch는 2개 정도의 모드를 가지는 반면, lock의 경우에는 S,X,IS,IX,SIX,U 등의 다양한 모드로 연산이 발생할 수 있다.

그리고, 이 lock은 dead lock이 발생하는 것을 허용하고, 또한 이러한 dead lock에 대한 내부적인 방지책을 가지고 있다.

가만히 생각해 보면, 너무나도 당연한 이야기인데, 사용자는 어떤 논리적 대상에 대한 lock을 걸 때 순서를 상관하지 않기 때문에 발생한다.

Oracle에서 Latch 가 성능 저하의 원인이 아닙니다. 증상일 뿐이죠.

예를 들어... Library cache 관련된 Latch가 나타날 경우... APP쪽에서 none-bind 를 사용한 SQL이 그만큼 많다거나 그런 원인이구요.

Buffer Cache Chain Latch 라면 뭔가 비효율적인 인덱스를 타거나 대량의 Full Scan을 하는 악성 SQL이 있다는 뜻입니다.

DBMS의 가장 주된 기능 중에 하나는 동일 자원에 대한 동시 액세스를 관리하는 것이며, 이를 위해 오라클이 사용하는 대표적인 제어 구조가 Enqueue와 Latch이다. Enqueue와Latch는 모두 특정 자원에 대한 접근을 serialize하는 것이 목적이라는 점에서는 같은 Lock의 일종이지만 관리방식이나 용도에서 차이가 있다.

Enqueue는 이름에서 보듯 Queue를 통해 관리된다. 대상 자원에 대한 Owner, Waiter, Converter Queue를 관리하면서 먼저 요청한 순서대로 Lock을 획득하도록 하는 구조이며, Exclusive 모드뿐 아니라 다양한 수준의 공유를 허용한다. 대표적인 것이 테이블 데이터를 Update할 때 사용되는 TM, TX Enqueue이다.

반면에, Latch는 Enqueue에 비해 훨씬 단순한 구조로서 매우 짧은 시간 내에 획득되고 해제된다. Queue를 통해 관리되지 않으므로 먼저 Request한 프로세스가 먼저 latch를 획득한다는 보장이 없으며, 대부분의 경우 Exclusive 모드로만 획득된다. Latch는 주로 SGA의 특정 메모리 구조체에 대한 액세스(library cache latch, cache buffers chains latch) 혹은 메모리 할당 시(shared pool latch) 사용되거나 오라클의 중요한 코드가 동시에 수행되지 않도록 하기 위한 용도로(redo writing latch) 사용된다. Latch는 Enqueue보다는 하위 level에서 Locking 자체의 부하를 최소화하며 작동하는 제어 메커니즘이라고 할 수 있으며, 실제로 Enqueue 역시 내부적으로는 Latch (enqueues, enqueue hash chains latch )에 의해 운영된다는 점을 생각하면 둘 사이의 차이를 쉽게 이해할 수 있을 것이다.

■ Enqueue

Enqueue 정보는 내부적으로 Enqueue Resource 배열과 Enqueue Lock 배열에 저장된다. 특정 자원에 대한 Lock이 요청되면 대상을 하나의 Resource로 정의하여 할당하고 그Resource에 대해 관련 Lock 정보를 Owner, Waiter, Converter가운데 하나로서 Link시키는 방식으로 운영되며, 이러한 정보는V$RESOURCE 와 V$LOCK View를 통해 조회해 볼 수 있다. V$RESOURCE와V$LOCK은1:M관계로 하나의 Resource에 대하여 여러 건의 Lock 레코드가 Owner (LMODE>0, REQUEST=0), Waiter (LMODE=0 ,REQUEST>0), Converter (LMODE>0, REQUEST>0) 중 하나로서 대응된다. 

Enqueue Wait이 발생하는 것은 다른 세션이 이미 나보다 먼저 해당 자원에 대한 Lock을 잡고 있으므로 인해 내가 원하는 모드로 Lock을 할당 받을 수 없기 때문이다. 자신이 필요로 하는 Lock의 획득에 실패한 세션은 Owner가 작업을 완료하고 자신을 깨워줄 때까지(세마포어를 포스트해줄 때까지) Waiter 혹은 Converter Queue에서 대기하게 되며, 기다려도 소식이 없으면3초 간격으로 timeout에 의해 일어나 혹시 Deadlock 상황이 아닌지 점검해 본 후 다시 Sleep에 빠져들기를 반복하게 된다. 튜닝관련 자료를 보다 보면 가끔 Enqueue에 대한 Wait이 많은 경우에 Enqueue_resource 나 Enqueue_lock 파라미터를 증가시켜 주어야 한다는 가이드를 보게 되는 경우가 있는데 이 파라미터들은 Enqueue resource 와 lock 배열의 크기를 늘려줄 뿐 특정 Enqueue 자원에 대한 동시 경합을 해소시키는 것과는 상관이 없다. Enqueue Wait를 해소하기 위한 구체적인 방법은 Enqueue type에 따라 달라지지만 결국은 Enqueue를 불필요하게 요청하는 경우가 없는지를 살펴 Enqueue에 대한 요청을 최소화하고 Enqueue를 점유하는 시간을 최대한 단축시키는 것이다. TX Enqueue에 대한 Wait은 대상 자원에 대한 Lock을 소유하고 있는 세션과 그 세션이 수행 중인 SQL을 찾아 트랜잭션이 장시간 지속되고 있는 이유가 무엇인지 애플리케이션 측면에서 조사해야 하며, SQ enqueue는 Sequence값 할당 시 발생하는 경합이므로 cache값을 늘려줌으로써 완화시킨다거나 ST Enqueue의 경합이 존재할 경우에는 Locally managed tablespace를 사용하거나 Initial, Next 등의 extent 크기를 적당한 값으로 조정하여 실시간 공간할당을 감소시켜주는 등의 방법들이 Enqueue Wait에 대처하는 대표적인 사례이다. 지난 호에서 소개한 Session Waiter 스크립트는 Enqueue Wait 이벤트에 대해서 Enqueue type과 모드를 함께 표시하여 주도록 하고 있으며, 참고로 Enqueue type별 누적 Wait현황을 확인하고자 하면 아래 SQL을 수행하면 된다.

SELECT q.ksqsttyp type, q.ksqstget gets, q.ksqstwat waits, round(q.ksqstwat/q.ksqstget,3) waitratio

FROM sys.x$ksqst q

WHERE q.inst_id = userenv(‘Instance’)

AND q.ksqstget > 0

ORDER BY waits DESC

/

■ Latch

오라클 운영 시에 하위레벨에서 내부적으로 처리되는 다양한 조작들이 latch의 관할 하에 수행되는데 V$LATCHNAME을 조회해보면 (9i기준으로) 239 종류나 되는 Latch가 존재하는 것을 확인할 수 있다. 이 가운데 우리가 자주 접하게 되는 latch는 다음과 같은 정도이며 각Latch의 기능은 관련 SGA별 Wait를 다룰 때 간단하게나마 소개하도록 하겠다. 

▷ Willing to wait 모드와 No-wait 모드

Latch 획득 방식은 No-wait 과 Willing to wait의 두 가지 모드로 구분할 수 있다.

Willing to wait 모드는 Latch의 획득에 실패하면 좀더 시간을 끌면서 해당 Latch를 잡을 때까지 재시도를 해보는 방식을 말한다. 일차적으로는 CPU를 놓지 않고 정해진 횟수(_SPIN_COUNT(기본값 2000) 파라미터 값 만큼 반복(스핀)하며 latch 획득을 시도한다)만큼 Spinning을 한 후 재시도를 해보다가 그래도 실패하면 CPU를 놓고 Sleep하다가 timeout되어 재시도하는 작업을 반복하면서 Latch의 획득을 노력하게 된다. Latch가 sleep에 들어가게 되면 ‘latch free’ wait event 대기가 시작된다. sleep의 지속 시간은 sleep 횟수가 늘어갈수록 점점 길어지게 되는데, 따라서 V$LATCH의Gets 와 Sleeps의 비율과 함께 Sleep1~sleep4 항목에서 몇 차 Sleep까지 발생했는지 여부도 각 Latch Wait의 심각성을 판단하는 요소 가운데 하나가 된다.

No-wait 모드는 Willing to wait과는 달리 더 이상 미련을 두지 않고 해당 Latch에 대한 획득을 포기하는 것이다. No-wait 모드가 사용되는 경우는 두 가지가 있는데, 하나는 동일한 기능을 하는 Latch가 여러 개 존재하여 그 중에 하나만 획득하면 충분하여서 특정 Latch에 미련을 가질 필요가 없는 경우이다. 물론, 이 때에도 같은 기능의 모든 Latch에 대한 시도가 실패로 끝날 경우에는 Willing to wait 모드로 요청을 할 것이다. No-wait 모드가 사용되는 다른 한가지 경우는 dead lock을 피하기 위해서 이다. 오라클은 기본적으로 latch dead lock 상황을 피하기 위하여 모든 Latch에 level을 부여하여 정해진 순서를 따라서만 Latch를 획득하도록 하고 있는데, 필요에 의해 이 규칙을 어기고 Latch를 획득하고자 할 경우 일단 No-wait 모드로 시도를 해보는 것이다. 다행히 Latch를 잡으면 좋은 것이고 비록 latch를 잡을 수 없더라도 무한정 기다림으로써 dead lock 상태에 빠지는 일은 피할 수 있는 것이다. No-wait 모드의 Latch작업에서는 당연히 Latch 관련 wait이 발생하지 않으며, redo copy latch를 제외하고는 Willing to wait 모드로 Latch를 획득하는 경우가 훨씬 많다.

▷ Parent latch와Child latch

Latch 가운데에는 동일 기능을 하는 Child latch들의 set으로 운영되는 Latch도 있으며 하나의 Latch로만 운영되는 Latch도 있다. 전자의 대표적인 예로는 cache buffers chains (버퍼 캐시 블록 들을 같은 이름의 다수의 Latch가 나누어 담당)가 있으며, 후자의 예로는 shared pool latch (shared pool내에서 메모리 할당을 위해 획득해야 하는 Latch로 시스템에 하나만 존재)가 있다. 이와 같은 Latch 관련 통계 정보는 Parent latch 와 Child latch의 개념으로 관리가 되는데 Latch set에서 개별 Child latch에 대한 통계정보는 V$LATCH_CHILDREN View를 통해 조회할 수 있으며, 단일 Latch 혹은 Latch set의 마스터 Latch (parent)에 대한 통계정보는 V$LATCH_PARENT View를 통해 조회할 수 있다.

Direct path read

Basic Info

direct path read 이벤트대기는 Parallel Query 수행시 슬레이브 세션(Slave Session)이 수행하는 direct path I/O 에 의해 발생한다. direct path I/O 는 SGA 내의 버퍼캐쉬를 거치지 않고 세션의 PGA 로 직접 블록을 읽어 들이는 것으로 direct read 는 I/O 방식(synchronous I/O, asynchronous I/O)에 상관없이 수행될 수 있으나, 하드웨어 플랫폼과 DISK_ASYNCH_IO 파라미터에 영향을 받는다. Direct read I/O 는 일반적으로 디스크에 위치한 임시(temporary) 세그먼트를 액세스 하는 동안 사용된다. 이러한 작업은 정렬(sort), 병렬조회(parallel query) 및 해쉬조인(hash join)시에 발생한다.

슬레이브 세션이 direct path read 를 수행하는 동안 코디네이터 세션(Coordinator Session)은 슬레이브 세션으로부터 응답이 오기를 기다리며 PX Deq: Excute Reply 이벤트를 대기하는 것으로 관찰된다. Parallel Query 수행시 발생하는 direct path read 대기는 필연적인 것이다. 만일 direct path read 이벤트의 대기시간이 지나치게 높게 나온다면 다음과 같은 관점에서 튜닝포인트를 찾아보아야 한다.

이 이벤트의 대기횟수와 대기시간은 오해의 소지가 있을 수 있다. 만일 비동기식 I/O가 사용되지 않았다면, 세션은 I/O 가 완료될 때까지 대기한다. 하지만, I/O 요청이 시작된 시점부터 대기시간을 계산하지 않고, I/O 요청이 완료된 후 데이터를 액세스 할 때 direct path read 대기이벤트를 기다리게 된다. 따라서 대기시간은 상당히 짧게 나타난다.

만일 비동기식 I/O 가 사용가능하고 현재 사용 중이라면, 세션은 다수의 direct read 요청을 한 후 , PGA 내부에 캐쉬 된 블록들에 대한 처리를 진행한다. 세션이 PGA내부에 캐쉬 된 블록들이 없어서 처리를 진행하지 못하는 시점에 direct path read 대기이벤트가 발생된다. 따라서, 읽기 요청횟수는 대기횟수와 동일하지 않다. 이러한 불일치 때문에, V$SYSTEM_EVENT 와 V$SESSION_EVENT 뷰에서 보여지는 direct path read 대기이벤트의 수치는 신뢰할 수 없다.

LOB 세그먼트를 읽을 때 발생되는 direct path read 대기는, 오라클 8.1.7부터는 direct path read(lob) 대기이벤트로 별도로 구분된다. 보통 direct path read 대기이벤트는 임시(temporary) 또는 일반 테이블스페이스로부터 direct read 오퍼레이션을 수행하는 SQL문에 의해 발생된다. ORDER BY, GROUP BY, UNION, DISTINCT, ROLLUP과 같이 정렬이 필요한 함수를 수행하는 SQL문은, PGA 내부에서 허용할 수 있는 것 보다 더 큰 데이터를 정렬해야 할 때 소트 런(sort run)들을 임시 테이블스페이스에 기록한다. 임시 테이블스페이스의 소트 런들은 최종 결과를 만들기 위해 순차적으로 PGA로 읽혀지고 머지 된다. 소트 런들을 PGA로 읽어 들이는 동안 해당 세션은 direct path read 대기이벤트를 대기한다. Hint또는 옵티마이저의 판단에 의해 MERGE 조인을 수행하는 SQL문 또한 정렬작업이 필요하다.

힌트(hint)또는 옵티마이저의 판단에 의해HASH 조인을 수행하는 SQL문은 PGA 내부에서 허용할 수 없는 크기의 해쉬 파티션들을 임시 테이블스페이스에 기록한다. 임시 테이블스페이스에 기록된 해쉬 파티션은 SQL문의 조건에 맞는 레코드를 찾기 위해 다시 PGA로 읽어 들여진다. 해쉬 파티션을 PGA로 읽어 들이는 동안 해당 세션은 direct path read 대기이벤트를 대기한다.

V$SESSION_EVENT 뷰의 TOTAL_WAITS 또는 TIME_WAITED 값을 이용하여 direct path read 대기이벤트를 평가하지 않는 것이 좋다. 대신에 아래의 쿼리를 사용하여 대량의 direct read 를 수행하고 있는 세션을 찾아낼 수 있다. physical reads direct 는 parent세션에 의해 시작된 direct reads와 parent세션이 관리하는 슬레이브 프로세스에 의해 발생된 모든 direct reads 의 합으로 구성된다.

select a.name, b.sid, b.value, 

       round((sysdate - c.logon_time) * 24) hours_connected

from   v$statname a, v$sesstat b, v$session c

where  b.sid        = c.sid

and    a.statistic# = b.statistic#

and    b.value      > 0

and    a.name       = 'physical reads direct'

order by b.value;

NAME                            SID      VALUE   HOURS_CONNECTED

-------------------------     ----     ---------- ------------------------

physical reads direct        2               41             980

physical reads direct        4               41             980

physical reads direct        5        445186             980

대량의 direct reads 를 발생시키는 세션을 찾는 것 이외에, 세션이 데이터를 읽어오는 위치 (임시 테이블스페이스, 데이터파일 등), 대기를 발생시키는 SQL문을 파악하여야 한다. 다음의 쿼리는 이것에 대한 답을 줄 수 있다. 임시 테이블스페이스에서 데이터를 읽는 세션은 소트 또는 해쉬 세그먼트를 읽고 있을 것이다. 데이터파일로부터 데이터를 읽고 있는 세션은 병렬 쿼리 슬레이브(parallel query slave)이다.

select a.event,

       a.sid, 

       c.sql_hash_value hash_value,

       decode(d.ktssosegt,

              1,'SORT', 2,'HASH',    3,'DATA',

              4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',

              null) as segment_type,

       b.tablespace_name,

       b.file_name

from   v$session_wait a, dba_data_files b, v$session c, x$ktsso d

where  c.saddr      = d.ktssoses(+)

and    c.serial#    = d.ktssosno(+)

and    d.inst_id(+) = userenv('instance')

and    a.sid        = c.sid

and    a.p1         = b.file_id

and    a.event      = 'direct path read'

union all

select a.event,

       a.sid,

       d.sql_hash_value hash_value,

       decode(e.ktssosegt,

              1,'SORT', 2,'HASH',    3,'DATA',

              4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',

              null) as segment_type,

       b.tablespace_name,

       b.file_name

from   v$session_wait a, dba_temp_files b, v$parameter c, 

       v$session d, x$ktsso e

where  d.saddr      = e.ktssoses(+)

and    d.serial#    = e.ktssosno(+)

and    e.inst_id(+) = userenv('instance')

and    a.sid        = d.sid

and    b.file_id    = a.p1 - c.value

and    c.name       = 'db_files'

and    a.event      = 'direct path read'

order by 1,2;

EVENT                SID HASH_VALUE  SEGMENT  TABLESPACE_N   FILE_NAME

------------------   ---- ----------------  ----------- ------------------  -----------------

direct path read     8     511952958      SORT      TEMP_BATCH   temp_batch_01.dbf

direct path read     9    3138787393                    ORDERS         orders_01.dbf

direct path read    11   3138787393                    ORDERS         orders_01.dbf

direct path read    12   3138787393                    ORDERS         orders_01.dbf

direct path read    14   3138787393                    ORDERS         orders_01.dbf

임시 테이블스페이스로부터 소트 세그먼트를 읽고 있는 세션은, SORT_AREA_SIZE (또는 오라클 9i에서 PGA_AGGREGATE_TARGET 을 사용하는 경우 work area size )가 메모리 소트를 수행할 만큼 충분치 않다는 것을 의미한다. 하지만 이것은 문제가 되지 않는다. 모든 소트가 메모리에서만 수행되는 것은 현실적으로 불가능하기 때문이다. 하지만, 많은 멀티패스( multi pass ) 소트는 가능한 방지해야 한다. 왜냐하면, 멀티패스 소트는 임시 테이블스페이스에 대한 상당한 I/O 를 유발하며 매우 느리기 때문이다. SQL문이 멀티패스 소트를 하는지 어떻게 확인할 수 있을까? 오라클 9i이전 버전에서는 쉽지 않은 일이다. 10032 트레이스 이벤트를 설정한 후 트레이스파일을 확인해야만 한다. 하지만, 오라클 9i부터는, 소트를 수행하는 SQL문의 hash value를 이용하여 V$SQL_WORKAREA 또는 V$SQL_WORKAREA_ACTIVE 뷰를 조회해 보는 것만으로도 확인이 가능하다. 소트에 대한 더욱 자세한 사항은 International Oracle Users Group (IOUG) 2004 conference proceedings (www.ioug.org) 의 “If Your Memory Serves You Right” 기술백서를 참고하라.

이러한 경우, 튜닝의 목표는 디스크 소트 횟수를 최소화하는 것이다. SORT_AREA_SIZE (또는 PGA_AGGREGATE_TARGET )을 크기를 증가함으로써 디스크 소트 횟수를 줄일 수 있다. 하지만, 이것은 극단적으로 SORT_AREA_SIZE 가 작게 설정된 경우가 아니라면 근본적인 해결방법이라고는 할 수 없다. 먼저 어플리케이션에서 소트가 반드시 필요한지를 확인해야 한다. 어플리케이션들은 DISTINCT와 UNION 함수를 남용하여 사용하는 경향이 있다. 가능하다면 UNION보다는 UNION ALL을 사용하고, SORT MERGE 보다는 HASH 조인, HASH 조인보다는 NESTED LOOPS조인을 사용하도록 해야 한다. 또한 옵티마이저가 드라이빙 테이블을 올바르게 선택했는지도 확인할 필요가 있다. 결합 인덱스의 컬럼을 자주 쓰이는 ORDER BY절과 잘 맞게 설정해 놓으면 소트를 피할 수 도 있다. 오라클 9i라면 PGA_AGGREGATE_TARGET 을 사용하여 SQL work area 를 자동적으로 할당 받아 사용할 수 있도록 설정하는 것도 고려해 볼 만 하다. 통계적으로 보았을 때, 자동적으로 메모리관리를 하도록 하면 메모리소트의 비율을 더 높여준다.

세그먼트를 읽고 있는 세션을 발견했다면, HASH_AREA_SIZE (오라클 9i 에서 PGA_AGGREGATE_TARGET 을 사용하고 있는 경우는 work area size )가 작아서 메모리에 해쉬 테이블을 수용하지 못하는 경우이다. 해결방법은 이미 언급됐던 것과 유사하다. 만약 HASH_AREA_SIZE 가 너무 작은 경우만 아니라면 HASH_AREA_SIZE (또는 PGA_AGGREGATE_TARGET )를 조정하기 전에 먼저 어플리케이션과 SQL문을 튜닝해야 한다.

만약에 병렬 쿼리(parallel query) 슬레이브에서 direct reads가 발생한다면, 병렬 스캔(parallel scan)이 parent SQL문에 적합한지와 슬레이브의 개수가 적당한지 확인해야 한다. 또한 쿼리 슬레이브들이 시스템의 CPU와 디스크 자원을 모두 써버리지는 않는지도 확인해야 한다. Parent SQL문의 hash value와 쿼리 슬레이브들이 수행하는 SQL의 hash value가 동일하지 않기 때문에 parent SQL문을 찾는 것은 쉽지 않은 일이다. 오라클 8.1.5에서 V$PX_SESSION 뷰가 소개되기 전까지는 더욱 힘든 일이었다. 아래의 2가지 예제는, 병렬 쿼리가 수행될 때 parent SQL문장을 찾는 방법을 오라클 8.1.5이전 버전과 이후 버전에 대해서 각각 설명한다.

--오라클 8.1.5이전 버전

--Note: 아래의 쿼리는 SYS 유저에 의해 수행되는 병렬 쿼리 문장들을 구분할 수 없다

--왜냐하면, SYS 유저들은 동일한 AUDSID를 공유하기 때문이다.

select decode(ownerid,2147483644,'PARENT','CHILD') stmt_level,

         audsid,

         sid,

         serial#,

         username,

         osuser,

         process,

         sql_hash_value hash_value,

         sql_address

from  v$session

where  type <> 'BACKGROUND'

and    audsid in (select audsid 

                        from   v$session

                       group by audsid

                      having count(*) > 1)

order by audsid, stmt_level desc, sid, username, osuser;

STMT_L   AUDSID  SID  SERIAL# USERNAME OSUSER  PROCESS HASH_VALUE SQL_ADDR

------       --------   ----  ----------  ------------- ---------- ----------- ---------------- --------

PARENT  3086501   20     779        INTREPID  cdh8455  16537     3663187692   A0938E54

CHILD    3086501   12     841        INTREPID  cdh8455  16544     817802256     A092E1CC

CHILD    3086501   14    2241       INTREPID  cdh8455  16546     817802256     A092E1CC

CHILD    3086501   17    3617       INTREPID  cdh8455  16540     817802256     A092E1CC

CHILD    3086501   21     370        INTREPID  cdh8455  16542     817802256     A092E1CC

아래의 쿼리는 오라클 8.1.5이상 버전에서 사용된다.

select decode(a.qcserial#, null, 'PARENT', 'CHILD') stmt_level,

         a.sid,

         a.serial#,

         b.username,

         b.osuser,

         b.sql_hash_value,

         b.sql_address,

         a.degree,

         a.req_degree

  from   v$px_session a, v$session b

where  a.sid = b.sid

 order by a.qcsid, stmt_level desc;

STMT_L   SID SERIAL# USERNAME OSUSER  HASH_VALUE SQL_ADDR        DEG REQ_DEG

---------   ----- --------  -------------  ---------     -------------    --------------  ------ -------

PARENT    20     779     INTREPID   cdh8455    3663187692    A0938E54

CHILD      17   3617     INTREPID   cdh8455      817802256    A092E1CC       4       4

CHILD      21     370     INTREPID   cdh8455      817802256    A092E1CC      4       4

CHILD      12     841     INTREPID   cdh8455      817802256    A092E1CC      4       4

CHILD      14   2241     INTREPID   cdh8455      817802256    A092E1CC      4       4

Parameter & Wait Time

Wait Parameters

Direct path read 대기이벤트의 대기 파라미터는 다음과 같다.

• P1 : Absolute File#
• P2 : Starting Block#
• P3 : 블록수

Wait Time

I/O관련 이벤트이므로 타임아웃이 발생하지 않으며, 세션은 I/O가 완료될 때까지 대기한다

Check Point & Solution

Parallel Query의 성능을 높인다

Parallel Query를 수행하는 과정에서의 direct path read 대기는 필연적인 것으로 이 대기 자체를 튜닝하는 것은 불가능하다. 오히려 SQL 튜닝을 통해 Parallel Query 자체의 성능을 개선시키는 것이 옳은 접근 방법이다. 시스템의 용량에 비해 불필요하게 Parallel Query를 수행하는 것은 오히려 성능을 저하시키는 요인이 된다. 한가지 기억할 것은 데이터 파일에 대해 직접 읽기 작업을 수행하기 전에 읽기의 대상이 되는 객체의 더티 블록이 데이터 파일에 기록이 되어야 한다는 것이다. 즉 체크포인트가 발생하게 된다. 이 작업을 수행하는 동안 코디네이터 세션은 enq: TC - contention 대기를 겪게 된다.

I/O 시스템의 성능을 높인다.

I/O 개요

I/O는 오라클에서 가장 중요한 자원이다. 오라클이 제공하는 모든 기능은 결국 어떻게 하면 데이터를 파일에 잘 쓰고 잘 읽느냐에 관한 것이다. 따라서 자연스럽게 오라클 성능문제 중 많은 수가 I/O와 관련이 있다. I/O와 관련된 오라클의 성능문제를 이해하려면 오라클에서의 I/O 작업은 여러 개의 레이어(Layer)로 이루어져 있다는 사실을 이해해야 한다. 오라클에서의 I/O 작업 레이어를 다음과 같이 나누어 설명해 보기로 하자.

1. 어플리케이션 레이어 : select/insert/update/delete/truncate...

2. 오라클 메모리 레이어: Buffer cache | PGA

3. 오라클 세그먼트 레이어: Datafile, tempfile, Tablespace, Segment

4. OS/디바이스 레이어: Asynch I/O, Direct I/O, Raw device, RAID, ...

I/O 성능문제의 원인을 파악하는 것과 해결책을 찾는 과정은 항상 1 -> 2 -> 3 -> 4 의 순서를 따라야 한다. 논리적인 면에서뿐만 아니라 경제적인 면에서 더욱 그렇다. 각각의 레이어에 대해 필요한 사전지식과 메커니즘에 대해서 논의해보자.

어플리케이션 레이어(Application Layer)

어플리케이션을 효과적으로 구현하여, 불필요한 I/O를 최소화해야 한다. 비효율적인 어플리케이션을 그대로 두고 시스템을 튜닝한다는 것은 거의 불가능하다. 오라클은 I/O를 효율적으로 처리하는 다양한 기법들을 제공한다. Parallel Query, Parallel DML, Nologging, Direct load, Direct read 등이 대표적인 예이다. 오라클의 버전이 올라갈수록 더욱 효과적으로 작업을 처리할 수 있는 강력한 SQL 기능이 추가된다. 해석함수( Analytical Function )가 대표적인 경우인데, 이 함수를 잘 이용하면 I/O를 크게 줄이면서 다양한 집계기능을 구현할 수 있다. 이러한 최신의 SQL 문장을 사용하여 I/O부하를 줄이도록 해야 한다. 데이터의 성격에 따라 클러스터(Cluster), IOT, 파티셔닝(Partitioning), 비트맵 인덱스(Bitmap Index)등의 기능을 적절히 사용해서 I/O를 효과적으로 사용하는 것 또한 어플리케이션의 몫이다.

오라클 메모리 레이어(Oracle Memory Layer)

버퍼 캐시는 오라클 I/O 관리의 핵심이다. 자주 사용하는 블록들을 메모리에 캐시함으로써 물리적인 I/O를 줄일 수 있다. 오라클의 버전이 올라감에 따라 버퍼 캐시를 처리하는 알고리즘이 끊임없이 개선되고, 더불어 새로운 관리 방법들이 제공된다. 버퍼 캐시를 효과적으로 사용하게 되면 물리적 I/O가 줄어들고 자연스럽게 I/O 성능문제가 해결되는 경우가 많다. 오라클이 제공하는 기능들에는 다음과 같은 것들이 있다.

• 첫째, Touch count 기반의 효율적인 LRU 알고리즘을 제공한다.
• 둘째, Buffer Pinning 기법을 통해 불필요한 래치 경합을 줄이고, 현재의 읽기 작업에 사용될 확률이 높은 블록들을 메모리에서 밀려나지 않게끔 한다.
• 셋째, 다중 버퍼 풀( Multiple buffer pool ) 기능을 이용하면 휘발성 블록과 메모리 상주 블록을 구분해서 효과적으로 관리할 수 있다. 시스템에서 보편적으로 자주 사용되는 객체들은 Default 버퍼를 사용한다. 비교적 주기적으로 사용되는 작은 크기의 객체들은 Keep 버퍼에 상주시키는 것이 좋다. 반대로 아주 적은 빈도로 사용되는 큰 크기의 객체들은 Recycle 버퍼를 사용함으로써 중요한 메모리 영역을 낭비하는 일을 방지할 수 있다.
• 넷째, 오라클 9i부터는 블록 크기를 2K ~ 32K까지 사용 가능하다. 객체의 속성을 고려하여 큰 사이즈의 블록을 사용하는 것이 유리한 경우(가령 로우의 크기가 크고 풀테이블스캔으로 데이터를 읽는 경우가 많은 경우)에는 큰 크기의 블록을 사용함으로써 성능 개선 효과를 얻을 수 있다.
• 다섯째, 메모리에 올릴 필요가 없는 대용량의 데이터를 처리할 때는 버퍼 캐시를 우회하는 방법을 사용할 수 있다. 이러한 기능을 direct path I/O 라고 부른다. Direct path I/O 를 사용하면 SGA영역을 거치지 않기 때문에 메모리 공유를 위한 동기화 메커니즘이 불필요하고 그만큼 성능이 개선된다. Direct path I/O 의 반대말은 conventional path I/O 로 SGA 즉, 버퍼 캐시를 경유하는 것을 말한다. 오라클은 영구 세그먼트(Permanent Segment)와 임시 세그먼트(Temporary Segment) 모두에 대해 direct path I/O 를 지원한다. Parallel Query나 Parallel DML등은 영구 세그먼트에 대해 direct path I/O 를 사용한다. 정렬 작업은 임시 세그먼트에 대해 direct path I/O 를 사용한다. LOB 세그먼트는 약간 독특한 처리 메커니즘을 가지고 있는데, LOB 컬럼 생성시 부여하는 스토리지(Storage) 속성에 따라 direct path I/O 를 사용할 수도 있고, conventional path I/O 를 사용할 수도 있다.

오라클 세그먼트 레이어(Oracle Segment Layer)

일반적인 데이터들은 데이터파일에 저장된다. 임시 테이블스페이스(Temporary tablespace)를 사용할 경우, 기본적으로 데이터 파일이 아닌 임시파일(Temp file)에 데이터를 저장한다. 오라클 7.3 이전 버전에서는 정렬 작업을 위해 영구 테이블스페이스(Permanent Tablespace)를 사용해야 했는데 이 경우 과도한 익스텐트의 할당과 해제로 인해 많은 성능문제가 야기되며, 특히 ST 락 경합으로 인한 성능 저하 현상이 생기는 경우가 많았다. 오라클 8i부터 사용가능한 임시 테이블스페이스(Temporary tablespace)와 임시파일(Tempfile) 기능을 사용하면 ST 락 경합은 더 이상 문제가 되지 않는다. 오라클 8i부터 제공되는 LMT( Locally Managed Tablespace )와 오라클 9i부터 제공되는 ASSM( Automatic Segment Space Management )을 사용하면 익스텐트 및 세그먼트 공간의 부적절한 관리에서 오는 성능문제를 대부분 해결할 수 있다. 대용량의 테이블은 파티션(Partition)을 이용해서 관리하는 것이 유리한 경우가 많다. 관리적인 측면에서뿐만 아니라, 대량의 데이터를 처리하는 경우 원하는 범위만을 스캔하는 것이 가능하므로 필요한 I/O 범위를 줄이는 효과가 있다.

OS/디바이스 레이어(Device Layer)

오라클은 가능하면 비동기 I/O(Asynchronous IO)를 사용할 것을 권장한다. 비동기 I/O는 읽기 작업뿐만 아니라 특히 DBWR이나 LGWR등 쓰기작업을 수행하는 프로세스가 비동기적으로 작업을 처리할 수 있도록 해줌으로써 I/O 작업의 속도를 전반적으로 개선시켜준다. 불행히도 많은 OS들에서 진정한 비동기 I/O는 로디바이스(Raw device)에서만 사용한 것으로 알려져있다. 비동기 I/O를 사용하는 것이 불가능하다면 OS차원에서 Direct I/O를 사용하는 것이 바람직하다. Direct I/O를 사용하는 경우 OS의 버퍼 캐시를 우회함으로써 불필요한 I/O 작업을 최소화한다. DBWR 프로세스를 복수개로 사용하는 것 또한 방법이 될 수 있다. 컨트롤 파일(control file)의 개수나 리두로그 파일의 개수가 불필요하게 많다면 복구가 가능한 최소한만큼만 유지하는 것도 도움이 된다.

Direct I/O를 사용하는 경우에는 로디바이스를 사용할 필요가 없다는 견해가 많다. Direct I/O를 사용할 경우 OS의 버퍼 캐시를 경유하지 않으므로 그 작동 방식이 로디바이스와 거의 동일하기 때문이다. 하지만, 로디바이스가 I/O 성능개선의 중요한 방법이라는 사실 자체는 의심의 여지가 없는 것으로 보인다.

한가지 유념할 것은 로디바이스나 Direct I/O가 비효율적으로 과다하게 I/O를 수행하는 어플리케이션에 대한 무조건적인 해결책이 아니라는 사실이다. 가령 매우 비효율적인 I/O를 수행하는 어플리케이션의 속도를 개선시키기 위해 기존의 파일 시스템을 로디바이스로 수정했다고 가정하자. 어플리케이션의 성능이 개선될까? 불행하게도 그렇지 않을 확률이 있다. 로디바이스를 사용함으로써 I/O 작업 자체는 빨라지지만, OS에서 제공하는 버퍼 캐시를 사용하지 못하기 때문이다. 가령 파일 시스템을 사용할 경우에는 100만번의 Physical Read중 실제로는 10만번만이 실제 디스크 읽기를 유발하고, 나머지는 90만번은 OS의 버퍼 캐시에서 이루어질 수 있다. 이런 경우에 로디바이스를 사용하면 100만번의 Physical Read가 모두 디스크 읽기로 이어지기 때문에 오히려 어플케이션의 성능이 저하될 수도 있다. 어플리케이션의 적절한 튜닝이 항상 우선이며, I/O 시스템의 성능 개선은 I/O 시스템이 “실제로” 느린 경우에만 필요하다.

RAID로 구성된 I/O 시스템을 사용하는 경우에는 RAID 레벨(level)을 신중하게 선택해야 한다. 리두 로그 파일과 같이 쓰기작업이 왕성한 자원에 대해 RAID-5를 사용하는 것은 성능에 큰 문제를 일으킨다. 데이터파일의 경우에도 RAID-5는 문제를 일으키는 경우가 많다. 가능하면 RAID1+0이나 RAID0+1을 사용하는 것이 기본적인 권고안이다. 파일들을 물리적으로 분리시킴으로써 디스크간의 경합을 피하는 것 또한 중요하다. SAN과 같이 통합된 스토리지 시스템을 이용하는 경우에는 해당 엔지니어와의 협의를 통해 파일들간에 적절한 분산이 이루어지도록 설정할 필요가 있다. 또한 아카이브 모드로 데이터베이스를 운영하는 경우에는 리두 로그와 아카이브 로그간의 경합이 발생하지 않도록 구성해 주어야 한다.
I/O 시스템을 변경하는 것은 많은 시간와 돈을 요구하는 경우가 많기 때문에 항상 최후의 선택사항으로 남겨 두어야 한다. 1~4 단계만으로 문제가 해결되지 않는 경우에만 5단계의 튜닝을 고려하는 것이 바람직하다.

RAID의 정의에 대해서만 간단하게 알아보기로 하자. RAID는 Redundant Arrays of Inexpensive(Independent) Disks의 약자로 복수개의 디스크를 조합해서 I/O 시스템을 구성하는 기술을 말한다. 소프트웨어적으로 구현될 수도 있으나 성능 면에서는 하드웨어적으로 구현된 것이 훨씬 유리하다.

• RAID0 : 스트라이핑(Striping). 데이터를 여러 개의 디스크에 분할 저장하도록 구성하는 것을 말한다. 데이터의 부하가 자동으로 분산되므로 성능면에서 유리하지만, 하나의 디스크만 고장나도 전체 I/O가 불가능해지는 단점이 있다.
• RAID1 : 미러링(Mirroing). 여러 개의 디스크에 같은 데이터를 저장하는 기법을 말한다. 하나의 디스크가 고장나도 I/O에 아무런 장애가 없다는 점에서 신뢰성이 높다. 하지만 항상 원하는 용량의 2배에 해당하는 디스크가 필요하다는 단점이 있다.
• RAID5 : 최소한 3개의 디스크를 사용해서 분산 저장하는 방식으로, 데이터 저장시 패리티(Parity) 비트를 저장해서 이후 하나의 디스크가 고장나도 패리티 비트를 이용해서 복구가 가능한 기법을 말한다. 디스크 활용도가 가장 높다고 할 수 있지만 패리티 저장에 따른 부하로 쓰기작업이 왕성한 경우 성능이 저하되는 문제점이 있다.

오라클은 데이터파일이나 컨트롤 파일, 리두 로그 파일에 대해서 RAID5보다는 RAID0+1 또는 RAID1+0을 사용할 것을 권장한다. RAID0+1은 물리적인 스트라이핑에 논리적인 미러링을 구현하는 것을 말하며, RAID1+0은 물리적인 미러링에 논리적인 스트라이핑을 구현하는 것을 말한다.

메타링크 문서번호 30286.1 에서 RAID 구성에 대한 자세한 정보를 제공한다.

Direct Path I/O

오라클의 I/O는 기본적으로 SGA(버퍼 캐시)를 경유한다. 하지만 특수한 상황에서는 SGA를 우회해서 PGA에 데이터를 올린다. 데이터를 공유할 필요가 없을때는 버퍼 캐시에 데이터를 적재하는 과정에서 발생하는 오버헤드를 피함으로써 성능을 개선하는 것이 가능하다. 버퍼 캐시내의 변경된 블록을 데이터파일에 기록하는 것은 DBWR 고유의 작업이다. 반면 버퍼 캐시를 우회하는 쓰기 작업은 개별 프로세스가 직접 수행하게 된다. 이처럼 버퍼 캐시를 우회하는 I/O 작업을 direct path I/O 라고 부른다. 오라클은 다음과 같은 경우에 direct path I/O 를 사용한다.

1. 정렬작업을 위해 정렬 세그먼트(Sort segment)를 읽고 쓰는 경우. direct path read temp , direct path write temp 이벤트를 대기한다.
2. Parallel Query를 위해 데이터파일을 읽는 경우. direct path read 이벤트를 대기한다.
3. PDML이나 CTAS를 위해 데이터파일을 쓰는 경우. direct path write 이벤트를 대기한다.
4. NOCACHE 속성으로 생성된 LOB 세그먼트를 읽고 쓰는 경우. direct path read(lob) , direct path write(lob) 이벤트를 대기한다.
5. I/O 시스템이 데이터를 읽어서 오라클에 반환하는 속도보다 훨씬 빠른 속도로 버퍼를 요구할 때. 이 경우 오라클 성능개선을 위해 readahead I/O (이후에 읽을 것으로 판단되는 데이터를 미리 한꺼번에 읽는 I/O 작업을 말함)를 이용한다. 이 경우 direct path read 이벤트를 대기한다.

Direct path I/O 와 관련된 통계값에 대해 정리하면 다음과 같다.

• physical reads : 디스크에서 읽은 블록수. Direct path I/O 여부와 무관하게 물리적인 읽기 작업이 발생할 경우에는 항상 증가한다.
• physical reads direct : Direct path I/O 를 통해 읽은 블록수. LOB 데이터에 대한 direct path I/O 는 포함하지 않는다.
• physical reads direct (lob) : LOB 데이터를 direct path I/O 를 통해 읽는 블록수
• physical writes : 디스크에 기록한 블록수. Direct path I/O 여부와 무관하게 물리적인 쓰기 작업이 발생할 경우에는 항상 증가한다.
• physical writes direct : Direct path I/O 를 통해 기록한 블록수. LOB 데이터에 대한 direct path I/O 는 포함하지 않는다.
• physical writes direct(lob) : LOB 데이터를 direct path I/O 를 통해 기록한 블록수
• sort(disk) : 디스크를 이용한 정렬 작업 회수. 디스크를 이용한 정렬 작업이 발생할 경우에는 정렬 세그먼트에 대해 direct path I/O 를 사용한다.
• sort(memory) : 메모리를 이용한 정렬 작업 회수

물리적인 읽기 작업 중 버퍼 캐시를 경유한( conventional path I/O ) 읽기 작업은 다음 공식으로 계산할 수 있다.

conventional physical reads = physical reads –( physical reads direct + physical reads direct(lob) )

Direct path I/O 의 성능문제는 대부분 I/O 시스템의 성능과 직접적인 관련이 있다. Direct path I/O 는 버퍼 캐시를 경유하지 않기 때문에 동기화에 따른 오버헤드가 없다. 따라서 경합으로 인한 성능저하 현상이 발생하지 않는다. 정렬작업과 같은 경우를 제외하고는 튜닝 작업을 통해 direct path I/O에서의 대기회수 및 대기시간을 줄이는 것은 불가능하다. 만일 direct path I/O 의 수행 성능상에 문제가 생긴다고 판단되면 I/O 자체의 성능을 개선시키는 것에 초점을 맞추는 것이 옳다.

오라클의 direct path I/O 는 OS의 direct IO와 다른 개념이라는 것에 유의하자. 오라클의 direct path I/O 는 SGA의 버퍼 캐시를 경유하지 않는 것이고, OS의 direct I/O는 OS의 버퍼 캐시를 경유하지 않는 것이다. 오라클의 캐시와 OS의 캐시를 같이 사용하는 것을 더블 버퍼링(double-buffering)이라고 부르는데, 보통 성능에 이롭지 않은 것으로 알려져 있다.
하지만 더블버퍼링이 성능에 미치는 영향의 정도는 시스템의 특성이나 어플리케이션의 특성에 따라 다를 수 있다.

_DB_FILE_DIRECT_IO_COUNT 의 조정

_DB_FILE_DIRECT_IO_COUNT 히든 파라미터의 값이 direct path I/O 에서의 최대 I/O 버퍼 크기를 결정한다. 오라클 9i부터 이 값은 기본적으로 1M의 값을 가진다. 하지만 실제로는 O/S나 하드웨어 설정에 따라 최대값이 결정된다. 이 값을 높이면 Parallel Query의 성능이 높아질 수도 있으나, 대부분 실제 사용가능한 값은 1M보다 작은 값이므로 실제로는 변경할 필요가 없다.

Event Tip

direct path read와 undo

direct path read 가 비록 데이터파일에서 직접 데이터를 읽지만, 언두를 참조하는 메커니즘은 동일하다. 즉, direct path read 는 SGA를 경유하지 않을 뿐, 읽기 일관성(Read consistency)을 보장하는 방법은 동일하다. 이것을 증명하는 방법은 크기가 작은 언두 테이블스페이스(Undo tablespace)를 생성한 후, Parallel Query를 수행하면서 다른 세션에서 DML을 과다하게 수행할때 ORA-01555(Snapshot too old) 에러가 나는 것을 관찰하는 것이다.

ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-01555: snapshot too old: rollback segment number 68 with name "_SYSSMU68$" too small

위의 에러는 곧 PQ 슬레이브 세션이 데이터파일에 대해 direct read 를 수행하면서 변경된 블록을 발견하면 언두 데이터를 참조하는 것으로 해석할 수 있다.

데이터 파일에 대한 direct path read의 증명

PQ 수행시 슬레이브 세션에서의 direct path read가 임시 영역이 아닌 데이터파일에 대한 direct path read 인 것을 어떻게 증명할 수 있을까? 하나의 세션에서 PQ를 수행한 후, PQ가 수행되는 동안 다른 세션에서 V$SESSION_WAIT 뷰를 조회해서 P1 값을 얻으면 어떤 파일에 대한 direct path read 인지 알 수 있다. 아래 스크립트를 보자.

Session A : Degree가 4 인 pq_test 테이블에 대해 PQ를 여러번 수행하면서 direct path read 유발

declare

v_count number;

begin

for idx in 1 .. 100 loop

  select count(*) into v_count from pq_test;

end loop;

end;

/

Session B: Session A에서 발생한 PQ의 슬레이브 세션에 대해 direct path read 이벤트를 캡쳐한다.(Session A의  SID = 162)

set serveroutput on size 100000

declare

begin

           for px in (select * from v$px_session where qcsid = 162) loop

                for wait in (select * from v$session_wait where 

                   sid = px.sid and event like '%direct path read%') loop

                    dbms_output.put_line('SID='||wait.sid || 

', P1=' || wait.P1);

                 end loop;

           end loop;                                         

end;

/

Session B의 수행결과는 아래와 같다.

SID=138, P1=1

SID=152, P1=1

SID=144, P1=1

...

SID=142, P1=1

SID=144, P1=1

SID=138, P1=1

direct path read 대기이벤트의 P1 = file#이므로 해당되는 파일이 실제 데이터파일인지 확인할 수 있다.

SQL>exec print_table('select * from v$datafile where file# = 1'); 을 

FILE#                         : 1

...

BLOCK_SIZE                    : 8192

NAME                          :

C:\ORACLE\PRODUCT\10.1.0\ORADATA\UKJADB\SYSTEM01.DBF

PLUGGED_IN                    : 0

BLOCK1_OFFSET                 : 8192                                   

AUX_NAME                      : NONE

위와 같이 system01.dbf라는 데이터 파일에 대한 direct path read임을 알 수 있다.

DB_FILE_DIRECT_IO_COUNT

DB_FILE_DIRECT_IO_COUNT 파라미터는 direct path read 성능에 영향을 미칠 수 있다. 해당 파라미터는 direct reads , direct writes 에 대한 최대 I/O 버퍼크기로 설정해야 한다. 오라클 8i까지는 대부분의 플랫폼에서 기본 설정값은 64 블록이었다. 따라서 

DB_BLOCK_SIZE 가 8K인 경우 direct reads , direct writes 에 대한 최대 I/O 버퍼크기는 512K(8K*64)이다. 최대 I/O 버퍼 크기는 하드웨어의 한계 값에 의해서도 제한된다. 오라클 9i에서는 DB_FILE_DIRECT_IO_COUNT 파라미터는 hidden 파라미터로 변경되었고, 블록수가 아니라 바이트(byte)단위로 변경되었다. 오라클 9i의 기본 설정값은 1 MB이다. 실질적인 direct I/O 크기는 하드웨어 환경설정(configuration) 및 한계 값에 의해서도 영향을 받는다

Direct Read I/O크기 알기

세가지 방법으로 실제적인 direct read I/O 크기를 알 수 있다.

• direct read 를 수행하는 세션에 대해 10046 트레이스 이벤트를 레벨 8로 설정한다. P3 파라미터는 읽은 블록수를 나타낸다. 아래의 예제의 경우, 블록 크기가 8K 이므로 direct path read I/O 크기는 64K(8K*8블록)이다. 또한, V$SESSION_WAIT 뷰를 조회하여 direct path read 대기이벤트의 P3값을 확인할 수 있다.

WAIT #1: nam='direct path read' ela= 4 p1=4 p2=86919 p3=8

WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86927 p3=8

WAIT #1: nam='direct path read' ela= 10 p1=4 p2=86935 p3=8

WAIT #1: nam='direct path read' ela= 39 p1=4 p2=86943 p3=8

WAIT #1: nam='direct path read' ela= 5 p1=4 p2=86951 p3=8

WAIT #1: nam='direct path read' ela= 38 p1=4 p2=86959 p3=8

...

• O/S의 truss, tusc, trace, 또는strace를 이용하여 direct reads 혹은 direct writes 를 수행하는 UNIX 프로세스를 트레이스 한다. 오라클 9i에서 truss 리포트의 일부분으로 direct I/O 크기가 64K임을 알 수 있다.

9218/1:         kaio(AIOWAIT, 0xFFBECE98)                       = 1

9218/1:         lwp_cond_signal(0xFEB7BFA0)                     = 0

9218/3:         pread64(256, "0602\0\001\0 ~13C19AEE }".., 65536, 0x0FC26000) = 65536

9218/1:         lwp_cond_signal(0xFEB69FA0)                     = 0

9218/4:         pread64(256, "0602\0\001\0 ~1BC19AEFE7".., 65536, 0x0FC36000) = 65536

• 10357 트레이스 이벤트를 설정하여(예: alter session set events '10357 trace name context forever, level 1') direct I/O를 수행하는 세션에 대한 디버그 정보를 확인한다.

Unix process pid: 4375, image: oracle@kccdeds73 (P000)

*** SESSION ID:(9.18) 2004-02-08 21:47:01.908

DBA Range Initialized: length is 1570, start dba is 0100602b

kcblin: lbs=fc86c1cc  flag=8 slot_cnt=32 slot_size=65536 state obj=24321224

kcblin: state objects are: Call=243a2210,Current Call=243a2210, Session=24321224

kdContigDbaDrCbk:starting from tsn 5

kdContigDbaDrCbk:starting from rdba 0100602b

kdContigDbaDrCbk:returning 1570 blocks

kcblrs:issuing read on slot : 0

kcbldio:lbs=fc86c1cc slt=fc86408c typ=0 async=1 afn=4 blk=602b cnt=8 buf=fc87fe00

kcblrs:issuing read on slot : 1

kcbldio:lbs=fc86c1cc slt=fc864210 typ=0 async=1 afn=4 blk=6033 cnt=8 buf=fc89fe00

kcblcio: lbs=fc86c1cc slt=fc86408c type=0 afn=4 blk=602b cnt=8 buf=fc87fe00

... 

이전의 예제에서, 트레이스 파일은 쿼리 슬레이브(query slave)#0 의 수행 내역이다. direct path 오퍼레이션을 위해 32개의 I/O 슬롯이 가용하다(slot_cnt=32). 하나의 슬롯이 I/O 단위이며, 각각의 슬롯은 65536 bytes이다(slot_size=65536). 읽기 오퍼레이션 시 비동기식 I/O가 사용된다(async=1). 쿼리 슬레이브는 데이터파일 #4를 읽는다(afn=4). 읽어 들이는 블록개수는 8개이다(cnt=8). 블록크기는 8K이므로 65536 bytes를 전송한다.

이 경우, direct I/O 슬롯크기는 프로세스가 1 MB 전부를 사용하지 못하게 한다.
_DB_FILE_DIRECT_IO_COUNT 파라미터의 기본 설정값은 1 MB이다. 슬롯크기는 10351 이벤트를 사용하여 변경할 수 있다. 또한 슬롯의 개수도 10353 이벤트를 사용하여 변경할 수 있다. Caution : 앞에서 기술한 정보를 이용하여, 자신이 사용하는 시스템에 대한 direct I/O 의 처리량을 파악할 수 있다. 슬롯크기와 direct I/O 슬롯의 개수에 대한 기본 설정값을 쉽게 변경하지 마라. 변경 작업 전에 자신이 사용하는 하드웨어의 한계를 파악해야 하며, 어플리케이션과 SQL문을 최적화시키는 데 초점을 맞추어야 한다.

제공 : DB포탈사이트 DBguide.net