DBMS Internals Vol1

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] 1

[편집] Oracle 성능분석 방법론

(주)엑셈 선임컨설턴트 김한도(artdb@ex-em.com)

Oracle 성능 분석 방법론이란 말 그대로 Oracle의 성능을 총체적으로 분석하는 방법론이다. 여기에는 Oracle의 성능은 무엇을 의미하며 그 성능을 측정하기 위해서는 어떠한 요소들을 관찰해야 하는지 또한 이 관찰한 값에 어떠한 의미를 부여하여 성능을 표현할 수 있는지 그리고 성능 저하를 야기한 원인과 그에 대한 해결 방법에 대한 논리적인 프로세스가 포함된다.

지금껏 Oracle의 성능을 분석하는 여러 방법론이 있었고 각각의 방법론은 성능 문제를 인식하는 시각과 원인을 분석해가는 방법에서 차이가 나타나고 있다. 이들의 방법론은 나름대로의 장점도 있지만 또 다른 관점이 등장하고 IT업무 환경이 진화해 가면서 좀 더 성능을 잘 표현해 줄 수 있는 방법론의 필요성이 대두되는 것도 현실이다. 이러한 측면에서 지금껏 현장에서 다년간 여러가지 성능 문제를 분석하며 여러 분석 방법론을 실험하고 또한 현실에 맞는 방법론을 연구한 ㈜엑셈은 기존의 방법론과는 조금 다른 시각으로 성능을 바라보고 각각의 방법론을 개선하여 새로운 방법론을 정리하게 되었다.

본지에서는 이러한 방법론이 성능을 어떻게 바라보고 있으며 이를 표현하기 위해 어떠한 요소들을 어떻게 다루고 있는지에 대해서 소개해 보려한다.


[편집] 성능을 어떻게 말할 수 있는가?

새로운 방법론에서 파악하고 있는 성능 문제는 Ratio-Base Analysis나 Wait Event Analysis등이 방법론에서 Resource 또는 대기 시간을 살펴 병목을 찾음으로써 성능문제를 인식한 것에서 크게 벗어나지는 않는다. 즉 새로운 방법론에서도 병목이라는 현상 혹은 상황을 주요한 성능 문제라고 파악한다. 그러나 이 새로운 방법론에서는 병목 보다는 경합이라는 의미가 더 적절하다.

병목이란 말 그대로 병의 목, 즉 갑자기 좁아지는 곳을 의미한다. 경합이란 경쟁을 의미한다. 무언가 틈바구니를 서로 헤집고 나간다는 상태를 표현하는 데는 서로 무리가 없지만 굳이 차이를 구별해 낸다면 병목은 장소의 의미가 강하고 경합은 행위의 의미가 강하다는 것이다. 그러므로 Oracle Area 중 어딘가에서 문제가 생겼다는 것을 표현하는 데에는 병목현상이라고 칭하는 것이 적절하고 세션들이 특정 리소스나 오브젝트 등을 놓고 대기하는 현상을 경합 현상이라고 칭하는 것이 적절하다 할 수 있다.

데이터베이스라는 것은 사용자의 요청에 대해 개개의 프로세스 혹은 세션이 그에 관계된 데이터를 처리해 결과값으로 응답하는 시스템이다. 이 데이터베이스를 사용하는 사용자들은 그 동안의 경험을 통해 학습된 요청의 응답시간 대한 어떤 기대 수준이 있다. 성능 관리란 이러한 기대 수준의 범위를 넘지 않도록 데이터베이스의 요청에서 응답으로의 흐름이 원활하게 지속될 수 있도록 하는 것이라 가정한다. 경합이란 이러한 흐름이 막히거나 원활하지 못하여 전체적인 요청-응답의 순환계에 지장을 주는 것이고 이는 데이터베이스 전반적인 성능의 저하, 즉 성능 문제를 야기하는 것이다.

새로운 방법론에서는 분석의 초점이 어디서 정체가 발생했나 보다는 응답을 기다리는 세션들이 얼마나 정체되어 있고, 얼마나 오래 기다리고 있는지에 대해서, 즉 대기 행렬과 대기 시간을 놓고 성능 문제를 인식하고 있기 때문에 병목이라는 용어보다는 오히려 경합이라는 용어가 성능 문제를 표현하는데 있어 더욱 적당하다. 다시 정리하자면 새로운 방법론에서 성능 문제란 결국 데이터베이스의 흐름이 막히는 것으로 여러 세션들이 어떠한 문제로 인해 서로 경합에 빠지게 되어 점점 경합에 참가하는 세션들과 그 세션들의 대기 시간이 증가하는 것을 의미한다고 할 수 있겠다.


[편집] 성능 분석의 흐름

이러한 성능 문제의 양상은 도로 교통의 상황과 비슷하다. 이미 건설된 도로를 전체 데이터베이스라고 하고 여기를 지나는 자동차를 개개 세션 혹은 프로세스라고 가정하자. 자동차들은 어떤 목적지를 향해 간다. 여러분이 만약 도로 교통을 관리하고 모니터링 하는 사람이라면 어느 부분에 관심을 가질까? 질문을 바꾼다면, 당신이 관심을 가져야 할 도로 교통의 문제 상황은 어떤 것일까? 아마도 어딘가에서 자동차들이 정체되기 시작하는 것을 가장 관심을 갖게 되고 또한 그것을 문제 상황이라고 판단할 것이다. 즉 교통의 흐름을 방해하는 경합이라는 것이 바로 교통의 문제인 것이다.

교통이 정체되었다는 사실을 인지하면 가장 먼저 하는 일은 언제부터 발생했고 어디에서 발생했는지를 파악하는 것이다. 그 이후 이 정체의 근본 원인이 사고인지, 아니면 도로에 방해물이 떨어졌는지 아니면 단순히 차량이 갑자기 몰려서 그러한 일이 발생했는지를 분석하고 이에 따라 교통 상황을 개선하기 위한 조치에 들어가게 된다.

이러한 과정은 성능 분석에서도 그대로 맞아 떨어지게 된다. 일단 성능 문제를 병목 또는 경합이라고 했을 때는 이러한 성능 문제를 판별하기 위한 방법적인 절차가 필요하게 된다. 즉 병목, 경합은 어떤 상황이냐에 대한 답이 필요한 것이다. 성능 분석에서도 교통과 마찬가지로 행렬과 속도로 판별할 수 있다. 즉 병목 또는 경합이 발생하는 것은 위에서 가정했듯 개개 세션의 수행 속도가 느려지게 되고 세션의 수가 급격하게 늘어나는 현상을 일컫는다. 개개 세션의 수행 속도는 바로 대기에 소비하는 시간에 반비례하고 세션들은 Active상태에서 대기에 빠지게 된다. 결국 전체적인 대기 행렬과 대기 시간으로 성능 문제를 인지 할 수 있게 된다.

그 이후의 상황도 교통 상황과 비슷하게 진행된다. 이 성능 문제가 언제부터 발생했고 또한 이 문제가 데이터베이스의 어느 영역에서 발생했는지를 파악하는 것이다. 이 성능 문제의 원인은 Oracle에서 제공하는 Wait Event를 이용하여 파악 할 수 있다. 이후에 이러한 원인 분석을 기반으로 하여 해결책을 모색할 수 있는 것이다. 다시 말해 성능문제가 인지되면 성능 문제의 정도를 판단하고 이 성능 문제가 어느 시점에 발생하고 있는지를 먼저 파악한 후 이에 대한 분석이 수행되어야 한다는 것이다.

그런데 여기서 한 가지 생각해 보아야 할 것이 있다. 성능 문제가 심각한 경우 과연 성능 분석을 빨리 수행하여 해결책을 제시할 수 있을지에 대한 것이다. 사실 성능 문제가 발생하면 그 원인을 분석하는 것보다 빨리 이 문제를 해결하여 정상화 시키는 것이 당면 과제가 된다. 하지만 성능 문제가 인지된 이상 성능 문제의 재발 방지를 위해서도 성능 분석은 불가피하다.


[편집] 성능 측정을 위한 요소들

이러한 이유에서 성능 분석을 위한 성능 데이터의 이력은 반드시 필요하다 할 수 있다. 성능 데이터의 이력이란 말 그대로 과거의 성능 데이터를 모아 놓은 것을 말하는데 성능 데이터의 이력이 없는 경우 우리가 볼 수 있는 정보는 세션들의 현 상태와 활동 상태, 그리고 매우 요약된 수준의 통계들을 보여주는 몇 몇 뷰들로 제한되며, 이 정보들만으로는 성능 문제의 발생 여부를 알 수 있을지는 몰라도 문제의 원인을 밝힐 수 없는 경우가 많다. 대부분의 경우 성능 문제 증상들을 재현하여 원인을 밝혀내기 위해 작업들을 재실행 하는 경우에는, 그 증상들이 재현 가능하고, 문제의 증상이 발생하는 시점을 안다는 전제가 있어야 한다. 성능 문제의 원인 파악이 없이 조치를 취하는 것은 밤중에 사격을 하는 것과 같고, 상황을 악화 시킬 수도 있다. [OWI]

그러나 성능 데이터의 이력을 교통 상황과 비교해 본다면 CCTV를 녹화해 둔 것이라 말할 수 있다. 즉 재현이 없이도 그 당시의 상황을 그대로 관찰해 볼 수 있다는 것이다. 일단 성능 문제가 발생했고 이것이 지금은 해결이 됐다고 생각은 하지만 성능 분석이 불가능하기 때문에 정확한 원인을 파악하지 못한 이상 조치를 취하기는 어렵고 이러한 문제가 앞으로 발생하지 않을 것이라는 보장도 없다. 그러나 성능 데이터의 이력이 있다면 그 당시의 상황에서 성능 분석이 가능하다. 성능 문제가 있는 당시보다는 조금 더 여유 있는 상태에서 성능 문제의 원인을 분석해 볼 수 있는 것이다.

성능 데이터의 이력을 생성하는 방법은 여러가지가 있다. 그러나 그 중에서 가장 시스템에 부하를 주지 않으면서도 충분히 많은 데이터를 수집할 수 있는 샘플링 방법이 가장 유용하다 할 수 있다. 그러나 이것은 Oracle에서 제공하는 유틸리티를 사용하는 것이 아니고 별도의 개발이 필요하다. 이는 상세한 성능 데이터를 수집할 수 있고 부하를 최소화 할 수 있으며 원하는 형태로 저장이 가능해야 한다는 필요에 의한 것이다. 샘플링 방법은 일정 주기로 데이터베이스에 접속된 모든 세션 및 시스템 레벨의 성능 통계자료와 SQL 및 대기 이벤트의 정보를 샘플링 하여 저장한다. 이를 통해 데이터베이스 전반에 대한 성능 데이터를 수집할 수 있는 것이다. [OWI]

이 샘플링 방법은 초기에 SQL*NET을 통해 원하는 데이터를 얻기 위해 SQL을 수행하여 데이터를 가져오는 방식으로 개발이 되었다. 그러나 이것은 샘플링의 주기가 너무 짧으면 데이터베이스에 부하를 가져오게 되고 또한 성능 문제가 심각한 경우 성능 데이터를 수집할 수 없다는 문제에 봉착하게 되었다. 그래서 SGA 직접접근 방식을 통해 최대 초당 100회에 이르기 까지 성능 데이터를 수집할 수도 있게 되어 보다 자세한 성능 분석이 가능하게 되었다. [OWI]

성능 데이터의 이력을 가지고 성능 분석을 하게 되면 고려해야 할 사항이 한 가지 더 생기게 된다. 성능 문제가 발생한 시점이 바로 그것이다. 물론 성능 분석 방법에 따라 성능 데이터의 이력도 다르게 되겠지만 성능 문제의 인식과 더불어 성능 분석 시점을 찾는 것은 효율적이고 정확한 성능분석을 위해서는 중요한 과정이라 생각된다. 새로운 방법론에서도 성능 데이터의 이력을 굉장히 중요하게 여기고 있을 뿐만 아니라 시간이라는 개념 자체를 필수적인 요소로 생각하고 있다.

이러한 샘플링 방법에 의해 가져오는 데이터는 크게 System Level과 Session Level로 나뉠 수 있다. System Level은 주로 데이터베이스 전반의 성능을 파악할 수 있는 자료들을 포함하는데 여기에는 데이터베이스의 일량을 가늠할 수 있는 통계정보(Statistic)와 대기 이벤트 정보(Wait Event)와 Oracle에서 적절히 제공하지는 않지만 분석의 요소로 사용하기 위해 별도의 방식으로 수집하는 Active Sessions Count등의 정보들이 있다. Session Level은 현재 작업을 수행하고 있는 Session들의 일반 정보 및 통계정보, 대기 이벤트 정보, SQL정보 등이 포함된다.

[편집] 성능의 평가 요소-경합지수와 그 요소

앞서 새로운 방법론에서 성능 문제란 결국 경합이 발생하는 상태 또는 현상이라고 정의하였다. 이러한 차원에서 새로운 방법론에서는 성능 문제를 가늠하는 척도로 경합지수(CQ : Contention Quotient)라는 것을 사용한다. 성능 문제라는 것이 데이터베이스의 속도 저하로 인한 경합으로 대기 행렬과 대기 시간이 증가하는 현상을 의미하기 때문에 경합지수도 대기 행렬을 반영하는 Active Sessions와 대기 시간, 더 정확히 말하자면 대기 시간의 비율인 Wait Time Ratio를 사용하여 구하게 된다. 그러므로 경합 지수란 Active Sessions와 Wait Time Ratio를 통해 성능 문제를 반영하는 것이라 할 수 있다.

그림:6p.png

Active Sessions란 Oracle에서 세션의 상태가 Active인 세션의 수를 의미하며 이는 Oracle에서 요청을 받고 이에 대한 응답을 하고 있는 세션의 수와 같다. Active Sessions는 실제로 작업을 수행하거나 작업을 수행하면서 대기에 빠져 있는 Session이다. 이 Active Sessions라는 것은 V$SYSSTAT과 같이 Oracle에서 제공하는 성능 지표는 아니다. 이 값을 가져오기 위해서는 V$SESSION에서 Status가 Active인 것을 헤아려 와야 한다.

Active Session의 증가 정도 즉 얼마나 많은 세션이 경합에 참가하였고 또 얼마나 이러한 상황이 지속되는지에 대한 것은 경합 자체의 성격을 규명하는 아주 중요한 요소이기 때문에 성능 문제를 인식하는데 있어 상당히 중요한 의미를 지닌다. 다시 말해 Active Sessions는 경합이 심화되면 상한이 없이 무한정 늘어날 수 있고 또한 경합이 진행될수록 큰 값이 나타나는 빈도도 상당히 잦아진다. 이는 결국 세션들의 속도 저하를 반영하는 것으로 볼 수 있다. 이는 세션들의 속도 저하가 세션들의 대기 행렬의 길이와 정비례관계에 있기 때문에 이를 뒤집어 생각해보면 Active Sessions가 상당히 큰 값으로 나타나고 큰 값이 자주 나타나 Active Sessions의 값의 범위가 커지면 커질수록 속도가 그만큼 저하되어 경합이 그만큼 심하다는 상황을 여과 없이 반영하고 있다고 할 수 있다. 즉 Active Session의 값의 범위가 커지면 수행 시간은 많이 걸렸다고 간주할 수 있게 되는 것이다.

그러나 이 Active Sessions를 가지고 성능 문제를 파악하기 위해 사용할 때 심각한 문제가 하나 있다. 이 Active Sessions는 시스템의 하드웨어적 성능이나 업무에 따라 그 많고 적음이 차이가 있다는 것이 바로 그것이다. 성능 문제를 판별하기 위한 요소로서의 Active Sessions가 사실 굉장히 가변적인 수치이기 때문에 이것을 비교 가능한 수치로 만들기 위해서는 적당한 가공이 필요하게 된다. 즉 절대적인 수치를 상대적인 수치로 변경하는 작업이 수반되어야 한다는 것이다.

이 Active Sessions를 상대적인 수치로 변경하기 위해서는 기준 값이 필요하다. 이를 위해 먼저 Active Session의 분포를 보도록 할 것이다. 분포란 Active Sessions값이 얼마나 퍼져 있는지를 나타내는 것으로 성능 문제가 없었던 그래프는 분포 자체도 적을뿐더러 최대값에 가까운 수치를 나타내는 빈도도 상당히 적다. 그 이유는 성능 문제가 발생하여 경합이 심해지면 Active Sessions의 값이 커지게 될 것이기 때문이다. 그러므로 Active Sessions의 분포는 성능 문제가 심각했다는 것을 한눈에 판별할 수 있게 해 준다. 다음의 그림은 성능 문제가 발생했던 데이터베이스와 성능 문제가 거의 없는 데이터베이스의 Active Sessions의 분포를 나타낸 그래프이다.

그림:7p-1.png

위의 그림에서 또 한가지 눈에 띄는 것은 점과 화살표이다. 이것은 각 Active Session 그래프에서 기준점과 각 점간의 거리를 나타낸다. 위의 그래프는 크기를 맞추느라 X축의 스케일을 왜곡한 결과 화살표의 길이가 같지만 분포를 가지고 화살표의 길이 비율을 따지면 같은 길이라 하더라도 성능 문제가 있는 왼편의 화살표는 10배 이상의 비율을 가진다.

벌써 분포만을 가지고도 서로를 비교할 수 있는 무언가가 나온 것 같다. 일단 기준점을 알게 되면 각 분포에서 그 거리를 가지고 어떤 수치를 나타낼 수 있게 되는 것이다. 그러나 이 기준점도 또한 쉽지는 않다. 이 기준점을 특정한 수치 즉 절대값으로 표현 할 수 있으면 좋겠지만 이렇게 설정할 경우 결과로 나오는 수치도 가변적일 것이 틀림없다. 그 이유는 기준값을 고정하게 되면 상한이 없는 Active Session의 성질에 따라 이 값도 상한이 없어질 것이기 때문이다. 그렇기 때문에 기준점도 또한 상대적으로 파악해야 한다.

이를 위해 Active Session으로 대변되는 성능 문제라는 것에 대해서 하나의 가정이 필요하다. Active Session이 평소와 달리 큰 수치를 기록하는 것은 특정 시구간, 특히 하루의 경우 일상적이 아닌 특수한 상황이라는 가정이다. 이 가정으로 인한 오류는 나중에 바로 잡도록 하고 일단 이 가정이 옳다고 생각하자. 이러한 가정에 입각하면 성능 문제가 발생하여 Active Session이 급격하게 증가하는 상황은 매우 특수한 상황으로 기준점을 가장 빈도가 높은 지점(α) 과 최소값과 최대값의 거리비율(β) 을 따져 보아 기준점(γ)을 놓게 된다면 이 이상한 현상의 수치에 대한 Ratio를 구할 수 있게 되는 것이다.

그림:7p-2.png

이렇게 구한 기준점을 하루의 데이터를 기준으로 하여 각각의 분포를 구하게 되면 Active Session Ratio가 나타나게 된다. 이 공식은 아래와 같다.

Active Sessions Ratio = DIST( Active Sessions / Day )

이제 Active Session이라는 성능 문제의 한가지 요소에 대한 계량화가 이루어졌다. 그러나 이 공식은 배경에 깔려 있는 가정에 약점이 있다. 만약 성능 문제가 하루 중 특이한 상황이 아니라면 어떻게 할 것인가? 즉 성능 문제가 일시적이어서 눈에 띄게 큰 수치를 나타낸다면 Active Session Ratio는 이를 잘 표현하겠지만 하루 종일 경합에 시달리는 경우는 그 반대의 상황과 비슷한 수치로 표현되기 때문에 오류가 발생하게 된다. 이를 해결할 수 있는 것이 바로 Wait Time Ratio이다.

Wait Time Ratio는 한마디로 말해 Elapse Time 대비 Wait Time의 비율이다. Elapse Time은 Service Time과 Wait Time으로 구성되어 있다이것은 Session Level이 아니라 System Level의 Wait Time Ratio이다. 또한 이 Wait Time Ratio는 각 시점 시점 마다의 Elapse Time에서 Wait Time의 비율을 구한다.

Wait Time Ratio를 구하기 위해서는 시점마다의 Elapse Time과 Wait Time을 구하는 작업이 선행되어야 한다. 우선 Elapse Time을 구해야 한다. 세션 차원에서 Elapse Time을 구하는 것은 쉽다. V$SESSION의 last_call_et라는 컬럼이 나타나기 때문이다. 또한 시점과 시점마다의 Elapse Time은 역시 시점간의 델타(Δ)로 구성하면 된다. 즉 주기를 1초로 한다면 1초 동안 세션이 유지되기만 하면 시점 사이의 Elapse Time은 1초가 되는 것이다. 그렇다면 이를 확장해서 생각해 보자. 현재 1초 동안 30개의 세션이 유지가 되었다면 전체 Elapse Time은 당연히 30초가 된다. 즉 각 주기 마다 Session의 개수만 안다면 전체 Elapse Time을 알 수가 있게 된다.

그러나 여기서 고려해야 할 두 가지 사항이 있다. 첫째는 이 Elapse Time을 계산할 때 Active Session이 아니면 의미가 없다는 것이다. 그 이유는 Elapse Time은 요청에서 응답까지의 시간을 의미하기 때문이다. 그렇기 때문에 전체 Elapse Time을 산정하는 대상은 Active Sessions로 한정해야 한다.

두 번째 고려해야 할 사항은 Active Session의 채집 주기이다. Active Sessions는 Oracle이 주는 통계정보가 아니라 Current하게 세션의 개수를 헤아려 오는 것이다.( Oracle의 통계정보 및 대기 이벤트의 통계정보는 그 값을 누적하여 가지고 있다. 누적 데이터는 어느 시구간 사이에서도 전체데이터이기 때문에 채집 주기에 그 정확성에 영향을 받지 않는다.) 그렇기 때문에 Active Session의 채집 주기는 짧을수록 좋으며 계산상의 이점까지 고려한다면 1초 정도가 적당하다. 만약 이 수치를 넘게 되면 Active Session의 채집된 데이터에 수집 주기를 초 단위로 곱하여 추정해야 하는데 이 주기가 길어지면 길어질수록 오차범위가 커지게 된다는 문제점이 있다.

그림:8p.png

위의 그림에서 보면 좌측에는 초당 1회씩 Active Sessions를 10초 동안 채집하여 전제 Elapse Time을 157초라고 산출한 반면 우측의 그림은 10초당 1회 Active Session을 채집하여 처음의 값인 15를 가지고 이것이 10회 지속되었다는 가정으로 150초로 산출(15 * 10)하였다.

이 두 가지 사항을 고려하여 Active Sessions로 Elapse Time을 구할 수 있다. 이제 Wait Time을 구할 차례다. Wait Time은 비교적 구해내기가 수월하다. V$SYSTEM_EVENT라는 뷰에서 time_waited라는 컬럼의 델타를 구해 모두 합하면 된다. Oracle은 이 데이터도 모두 누적값으로 관리하기 때문에 채집 주기를 1초로 하든 10초로 하든 큰 문제는 없다. 나중에 Wait Time Ratio를 계산할 때만 그 주기를 맞추어 주기만 하면 된다. 그러나 이대로 이 Total Wait Time을 사용할 수는 없다. 그 이유는 Oracle의 Wait Time은 Active Session만 증가 시키는 것이 아니라 Inactive도 또한 증가시키기 때문이다. Inactive 상태에서 증가하는 Wait Event는 SQL*Net message from client와 같은 것이 있는데 이러한 것을 통칭하여 Idle Event라고 한다. 보다 정확한 계산을 위해 이러한 Event를 제외시켜야 한다. (Oracle 10g부터는 각 Wait Event에 Class를 부여하였다. Idle Event를 알기 위해서는 Class가 Idle인 것을 참조하면 될 것이다.)

이렇게 구한 아래의 공식처럼 Wait Time에 Elapse Time을 각각 단위를 통일하여 나누게 되면 Wait Time Ratio를 구할 수 있다. 이 때 주의할 것은 앞서 언급한 것처럼 주기를 통일시키는 것이다. 즉 1분 단위 주기로 이 Wait Time Ratio를 구하기로 했다면 각 초마다 채집한 Active Session의 값을 1분 단위로 합한 값을 1분의 Elapse Time으로 사용하고 Wait Time도 1분 사이의 Delta를 가지고 사용하여 구해야 한다는 것이다. 이렇게 구한 것은 분당 Wait Time Ratio라고 할 수 있다. 이 Wait Time Ratio는 앞에서 언급한 것처럼 System Level에 해당하는 값이다. 그러나 Elapse Time을 Active Sessions로 구해왔기 때문에 이것은 결국 세션당 Wait Time의 비율이기도 하다는 사실을 염두에 두기 바란다.

Wait Time Ratio = AVG(Non-Idle Wait Time / ∑Active Sessions)

[편집] 성능 문제의 인지 - 경합지수의 산출

이제 성능 문제인 경합을 인지하기 위하여 수량화 한 두 가지 요소를 통하여 실제로 경합지수를 구하는 작업을 수행해야 한다. 이를 위해 앞서 언급한 경합지수의 정의부분으로 잠시 돌아가 보도록 하겠다. 우리는 경합지수를 Active Sessions와 Wait Time Ratio를 통해 성능 문제를 반영하는 것이라 하였다. 그렇다면 경합지수는 Active Sessions Wait Time Ratio의 어떠한 상황을 반영하고 있는 것일까?

경합의 상황에서는 실제로 Active Sessions가 급격하게 증가하고 이 경합에 참가하는 세션들의 대기 시간도 아울러 증가한다. 그렇기 때문에 경합지수는 이 두 가지 요소에 비례하는 관계가 있다. 이 수량화한 두 개의 Ratio를 2차원 그래프로 그려보면 다음의 그림과 같다. 이 그래프의 X축은 Active Session Ratio이고 오른쪽으로 갈수록 증가한다. 또한 Y축은 Wait Time Ratio이다. 이 수치는 위로 갈수록 증가하게 된다.

그림:8p-2.png

이 그래프의 오른쪽 상단으로 가면 갈수록 Active Session Ratio도 커지고 Wait Time Ratio도 증가한다. 결국 이 방향은 성능 문제의 심각성 즉 경합 현상이 심해지는 구간이라고 할 수 있겠다. 그렇다면 이는 원점에서 멀어지면 멀어질수록 경합 현상이 심해지는 것을 의미하게 된다. 이를 통해 우리는 점의 거리를 구함으로써 경합 지수를 구할 수 있게 된다. 이를 다음과 같이 정의 할 수 있다.

RAW-CQ = f( Active Sessions Ratio , Wait Time Ratio )

그런데 이 공식에는 문제가 있다. 만약 점의 좌표가 α나 β의 위치, 즉 축에 가까이 붙는 경우는 이 경합 지수가 왜곡 될 수 있다. 다시 말해 Wait Time의 비율은 높으나 Active Session이 많지 않은 경우 또는 Active Session은 비교적 많으나 Wait Time의 비율이 그리 높지 않은 경우이다. 전자의 경우는 적은 수의 세션 중에서 Enqueue등의 Event를 대기한 경우로 이는 경합의 상황임에는 틀림이 없으나 시스템 전체에 미치는 영향을 그렇게 크지 않다고 보고 있다. 이 경합 지수는 데이터베이스의 흐름이 막혀 대기에 참가하는 세션과 대기 시간이 증가하는 현상을 성능 문제로 보고 이를 반영하기 위한 개념이기 때문에 이러한 상황은 우선순위를 두지 않고 있다. 후자의 경우는 세션은 많으나 대기가 없는 경우이기 때문에 흐름에는 지장이 없는 것으로 간주한다. 이러한 경우도 우선순위를 두지 않고 있다.

그림:9p-1.png

그렇다면 결국에는 축에 치우치지 않고 멀어지는 것에 가중치를 부여해 경합지수를 다시 가공해야 한다. 위의 그림에서 왼편에 있는 그래프가 이전의 산출된 경합 지수에 해당하는데 노란 선상에 있는 모든 점은 같은 경합지수를 나타낸다. 이는 단순히 거리에 따라 같은 값을 가지는 것을 표현한다. 그러나 앞에서 지적한 부분에 대해 가중치를 부여하여 이미 구해진 경합지수를 가공하면 오른편의 그래프와 같아지게 된다. 이는 대각선 방향의 점과 축에 가까운 점과의 거리가 같지 않아도 같은 경합지수로 나타나게 된다. 즉 Active Session이 증가하면서 Wait Time이 같이 증가하는 것을 우선 순위가 높은 성능 문제로 생각하고 있으며 경합 지수는 이를 반영하도록 한 것이다. 이 경합 지수는 다음과 같이 표현 할 수 있다.

CQ = cq( RAW-CQ )

이 경합 지수는 30분 정도의 주기를 가지고 각각 구해 낼 수 있다. 하루를 기준으로 48개의 경합 지수 중 대표되는 경합지수는 최대값이다. 최대값을 구하는 이유는 경합지수는 성능 문제의 심각성을 반영하기 때문에 가장 위급한 시점의 것을 취해 와야 하기 때문이다. 이렇게 가장 위급한 것을 전면에 부각하여 성능 문제를 진단하고 해결한 이후에는 그 다음의 최대값을 대표 값으로 하기 때문에 데이터베이스의 성능 문제의 정도는 약간 떨어지게 된다. 이러한 방식을 흔히 Divide & Conquer라고 칭하는데 문제가 있는 것들을 나누어 하나씩 차근차근 해결하는 것을 의미한다. 이러한 방식을 원용한 것이라 생각하면 될 것이다.

그림:9p-2.png

그렇다면 이 경합지수의 가이드라인은 어디일까? 이 가이드 라인은 경험적으로 판단할 수 밖에는 없다. 이를 알아내기 위해 지금까지 여러 성능 분석의 결과를 토대로 경합지수를 나타내어 보기로 하겠다. 이 그래프는 Active Session Ratio와 Wait Time Ratio를 양 축으로 하여 경합지수를 점으로 표시한 것이다. 이중 ⓐ 영역은 경합지수의 분포를 나타내고 있으며 ⓑ 는 성능 문제라고 판단되는 영역을 나타낸 것이다.

우선 경합지수의 분포는 우측 상단을 향하는 럭비공 모양을 나타내고 있다. 이 그림을 통해 알 수 있는 것은 Active Session Ratio와 Wait Time Ratio가 양의 상관관계 즉 비례관계에 있다는 것이다. 다시 말해 Wait이 증가할수록 Active Session에 영향을 미쳐 시너지 효과를 보게 된다고 할 수 있다.

그런데 여기서 한 가지 의문이 생길 수 있다. Active Session Ratio와 Wait Time Ratio가 어차피 속성이 비슷하다면 굳이 두가지 요소로 복잡한 연산을 할 필요가 있을까 하는 부분이다. 그러나 이 의문의 답이 바로 성능 문제의 영역이다. 성능 문제가 발생한 부분은 주로 Active Session Ratio와 Wait Time Ratio도 높은 구간이다. 그러나 이것도 일부일 뿐 전체는 아니다. 나머지 부분은 Active Session Ratio가 다소 높거나 낮고 Wait Time Ratio도 다소 낮거나 높아 서로 비례관계가 성립되지 않는 경우이다.

앞서 우리는 경합지수를 30분 간격으로 구한다고 하였다. 이 30분의 추이를 살펴보면 성능 문제가 발생하기 시작하는 단계에서는 Wait Time Ratio와 Active Session Ratio가 모두 높지는 않다. 초기에는 둘 중 한가지만 높은 비율을 나타내다가 이것이 심화되면 동반하여 두 지표가 모두 높아지게 되어 결국에는 우측 상단으로 수렴되는 형태를 관찰 할 수 있었다. 그런데 중요한 것은 성능 문제 자체이기 때문에 이미 수렴되어 Active Session Ratio와 Wait Time Ratio가 모두 높아지는 상태뿐만이 아니라 둘의 관계가 이미 임계상황을 넘기 시작하면 이를 주시해야 한다. 그렇기 때문에 비례관계가 성립되지 않다 하더라도 다른 사분면에 걸쳐 있는 부분들을 찾아내야 한다. 이를 위해 두 가지 요소로 쉽지 않은 연산을 할 수 밖에 없는 것이다.

그럼 다시 가이드 라인이 어디인가 하는 질문으로 돌아가보자. 경합지수는 성능 문제를 대변하는 수치이기 때문에 성능 문제의 영역을 구분짓는 값이 결국 가이드 라인이 된다. 그러므로 가이드 라인의 값은 결국 ⓑ 의 그래프의 값이 되고 이는 경험적으로 50을 분기점으로 정하고 있다. 50이하인 경우는 성능 문제가 없다고 가정해도 무방하고 50이 넘어 수치가 커지면 커질수록 성능 문제가 심각하다는 것을 의미한다.

[편집] 성능 문제 시점의 파악 - Hot Spot의 검출

Hot Spot은 사전적으로는 보통 위험한 지역이나 분쟁 지역을 의미한다. 그러나 여기서의 Hot Spot은 경합이 많이 발생하여 문제가 있는 구간이기도 하지만 집중적인 대처를 통해 성능상의 이득을 가장 많이 볼 수 있는 구간을 의미하기도 한다.

경합지수를 산출하여 50이 넘지 않는 경우는 성능 문제가 심각하지 않기 때문에 굳이 성능 분석에 들어가 볼 필요는 없다. 굳이 무언가 작업을 원한다면 Elapse Time과 Wait Time Ratio등을 기준으로 Top SQL을 찾아 튜닝 정도만 해준다면 예방차원에서도 큰 문제는 없을 것이다.

그러나 문제는 경합지수가 50이 넘는 경우, 즉 성능 문제가 발생하고 있다고 판단되는 경우일 것이다. 앞서 경합지수는 보통 하루 단위로 30분마다 산출하여 그 최대값으로 대표한다고 하였다. 그렇다면 이 30분 마다의 경합지수가 의미하는 것은 당연히 30분의 성능 문제를 알려주는 척도라고 할 수 있다. 이를 통해 우리는 Hot Spot을 찾아 낼 수 있다.

성능 문제가 발생했을 때 제일 먼저 찾을 수 있는 Hot Spot은 경합지수의 대표값을 가지고 있는 구간이다. 이 구간부터 Hot Spot으로 지정해서 진단에 들어가면 된다. 아래의 그림은 하루를 기준으로 하여 Hot Spot이 3개로 나타난 것이다. 이 Hot Spot을 구하는 방법에는 두 가지 조건이 있다.

그림:10p.png

첫 번째 조건은 당연히 성능 문제를 알려주는 기준점인 50을 넘어야 한다는 것이다. 두 번째 조건은 48개의 경합지수 중에서의 이상치(異常置, outlier)에 속해야 한다는 것이다. 이것은 성능 문제가 일상적이지 않다는 가정에 기반을 두고 있다. 48개의 수치 중에서 말 그대로 정상적이지 않은 그 수치는 당연히 일상적으로 발생하는 수치가 아니라는 의미로 받아들일 수 있다.

이 두 가지 조건 중에서 두 번째 조건이 첫 번째 조건의 부분 집합이 된다. 그러므로 경합지수 50이 넘는 것 중에서 이상치에 속하는 것들을 우리는 Hot Spot으로 간주한다. 30분 간격의 경합지수에서 인접한 시구간이 연속적으로 Hot Spot인 경우는 이를 하나로 본다. 그렇기 때문에 Hot Spot의 시구간은 일정하지 않게 된다.

[편집] 성능 문제의 원인과 해결

Hot Spot을 찾아내었다는 것은 가장 성능 문제가 심각한 시구간을 검출했음을 의미한다. 그러므로 Hot Spot을 찾아낸 이후에는 이 시구간을 집중적으로 분석하여 그 원인을 밝혀내는 것이 중요하다.

이 새로운 방법론도 역시 Wait Event를 가지고 성능 문제의 원인을 찾는다. 그러나 앞서 Wait Event Analysis에서 지적한 대로 Top Wait은 오류의 가능성을 내재하고 있다는 사실을 이미 지적한 바 있다.

이 Hot Spot은 Wait Event Analysis에서 지적한 대로 Top Wait의 진단의 오류 가능성을 상쇄하는 역할을 한다. Hot Spot이라는 것은 각 시구간을 나누어 본 것에 불과할 것이라 생각할 수 있지만 사실 이 개념 안에는 Wait과 Active Sessions의 추이를 본다는 전제가 숨어 있다. Top Wait는 추이가 아니라 합산한 결과를 가지고 순위를 매기게 되지만 이 Hot Spot은 각 시구간 별로 추이가 나타나게 된다. 가령 7시간 동안 큰 수조에 한 컵의 물 만큼 계속 부어 넣고 다른 경우는 컵 하나씩 분리되어 증가한다고 가정하자. 그 중 4시간이 되는 어느때 양쪽에 한방울의 잉크를 떨어트린 경우 후자의 경우가 더 명확하게 나타나게 될 것이다. 이러한 원리로 Hot Spot을 구분하게 되면 성능 문제가 발생한 시구간과 문제 상황도 한눈에 드러나게 될 것이다.

그림:11p-1.png

그러므로 Hot Spot을 알아낸 이상 Top Wait의 문제는 간단히 해결된다. 왜냐 하면 긴 시구간이 아니라 성능 문제가 일어난 시구간 내에서 Top Wait를 뽑아내면 당연히 그 시구간 동안에 가장 오래 대기를 했던 Wait Event가 나올 것이고 이것이 바로 성능 문제의 원인일 가능성이 크기 때문이다.

그런데 Top Wait이 확실한 원인이 아니라 아주 큰 가능성이라고 표현하였다. 그 이유는 이것이 원인이기 위해서 증명해야 할 무언가가 또 한가지 있기 때문이다. 이것은 Active Session과의 관계이다. 즉 성능 문제에서 Active Session의 증가하는 것과 Wait Time의 증가라는 두 가지 요소에 주안점을 두고 있기 때문에 Wait Time이 증가한 정황만 가지고서는 경합지수와 그 원인과의 논리적 연계성이 약하다는 생각이다. 그렇기 때문에 Active Session과의 관계라는 것을 증명하기 까지는 이 Wait Event가 Hot Spot내 성능 문제의 원인이라고 꼬집어 말하는 것을 유보하고 있는 것이다.

이 Active Session과 Top Wait Event와의 관계를 나타내는 것을 패턴지수(Pattern Index)라고 하자. 이 패턴지수는 다음과 같은 개념을 지니고 있다. 아래의 그림은 특정 Hot Spot에서 Top Wait Event가 Latch Free일 경우를 나타낸 것이다. 두 그래프 중 상단의 것은 Active Session의 추이이다. 아래의 그래프는 Top Wait Event인 Latch Free의 그래프이다. 이 두 그래프의 X축은 시간이며 Y축은 각 지표값이다. 여기서 우리가 관심을 가지고 보아야 할 것은 바로 이 그래프가 나타내는 값이 아니라 바로 그래프의 모양이다. 보통 Active Session은 개수가 단위이고 Wait Event는 시간이 단위이기 때문에 동등 비교는 불가능하다. 그러므로 이들의 비교는 그래프의 모양 즉 추이의 비교여야 한다.

그림:11p-2.png

두 그래프를 살펴보면 Latch Free와 Active Session의 그래프는 상당한 유사성을 지니고 있는 것을 확인해 볼 수 있다. 즉 Latch Free가 발생하면서 Active Session이 증가하고 Latch Free가 사라지면서 Active Session도 성능 문제가 발생하기 이전의 수준으로 떨어짐을 알 수 있다. 이러한 추이는 아래와 같은 식으로 구현해 볼 수 있다. 패턴 지수의 값의 범위는 0에서 100으로 Ratio로 간주할 수 있다.

PI = PTN( Active Sessions, Top Wait Event )

패턴지수를 알아냈고 Top Wait Event도 알아 내었다. 그렇다면 이 둘을 조합해서 원인이 되는 Wait Event를 판별하는 하나의 지수를 만들어 낼 수도 있을 것이다. 이미 패턴지수는 값의 범위가 0에서 100이고 Wait Event도 Hot Spot내의 Total Wait Time에서 해당 Wait Time의 비율을 구하게 되면 이도 또한 0에서 100사이를 나타낼 수 있다. 이 값들을 아래와 같이 연산하여 연관성 지수(Relation Index)를 산출할 수 있다. Hot Spot내에서 이 연관성 지수가 가장 높은 Wait Event가 바로 해당 시구간을 Hot Spot으로 만든 원인이 되는 것이다.

RI = RLT( PI, Wait Ratio )

이렇게 찾아낸 Wait Event는 경합을 유발하는 원인으로 생각할 수 있다. Wait Event를 가지고 그야말로 궁극적인 원인을 알아내기 위해서는 해당 Wait Event에 대한 지식을 필요로 한다. 그러나 이 부분에 대한 내용은 이미 여러 서적이나 문서, Metalink등을 통해 공개되어 있다. 이 글의 참고자료 중 하나인 ㈜엑셈에서 번역 출간한 “OWI를 활용한 오라클 진단&튜닝”이라는 서적에도 Wait Event에 대한 설명이 자세히 나와있기 때문에 이들의 자료를 참고하는 것이 좋을 것이다.

[편집] 기존 성능 방법론과의 비교

지금까지 우리는 새로운 방법론이 어떠한 상황을 성능 문제로 가정하고 이를 해결하기 위해 어떻게 성능 문제를 인식, 진단, 해결 할 수 있는지를 살펴보았다. 요약하자면 새로운 방법론에서는 데이터베이스를 하나의 흐름으로 보고 이 흐름이 막히는 상황을 성능 문제라고 정의하고 있다. 이러한 성능 문제를 인식하기 위해 Active Session Ratio와 Wait Time Ratio를 각각 산출하여 경합지수(CQ)를 추출하여 이 수치를 통해 문제의 유무 및 정도를 파악하였다. 경험적으로 경합지수가 50이 넘는 경우를 성능 문제가 있다고 간주하여 성능 문제를 진단하기 시작하게 된다.

성능 데이터의 이력을 바탕으로 성능 문제의 진단을 하기 때문에 우선적으로 시도하는 것은 바로 성능 문제가 발생한 시점을 찾는 것이다. 이것은 이미 경합지수를 찾으면서 생성된 데이터를 이용하여 위험 구간이며 해결 시 가장 큰 효과를 볼 수 있는 Hot Spot을 선정할 수 있다. 그리고 이 Hot Spot 구간에서 성능 문제를 일으킨 Wait Event를 Wait Ratio와 Pattern Index를 통해 산출된 연관성지수(RI)로 판별해 내었다.

이제 성능 문제의 진단과정에서 나타난 Wait Event를 통해 성능 문제를 해결할 실마리를 잡게 되었다. 검출된 Wait Event는 그 자체로 문제의 원인과 해결책을 내포하고 있다. 그러나 이것도 Wait Event에 대한 지식 체계가 갖추어 지지 않는다면 의미 없는 암호에 지나지 않게 된다. 그렇기 때문에 적절한 해결책을 제시하기 위해서는 각 Wait Event에 대한 지식이 뒷받침 되어 있어야 한다.

이 새로운 방법론은 기존의 방법론을 보완, 발전시킨 방법론이다. 새로운 방법론은 기존의 방법론의 강점을 수용, 변화 발전하고 약점을 보완하였는데 이는 이미 언급한 현자의 어깨 위에서 더 멀리 내다 본다는 말로 가장 잘 표현된다.

성능 문제의 인식을 위해 사용된 경합지수(CQ)는 우선 Oracle Response Time Analysis의 강점인 하나의 수치로 전체적인 데이터베이스의 성능을 대변할 수 있다는 강점을 그대로 살리고 있다. 또한 경합지수 그 자체를 하나의 지수로 만들어 Ratio-Base Analysis의 강점이 절대량을 상대량으로 변경하여 비교가 가능하도록 규격화하고 있다는 강점도 아울러 살리고 있다. 그리고 새로운 방법론에 와서 전체 흐름에 성능 분석에 초점을 두고 있다는 것은 Session Level Profiling Analysis의 약점인 전체의 흐름을 볼 수 없다는 단점을 보완하고 있다.

그림:12p.png

또한 Hot Spot이라는 개념을 통해 Wait Event Analysis의 병목을 판단하기 좋다는 강점을 Wait과 더불어 성능 문제를 입체적으로 판단하는 장치를 마련하여 더욱 발전시키고 있다. 그리고 Top Wait이라는 것이 문제의 원인을 찾는 과정에서 오류가 발생할 수 있다는 약점은 Hot Spot으로 보완할 수 있기 때문에 Wait Event Analysis 및 Oracle Response Time Analysis의 약점을 보완하고 있다.

성능 문제 해결에서 사용하고 있는 Wait Event는 Ratio-Base Analysis에서 Wait를 배제하고 있어 정확한 원인 파악이 어렵다는 약점을 극복하고 있으며 Wait Event Analysis의 장점을 그대로 수용하고 있다는 것을 알 수 있다.

그리고 또한 방법론 전반에 사용되고 있는 System Level에서 Session Level로의 Top Down의 흐름은 다양한 경로로 튜닝의 대상을 찾을 수 있기 때문에 Session Level Profiling Analysis의 강점을 발전시켰다고 할 수 있다. 앞의 표는 기존의 방법론의 강점 및 약점이 새로운 방법론에서 어떻게 변화되고 수용되는지를 간략하게 나타내고 있다.

여기까지가 새로운 방법론의 개략적인 설명이다. 앞에서도 언급되었지만 이 방법론은 다년간의 경험의 산물이기 때문에 현재의 이 방법론은 글을 작성하는 지금까지의 경험이 녹아 있는 것이다. 바꿔 말해 앞으로 더 나은 경험과 연구, 시도가 있으면 이 방법론도 그에 맞게 개선 및 보강이 될 것이다.


[편집] 2

[편집] Buffer Lock과 Buffer Busy Waits

(주)엑셈 책임컨설턴트 조동욱(ukja@ex-em.com)

Oracle은 매우 정교하고 효과적인 Row level locking 메커니즘을 제공하며, 사용자의 동시변경으로부터 데이터를 보호하기 위해 Block단위로 Lock을 거는 일은 없는 것으로 알려져 있다. 하지만 이것은 절반만의 사실이다. 공식적으로는 Block 단위의 Lock이 존재하지 않지만, Oracle의 논리적인 IO가 Block 단위로 이루어지기 때문에 내부적으로 Block 단위의 Lock은 반드시 필요하다. 가령 Row1, Row2 두 개의 Row가 같은 Block안에 있다고 가정하자. 두 명의 사용자 User1, User2가 각각 Row1, Row2를 Update한다면 논리적으로는 두 개의 Update 행위 사이에는 서로 보호해야 할 데이터가 존재하지 않는다. Oracle은 Row level을 Lock을 제공하기 때문에 서로 다른 Row를 변경하는 것은 전혀 문제가 되지 않기 때문이다. 하지만 두 개의 Row가 같은 Block안에 있다는 물리적인 제한으로 인해 Block을 변경하는 행위 자체는 동시에 이루어져서는 안 된다. 각 사용자는 Row를 변경하기 위한 TX Lock을 Exclusive하게 획득했다 하더라도 현재 단 한 명의 사용자만이 Block를 변경하고 있다는 것을 보장받아야 한다. 이 경우에 획득해야 하는 Lock을 Buffer Lock이라고 부른다. 만일 Buffer Lock을 획득하지 못하면 다른 Lock들과 마찬가지로 Lock을 획득할 때까지 대기해야 한다.

Buffer Lock을 획득하는 모드에는 Shared 모드와 Exclusive 모드가 있다. Buffer를 읽는 과정에서는 Shared 모드의 Lock을 획득해야 하고, 변경하는 과정에서는 Exclusive 모드의 Lock을 획득해야 한다. Buffer Lock을 획득하려는 세션들간의 모드가 호환성이 없을 때(가령 서로 Exclusive하게 획득하려고 하거나 Shared 모드로 읽고 있는 중에 Exclusive 모드로 획득하는 경우) 경합이 발생하게 된다. Buffer Lock 은 cache buffers chains latch, TX Lock 과 함께 Buffer의 변경을 동기화하는 역할을 한다. 추상적인 레벨에서 하나의 Row를 변경하기 위해서 latch 나 lock을 획득하는 과정은 다음과 같다.

1. 변경하고자 하는 Row에 해당하는 Block 이 존재하는 위치에 찾아가기 위해 cache buffer chains latch를 획득한다.
2. Block을 찾은 해당 Buffer에 대해 Buffer Lock을 획득하고, cache buffers chains latch를 해제한다.
3. 해당 Row에 대해 TX Lock을 획득하고 Row를 변경한다.
4. Buffer Lock을 해제한다.

불행히도 Buffer Lock이라는 용어는 Oracle의 공식용어가 아니며, 일반적으로 통용되는 용어도 아니다. Steve Adams나 Jonathan Lewis같은 Oracle 전문가들이 Buffer Lock이라는 용어를 사용하고 있다. Buffer가 변경 중이라는 사실을 보호하는 것이므로 Buffer Pin 이라는 용어가 의미적으로는 더 적합하다고 볼 수도 있지만, 큰 차이가 없으므로 본 Article에서는 Buffer Lock이라는 용어를 따르기로 한다.

Buffer Lock을 획득하기 위해 대기하는 것을 일반적으로 buffer busy waits 대기라고 부른다. buffer busy waits 는 가장 일반적으로 발생하는 대기현상 중 하나이며 그 원인 또한 다양하다. 또한 다른 System type lock들과 마찬가지로 문제를 해결하는 것 또한 까다롭다.

buffer busy waits 대기이벤트의 정의는 Oracle의 버전 별로 조금씩 다르며 10g이후부터는 대기이벤트 자체가 두 개로 분화되었다.

Metalink NoteID<34405.1 >에서 자세한 정보를 얻을 수 있다.

Oracle 10g 이전 버전: 아래 내용은 메타링크에서 발췌한 것이다. buffer busy waits 대기이벤트의 대기 파라미터는 다음과 같다. P1: 절대(absolute) File# P2: Block# P3: 원인(reason) 코드. 오라클 10g 이전까지는 대기의 원인을 나타낸다.

오라클은 다양한 원인코드(reason code)를 이용하여 커널 코드 안의 여러 부분에서 발생되는 다양한 대기 원인을 나타내주었다. 원인코드의 값은 오라클 버전에 따라 다르며, 오라클 8이전부터 9i까지 변경되어왔다. 하지만, 오라클 10g에서는 원인코드를 더 이상 사용하지 않는다. 오라클 10g에서 P3는 V$WAITCLASS 뷰의 클래스를 나타낸다. 6장에서는 이러한 정보들을 해석하는 방법을 자세히 설명할 것이다.

아래의 표는 오라클 10g이전에 사용된 원인코드와 그에 대한 설명을 기술한다. 괄호 안의 원인코드는 오라클 8.1.5 이하에서 사용된 원인코드다.

그림:17p-2.png

Oracle10g 이후: Oracle10g부터는 buffer busy waits 대기이벤트의 정의가 크게 변경되었다.

첫째, 기존의 buffer busy waits가 read by other session 과 buffer busy waits 두 개의 이벤트로 분화되었다. read by other session 은 Reason code 130에 해당하고 buffer busy wait 은 Reason code 는 220에 해당한다.

둘째, Oracle10g이후부터는 P3의 용도가 변경되었다. Reason code는 더 이상 제공되지 않으며 P3의 값은 Class#을 나타낸다. Class#은 Block의 종류를 나타내는 것으로 Class#은 다음 쿼리를 통해서 확인 가능하다. 비록 Reason code는 없어졌지만 대기이벤트가 분화되었고, Class#이 제공되므로 분석에는 전혀 지장이 없다. 오히려 명확한 Block의 Class를 알 수 있기 때문에 원인분석에 오히려 유리해졌다고 보는 것이 맞을 것이다.

Oracle 이 사용하는 기본적인 Block Class 는 다음과 같다.

SQL> select rownum as class#, class from v$waitstat;
        1 data block
        2 sort block
        3 save undo block
        4 segment header
        5 save undo header
        6 free list
        7 extent map
        8 1st level bmb
        9 2nd level bmb
       10 3rd level bmb
       11 bitmap block
       12 bitmap index block
       13 file header block
       14 unused
       15 system undo header
       16 system undo block
       17 undo header
       18 undo block

undo header, undo block인 경우 실제로 Buffer Header에 지정되는 Class 값은 위의 값과는 다른데, 자세한 내용은 잠시 후에 설명하기로 한다.

Buffer busy waits에 대해서 본격적으로 논의하기 전에 Oracle에서 Buffer Cache의 작동 메커니즘에 대해 기본적인 사항을 짚고 넘어가기로 하자.

[편집] Buffer

Oracle의 Buffer Cache 는 다음과 같은 구조로 이루어져 있다.

Hash table -> Hash bucket -> buffer header chain -> buffer header -> buffer body -> block header -> block body

그림:17p.png

buffer header와 buffer body 는 1:1의 구조로 이루어져 있다. 하나의 buffer에 대해 buffer lock을 획득한다는 것은 buffer header에 대해 lock을 획득한다는 의미이다. buffer header에는 buffer lock을 소유한 프로세스목록과 buffer lock을 대기하고 있는(즉 buffer busy waits 대기이벤트를 겪고 있는) 프로세스목록 정보가 있으며 이를 이용해 lock의 획득과 해제가 순차적으로 이루어지게 된다.

buffer cache와 buffer header의 구조를 알 수 있는 가장 좋은 방법은 buffer cache를 파일로 dump하는 것이다. buffer cache를 dump하는 방법은 여러 가지가 있지만 여기서는 oradebug를 사용하기로 한다.

SQL> oradebug setmypid
SQL> oradebug dump buffers 10
(참조 1 = header only, 10 = header and body)
SQL> oradebug tracefile_name
/home/oracle/admin/ORA102/udump/ora102_ora_19249.trc

dump 파일의 내용 중에 Buffer Header에 해당하는 부분은 다음과 같다.

BH (c000000010ff03b0) file#: 4 rdba: 0x01000d5d (4/3421) class: 1 ba: c000000010e5a000
 set: 6 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 0
 dbwrid: 0 obj: 53733 objn: 53733 tsn: 4 afn: 4
 hash: [c000000017acecc8,c000000017acecc8] lru: [c0000000107f9868,c0000000113eb998]
 lru-flags:
 ckptq: [NULL] fileq: [NULL] objq: [c0000000113eba00,c000000013fe3420]
 use: [c000000017b41b18,c000000017b41b18] wait: [NULL]
 st: CR md: SHR tch: 0
 cr: [scn: 0x1.9f98e79e],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x1.9fb3ed19],[sfl: 0x0]
 flags: only_sequential_access 

위의 항목 중 use, wait 항목이 Buffer lock을 획득한(즉 현재 buffer에 대해 pin을 수행하는) 프로세스와 대기하고 있는 프로세스를 나타낸다.(해당 값은 프로세스 주소로 추정됨) lock을 획득한 세션이 여러 개인 것은 Buffer lock을 획득하는 모드가 Shared 모드와 Exclusive 모드가 가능하기 때문이다.

md(Lock Mode)값은 SHR로, Shared 모드로 현재 Buffer Lock을 획득된 상태이며, Buffer Lock을 획득한 프로세스는 c000000017b41b18 하나이다. 또한 class = 1 은 data block 임을 가리킨다.

st(Status)는 Buffer의 상태를 가리킨다. 일반적으로 다음과 같은 값을 지닌다. (이 값에 대한 정의는 v$bh 와 x$bh 뷰를 참조한다)

0	FREE	no valid block image	
1	XCUR	a current mode block, exclusive to this instance	
2	SCUR	a current mode block, shared with other instances	
3	CR	a consistent read (stale) block image	
4	READ	buffer is reserved for a block being read from disk
5	MREC	a block in media recovery mode	
6	IREC	a block in instance (crash) recovery mode	
8	PI	past image (RAC에서만 쓰임) 

Single Instance인 경우에는 대부분 XCUR(RENT) 또는 CR 상태이며, RAC와 같은 Multi instance환경에서는 SCUR(RENT)와 PI상태를 지니기도 한다.

[편집] Block

Buffer Header가 가리키고 있는 Buffer body는 Block정보를 담고 있다. Block의 종류는 아래와 같이 매우 다양하다.

SQL> select rownum as class#, class from v$waitstat;
        1 data block
        2 sort block
        3 save undo block
        4 segment header
        5 save undo header
        6 free list
        7 extent map
        8 1st level bmb
        9 2nd level bmb
       10 3rd level bmb
       11 bitmap block
       12 bitmap index block
       13 file header block
       14 unused
       15 system undo header
       16 system undo block
       17 undo header
       18 undo block

위와 같이 보통 18가지 정도(Version에 따라 다름) Block이 존재하며 종류에 따라 다른 내용을 담고 있다.

Undo 에 해당하는 block의 클래스 값(15이상)을 해석할 때 주의할 점이 있다. 실제로 buffer busy waits 대기가 발생할 때 p3(class)를 추적해보면 18 보다 큰 값이 나타나는 경우가 많다. 이러한 값들은 undo header block 또는 undo data block(또는 rollback segment)를 가리키는 것이며 다음 공식에 의해 계산된다. (9i 이상)

Undo Header Block = 15 + 2*r (8i 의 경우는 11 + 2*r) Undo Data Block = 16 + 2*r *(8i 의 경우는 12 + 2*r)

여기서 r 은 Rollback(Undo) segment 번호를 말하는데 dba_rollback_segs.segment_id 값으로 확인할 수 있다. 위의 공식을 간단하게 해석하면 만일 p3(class)가 15이상이면서 홀수이면 Undo head block에 해당하며, 짝수이면 Undo data block에 해당한다. 15번에 해당하는 system undo header 는 segment_id 가 0 이므로 15번이 된다.

Buffer Lock 경합이 어떤 종류의 Block에 대해 발생하느냐에 따라 그 문제의 원인 및 해결책이 모두 다르므로 buffer busy waits 대기가 발생하는 Block의 종류(Class)를 정확하게 판별하는 것이 매우 중요하다.

Block의 구조를 가장 직관적으로 알 수 있는 방법은 block dump를 수행하는 것이다. Block dump는 앞서 설명한 buffer cache dump를 통해서도 가능하며 "alter system dump datafile #, block# " 명령을 통해서도 가능하다.

가장 일반적인 block인 data block 에 대한 dump 결과는 다음과 같다.

Block Header

DBA(rdba), Segment/Object ID(seg/obj), SCN(csc), Type(typ, 1=Table, 2=Index), ITL(itl)등의 정보가 Header에 보인다.

Block header dump:  0x0040197c
 Object id on Block? Y
 seg/obj: 0x2  csc: 0x00.e903  itc: 2  flg: -  typ: 1 - DATA
    fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                        Uba         Flag   Lck      Scn/Fsc
0x01   0x0007.02b.0000001d  0x00800c6c.0015.4a  C---    0  scn 0x0000.0000e8f4
0x02   0x0004.014.00000120  0x00800222.00c8.02  --U-    3  fsc 0x0000.00088f91 

Block Body

Data area size(tsiz), Data header size(hsiz), Row count(nrow) 등의 정보와 Row dump(block row dump) 정보를 확인할 수 있다.

data_block_dump,data header at 0xc00000001093005c
===============
tsiz: 0x1fa0
hsiz: 0x8e
pbl: 0xc00000001093005c
bdba: 0x0040197c
     76543210
flag=--------
ntab=12
nrow=40
frre=21
fsbo=0x8e
fseo=0x1a7d
avsp=0x19ef
tosp=0x19ef
0xe:pti[0]	nrow=10	offs=0
0x12:pti[1]	nrow=0	offs=10
...
block_row_dump:
tab 0, row 0, @0x1f89
tl: 23 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 8 comc: 8 pk: 0x00401979.2 nk: 0x00401979.2
col  0: [ 3]  c2 25 4f
tab 0, row 1, @0x1f72
tl: 23 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 1 comc: 1 pk: 0x0040197c.1 nk: 0x0040197c.1
col  0: [ 3]  c2 26 33
...
end_of_block_dump 

직접 dump를 수행한 후 class# 에 따라 block header와 block body 가 어떤 구조를 지니는지 직접 살펴보기 바란다.

필자는 buffer busy waits 대기이벤트를 분석함에 있어서 기존의 자료와는 약간의 다른 접근방법을 따른다. 대부분의 책이나 자료에서는 Reason code나 Class# 에서 시작하여 buffer busy waits를 설명하고 있으나 이 방법은 직관적인지 못한 면이 있다. 따라서 필자는 Select, Insert, Update, Delete 의 실제 SQL문 수행 시 Buffer Lock이 어떻게 획득되고 buffer busy waits가 어떻게 발생하는지를 분석하고 이를 통해서 buffer busy waits대기를 줄이는 방법을 논의할 것이다. 또한 테스트환경을 간결하게 하기 위해 Table Segment 에 대해서만 테스트를 수행하되, 필요한 경우 Index Segment에 대해서도 언급할 것이다.

특별한 언급이 없다면 모든 테스트의 테스트환경은 10g R2 버전에서 수행한다.

[편집] A. Select / Select 와 Buffer Busy Waits

Select / Select 에 의한 Buffer Lock 경합은 동시에 같은 Block을 메모리에 올리는 과정에서 발생하는 것이므로 read by other session 대기를 유발할 것으로 예상할 수 있다. 아래 테스트스크립트와 결과를 보자.

[테스트스크립트]

create tablespace bfw_tbs datafile size 50M autoextend on
extent management local uniform size 1M
segment space management auto;
create table bfw_test(id char(1000)) tablespace bfw_tbs;
insert into bfw_test select ' ' from all_objects where rownum <= 50000;
create or replace procedure bfw_do_select
is
begin 

for x in (select * from bfw_test) loop null; end loop;

end;
/
connect maxgauge/maxgauge@ora10gr2 
select sid from v$mystat where rownum = 1;
var job_no number;
begin 

for idx in 1 .. 20 loop dbms_job.submit(:job_no, 'bfw_do_select;'); end loop; commit;

end;
/
exec bfw_do_select; 
@event;

[테스트결과]

EVENT	TOTAL_WAITS 	TIME_WAITED
------------------------------	-----------	-----------
db file sequential read	266	212
read by other session	164	205
db file scattered read	262	195
SQL*Net message from client	20	5
latch: cache buffers lru chain	1	4
log file sync	4	0
SQL*Net message to client	21	0 

20여 개의 세션이 동시에 동일한 테이블을 읽어 들일 때 예상한 바대로 read by other session 대기이벤트가 광범위하게 나타난다. v$session_wait 뷰를 이용해 read by other session 대기이벤트를 캡쳐한 결과는 다음과 같다.

sid=149, event = read by other session, p1 = 10, p2 = 3820, p3 = 1

p1(file#) = 1 에 해당하는 data file은 해당 테이블이 존재하는 테이블스페이스의 데이터 파일이며

SQL> select name from v$datafile where file# = 10;
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UKJADB\UKJADB\DATAFILE\O1_MF_BFW_TBS_1W622R9R_.DBF

p3(class#) = 1 이므로 data block(여기서는 table)임을 알 수 있다.

하지만, 위의 테스트 결과를 해석할 때는 상당한 주의를 요한다. 동일한 방식으로 다시 한번 테스트를 수행하면 read by other session 대기 및 db file sequential read, db file scattered read 대기이벤트가 대부분 사라진다.

db file sequential read, db file scattered read 대기가 사라진 것은 모든 Block들이 SGA에 로드 되었기 때문에 물리적 IO가 사라진 것과 관련이 있다. read by other session 대기이벤트가 사라진 것 또한 같은 이유다. 읽고자 하는 Block들이 이미 SGA에 로드 되어 있는 경우에는 Buffer Lock 경합이 발생하지 않는다. 즉, read by other session 대기이벤트는 db file sequential read, db file scattered read 대기이벤트와 밀접한 관련이 있다.

앞서 Buffer Lock은 Shared 모드와 Exclusive 모드만이 존재한다고 했다. 이미 SGA에 적재되어 있는 Block을 읽을 때는 Shared 모드로 Buffer Lock을 획득하기 때문에 Buffer Lock에 의한 경합이 발생하지 않는다. 하지만 물리적 IO가 발생하여 Block을 새롭게 SGA에 올리는 것은 Buffer를 새로 생성/변경하는 작업을 요구하므로 최초로 Buffer를 생성하는 세션은 Buffer Lock을 Exclusive하게 획득하게 된다. 따라서 해당 Block을 읽기 위해 Buffer Lock을 Shared 모드로 획득하려는 다른 세션들은 Exclusive Buffer Lock이 해제될 때까지 기다려야 한다. 이로 인해서 read by other session 대기가 발생하게 되는 것이다.

위의 테스트결과는 가능한 물리적 IO를 줄이고 논리적 IO를 늘리는 것이 얼마나 중요한지에 대한 또 다른 예가 된다. 더불어 논리적 IO자체를 최적화해야하는 당위성을 제공한다.

위의 테스트에서는 SGA의 크기가 읽을 대상이 되는 Block들을 모두 메모리에 상주시킬 정도로 크기 때문에 두 번째 테스트에서는 IO 및 Buffer관련 대기가 거의 없어졌지만 만일 SGA가 너무 작거나 너무나 많은 Block을 읽어버리는 바람에 다른 세션들에 의해 해당 Block들이 SGA에서 내려가버리면 다시 물리적 IO가 발생하게 되고 read by other session 대기가 계속해서 나타나게 된다.

따라서 Select / Select 에 의한 read by other session 대기를 줄이는 방법은 다음과 같이 정리할 수 있다. - SQL 최적화를 통해서 최소한의 논리적 IO 만으로 원하는 결과를 가져올 수 있도록 해야 한다. - SGA 사이즈(또는 db cache size)가 시스템 전반적인 IO에 비해 작다면 SQL튜닝만으로는 문제를 해결할 수 없으며 SGA의 물리적 크기를 늘려주어야 한다.

[편집] B. Select / Update 에 의한 Buffer Busy Waits

Select / Update 에 의한 Buffer Lock 경합은 Select / Select 나 Update / Update 에 의한 Buffer Lock 경합과는 그 메커니즘이 상당히 다르다. Oracle의 Select는 기본적으로 Consistent Read 에 기반하므로 실제 읽어야 할 데이터 block이 변경되었다면 해당 Block의 과거 이미지를 가지고 있는 CR Block 또는 Undo Block을 읽어야 한다. 만일 여러 세션이 동시에 Undo Block에 대해 읽기를 시도할 경우 Undo block을 메모리에 올리는 과정에서 Buffer Lock경합이 발생하게 된다. 따라서 Select / Update 에 의한 Buffer Lock 경합은 다음과 같은 상황에서 발생할 것으로 예측할 수 있다.

- 특정 세션이 특정 테이블을 변경(Update)한다. - 이때 다른 세션(들)이 변경 중인 Block에 대해 읽기를 시도한다.

아래 테스트를 통해 이를 확인해보자.

[테스트스크립트] -- Select를 수행하는 프로시저

create or replace procedure bfw_do_select
is
begin
     for x in (select t1.id as id1, t2.id as id2 from bfw_test t1, bfw_test t2 where rownum <=  500000) 
loop

null;

    end loop;
end;
/

-- 동일 테이블에 대해 Update를 수행하는 프로시저

create or replace procedure bfw_do_update
is
begin

update bfw_test set id = ' ';

end;
/
connect system/oracle
select sid from v$mystat where rownum = 1;
var job_no number;

-- 업데이트를 수행하는 동안 여러 세션이 동시에 Select를 수행한다.

begin
         dbms_job.submit(:job_no, 'bfw_do_update;');
        commit;
        for idx in 1 .. 10 loop

dbms_job.submit(:job_no, 'bfw_do_select;');

        end loop;
        commit;
end;
/
exec dbms_lock.sleep(1);
exec bfw_do_select;
@event;

[테스트결과] 위의 테스트스크립트가 수행되는 도중에 v$session_wait 뷰를 캡쳐 하면 다음과 같다. p3(class#)에 유의하자.

sid=139, event = read by other session, p1 = 10, p2 = 1980, p3 = 1
sid=140, event = read by other session, p1 = 10, p2 = 1980, p3 = 1
sid=139, event = buffer busy waits, p1 = 7, p2 = 169, p3 = 73
sid=154, event = buffer busy waits, p1 = 7, p2 = 57, p3 = 59
sid=123, event = read by other session, p1 = 7, p2 = 36549, p3 = 60
sid=123, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=130, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=137, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=138, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=139, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=140, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=143, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=149, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=152, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
sid=154, event = read by other session, p1 = 7, p2 = 58139, p3 = 60
... 

p3가 73,59 등의 값을 지니는 buffer busy waits 대기가 일부 보이며 p3(class)가 60의 값을 지니는 read by other session 대기가 광범위하게 나타난다.

우선, 그 정도는 미약하지만 특수성으로 인해 충분한 분석가치가 있는 buffer busy waits의 경우를 먼저 살펴보자. buffer busy waits의 경우 p1(file#) = 7 이고 p3 = 59, 73으로 나타난다.

일단 file# = 7 에 해당하는 파일명과 테이블스페이스 명은 다음과 같이 Undo 영역임을 알 수 있다.

SQL> select f.name, t.name from v$datafile f, v$tablespace t where f.file# = 7 and t.ts# = f.ts#
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UKJADB\UKJADB\DATAFILE\O1_MF_UNDOTBS2_1Q818KMK_.DBF
UNDOTBS2

SQL>show parameter undo_tablespace 
UNDOTBS2

문제는 class 73, 59 에 해당하는 Block의 종류를 얻는 것이다. 앞서 설명한 Block 종류에 대한 내용을 기억한다면 73, 59 는 15 이상의 홀수이므로 Undo head block임을 알 수 있다. 실제로 block의 종류를 얻기 위해 buffer cache에 대한 dump 파일을 이용하면 다음과 같은 값을 확인할 수 있다.

class = 73 : KTU SMU HEADER BLOCK 
class = 59 : KTU SMU HEADER BLOCK 

KTU는 언두영역의 내부관리(Internal management of undo and rollback segments)와 관련된 오라클 커널(Kernel) 영역을 가리키는 말이며 SMU는 System Management Undo의 약자로 AUM(Automatic Undo Management)을 사용하는 언두 영역임을 말한다. 따라서 73, 59 클래스의 블록들은 AUM을 사용하는 언두 세그먼트 헤더 블록(Undo Segment Header Block)이다. 이 경우 언두 헤더 블록에서의 buffer lock 경합은 Update 세션의 언두 헤더 블록 변경 작업과 Select 세션에 의한 언두 헤더 블록 읽기 작업간의 buffer lock 경합에 의해 발생한다. 헤더 블록을 변경하는 프로세스는 buffer lock을 Exclusive하게 획득해야하고, 헤더 블록을 읽는 프로세스는 buffer lock을 Shared 모드로 획득해야 하기 때문이다.

Undo Segment Header에서의 buffer busy waits 는 일반적으로 Manual Undo Segment(즉 Rollback segment)를 쓰는 환경에서 Rollback Segment의 개수가 너무 작거나 Extent 크기가 작아서 Header의 정보가 자주 변경되는 경우에 발생한다고 알려져 있다. 하지만, 위의 테스트 결과에서 알 수 있듯이 SMU(AUM)을 쓰는 환경에서도 Segment Header Block에 대한 경합은 분명히 발생한다.

가장 성능에 많은 문제를 일으키는 class = 60에 해당하는 read by other session 대기이벤트를 분석해보자. class = 60에 해당하는 Block은 15이상의 짝수이므로 Undo data block에 해당하며 buffer cache dump 파일에서 다음과 같이 확인할 수 있다.

class = 60 : KTU UNDO BLOCK 

Undo block에 대한 경합은 Oracle의 가장 기본적인 메커니즘 중 하나인 Consistent Read 에 그 원인이 있다. Select 세션들이 Data block을 읽을 때, Update에 의해 변경된 상태인 경우에는 기본적으로 Undo Block을 이용해서 과거의 상태를 읽어와야 한다. 이 때, Undo Block을 동시에 많은 세션들이 읽기를 시도하면서 Undo block 에 대해서 1번 테스트에서와 동일한 Select / Select 에 의한 Buffer Lock 경합이 발생하고 이로 인해 read by other session 대기이벤트가 발생하게 된다. 일반적으로 Select와 Update는 서로간에 경합을 일으키지 않는다고 알려져있지만 위의 테스트결과와 같이 좀 더 하위 레벨에서는 buffer lock 경합이 발생하는 것을 확인할 수 있다.

Select / Update에서 언두 블록을 읽는 과정에서 발생하는 read by other session 대기의 해결책은 데이터 블록을 동시에 읽는 과정에서 발생하는 read by other session 대기의 경우와 동일하다.

SQL 문을 적절히 튜닝해서 불필요하게 많은 언두 블록을 읽지 않도록 한다. SGA 크기가 지나치게 작으면 버퍼 캐시에 CR 블록이 상주하지 못해 물리적 읽기 작업이 발생하고 이로 인해 read by other session 대기가 증가할 수 있다. 따라서 SGA의 크기를 적절이 유지해주어야 한다.

[편집] C. Insert / Insert 에 의한 Buffer Busy Waits

여러 개의 세션이 동시에 같은 테이블에 동시에 Insert를 수행하는 경우, 매우 복잡한 성능문제가 발생하며 이로 인해 다양한 대기이벤트가 관찰된다. 대량의 Insert 가 발생하는 경우 Segment 영역이 급속히 확장되기 때문이다. 일반적으로 관찰되는 대기이벤트는 다음과 같다.

- enq: HW - contention
- enq: US - contention
- enq: ST - contention
- enq: TX - row lock contention, enq:TX - allocate ITL entry, enq:TX - index contention
- buffer busy waits 

Insert / Insert 에 의한 Buffer Lock의 경합은 대부분 잘못된 Freelist 값의 설정에 기인한다고 알려져 있다. FLM(Free List Management)를 사용하면서 Freelist 값을 1로 주는 경우 Buffer busy waits 대기가 어떻게 나타나는지 테스트해보자.

[테스트스크립트] -- Manual Segment Space Management를 사용하는 경우

create tablespace bfw_tbs datafile '/home/oracle/oradata/10gr2/ORA102/ukja_test_01.dbf' 
size 50M  autoextend on
extent management local uniform size 1M
segment space management manual;

-- Freelist에 의한 효과를 설명하기 위해 freelists = 1로 매우 작은 값을 줌(기본설정 값이 1)

create table bfw_test(id char(1000)) 
storage(freelists 1)
tablespace bfw_tbs;
create or replace procedure bfw_do_insert
is
begin

for idx in 1 .. 10000 loop insert into bfw_test values(' '); end loop; commit;

end;
/
connect maxgauge/maxgauge@ora10gr2
select sid from v$mystat where rownum = 1;
var job_no number;

-- 동시에 insert를 수행.

begin
       for idx in 1 .. 10 loop

dbms_job.submit(:job_no, 'bfw_do_insert;'); commit;

       end loop;
end;
/
exec bfw_do_insert; 
@event;

[테스트결과] 아래와 같이 HW enqueue와 buffer busy waits 에 의한 대기가 광범위하게 나타난다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
buffer busy waits	2386	1585
enq: HW - contention	243	1103
events in waitclass Other	23	171
log buffer space	11	130
latch: cache buffers chains	61	10
log file sync	3	9
SQL*Net message from client	22	7
latch: library cache pin	16	3
db file sequential read	70	3
latch: library cache	12	1
SQL*Net message to client	23	0
이 경우 buffer busy waits 대기의 발생은 freelists = 1 에 의한 Buffer Lock의 경합으로 해석할 수 있다.

ASSM을 사용하지 않고 FLM(Manual 모드로 Segment Space를 관리)을 사용하는 경우 freelists 의 기본설정 값이 1로 설정된다.

freelists = 1 이라는 것은 곧 여러 개의 세션이 하나의 freelist로부터 새로운 Block을 할당 받는다는 의미이다. 이 경우 여러 개의 세션이 동시에 Insert를 수행하면 모두 하나의 freelist에서 Block을 할당 받아 Buffer를 생성하므로 같은 Buffer에 대해 Exclusive하게 Buffer Lock을 획득하기 위해 경쟁하게 된다(HW Lock Article에서 Freelist 에 대해 비교적 자세히 설명하고 있으므로 참조하기 바란다).

v$session_wait 뷰를 캡쳐 하면 다음과 같이 p3(class) 가 대부분이 1 이며 간혹 4 인 것을 확인할 수 있다.

sid=9, event = buffer busy waits, p1 = 11, p2 = 1666, p3 = 1
sid=15, event = buffer busy waits, p1 = 11, p2 = 1662, p3 = 1
sid=17, event = buffer busy waits, p1 = 11, p2 = 1665, p3 = 1
...
sid=77, event = buffer busy waits, p1 = 11, p2 = 9, p3 = 4
sid=83, event = buffer busy waits, p1 = 11, p2 = 9, p3 = 4
...
sid=118, event = buffer busy waits, p1 = 11, p2 = 1665, p3 = 1
sid=131, event = buffer busy waits, p1 = 11, p2 = 1665, p3 = 1
sid=151, event = buffer busy waits, p1 = 11, p2 = 9, p3 = 4

p3(class)가 1인 경우는 data block에 해당하므로 대부분의 buffer busy waits 대기가 테이블의 같은 Block을 업데이트하려는 과정에서 발생함을 알 수 있다.

p3(class)가 4인 경우는 segment header block 에 해당한다. Insert를 수행하면서 segment header block을 변경하는 것은 freelist 정보를 변경하거나 High water mark를 변경하기 위한 것이다. HW Enqueue 대기는 High water mark를 변경하는 과정에서 발생한다.

위의 테스트결과를 보면 freelists = 1의 기본설정 값을 사용하는 것이 얼마나 위험한 것인지 알 수 있다. HW Enqueue 대기와 buffer busy waits 대기가 모두 잘못된 freelist 값 설정에서 비롯한다.

동일한 테스트를 ASSM을 사용하는 환경에서 수행해보자.

-- Auto Segment Space Management를 사용하는 경우

create tablespace bfw_tbs datafile '/home/oracle/oradata/10gr2/ORA102/ukja_test_01.dbf' 
size 50M  autoextend on
extent management local uniform size 1M
segment space management auto;
create table bfw_test(id char(1000)) tablespace bfw_tbs ;

-- 동일한 Insert 수행

[테스트결과]

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------ ----------- -----------
events in waitclass Other	193	2304
free buffer waits	817	1596
buffer busy waits	1964	1237
log buffer space	44	830
enq: HW - contention	137	390
log file switch (private stran	3	130
             d flush incomplete)
log file switch completion	2	108
log file sync	4	47
latch: cache buffers chains	65	15
... 

buffer busy waits 대기의 경우 만족스럽지는 않지만 분명히 줄어든 것을 확인할 수 있다. HW Enqueue 대기는 크게 줄어들었다.

v$session_wait 뷰를 캡쳐 하면 p3(class)가 다음과 같이 8번이 많은 비중을 차지하면 9번도 눈에 띈다.

sid=10, event = buffer busy waits, p1 = 11, p2 = 11, p3 = 9
sid=11, event = buffer busy waits, p1 = 11, p2 = 1802, p3 = 8
sid=55, event = buffer busy waits, p1 = 11, p2 = 1802, p3 = 8
sid=66, event = buffer busy waits, p1 = 11, p2 = 1802, p3 = 8
sid=77, event = buffer busy waits, p1 = 11, p2 = 1913, p3 = 1
sid=118, event = buffer busy waits, p1 = 11, p2 = 11, p3 = 9
sid=127, event = buffer busy waits, p1 = 11, p2 = 1913, p3 = 1
sid=130, event = buffer busy waits, p1 = 11, p2 = 1802, p3 = 8
sid=132, event = buffer busy waits, p1 = 11, p2 = 1802, p3 = 8 

8, 9 번에 해당하는 Block Class는 다음과 같다.

p3(class#) = 8 : 1st level bmb
p3(class#) = 9 : 2nd level bmb

ASSM을 사용하는 경우 Segment space 관리에 사용되는 Bitmap Block에 대한 Buffer Lock 경합이 발생함을 알 수 있다. 특히 1st level bmb Block이 Leaf Block으로 많은 변경이 발생하게 되므로 해당 block에서 p3 = 8 에 해당하는 buffer busy waits 대기가 많이 발생하게 된다. (ASSM에서의 Bitmap block에 대한 자세한 설명은 참조 문서 중 Poder의 자료를 참조한다)

즉, ASSM을 사용하지 않는 경우에는 잘못된 freelists 지정으로 인해 Data block 또는 Segment Head block에 대한 buffer busy waits 대기가 많이 발생하는 반면, ASSM을 사용하는 경우에는 Bitmap block 에 대한 buffer busy waits 대기가 많이 발생함을 알 수 있다. 다행히 ASSM을 사용하면 buffer busy waits 가 전반적으로 줄어드는 효과가 있으며 특히 HW Enqueue 대기가 크게 줄어드는 효과가 있다.

Insert / Insert 에 의한 buffer busy waits 대기를 줄이는 방법은 다음과 같이 정리할 수 있다.

- ASSM을 사용할 수 없는 환경이라면 freelists, freelist groups 의 값을 시스템의 부하를 고려해서 적절히 부여한다. freelist 와 관련된 속성을 기본값으로 사용하는 것은 매우 위험하다.

- 9i 이상부터는 가능하면 ASSM을 사용한다. ASSM을 사용하는 경우에는 어떤 환경에서도 극단적인 성능 저하를 피할 수 있다.

[편집] D. Update / Update 에 의한 Buffer Busy Waits

동시에 여러 개의 세션이 같은 Row를 업데이트하는 것은 기본적으로 TX Enqueue 에 의해 동기화가 이루어진다. 하지만 동시에 여러 개의 세션이 서로 다른 Row를 업데이트하는 경우에도 만일 해당 Row들이 같은 Block안에 있다고 하면 Buffer Lock에 의한 동기화가 필요하다. 이 경우에는 발생하는 Buffer Lock에 의한 경합은 TX Lock에 의한 경합과는 전혀 다른 성격을 지니고 있으므로 그 현상을 해석할 때 주의하여야 한다.

가령 TX Lock이 발생하는 경우에는 Lock을 획득한 Lock Holder의 Transaction을 종료(commit, rollback, kill)하는 것만이 유일한 해결책이다. 하지만 동일 Block을 변경하는 과정에서 발생하는 Buffer Lock 경합은 그 해결책이 전혀 다르다.

먼저 Update / Update 에 의한 Buffer busy waits 대기현상을 테스트해보자.

[테스트스크립트] -- 여러 개의 row 가 같은 block에 있으면서 동시에 여러 세션이 같은 Row를 업데이트하지 않으면서 같은 Block을 업데이트하게끔 하는 것이 테스트의 요점이다. 테스트 스크립트가 복잡할 수도 있으므로 유의해서 보기 바란다.

create tablespace bfw_tbs datafile size 50M autoextend on
extent management local uniform size 1M
segment space management auto; 

-- Block Size가 8K 인 점을 고려해서 적절한 크기의 Row가 되게끔 설정

create table bfw_test(id number, name char(700)) 
tablespace bfw_tbs;

위와 같이 table을 생성하면 정확하게 10개의 Row가 하나의 Block에 들어가게 된다. -- 총 100건(=10 Block)의 데이터를 생성한다.

begin
for idx in 1 .. 100 loop 
  insert into bfw_test values(idx, ' ');
end loop;
end;
/

-- Block별로 Row수를 확인해보면 아래와 같이 정확하게 10개씩 들어가있음을 확인할 수 있다.

SQL> select dbms_rowid.rowid_block_number(rowid) as block_no, count(*)

from bfw_test group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO	COUNT(*)
----------	----------

12 10 17 10 16 10 18 10 13 10 14 10 19 10 10 10 11 10 15 10

-- 위와 같이 Block을 생성한 후 10개의 독립적인 세션이 TX Lock을 유발하지 않으면서 즉 동일 Row를 업데이트하지 않으면서 동일 Block을 업데이트하게끔 한다. 가령 1번 세션은 id(1,11,21,..,91)을 업데이트하고, 2번 세션은 id(2,12,22,...,92)를 업데이트하는 식이다.

create or replace procedure bfw_do_update(p_idx in number)
is
begin
     for n in 1 .. 1000 loop
        for idx in 1 .. 10 loop

update bfw_test set name = ' ' where id = 10*(idx-1) + p_idx; commit;

        end loop;
     end loop;
end;
/
connect maxgauge/maxgauge@ora10gr2
select sid from v$mystat where rownum = 1;
var job_no number;
begin
    for idx in 1 .. 9 loop

dbms_job.submit(:job_no, 'bfw_do_update('||idx||');'); commit;

    end loop;
end;
/
exec bfw_do_update(10); 
@event;

[테스트결과] 아래와 같이 buffer busy waits 대기이벤트가 매우 광범위하게 나타난다. 이 세션의 전체 수행시간은 67초였는데 buffer busy waits대기이벤트에 45초를 소비하고 있으므로 대단히 심각한 수준이라고 할 수 있다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
buffer busy waits	3616	4951
enq: HW - contention	103	1058
events in waitclass Other	882	901
latch: cache buffers chains	1714	680
latch: In memory undo latch	905	573
log file switch (checkpoint in complete)	4	304
Data file init write	15	75
latch: row cache objects	125	62
control file parallel write	15	38
...

v$session_wait 뷰를 캡쳐 해보면 아래와 같다.

sid=101, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1
sid=110, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1
sid=112, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1
sid=120, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1
sid=123, event = buffer busy waits, p1 = 6, p2 = 2617, p3 = 101
sid=131, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1
sid=133, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1
sid=140, event = buffer busy waits, p1 = 11, p2 = 907, p3 = 1

우선, 동일한 11번 Block에 대해 Buffer Lock을 Exclusive하게 획득하기 위해 대기하는 것을 확인할 수 있다.

즉, 비록 서로 다른 Row를 업데이트하지만 많은 세션이 같은 Block을 변경하는 것만으로 매우 심각한 성능문제를 야기하게 됨을 확인할 수 있다.

이것은 Index의 경우에도 마찬가지다. Index Segment의 동일 Leaf Block에 대해 Block변경이 동시 다발적으로 발생하는 경우에도 buffer busy waits 대기에 의한 성능저하가 생기게 된다.

둘째로 Undo Segment Header block에 대한 경합도 많이 보인다. 이것은 Update문에 의해 Undo를 생성하면서 Header block을 변경하는 과정에서 발생한다.

Update / Update 에 의해 Buffer Lock 경합이 발생하는 경우에는 다양한 해결책들이 제시되고 있다. (해결책이 많다는 것은 거꾸로 Update / Update 에 의한 경합이 매우 보편적이라는 것을 반증한다)

Buffer Lock 경합이 발생하는 원인이 서로 다른 Row가 같은 Block에 있다는 데서 기인하므로 서로 다른 Row를 서로 다른 Block에 흩어지게끔 분산시키는 방법이 가장 보편적으로 사용된다. Row를 분산시키는 방법은 여러 가지가 있다.

- PCTFREE를 높게 준다. 이 방법은 Row를 분산시키는 가장 확실한 해결책이지만 공간의 낭비를 초래하고 그로 인해 Table Full Scan이나 Index Full Scan/Range Scan 의 성능에 영향을 주게 된다. 뿐만 아니라 동일한 데이터를 처리하기 위해 생성해야 할 Block의 수가 늘어나므로 SGA내의 Buffer Cache에 대한 낭비를 초래하고 이로 인해 cache buffers chains latch 경합과 관련된 성능 문제를 야기할 수 있다.

- Partition 기법을 사용하여 물리적으로 다른 Block으로 흩어지게끔 한다. 이 경우 PCTFREE를 높게 주는 방법에 비해 공간을 낭비하는 문제는 없지만 업무 로직 변경에 의해 Update 하는 방식이 바뀌면 같은 문제가 재현될 가능성이 있다.

- 작은 Block 사이즈를 사용한다. 이것은 Block 사이즈가 작으면 자연스럽게 한 Block안에 들어가있는 Row수가 줄어든다는 것을 이용하는 것이다. Oracle9i부터는 서로 Block 사이즈가 다른 Tablespace를 생성할 수 있다. 따라서 작은 Block 사이즈를 사용하는 Tablespace를 생성한 다음 문제가 발생한 Table이나 Index를 Move 함으로써 문제를 해결할 수 있다. 하지만 이 경우에도 Table Full Scan 이나 Index Full Scan/Range Scan의 성능에는 부정적인 영향을 준다. 또한 Block의 수가 늘어나므로 cache buffer chain과 관련된 성능 문제를 야기할 수 있다.

하지만 위의 해결책을 적용할 때는 대단히 조심해야 한다.

가령, 대부분의 책이나 자료에는 PCTFREE를 높게 줌으로써 쉽게 문제를 해결할 수 있다고 되어 있으나, 높은 PCTFREE에 의해 야기되는 다른 성능상의 문제에 대해서는 별다른 언급을 하지 않고 있다.

PCTFREE를 높게 주는 경우 Buffer busy waits 대기에 주는 영향이 어느 정도인지 테스트해보자..

우선, 아래와 갈이 PCTFREE를 매우 높게 주었다.

create table bfw_test(id number, name char(700)) 
pctfree 90 pctused 10
tablespace bfw_tbs;

위와 같이 테이블을 생성한 후 100개의 Row를 insert 하면 정확하게 한 Block당 하나의 row가 생성된다.

이 테이블에 대해 동일한 테스트를 수행하면 그 결과는 다음과 같다.

[테스트결과]

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
latch: cache buffers chains	318	2637
enq: HW - contention	90	1770
buffer busy waits	1120	1696
events in waitclass Other	947	890
latch: In memory undo latch	389	382
Data file init write	5	320
control file parallel write	51	129
log file switch completion	13	119
latch: library cache	39	85
latch: row cache objects	44	46
latch: library cache pin	28	27
...

buffer busy waits 는 줄어들었지만 기대한 만큼 크게 줄어들지는 않았고, 늘어난 Buffer(Block)의 수만큼 cache buffers chains latch 경합이 크게 증가해서 전체 성능 면에서는 큰 개선효과가 없어진다. 한가지 재미있는 것은 보통 cache buffers chains latch 경합을 해소하는 방법 중의 하나로 PCTFREE를 높이는 것을 권고한다는 것이다. PCTFREE를 높임으로써 데이터의 분산이 이루어지고, latch의 경합도 줄어드는 경우가 있기 때문이다. 하지만 이번 테스트에서는 Block의 수가 늘어남으로써 오히려 latch 경합이 증가하는 것을 목격할 수 있다. 9i 이후로는 cache buffers chains latch는 읽기전용인 경우에는 공유가 가능하므로 경합이 줄어드는 효과가 있다. 하지만 본 테스트와 같이 변경을 위해 접근하는 경우에는 Exclusive 모드로 latch를 획득해야 하기 때문에 latch 경합이 크게 증가한 것으로 해석할 수 있다.

PCTFREE를 크게 해서 Row를 분산시켰음에도 불구하고 buffer buys waits가 여전히 높은 이유는 무엇일까? 이것은 앞서 언급한 것처럼 Update에 의한 Undo Segment Header Block에 대한 경합은 없어지지 않으며, Block수가 늘어남에 따라 오히려 생성해야 할 Undo Block수가 늘어났기 때문이다.

실제로 v$session_wait 뷰를 캡쳐 하면 다음과 같은 결과가 나타난다.

sid=113, event = buffer busy waits, p1 = 8, p2 = 2, p3 = 13
sid=123, event = buffer busy waits, p1 = 8, p2 = 2, p3 = 13
sid=130, event = buffer busy waits, p1 = 8, p2 = 2, p3 = 13
...
sid=110, event = buffer busy waits, p1 = 6, p2 = 2617, p3 = 101
sid=120, event = buffer busy waits, p1 = 6, p2 = 1865, p3 = 87
sid=123, event = buffer busy waits, p1 = 6, p2 = 1017, p3 = 73 

위의 결과는 Undo Header 에 대한 경합이 발생하고 있다는 것을 말한다. p1(file#) 6, 8 번은 모두 Undo data file 번호이다.

높은 PCTFREE에 의해 Block수가 늘어나면서 Update에 의한 Undo Block수가 늘어나면서 Undo Segment Header Block에 대한 경합이 늘어난 것이 Buffer busy waits 대기가 크게 줄어들지 않은 가장 큰 이유이다.

위의 테스트결과를 정리해보면 Update / Update 에 의한 buffer busy waits를 줄이기 위해 높은 PCTFREE 값을 사용하는 것은 cache buffers chains latch 경합을 증가시키고 Undo header block 에 대한 경합을 증가시킬 우려가 있다는 것이다.

따라서, buffer busy waits 문제를 해결하기 위해 Storage 속성을 바꿀 때는 충분한 테스트를 거쳐 다른 성능상의 부정적인 효과가 없는지를 충분히 검토해서 적용해야 한다.

그렇다면, Partitioning 기법을 이용해 Block수를 늘이지 않으면서 Row를 분산시키는 효과를 가져올 수 있는 방법을 사용하면 어떨까?

아래 테스트결과를 보자.

[테스트스크립트] -- Hash partitioned table 을 생성

create table bfw_test(id number, name char(700) default  ' ')
partition by hash(id)
partitions 5
tablespace bfw_tbs;

-- 100건을 생성

begin
for idx in 1 .. 100 loop 
  insert into bfw_test values(idx, ' ');
end loop;
end;
/

-- Block별로 Row수를 확인해보면 적절히 분포가 이루어졌음을 알 수 있다.

SQL> select dbms_rowid.rowid_block_number(rowid) as block_no, count(*)

from bfw_test group by dbms_rowid.rowid_block_number(rowid);

BLOCK_NO COUNT(*) ---------- ---------- 524 5 651 10 652 7 778 9 395 10 650 10 266 10 394 10 396 8 267 1 522 10 523 10

-- 위의 테이블에 대해 동일하게 Update / Update를 수행한다.

[테스트결과]

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
buffer busy waits	1675	3177
enq: HW - contention	98	1387
events in waitclass Other	664	639
latch: cache buffers chains	549	364
latch: In memory undo latch	410	268
Data file init write	21	122
log file switch completion	10	85
control file parallel write	21	57
latch: row cache objects	67	17
latch: library cache	24	11
latch: library cache pin	28	9
...

위의 테스트결과를 보면 Partitioning을 한 경우 Partitioning을 하지 않은 경우에 비해 Buffer busy waits 가 다소 줄었지만 만족할만한 수치는 아니다. Partitioning 을 이용해 Buffer Lock 대기를 줄이려면 Update하는 방식과 Partition을 나누는 방식을 고려해서 최적의 분산효과가 나게끔 설계를 해주어야 한다. 위의 테스트스크립트에서 확인할 수 있듯이 현재 업데이트 방식이 1,11,21,..,91 을 하나의 세션에서 업데이트하고, 2,12,22, ..,92를 다른 세션에서 업데이트하는 식이므로 하나의 Block에 1,11,21,...,91 이 들어가고 또 다른 Block에 2,12,22,..92 가 들어가게끔 배치하는 것이 가장 이상적인 Partitioning 이라고 할 수 있다.

아래의 테스트스크립트와 테스트결과를 보자.

[테스트스크립트] -- List Partition 을 이용해서 최적화된 방식으로 데이터가 들어가게끔 한다.

create table bfw_test(id number, name char(700) default  ' ')
partition by list (id)
(
partition id_1 values(1,11,21,31,41,51,61,71,81,91),
partition id_2 values(2,12,22,32,42,52,62,72,82,92),
partition id_3 values(3,13,23,33,43,53,63,73,83,93),
partition id_4 values(4,14,24,34,44,54,64,74,84,94),
partition id_5 values(5,15,25,35,45,55,65,75,85,95),
partition id_6 values(6,16,26,36,46,56,66,76,86,96),
partition id_7 values(7,17,27,37,47,57,67,77,87,97),
partition id_8 values(8,18,28,38,48,58,68,78,88,98),
partition id_9 values(9,19,29,39,49,59,69,79,89,99),
partition id_10 values(10,20,30,40,50,60,70,80,90,100),
partition id_rest values(default)
)
tablespace bfw_tbs;

-- 100건을 생성

begin
for idx in 1 .. 100 loop 
  insert into bfw_test values(idx, ' ');
end loop;
end;
/

-- 원하는 대로 데이터가 들어가있는지 확인해본다. 10 block에 골고루 분산되었음을 알 수 있다.

SQL> select mod(id,10), dbms_rowid.rowid_block_number(rowid) as block_no, count(*)

from bfw_test group by mod(id,10), dbms_rowid.rowid_block_number(rowid) order by 1

MOD(ID,10) BLOCK_NO COUNT(*) ---------- ---------- ---------- 0 2954 10 1 1802 10 2 1930 10 3 2058 10 4 2186 10 5 2314 10 6 2442 10 7 2570 10 8 2698 10 9 2826 10

-- 이 상태에서 위의 테스트와 동일하게 업데이트를 수행한다.

[테스트결과] 결과는 매우 극적이다. Buffer busy waits 대기가 완전히 사라진 것을 확인할 수 있다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
log file sync	9834	9315
events in waitclass Other	259	126
latch: In memory undo latch	196	125
latch: cache buffers chains	109	102
latch: library cache pin	32	28
SQL*Net message from client	22	8
latch: library cache	25	4
db file sequential read	1	0
SQL*Net message to client	23	0

Update방식을 고려하여 최적의 Partition을 구성한 결과 Buffer Lock 경합이 완전히 사라진 것을 확인할 수 있다.

위에서 살펴본 바와 같이 Update / Update 에 의한 Buffer busy waits 대기를 줄이기 위해 제시되고 있는 다양한 해결책들이 제시되고 있지만, 기본적인 테스트나 개념 이해 없이 무작정 적용하는 것은 바람직하지 못하다. 이러한 해결책들을 기본으로 하되, 다양한 테스트를 통해 최적의 해결책을 찾는 것이 중요하다.

지금까지의 테스트 결과와 분석 결과를 토대로 Buffer Lock 경합을 줄이는 방법을 정리하면 다음과 같다.

- select / select 에 의해 발생하는 read by other session 대기를 줄이는 최선의 방법은 SQL 최적화를 통해 가장 적은 I/O로 원하는 결과를 얻는 것이다. 이 작업이 선행되어도 해결이 되지 않는다면 SGA(Buffer cache)의 크기가 적절한지 점검해보아야 한다.

- Select / Update 에 의해 발생하는 read by other session 대기는 Select / Select 에 의한 read by other session 대기와 해결책이 동일하다.

- insert / insert 에 의해 발생하는 buffer busy waits 대기는 적절한 Space 관리 기법을 사용함으로써 해결이 가능하다. 9i 이상의 버전이라면 ASSM을 사용할 것을 권장한다. 8i 라면 freelists 속성을 적절히 지정해야 한다. Transaction의 양에 비해 freelists 값이 작은 경우에 buffer lock에 의한 경합이 광범위하게 나타난다. freelists 값만으로 해결이 되지 않을 때는 _BUMP_HIGHWATER_MARK_COUNT 히든 파라미터 값을 크게 해주는 것 또한 도움이 된다.

- update /update 에 의해 발생하는 buffer busy waits 대기는 동일 Block에 대해 동시에 업데이트가 이루어지지 않게끔 개선함으로써 해결이 가능하다. Update 형태를 고려한 최적의 Partitioning 을 구성하는 것이 좋은 해결책이 된다. PCTFREE를 높게 주거나 작은 크기의 Block 사이즈(9i부터 가능)를 사용함으로써 Block을 분산시킬 수 있으며 이로 인해 Buffer Lock 경합을 줄어들 수 있다. 하지만 위의 테스트 결과에서 보듯이 테스트를 통해 사이드 이펙트가 없는지 충분한 검토가 필요하다.


[편집] 3

[편집] Library Cache Lock, Library Cache Pin

(주)엑셈 책임컨설턴트 조동욱(ukja@ex-em.com)

I 에서 다루었던 Buffer Lock 과 Buffer Busy Waits에 이어서 이번에는 Library cache lock, library cache pin에 대한 내용을 다루고자 한다. 우선 library cache lock 과 library cache pin 에 대해 논의하기 전에 Oracle에서 Library cache 메모리 영역이 어떻게 관리되는지 알아보기로 하자. Library cache 메모리 영역은 Shared pool 내에 존재하며, SQL Cursor, Procedure, Function, Package, Table, View 등 SQL문 수행에 필요한 모든 종류의 객체에 대한 정보를 관리한다.

기본적인 구조는 Hash Table -> Hash Bucket -> Hash Chain -> Handle -> Object 의 형태를 가지고 있다(이 구조는 Oracle에서 매우 보편적으로 사용하는 패턴이다). 아래 그림을 참조하자.

그림:30p.png

하나의 Library Cache Handle(이하 Handle)은 하나의 Library Cache Object(이하 LCO)를 관리한다. Handle은 실제 LCO에 대한 메타정보 및 포인터 역할을 하며 LCO가 실제 정보를 담고 있다. LCO 안의 Data Block안에는 실제 물리적 정보(SQL의 경우 Parse tree, source code, cursor context 등)를 담고 있는 메모리영역에 대한 주소를 가지고 있다.

Procedure와 같이 지정된 이름이 존재하는 객체의 경우에는 하나의 Handle과 LCO로 정보를 관리한다. 따라서 이 경우에는 Child 가 존재하지 않는다. 반면 SQL Cursor 와 같이 이름이 없는 객체는 SQL 텍스트를 이름으로 가지는 부모 Handle과 LCO를 생성하고, 이 부모의 Child Handle과 LCO를 생성해 실제 Cursor 정보를 관리한다.

만일 두 명의 유저가 SQL 텍스트는 일치하지만 Schema가 다른 상태에서 SQL문장을 수행하면 어떻게 될까?

이 경우 총 세 개의 Handle과 LCO의 짝이 생성된다. 부모 Handle과 LCO는 SQL문 자체를 가리키며 두 개의 Child Handle 과 LCO 정보를 child table에 관리한다. 각각의 Child Handle 과 LCO는 각각의 유저가 실제 수행한 Cursor의 정보를 관리한다.

Oracle의 다른 자원들과 마찬가지로 library cache 또한 동기화 장치를 필요로 한다.

library cache를 보호하기 위한 동기화 장치는 크게 두 개로 분류할 수 있다.

첫째, Latch. Oracle은 Concurrent한 프로세스들이 library cache 객체에 동시에 접근하는 것을 막기 위해 shared pool latch와 library cache latch를 사용한다. Library cache를 탐색하고 변경하는 과정에서는 library cache latch를 획득해야 하며, 하드 파싱과 같은 과정에서처럼 새로운 Chunk을 필요로 하는 경우에는 shared pool latch를 획득해야 한다.

둘째, Lock. latch 가 library cache 메모리 영역에 대한 미세한 접근을 동기화하는데 사용된다면, lock은 파싱, 실행, 변경(create, replace, alter, compile, flush 등) 등의 실제적인 Operation을 동기화하는데 사용된다. Oracle은 library cache lock, library cache pin 이라는 두 가지 종류의 lock을 사용한다.

Library cache latch 와 shared pool latch와 관해서는 추후에 다시 논의하기로 하고, 본 Article에서는 Library cache를 보호하기 위한 두 개의 Lock 즉, Library cache lock 과 library cache pin 에 관해서 다루어보기로 한다.

Library cache lock 과 library cache pin 에 대한 기본적인 정의는 다음과 같다.

library cache lock:

Library cache 객체에 접근하거나 변경할 때 library cache handle에 대해 획득해는 lock. Library cache lock을 획득하기 위해 대기하는 것을 library cache lock 대기라고 부른다. 가령 특정 SQL문을 실행하는 프로세스는 해당 SQL문에 해당하는 library cache 객체에 대해 파싱을 수행하는 동안에는 Shared 모드로 library cache lock을 획득해야 한다. 파싱이 끝나고 나면 library cache lock을 Null 모드로 변환하게 된다. library cache lock은 SQL이 참조하고 있는 모든 객체(Table, View 등)에 대해서도 동일한 모드로 획득된다. Shared 모드로 lock을 획득한다는 것은 곧 여러 개의 프로세스가 동시에 같은 SQL문을 수행할 수 있다는 의미가 된다. 따라서 동일 library cache 객체(SQL Cursor, Procedure, Package 등)에 대해 파싱과 수행만 수행하는 과정에서는 library cache lock 이나 library cache pin 과 관련된 대기가 발생하지 않는다. 단, 그런 경우에도 latch와 lock의 획득 자체는 필요하기 때문에 파싱과 수행이 과다한 경우에는 shared pool latch와 library cache latch와 관련된 대기가 발생하게 된다.

반면 create or replace procedure 명령을 이용해서 프로시저를 생성/변경하는 프로세스는 프로시저에 해당하는 library cache 객체에 대해 library cache lock 을 Exclusive하게 획득해야 한다. Table을 alter하는 경우에도 역시 table에 해당하는 library cache 객체에 대해 library cache lock을 Exclusive하게 획득해야 한다. 따라서 많은 세션이 특정 table에 대해 select를 수행하는 중에 해당 table에 대해 alter 작업을 수행하는 경우 library cache lock 대기로 인해 select 세션의 성능저하가 발생할 수 있다.

(참조) SQL문이나 프로시저 수행 시 library cache 객체에 대해 library cache lock을 Shared 모드로 획득한 후, 완전히 해제하지 않고 Null모드로 변환하고 해당 Lock을 계속 보유하는 이유는 무엇일까?

이유는 Library Cache 객체의 Invalidation을 자동화하기 위해서이다.

SQL Cursor와 같은 경우는 해당 Cursor가 참조하고 있는 객체가 변하면 자동으로 Invalidation되어야 한다. SQL Cursor은 자신이 참조하는 모든 객체에 대해서 library cache lock을 Null 모드로 획득하고 있다. 따라서 해당 객체에 대해 DDL(alter, drop 등)이 수행되면 Null 모드로 획득되어 있는 library cache lock 정보를 참조해서 관련된 library cache 객체를 Invalidation하게 된다. 이러한 이유로 Oracle의 Concept Manual에서는 library cache lock을 Breakable parse lock이라고 부른다.

library cache pin:

Library cache 객체에 대한 수행이나 변경 시 library cache object(LCO)에 대해 획득하는 lock. Library cache pin을 획득하기 위해 대기하는 것을 library cache pin 대기라고 부른다. library cache pin은 library cache lock을 획득한 후에 library cache 객체에 대해 추가적인 작업이 필요할 때 획득하게 된다. 가령 특정 프로시저나 SQL 문장을 수행하고자 하는 프로세서는 library cache lock을 Shared 모드로 획득한 후에, library cache pin을 Shared 모드로 획득해야 한다.

프로시저를 컴파일(alter procedure xxxx compile..)하는 경우에는 library cache pin을 Exclusive하게 획득해야 한다. 또한 특이한 것은 하드 파싱이 발생하는 경우 해당 SQL Cursor 에 대해 library cache pin을 Exclusive하게 획득한다는 것이다. 이것은 기존의 어떤 자료에도 직접적으로는 언급되어있지 않은 것으로 여러 가지 테스트에서 증명해보기로 한다.

왜 Oracle은 하나의 library cache 객체에 대해 하나의 lock을 사용하지 않고 library cache lock과 library cache pin 두 개의 lock을 사용할까?

이 질문에 대해 답을 얻으려면 Oracle의 library cache 관리 메커니즘에 대한 약간의 지식이 필요하다. Library cache lock은 Handle에 대해 획득하며, Library cache pin은 LCO에 대해 획득한다. 따라서 이론적으로는 두 개의 프로세스가 부모 Handle에 대해 각각 library cache lock을 Shared모드로 획득한 상태로, 두 개의 프로세스가 각각 Child LCO에 대해 library cache pin을 Exclusive하게 획득하는 것이 가능하다. 따라서 SQL Cursor와 같이 하나의 논리적 객체에 대해 여러 개의 LCO가 생성되는 경우에 library cache 영역에 대한 접근성을 최대한으로 높일 수 있다(하지만 실제 상황에서 이것이 어떻게 사용되는지에 대해서 설명하는 것은 불행히도 필자의 지식범위를 넘어선다).

Library cache lock 에 대한 정의에서 SQL을 파싱 하는 단계에서는 library cache lock을 Shared mode로 획득하고, 수행단계에서는 library cache pin을 Shared mode로 획득한다고 설명했는데, 이것은 소프트 파싱이 수행되는 상황에서만 적용되는 규칙이며 하드 파싱이 발생할 경우에는 약간 다른 방식을 따른다. 우선 소프트 파싱과 하드 파싱의 차이점에 대해서 알아보자.

a. Syntax 체크(문법체크)
b. Semantic 체크(객체체크)
c. 권한체크
d. Shared Pool의 동일 SQL 검색
(Hash 값을 이용해 Library cache 영역 검색 + SQL Text 비교 + 동일 Object 참조 비교 등)

a~d 과정까지를 모두 통과해서 동일 SQL문장을 찾게 되는 과정을 소프트 파싱이라고 부른다.

e. Parse Tree 생성
f. Execution Plan 생성

a~ f 번의 과정을 수행하는 것을 하드 파싱이라고 부른다.

Library cache 객체에 대해 Library cache lock을 잡는 것은 소프트 파싱까지의 단계이며, 하드 파싱이 발생해서 e, f 번의 과정을 수행하는 동안에는 library cache lock을 Null 모드로 변환하고, library cache pin 을 Exclusive하게 획득한다. 하드 파싱이 발생하는 동안에는 해당 LCO에 대해 변동이 발생하는 것을 막기 위해서이다. 하드 파싱 단계가 끝나고 나면 library cache pin을 Shared 모드로 변환하고 수행 단계로 나아간다. Library cache lock과 library cache pin 용도의 이러한 미묘한 차이는 기존의 어떤 문서에도 기술되어 있지 않은 것으로 테스트를 통해서 다시금 상세하게 논의하기로 하자.

library cache lock 과 library cache pin 에 대해 또 한가지 유념할 것은 이 두 개의 lock이 비록 Enqueue lock으로는 분류되지 않지만, 내부적으로는 Enqueue lock 와 유사한 메커니즘을 사용한다는 것이다. Enqueue lock의 경우에는 SGA의 shared pool 영역에 존재하는 Enqueue resource와 Enqueue lock을 관리하는 array 영역에서 모든 정보를 관리하지만, Enqueue lock으로 분류되지 않는 library cache lock/pin, row cache lock, buffer lock 등은 각각 다른 메모리 영역에 lock정보를 관리한다.

위와 같은 개념적인 바탕 위에서 다음과 같은 테스트를 통해 library cache lock과 library cache pin의 획득 및 대기 메커니즘에 대해 좀 더 심도 깊게 알아보기로 하자.

A. 두 개의 세션에서 계속해서 같은 CREATE OR REPLACE PROCEDURE XXX를 호출하면 어떤 대기이벤트를 겪을 것인가?

B. 두 개의 세션에서 계속해서 같은 프로시저를 컴파일 한다면 어떤 대기이벤트를 겪을 것인가?

C. 한 세션에서 프로시저를 수행하는 동안 다른 세션에서 해당 프로시저를 컴파일 한다면 어떤 대기이벤트를 겪을 것인가?

D. 하드 파싱에 아주 많은 시간이 걸리는 SQL문을 두 개의 세션에서 동시에 수행한다면 어떤 대기이벤트를 겪을 것인가?

E. 두 개의 세션에서 계속해서 같은 테이블의 정의를 변경한다면 어떤 대기이벤트를 겪을 것인가?

F. 한 세션에서 alter table 명령어를 이용해 테이블 정의를 변경하는 동안 다른 세션에서 같은 테이블에 대해 select를 수행하면 어떤 대기이벤트를 겪을 것인가?

G. 한 세션에서 select from xxxx를 수행하는 동안 다른 세션에서 alter system flush shared_pool을 수행하면 어떤 대기이벤트를 겪을 것인가?

[편집] A. 두 개의 세션에서 계속해서 같은 CREATE OR REPLACE PROCEDURE XXX를 호출하면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

두 개의 세션에서 아래와 같은 스크립트를 동시에 수행한다.

connect maxgauge/maxgauge@ora10gr2
begin
   for idx in 1 .. 1000 loop
        execute immediate 'create or replace procedure lib_test
        is
          begin
               null;
          end;
        ';
   end loop;
end;
/
@event;

[테스트결과]

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
log file sync	975	795
library cache lock	150	43
events in waitclass Other	1	6
SQL*Net message from client	17	5
SQL*Net message to client	18	0
latch: library cache	1	0

library cache lock 에 대해서 v$session_wait 뷰의 이벤트를 캡쳐 하면 다음과 같다.

event = library cache lock, p1raw = C000000028D94508,p3 = 301

p1raw 값을 이용해서 아래와 같이 Object 명을 알 수 있다.

SQL> SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr = 'C000000028D94508';

Object 의 이름은 lib_test 이고, p3 = 3(=Mode)*100 + 1(=Namespace) 이므로 Mode는 Exclusive이고 namespace = 1(table/procedure) 임을 알 수 있다.

즉, procedure등의 object를 새로 생성할 때는 해당 library cache 객체에 대해 library cache lock 을 exclusive하게 획득해야 함을 알 수 있다.

[편집] B. 두 개의 세션에서 계속해서 같은 프로시저를 컴파일 한다면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

아래의 스크립트를 두 개의 세션에서 동시에 수행한다.

connect maxgauge/maxgauge@ora10gr2
begin
       for idx in 1 .. 100 loop
          execute immediate 'alter procedure lib_test compile';
       end loop;
end;
/ 
@event;

[테스트결과]

EVENT	TOTAL_WAITS	TIME_WAITED
-----------------------------------	-----------
library cache lock	82	378
log file sync	56	25
SQL*Net message from client	17	5
latch: shared pool	3	1
latch: library cache	4	1
SQL*Net message to client	18	0
latch: library cache pin	1	0
latch: library cache lock	1	0 

v$session_wait 뷰를 캡쳐한 결과는 다음과 같다.

event = library cache lock, p1raw = C000000028D94508, p3 = 301

프로시저를 컴파일 하는 경우에도 1번 경우와 동일하게 library cache lock을 exclusive하게 획득해야 함을 알 수 있다.

[편집] C. 한 세션에서 프로시저를 수행하는 동안 다른 세션에서 해당 프로시저를 컴파일 한다면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

세션 A: Procedure를 수행한다.

connect maxgauge/maxgauge@ora10gr2
select sid from v$mystat where rownum = 1;
begin
       for idx in 1 .. 1000 loop
           lib_test;
       end loop;
end;
/ 
@event;

세션 B: Procedure를 컴파일 한다.

connect maxgauge/maxgauge@ora10gr2
select sid from v$mystat where rownum = 1;
begin
        for idx in 1 .. 50 loop
             execute immediate 'alter procedure lib_test compile';
        end loop;
end;
/
@event;

[테스트결과]

세션 A:

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
library cache pin	1	10
SQL*Net message from client	20	7
library cache lock	1	3
SQL*Net message to client	21	0
log file sync	1	0

세션A에서는 library cache lock 또는 library cache pin을 대기한다(둘 다 나타나는 경우도 있음).

세션 B:

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
library cache pin	27	7689
log file sync	40	20
SQL*Net message from client	20	6
library cache lock	1	1
latch: library cache	1	0
SQL*Net message to client	21	0

Flush를 수행한 세션 B는 library cache pin 이벤트를 주로 대기한다. 각 세션에 대해 v$session_wait 뷰를 캡쳐한 결과는 다음과 같다.

세션A:

sid=139, event = library cache lock, p1raw = C000000028D94508, p3 = 201 또는
sid=139, event = library cache pin, p1raw = C000000028D94508, p3 = 201

세션B:

sid=142, event = library cache pin, p1raw = C000000028D94508, p3 = 301

세션A는 Procedure를 "수행"하기 위해 library cache lock을 Shared Mode로 획득한 후 Null Mode로 변환하게 되고, library cache pin을 Share하게 획득한다.

세션B는 컴파일을 수행하기 위해 library cache lock과 library cache pin을 Exclusive하게 획득한다.

세션B에서 컴파일 수행 시에 library cache lock대기는 거의 나타나지 않고 library cache pin 대기만이 많이 나타나는 이유는 무엇일까?

Procedure를 수행하는 세션 A에서는 library cache lock을 SQL문을 파싱 하는 아주 짧은 시간 동안만 Share모드로 유지하고 파싱이 끝난 후에는 library cache lock을 Null모드로 변환하게 된다. 따라서 Procedure를 컴파일 하는 세션 B는 library cache lock을 상대적으로 손쉽게 Exclusive 하게 획득할 수 있다. library cache pin의 경우에는 Procedure가 수행되는 동안 계속해서 Share 모드로 유지되므로 세션B가 컴파일을 수행하기 위해서 library cache pin을 Exclusive하게 획득하기가 어렵게 된다. 따라서 library cache lock 대기시간은 짧은 반면 library cache pin 대기시간은 매우 길게 나타난다.

[편집] D. 하드 파싱에 아주 많은 시간이 걸리는 SQL문을 두 개의 세션에서 동시에 수행한다면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

세션 A 에서 해당 SQL을 실행(하드 파싱 중)

세션 A 가 하드 파싱을 수행하는 도중에 세션 B 에서 동일한 SQL문을 수행

먼저 실행한 세션 A: 대기가 거의 발생하지 않음

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
SQL*Net message from client	19	9
library cache pin	1	9
log file sync	2	1
SQL*Net more data from client	1	0
SQL*Net message to client	20	0

나중에 실행한 세션 B : library cache pin 을 대기. 대기시간은 세션 A의 하드 파싱 시간과 거의 동일하다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
library cache pin	16	4529
SQL*Net message from client	19	9
log file sync	1	1
SQL*Net more data from client	1	0
SQL*Net message to client	20	0

우선 하드 파싱을 먼저 수행한 세션 A의 경우에는 대기는 거의 발생하지 않지만 파싱에 소비되는 수행시간은 40여 초 정도로 매우 길다. 대기가 발생하지 않으면서 이렇게 많은 시간을 소비한다는 것은 곧 순수하게 파싱 하는데 대부분의 시간을 소비한다는 것을 의미한다.

세션 A에서 하드 파싱이 발생하는 동안 동일 SQL문장을 수행한 세션 B에 대해 v$session_wait 뷰를 추적하면 다음과 같이 대기하고 있다.

sid=133, event = library cache pin, p1raw = C0000000295A8880, p3 = 200

p1raw 값을 이용해 Object 정보를 다음과 같이 얻어올 수 있다.

SQL>   SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob
WHERE kglhdadr = 'C0000000295A8880'

수행결과는 " select count(*) from SYS.CON$, SYS.IND$, SYS.BOOTSTRAP$, SYS.UET$, SYS.COL$, SYS.CLU$, SYS.CCOL$, SYS.CDEF$, SYS.TAB$, SYS.PROXY_DATA$, SYS.PROXY_ROLE_DATA$, SY .... "으로 실제 수행중인 SQL문장이 library cache object의 이름으로 사용됨을 알 수 있다.

즉, 해당 SQL Cursor에 대해 Shared 모드로 library cache pin 을 획득하기 위해 대기하고 있는데, 이것은 곧 하드 파싱을 수행하는 프로세스가 해당 SQL Cursor에 대해 library cache pin을 Exclusive하게 획득하고 있음을 의미한다.

하드 파싱이 과다하게 발생하는 시스템의 경우 대부분 library cache latch 나 shared pool latch 대기에 의해 성능저하현상이 나타나게 된다. 하지만 이 경우 library cache pin에 대한 대기가 같이 발생하는 경우가 많다. 하드 파싱에서의 library cache pin 대기는 위에서 설명한 현상을 통해서만 설명이 가능하다.

library cache 객체의 상태에 대한 가장 정확한 관찰은 library cache dump를 통해서 가능하다. 하드 파싱 발생시 library cache pin을 정말로 Exclusive하게 잡는지 dump를 통해 직접 확인해보기로 하자.

-- SQL문장을 수행한다.

SQL>   select count(*) from
SYS.CON$, 
...
...
==> 하드 파싱에 수십 초 이상의 시간 소요

하드 파싱이 발생하는 동안 아래와 같이 library cache dump를 수행한다.

sqlplus "/as sysdba"
SQL> oradebug setmypid
SQL> oradebug dump library_cache 10
SQL> oradebug tracefile_name
/home/oracle/admin/ORA102/udump/ora102_ora_17610.trc

아래의 dump 내용을 이해하려면 앞서 설명한 library cache 의 구조에 대한 약간의 이해가 필요하다.

BUCKET 37366:
 LIBRARY OBJECT HANDLE: handle=c00000001346c3e0 mutex=c00000001346c510(0)
 name=
select count(*) from
SYS.CON$,
...
SYS.SETTINGS$, ...
 hash=2e731935bd651de06bc32481d38c91f6 timestamp=01-06-2006 14:53:43
 namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/DBN/MTX/[100100c4]
 kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=4 hpc=0002 hlc=0002
 lwt=c00000001346c488[c00000001346c488,c00000001346c488] 
 ltm=c00000001346c498[c00000001346c498,c00000001346c498]
 pwt=c00000001346c450[c00000001346c450,c00000001346c450] 
 ptm=c00000001346c460[c00000001346c460,c00000001346c460]
 ref=c00000001346c4b8[c00000001346c4b8,c00000001346c4b8] 
 lnd=c00000001346c4d0[c0000000148c4558,c0000000136f99f0]
   DEPENDENCY REFERENCES:
   	reference	latch	flags
   	-----------------	-----	----------
   	c000000014599890     	1 	[60]
   LOCK OWNERS:
 	lock	user	session 	count	mode	flags
   --------------	----------	----------	-----	----	-----
   	c000000014191428	c000000018367c90	c000000018367c90	1 	N	[00]
   LIBRARY OBJECT: object=c0000000131a26b0
   type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
   CHILDREN: size=16
            child#	table	reference	handle
            -------	--------------	--------------	--------------	
             0	c0000000131a2178	c0000000131a1de8	c0000000144ebcd0
   DATA BLOCKS:
            data#	heap	pointer	status	pins	change	whr
            -----	------------	------------	-------	----	------	---
              0	c000000013579360	c0000000131a27c8	I/P/A/-/-	0	NONE	00 
 BUCKET 37366 total object count=1
...

우선 handle=c00000001346c3e0 는 select count(*) ... SQL 문장에 해당하는 부모 Handle이다.

현재 하드 파싱이 실행 중이지만 부모 객체에 대해서는 lock=N이고, pin=0으로 library cache lock은 Null모드로 library cache pin은 걸려있지 않음을 알 수 있다. SQL Cursor의 경우 부모 객체는 Text정보만을 관리하고 실제 Cursor에 대한 정보는 Child 객체에서 관리하기 때문이다. child table에서 child handle = c0000000144ebcd0를 이용해서 child 객체를 찾아보면 아래와 같다.

LIBRARY OBJECT HANDLE: handle=c0000000144ebcd0 mutex=c0000000144ebe00(0)
 namespace=CRSR flags=RON/KGHP/PN0/[10010000]
 kkkk-dddd-llll=0000-0001-0000 lock=N pin=X latch#=4 hpc=0002 hlc=0002
 ...
 CHILD REFERENCES:
            reference	latch	flags
           ----------------	-------	--------
          c0000000131a1de8	1	CHL[02]
   LOCK OWNERS:
             lock	user	session	count	mode	flags
           ----------------	----------------	----------------	-----	----	--------
          c0000000143bccb8	c000000018367c90	c000000018367c90	1	N	[00]
   PIN OWNERS:
             pin	user	session	lock	count	mode	mask
           ----------------	----------------	-----------------	-----	-----	 ----	----
          c000000014046e90	c000000018367c90	c000000018367c90	0	0	X	0041
   LIBRARY OBJECT: object=c000000014599670
    ...

Child 객체에 대해서는 library cache lock을 Null 모드로, library cache pin을 Exclusive 모드로 잡고 있음을 알 수 있다. 즉, 하드 파싱 수행 시 library cache pin을 Exclusive 모드로 잡고 있는 것을 물리적으로 확인할 수 있다.

[편집] E. 두 개의 세션에서 계속해서 같은 테이블의 정의를 변경한다면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

두 개의 세션에서 테이블 정의를 계속해서 변경한다.

connect maxgauge/maxgauge@ora10gr2
begin
     for idx in 1 .. 1000 loop
            execute immediate 'alter table test enable all triggers';
     end loop;
end;
/
@event;

[테스트결과]

세션 A와 세션 B가 아래와 같이 거의 동일한 대기현상을 겪는다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
library cache lock	386	750
latch: library cache	142	98
log file sync	273	93
latch: shared pool	25	5
SQL*Net message from client	17	4
events in waitclass Other	1	0
SQL*Net message to client	18	0
cursor: mutex X	1372	0 

v$session_wait 뷰를 캡쳐 하면 다음과 같다.

sid=130, event = library cache lock, p1raw = C000000027CCBB28, p3 = 301
sid=137, event = library cache lock, p1raw = C000000027CCBB28, p3 = 301
SQL>   SELECT kglnaown "Owner", kglnaobj "Object"
FROM x$kglob 
WHERE kglhdadr = 'C000000027CCBB28'
Object = TEST

위의 테스트결과와 같이 테이블의 속성을 변경(alter)하려면 해당 테이블의 library cache 객체에 대해 Exclusive 모드로 library cache lock을 획득해야 함을 알 수 있다.

[편집] F. 한 세션에서 alter table 명령어를 이용해 테이블 정의를 변경하는 동안 다른 세션에서 같은 테이블에 대해 select를 수행하면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

세션 A: 테이블 정의를 계속해서 변경한다.( sid = 130 )

connect maxgauge/maxgauge@ora10gr2
begin
            for idx in 1 .. 1000 loop
                    execute immediate 'alter table test enable all triggers';
            end loop;
end;
/
@event;

세션 B: 해당 테이블에 대해 Select를 수행한다.( sid = 142 )

connect maxgauge/maxgauge@ora10gr2
declare
            v_value number;
begin
            for idx in 1 .. 50000 loop
                  select 1 into v_value from test where rownum = 1;
            end loop;
end;
/
@event;

[테스트결과]

세션 A, B 모두 library cache lock 대기를 겪는다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
library cache lock	184	509
log file sync	41	15
SQL*Net message from client	20	6
latch: shared pool	4	3
latch: library cache	9	2
events in waitclass Other	1	0
SQL*Net message to client	21	0 

하지만, v$session_wait 뷰를 캡쳐 해보면 library cache lock의 대기모드는 아래와 같이 전혀 다르다.

세션A: sid=130, event = library cache lock, p1raw = C000000027CCBB28, p3 = 301 
세션B: sid=142, event = library cache lock, p1raw = C000000027CCBB28, p3 = 201

테이블을 alter 하는 세션 A는 library cache lock을 Exclusive모드로 획득하기 위해 대기하고, select를 수행하는 세션 B는 library cache lock을 Shared 모드로 획득하기 위해 대기함을 알 수 있다. 이는 곧 alter table xxxx 와 select from xxx 는 동시에 수행될 수 없다는 것을 의미한다.

이것은 동일한 형식의 테스트를 Procedure에 대해서 수행한 3번의 경우와는 전혀 다른 결과임에 유의하자.

하나의 세션에서 Procedure를 컴파일하고 다른 세션에 Procedure를 수행하는 경우에는 컴파일을 수행하는 세션에서 library cache pin + Exclusive 대기가 광범위하게 나타난다(이것은 프로시저 컴파일 수행 시 library cache pin을 Exclusive하게 획득해야 함을 의미한다).

하지만 하나의 세션에서 Table을 alter하고 다른 세션에서 Table을 select하는 경우에는 alter를 수행하는 세션에서는 library cache lock + Exclusive 대기가 나타나고, select를 수행하는 세션에서는 library cache lock + Shared 대기가 나타난다.

[편집] G. 한 세션에서 select from xxxx를 수행하는 동안 다른 세션에서 alter system flush shared_pool을 수행하면 어떤 대기이벤트를 겪을 것인가?

[테스트스크립트]

세션 A: select를 수행한다.( sid = 130 )

connect maxgauge/maxgauge@ora10gr2
select sid from v$mystat where rownum = 1;
declare
            v_value number;
            v_cursor sys_refcursor;
begin
       for idx in 1 .. 10000 loop
          open v_cursor for 'select 1 from test test' || idx || ' where rownum = 1';
                   fetch v_cursor into v_value;
                   close v_cursor;
       end loop;
end;
/ 
@event;

세션 B: shared pool flush를 수행한다.( sid = 141 )

connect maxgauge/maxgauge@ora10gr2
select sid from v$mystat where rownum = 1;
begin
       for idx in 1 .. 1 loop
          execute immediate 'alter system flush shared_pool';
       end loop;
end;
/
@event;

[테스트결과]

세션 A: Select 수행

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
latch: library cache	21	59
db file sequential read	10	30
library cache pin	2	13
SQL*Net message from client	19	9
log file sync	1	1
db file scattered read	1	0
SQL*Net message to client	20	0

세션 B: Flush 수행

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
db file sequential read	48	130
latch: library cache	9	28
library cache pin	3	22
log file sync	3	6
SQL*Net message from client	24	6
latch: shared pool	2	0
SQL*Net message to client	25	0

Select를 수행하는 세션과 flush를 수행하는 세션 모두 library cache pin 을 대기하게 된다. v$session_wait 뷰를 캡쳐 해보면 다음과 같다.

세션 A: Select 세션

sid=130, event = library cache pin, p1raw = C0000000297896B0, p3 = 200
sid=130, event = library cache pin, p1raw = C0000000297810F4, p3 = 200
...

세션 B: Flush 세션

sid=141, event = library cache pin, p1raw = C000000027D49F18, p3 = 200
sid=141, event = library cache pin, p1raw = C00000002E66B748, p3 = 200
...

SQL Trace를 이용해 정확하게 어떤 SQL 문장에 대해서 library cache pin 대기가 발생하는지를 추적할 수 있다. SQL Trace는 Select 세션, flush 세션 모두에서 아래와 같이 user$, obj$, tab$ 등의 Dictionary를 참조하는 과정에서 library cache pin 대기가 발생함을 보여준다.

PARSING IN CURSOR #3 len=196 dep=2 uid=0 oct=3 lid=0 tim=3553193304214 
hv=3275427458 ad='5e6b7808'
 select name,password,datats#,tempts#,type#,defrole,resource$, ptime, exptime, ltime, astatus,
 lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1 from user$ 
 where user#=:1
END OF STMT
PARSE #3:c=0,e=3729,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=4,tim=3553193304179 
...
WAIT #3: nam='library cache pin' ela= 103837 handle address=-4611686016843316608 
pin address=-4611686016901293864 100*mode+namespace=200 obj#=-1 tim=3553193447525 

이는 곧, Flush에 의해서 Shared pool 에 적재되었던 Dictionary 정보가 모두 사라지면서 해당 정보를 참조하려는 세션들이 해당 Library cache 객체에 대해 library cache pin을 대기함을 의미한다. 위의 테스트내용에 기반하여 library cache lock과 library cache pin에 관련된 성능 이슈를 정리해보면 다음과 같다.

1. library cache lock과 library cache pin 대기에 의한 성능저하현상은 대부분 부적절한 DDL(create, alter, compile, flush등)에 의해 발생한다. 따라서 트랜잭션이 왕성한 시스템에 대해서 DDL을 수행할 때는 위의 내용을 충분히 고려한 후 수행하도록 해야 한다. 간혹 하드 파싱이 왕성한 시스템에서 Shared Pool 메모리 고갈을 피하기 위해(즉 ORA-4031 에러를 피하기 위해) flush를 수행하는 경우가 있으나 위의 테스트 결과에서 알 수 있듯이 오히려 시스템에 악영향을 주는 경우가 많다. 하드 파싱도 나쁘지만, 하드 파싱이 발생하는 도중에 DDL을 수행하는 것은 말할 수 없이 나쁘다.

2. 하드 파싱이 왕성한 시스템에서도 library cache pin 대기가 나타날 수 있다. library cache pin 뿐만 아니라 library cache 와 관련된 대기의 대부분이 과도한 하드 파싱에 의해 유발된다. 최선의 해결책은 바인드 변수를 적절히 사용하는 것이며, Cursor sharing과 같은 기법을 검토할 수도 있다.(단, Cursor sharing은 충분한 테스트를 통해서만 실제 시스템에 적용할 것을 권장한다)

[편집] 4

[편집] HW Lock 과 HW Enqueue 대기

(주)엑셈 책임컨설턴트 조동욱(ukja@ex-em.com)


II에 이어서 이번에는 HW Lock과 HW Enqueue 대기에 대하여 알아보는 part III으로, 들어가기에 앞서 HW Lock과 HW Enqueue 대기에 대해 논의하기 전에 Oracle의 Segment 관리기법에 대해 간단히 알아보자.

Segment는 Table, Index, Undo, LOB 등 Oracle이 제공하는 모든 종류의 논리적인 공간을 말한다. Segment 는 다시 Extent(확장)이라는 논리적인 요소로 나누어지는데 Extent 는 Insert 등에 따른 Segment 공간의 확장 단위이다. 가령, Segment의 기존 여유공간이 다 소모된 상태에서 추가적인 Insert가 발생하면 Oracle은 약속된 크기만큼의 Extent를 추가로 할당해서 데이터를 저장한다. 또한 Extent는 Block이라는 물리적 조각으로 이루어진다. 하나의 Extent 는 보통 여러 개의 Block으로 이루어진다. Block은 Oracle의 물리적/논리적 IO의 최소 단위이다.

Extent가 추가로 할당됨에 따라서 Oracle은 사용된 공간과 아직 사용되지 않은 공간을 구분하는 표식을 필요로 한다. 이 표식을 HWM(High Water Mark) 이라고 부른다. 이 개념을 정확하게 이해하기 위해 예를 들어 설명해보자.

기존에 이미 500 block이 할당되어 있으며 모든 Block들을 다 사용중인 테이블에 1건의 Row를 추가로 Insert 한다고 하자. 사용 가능한 공간이 없으므로 Extent가 추가로 할당되고 이에 따라 총 50 Block(1 Extent = 50 Block이라고 가정)이 Segment에 추가된다. 50개의 Block중에서 Oracle은 몇 개의 Block(여기서는 5개라고 가정)만을 Format하고 사용가능공간으로 등록한다. 즉, 전체 550 Block 중 Used Block = 500 + 5 = 505, Unused Block = 45 가 된다. 이러한 기준을 Oracle에서는 High watermark(고수위선. 이하 HWM)라고 부른다. 즉, HWM은 Segment의 전체 공간 중 사용가능 공간과 미사용 공간을 구분하는 기준이 된다. 만일 추가로 계속해서 Insert 가 이루어져서 Format된 5 block을 다 소진하게 되면 추가로 5개의 Block을 Format하고 이를 사용가능공간으로 전환하고 HWM을 이동하게 된다. 이러한 HWM 의 이동은 하나의 Extent가 모두 사용될 때까지 계속된다. 만일 하나의 Extent를 다 사용하게 되면 Oracle은 추가적으로 다시 Extent를 할당한다. 그 이후의 과정은 동일하다.

HWM을 여러 프로세스가 동시에 변경하는 것(즉 HWM을 이동시키는 것)을 막기 위한 Lock을 HW Lock이라고 부른다. 그리고, HW Lock을 획득하기 위해 대기하는 것을 HW Enqueue 대기라고 부른다.

HWM는 Segment Header Block에 그 정보가 저장되며, 기준이 되는 특정 Block의 주소에 대한 정보를 이용해 HWM을 관리한다.

HW Enqueue 대기 문제의 대부분이 대량 Insert 에 의해 발생하지만, 간혹 대량 Update 에 의한 Undo Segment 에서의 HW Enqueue 대기 현상이 문제가 되는 경우도 발생한다. Update의 경우 Table Segment의 추가확장은 크지 않지만 Undo 데이터를 생성하는 과정에서 Undo Segment의 급속한 확장이 필요하기 때문이다. Undo Segment에서 HW Enqueue 대기가 발생하는 경우에도 아래에 설명한 개념과 방법을 이용해 어느 정도의 성능 개선이 가능하다.

<참조>

HWM 의 위치를 파악하는 방법에는 여러 가지가 있다. 가장 쉬운 방법은 dbms_space.unused_space 프로시저를 사용하는 것이다. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:5350053031470 을 참조하면 이 프로시저를 이용하는 방법에 대한 자세한 방법을 알 수 있다. 해당 URL에서 구할 수 있는 show space 프로시저를 이용해 특정 테이블(Segment)의 공간사용상태를 출력해보면 다음과 같다.

SQL> create table test(object_id number);
SQL> @show_space;
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................4
Last Used Ext BlockId...................57
Last Used Block.........................3

전체 8개의 Block 중 Unused Block(미사용 Block)이 5개이므로 HWM 은 3번째 Block 다음에 존재하게 된다. 하나의 row를 Insert하면 어떻게 될까?

SQL> insert into test values(1);
SQL> commit;
SQL> @show_space;
Total Blocks..................................8
Total Bytes.............................65536
Total MBytes..................................0
Unused Blocks...............................0
Unused Bytes.................................0
Last Used Ext FileId..........................4
Last Used Ext BlockId......................57
Last Used Block...............................8 

전체 8개 Block이 다 Used Block으로 바뀌었음을 알 수 있다. 즉 HWM을 한번에 5 block 만큼 이동했음을 의미한다. HWM의 이동 크기는 tablespace 및 table 생성시 부여하는 space/storage 관련 속성에 따라 다르다.

만일 HWM이 내부적으로 그리고 물리적으로 어떻게 저장되는지 알고 싶다면 Segment Header Block을 dump 함으로써 관찰이 가능하다.

우선 dba_segments 뷰에서 header_file과 header_block 값을 이용해 Segment Header Block의 위치를 얻는다.

SQL> select header_file, header_block from dba_segments where segment_name = 'TEST';
HEADER_FILE	HEADER_BLOCK
-------------	---------------
          4	            59
SQL> alter system dump datafile 4 block 59;
User dump directory 에서 해당되는 Dump trace 파일을 열어보면 다음과 같은 정보를 확인할 수 있다.
 ...
 Extent Control Header
 -------------------------------------------------------------
 Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                 last map  0x00000000  #maps: 0      offset: 2720
     Highwater::  0x01000041  ext#: 0      blk#: 8      ext size: 8
 #blocks in seg. hdr's freelists: 0
 #blocks below: 5
 mapblk  0x00000000  offset: 0
                  Unlocked
 --------------------------------------------------------

위의 정보를 보면 0 번째(첫 번째) extent 의 8 번째 Block 에 HWM이 존재함을 알 수 있다. 또한 HWM의 물리적 위치는 DBA(Data Block Address)로 '0x01000041' 인데, 10진수로 변환한 후(16777281) 다음과 같이 file#과 block#을 얻을 수 있다.

SQL> select dbms_utility.data_block_address_file(16777281) as file_no,
         dbms_utility.data_block_address_block(16777281) as block_no
         from dual;
FILE_NO 	BLOCK_NO
---------------	--------
              4 	    65

즉 4번 datafile 의 65번 Block에 TEST Segment의 HWM이 존재한다는 의미이다.

Oracle8i 까지는 기본적인 Segment 관리 공간은 FLM(Freelist Management)에 의해 이루어졌으나, 9i 에서 ASSM(Auto Segment Space Management)이 추가되면서 전혀 다른 두 개의 공간관리기법을 갖추게 되었다. 이에 대한 내용은 Oracle Concept Guide 및 참조문서에 잘 나와있으므로 참조하기 바란다.

본 Article에서는 HW Enqueue대기를 설명하면서 필요한 경우에 한해 FLM과 ASSM 기법의 차이점에 대해 논의할 것이다.

HW Enqueue 대기는 Segment 의 급속한 공간확장이 필요한 경우에 매우 보편적으로 나타나는 대기현상이며, 극단적인 성능 저하를 야기하는 경우도 종종 발생한다. 이러한 현상은 Table Segment에 국한되지 않고 Undo Segment 등 추가적인 Extent가 필요한 경우에는 항상 나타날 수 있다.

HW Lock의 id1 = Tablespace # 이고, id2 = Segment header block의 dba 이다.

따라서 v$session_wait 의 p2, p3 정보를 이용해서 어떤 테이블스페이스의 어떤 Segment 에 대해 HW Enqueue 대기가 발생하는지 확인할 수 있다.

우선 대량의 동시 Insert 에 의해 HW Enqueue대기가 발생하는 경우를 시뮬레이션 해보자.

[테스트스크립트] -- FLM을 사용하는 테이블 스페이스의 생성

create tablespace hwm_test_tbs
datafile '/home/oracle/oradata/10gr2/ORA102/hwm01.dbf' size 100M autoextend on
extent management local uniform size 1M
segment space management manual;

-- 테스트의 효율성을 위해 1 row = 1 block 이 되게끔 생성한다. -- Block Size = 8K 이므로 7.5K Row를 생성하면 1 row = 1 block 이 된다. -- PCTFREE = 10 이므로 1 row가 8K*0.9 = 7.2K 이상이면 1 block을 차지하게 된다.

create table hwm_test(
name1 char(2000) default ' ', 
name2 char(2000) default ' ',
name3 char(2000) default ' ', 
name4 char(1500) default ' ') 
tablespace hwm_test_tbs;

-- Procedure for Insert Simulation

create or replace procedure massive_insert
is
begin
       for idx in 1 .. 5000 loop
            insert into hwm_test(name1) values(' ');
            commit;
       end loop;
end;
/
connect maxgauge/maxgauge@ora10gr2
var job_no number;
begin
       for idx in 1 .. 5 loop
           dbms_job.submit(:job_no, 'massive_insert;');
       end loop;
end;
/
commit;
exec massive_insert;
@event;

[테스트결과]

아래와 같이 HW Enqueue 대기가 광범위하게 나타남을 확인할 수 있다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
enq: HW - contention	1310	4967
buffer busy waits	8404	1786
free buffer waits	549	1051
latch: In memory undo latch	838	554
log buffer space	24	216
events in waitclass Other	212	145
log file sync	3	35
latch: cache buffers chains	178	15
SQL*Net message from client	21	9
latch: library cache pin	10	3
latch: library cache	12	1 

HW Enqueue 대기를 정확하게 해석하려면 현재 사용되고 있는 Segment 공간 관리 기법의 종류와 그 속성값을 이해해야 한다.

hwm_test_tbs 테이블스페이스를 생성한 스크립트를 보면 Segment space management = Manual로 이는 ASSM을 사용하지 않고 공간관리를 수행함을 의미한다. FLM을 사용할 경우 Oracle은 Freelist라는 방법을 사용해서 Segment space를 관리한다.

별도로 지정하지 않은 경우 Freelist 의 기본값은 1이다. 이 말은 곧, 해당 Segment 에 대해 하나의 Freelist로 공간을 관리함을 의미한다. 사용자가 Insert를 수행하면 Oracle의 Freelist로부터 하나의 Block을 할당 받아 해당 Block을 사용한다. Oracle은 해당 Block이 꽉 차면(즉 PCTFREE로 지정된 만큼 여유공간이 없으면) Freelist에서 제거한다.

Oracle은 Segment 별로 별도의 Master freelist를 할당하며 Segment 생성시 freelists 속성값에 할당된 수만큼 Process freelist를 생성한다. 기본적으로 Free block들은 Master freelist에 저장되며 Process freelist가 생성되는 시점에 Master freelist로부터 필요한 수의 Free block을 얻어온다. 특정 Insert 작업을 위해 Free block이 필요한 경우 자신에게 할당된 Process freelist에 있는 Block을 사용하며, 필요한 경우 Process list는 Master block으로부터 Free block을 다시 얻어온다. Master freelist에 더 이상 Free block이 없으면 Oracle은 추가로 Extent를 할당하고 HWM을 이동해서 Free block을 확보하게 된다.

만일 특정 Transaction이 Update나 Delete에 의해 기존에 Freelist에 없던 Block을 Free Block으로 바꾸게 되면 Transaction freelist를 생성하고, Transaction이 유지되는 동안은 가능하면 Transaction freelist를 사용한다. 사용이 끝난 Free Block은 이후 다시 Master freelist에 반환한다.

만일 프로세스의 개수에 비해 Process freelist 개수가 지나치게 작다면 freelist 에서의 경합이 발생하게 되는데 이로 인해 다양한 성능문제가 유발된다.

Freelist 는 또한 Extent 할당 시 HWM을 얼마만큼 이동시키는지 와도 관련이 있다. Oracle은 Extent 할당 시 Freelist * _BUMP_HIGHWATER_MARK_COUNT 값만큼 HWM을 이동시킨다.

_BUMP_HIGHWATER_MARK_COUNT는 HWM의 이동크기를 결정하는 히든 파라미터로 기본값은 5 이다. 따라서 Freelist = 1 인 경우에는 5 block 만큼 HWM을 이동하게 된다. 이 말은 곧 5개의 Block이 Insert시 Free block으로 활용된다는 것을 의미한다. 위의 테스트와 같이 빠른 속도로 Freelist를 소비하는 경우에는 Free Block이 빠른 속도로 소진되어 그 만큼 HWM의 이동이 자주 발생하며 특히 여러 프로세스가 동시에 HWM의 이동을 요구하는 경우에는 HW Lock의 경합이 발생하고 따라서 HW Enqueue 대기시간이 크게 증가하게 된다. 따라서 위의 테스트의 경우 Freelist = 1이라는 부적절한 기본값을 그대로 사용함에 따라 HW Enqueue 대기가 크게 나타난 것으로 결론을 낼 수 있다. Freelist 의 기본값은 부적합한 초기화 파라미터값의 대표적인 예라고 할 수 있다.

Freelist 의 값뿐만 아니라 Extent의 크기 또한 HW Lock의 경합에 영향을 미친다. Extent의 크기가 작으면 HWM의 아동시에 그 만큼 Extent 할당 작업이 빈번하게 발생하고 HW Lock을 획득하는 시간 또한 길어지기 때문이다. 따라서 Transaction의 크기를 고려해서 Extent 의 크기를 적절하게 설정하는 것이 중요하다. 대부분의 경우 Extent Management Local Uniform size 5M 정도의 설정 값을 권고하고 있다.

Freelist 값과 _BUMP_HIGHWATER_MARK_COUNT값을 합리적으로 조정하는 경우 HW Enqueue 대기가 얼마나 줄어드는지 테스트해보기로 하자.

[테스트스크립트]

-- freelist = 6으로 지정한다.

-- 또한 _BUMP_HIGHWATER_MARK_COUNT의 값을 50으로 변경한다.

create table hwm_test(
 name1 char(2000) default ' ', 
 name2 char(2000) default ' ',
 name3 char(2000) default ' ', 
 name4 char(1500) default ' ') 
storage ( freelists 6 )
tablespace hwm_test_tbs;

-- 동일한 방식으로 Insert 수행

[테스트결과]

HW Enqueue가 크게 줄어든 것을 확인할 수 있다.

EVENT	TOTAL_WAITS	TIME_WAITED
---------------------------------------	-----------	-----------
log file switch (checkpoint in complete)	32	2735
latch: In memory undo latch	730	1331
log file switch completion	19	734
log buffer space	17	557
buffer busy waits	791	259
events in waitclass Other	199	246
enq: HW - contention	269	200
latch: cache buffers chains	47	39
log file sync	3	24 

Freelists = 6으로 설정하고 _BUMP_HIGHWATER_MARK_COUNT의 값을 Transaction의 크기에 맞게 크게 조정한 경우 기본값을 사용한 경우에 비해 HW Enqueue 대기가 크게 줄어드는 것을 확인할 수 있다. 즉, 특정 Segment에서 공간확장이 급격하게 이루어지는 경향이 있다면 freelist 및 _BUMP_HIGHWATER_ MARK_COUNT값을 충분히 크게 해줌으로써 HW Enqueue 에 의한 성능 저하를 피할 수 있다. 하지만, 이 값을 불필요하게 크게 할 경우 공간의 낭비가 생길 수 있으므로 Transaction의 속성을 고려해서 적절한 값을 지정하는 것이 좋다.

(HW Enqueue 외에도 Buffer busy waits 가 크게 줄어든 것을 확인할 수 있는데 이것에 관한 내용은 Buffer Lock 및 Buffer busy waits에 관한 Article에서 논의한 바 있으므로 참조하기 바란다)

위에서 논의한 Freelist에 의한 공간관리 방법은 9i부터 추가된 ASSM을 사용하는 경우에는 더 이상 적용되지 않는다. Oracle의 Segment 공간관리를 자동화함으로써 복잡하고 이해하기 어려운 Freelist 관리 및 PCTUSED, PCTFREE 속성값 지정 등을 더 이상 불필요하게 만들었다.

ASSM을 사용하는 경우, Oracle은 Tree형태의 세 단계 Bitmap block들을 이용해 Block들의 상태를 관리한다. 또한 공간관리의 모든 과정들은 철저하게 내부적인 알고리즘만으로 이루어지고 설정 값을 바꾸는 방식으로 튜닝을 하거나 관리를 할 수도 없고 할 필요도 없게 된다. 그리고 당연한 이야기지만 HWM이 이동하는 정도 또한 내부적인 알고리즘에 의해 결정된다.

ASSM에서의 공간관리 방식에 대한 자세한 설명은 참조문서 중 Poder의 자료를 참조하기 바란다.

ASSM을 사용하는 경우, 얼마나 많은 성능 개선효과가 있는지 직접 확인해보자.

[테스트스크립트]

-- ASSM을 사용하는 테이블스페이스 생성

create tablespace hwm_test_tbs
datafile '/home/oracle/oradata/10gr2/ORA102/hwm01.dbf' size 100M autoextend on
extent management local uniform size 1M
segment space management auto;

-- 동일한 방식으로 Insert 수행

@event;

[테스트결과]

테스트결과는 매우 인상적이다. ASSM을 사용하는 경우 Freelist와 _BUMP_HIGHWATER_MARK_COUNT의 값을 충분히 크게 해주는 것과 거의 비슷한 효과가 있음을 알 수 있다.

EVENT	TOTAL_WAITS	TIME_WAITED
------------------------------	-----------	-----------
log buffer space	49	1996
buffer busy waits	609	1076
log file switch completion	15	477
log file switch (checkpoint in	8	266
                     complete)
log file sync	4	108
enq: HW - contention	158	86
events in waitclass Other	267	76
latch: cache buffers chains	28	16
SQL*Net message from client	21	6

위의 테스트결과에서 알 수 있듯이 ASSM을 사용하는 경우에는 특별한 설정이나 관리기법이 없이도 HW Lock 경합 문제가 자연스럽게 해결된다. ASSM은 부적절한 Freelist 값이나 Storage 속성값에 의한 성능저하 현상을 미연에 방지할 수 있는 좋은 솔루션이다. 다만, 공간관리를 위해 사용하는 Bitmap Block들에 의해 약간의 공간낭비가 있을 수 있으나 이점에 비하면 사소한 단점이라 할 수 있다.

ASSM의 진정한 장점은 유연성에 있다. Freelist를 사용하는 경우 Storage 속성이나 히든 파라미터를 이용해 최적화된 튜닝이 가능하지만, Transaction의 양과 크기가 변경되면 기존의 최적화된 상태가 깨어질 수 있다. 하지만 ASSM을 사용하면 처리해야 할 데이터 량의 변동 정도에 따라 동적으로 공간관리를 수행한다. 따라서 유연성과 확장성이라는 면에서 큰 장점을 가지고 있다고 할 수 있다.

지금까지의 논의 및 테스트결과를 토대로 HW Lock 경합 및 HW Enqueue 대기를 줄이는 방법을 정리하면 다음과 같이 요약할 수 있다.

1. 9i 이상이라면 ASSM을 사용한다. ASSM은 대부분의 경우에 만족할만한 결과를 보장한다. 8i 이하라면 freelists 값을 적절하게 설정하고 필요한 경우 _BUMP_HIGHWATER_MARK_COUNT속성값을 크게 지정하여 HWM의 이동을 최소화한다. 하지만 지나치게 큰 값은 부정적인 사이드 이펙트를 유발할 수 있음에 유의해야 한다.

2. 버전과 무관하게 적절한 크기의 Extent를 사용한다. 보통 5M 정도를 권장하지만 절대적인 수치가 있는 것이 아니다.

3. 8i 이상이라면 Locally Managed Tablespace를 사용한다(9i부터 LMT는 기본설정 값이다). 더불어 특별한 경우가 아니라면 Uniform Size의 Extent를 사용하며 충분한 크기의 Extent 크기를 사용한다.


[편집] 5

[편집] Oracle Wait Event를 활용한 TFM(Transaction Flow Model) 설계

(주)엑셈 수석컨설턴트 김시연(siyeon@ex-em.com)

[편집] REDO Internal 및 INSERT TFM 설계

오라클 데이터베이스에서 트랜잭션이 수행될 경우, 트랜잭션 각 단계별로 필요한 데이터 스트럭처와 리소스가 존재하며, 트랜잭션 수행 시에는 오라클 성능 정보 (Statistics) 와 대기 정보 (Event)를 발생시키게 된다.

트랜잭션 수행단계는 1. Parse 단계 2.Execute 단계 3.Commit 단계로 크게 구분할 수 있으며, 각 단계별로 필요한 데이터 스트럭처들과 리소스들이 존재하게 된다. 이번 호에서는 트랜잭션을 분석하기 위해서 필요한 REDO Internal에 대한 설명과, 트랜잭션의 처리 순서를 오라클의 대기 정보와 연관 지어, 그것을 이용하여 TFM(Transaction Flow Model) 을 설계하도록 한다. 이러한 TFM을 통하여, 현재 발생되고 있는 문제 현상이 트랜잭션의 어떤 처리 단계에 의해 발생되는지를 파악하며, 오라클 트랜잭션 처리에 대한 이해를 심도 깊게 하는 것이 이번 기사의 주 목적이다.

[편집] REDO Internal

리두(Redo)란? 리두 로그는 데이터베이스에 적용된 모든 변경사항에 대한 이력을 저장하고 있으며, 주로 3가지 목적에 의해서 사용된다.

1. 복구 (Instance Recovery 및 Media Recovery) 
2. 로그 마이너 (Log Miner) 
3. 오라클 스트림 (Oracle Stream)

이 중에서 로그 마이너와 오라클 스트림 기능은 특정 목적에 의해 필요한 경우에만 사용되므로, 리두 로그의 일반적인 목적은 데이터베이스 복구라고 해도 무방할 것이다. 오라클은 리두 로그에 대한 기록 시, 2개의 “Write Ahead Log” 룰을 사용하고 있으며, 커밋 수행 시 “Log Force at Commit”이라는 메커니즘을 이용한다. “Write Ahead Log” 룰이란 1) 데이터베이스에 적용된 모든 변경사항들은 버퍼캐시 내의 데이터 블록 버퍼에 기록되기 전에 리두 로그 버퍼에 기록되어야 하며 2) DBWR 프로세스가 버퍼캐시 내의 데이터 블록 버퍼를 데이터 파일에 기록하기 전에 LGWR 프로세스는 해당되는 리두 레코드를 리두 로그 버퍼에서 리두 로그 파일에 기록해야 한다는 것이다. “Log Force at Commit” 메커니즘을 간단히 설명하면, 커밋 리두 레코드를 포함한 트랜잭션과 관련된 모든 리두 레코드들이 온라인 리두 로그 파일에 저장될 때 커밋이 종료된다는 것이다. 이 시점에 변경된 데이터 블록 버퍼를 디스크에 기록할 필요는 없다. 이러한 메커니즘을 통해 커밋된 모든 트랜잭션들은 instance failure로부터 보호받게 된다.

Redo log file에 저장되는 정보들

리두 로그 파일에는 DML 문장(INSERT, UPDATE, DELETE, SELECT FOR UPDATE)에 의한 모든 변경사항을 포함하며, DML 문장 자체를 저장하지는 않는다. 또한 DDL 문장에 의해 영향 받는 딕셔너리 오브젝트에 대한 변경 사항을 저장하며, 오라클 9.0.1 이상에서는 DDL 문장 자체를 저장하며, recursive 문장에 의한 모든 변경 사항을 저장하게 된다.

Redo Log Block Size

리두 로그 버퍼 및 온라인 리두 로그에 기록되는 로그 블록의 크기는 DB_BLOCK_SIZE 파라미터에 의해 정의된 크기가 아니다. 리두 로그 블록 크기는 오라클 소스 코드 안에 정의되어 있으며, O/S블록 사이즈를 사용한다. 일반적으로는 512 bytes 이며, O/S에 따른 리두 로그 블록 사이즈는 표2.1 을 참고하기 바란다.

그림:47p-1.png

현재 사용하고 있는 O/S 가 표 2.1에 없는 경우에는 아래의 쿼리를 이용하여 현재 사용하는 데이터베이스의 리두 로그 블록 사이즈를 확인하는 것이 가능하다.

select max(lebsz) from sys.x$kccle;
MAX(LEBSZ)
----------
     1024

Redo Log의 구성 및 Redo Structure Hierarchy

각각의 리두 로그는 헤더와 리두 레코드로 구성되어 있으며, 리두 로그 파일에 순차적으로 기록된다. 리두 레코드는 리두 레코드 헤더와 하나 이상의 Change Vector들로 구성되어있다. 각각의 리두 레코드는 atomic한 변경에 대한 undo와 redo를 저장한다. 그림 2.1은 리두 스트럭처에 대한 계층 구조를 나타내며, 표 2.2는 1개의 인덱스를 가진 테이블에 1건을 INSERT한 후에 커밋한 내용이 어떠한 형식으로 리두 로그파일에 기록되는지를 나타낸다.

그림:47p-2.png

DUMP OF REDO FROM FILE '/home1/oracle/oradata/TR/redoTR01a.dbf'
 Opcodes *.*
 DBA's: (file # 0, block # 0) thru (file # 65534, block # 4194303)
 RBA's: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCN's scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER: [리두 로그 파일에 대한 헤더 정보]
             Software vsn=153092096=0x9200000, Compatibility Vsn=153092096=0x9200000
              Db ID=1965088377=0x7520de79, Db Name='TR'
              Activation ID=1965103993=0x75211b79
              Control Seq=47=0x2f, File size=51200=0xc800
              File Number=1, Blksiz=1024, File Type=2 LOG
….
REDO RECORD - Thread:1 RBA: 0x000007.00000023.00b0 LEN: 0x01e8 VLD: 0x01
SCN: 0x0000.000121fd SUBSCN:  1 01/16/2006 15:40:23 [리두 레코드 헤드]
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.000121dc SEQ:  1 OP:5.2
ktudh redo: slt: 0x001b sqn: 0x00000037 flg: 0x0012 siz: 88 fbi: 0
            uba: 0x00801ddc.0015.29    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x00801ddc SCN:0x0000.000121db SEQ:  1 OP:5.1
ktudb redo: siz: 88 spc: 4650 flg: 0x0012 seq: 0x0015 rec: 0x29
            xid:  0x0006.01b.00000037  
ktubl redo: slt: 27 rci: 0 opc: 11.1 objn: 5858 objd: 5858 tsn: 3
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00801ddc.0015.28 
prev ctl max cmt scn:  0x0000.0000cd6e  prev tx cmt scn:  0x0000.0000cd71 
KDO undo record:
KTB Redo 
op: 0x03  ver: 0x01  
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA  bdba: 0x00c00012  hdba: 0x00c00011
itli: 3  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1)
CHANGE #3 TYP:0 CLS: 1 AFN:3 DBA:0x00c00012 SCN:0x0000.000121fd SEQ:  1 OP:11.2
KTB Redo 
op: 0x01  ver: 0x01  
op: F  xid:  0x0006.01b.00000037    uba: 0x00801ddc.0015.29
KDO Op code: IRP row dependencies Disabled
  xtype: XA  bdba: 0x00c00012  hdba: 0x00c00011
itli: 3  ispac: 0  maxfr: 4863
tabn: 0 slot: 1(0x1) size/delt: 55
fb: --H-FL-- lb: 0x3  cc: 28
null:
01234567890123456789012345678901234567890123456789012345678901234567890123456789
N--NNNNNNNNNNNNNNNNNNNNNN-N-
col  0: *NULL*
col  1: [ 2]  c2 02
col  2: [ 2]  c1 02
col  3: *NULL*
col  4: *NULL*
...
col 25: [10]  6b 73 79 5f 6d 6f 64 75 6c 65
col 26: *NULL*
col 27: [10]  6b 73 79 5f 61 63 74 69 6f 6e
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:  0 OP:5.20
session number   = 9
serial  number   = 24
transaction name = 
REDO RECORD - Thread:1 RBA: 0x000007.00000023.0298 LEN: 0x0100 VLD: 0x01
SCN: 0x0000.000121fd SUBSCN:  1 01/16/2006 15:40:23
CHANGE #1 TYP:0 CLS:28 AFN:2 DBA:0x00801ddc SCN:0x0000.000121fd SEQ:  1 OP:5.1
ktudb redo: siz: 112 spc: 4560 flg: 0x0022 seq: 0x0015 rec: 0x2a
            xid:  0x0006.01b.00000037  
ktubu redo: slt: 27 rci: 41 opc: 10.22 objn: 5860 objd: 5860 tsn: 3
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index undo for leaf key operations
KTB Redo 
op: 0x04  ver: 0x01  
op: L  itl: xid:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x0000.000120bc
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0xc00021 block=0x00c00022
purge leaf row
key :(14):  02 c2 02 02 c1 02 ff 06 00 c0 00 12 00 01
CHANGE #2 TYP:0 CLS: 1 AFN:3 DBA:0x00c00022 SCN:0x0000.000120bd SEQ:  1 OP:10.2
index redo (kdxlin):  insert leaf row
KTB Redo 
op: 0x01  ver: 0x01  
op: F  xid:  0x0006.01b.00000037    uba: 0x00801ddc.0015.2a
REDO: SINGLE / -- / -- 
itl: 2, sno: 1, row size 18
insert key: (14):  02 c2 02 02 c1 02 ff 06 00 c0 00 12 00 01
REDO RECORD - Thread:1 RBA: 0x000007.00000023.0398 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.000121fe SUBSCN:  1 01/16/2006 15:40:23
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.000121fd SEQ:  1 OP:5.4
ktucm redo: slt: 0x001b sqn: 0x00000037 srt: 0 sta: 9 flg: 0x2 
ktucf redo: uba: 0x00801ddc.0015.2a ext: 21 spc: 4446 fbi: 0 
REDO RECORD - Thread:1 RBA: 0x000007.00000023.00b0 LEN: 0x01e8 VLD: 0x01
SCN: 0x0000.000121fd SUBSCN:  1 01/16/2006 15:40:23

그림:48p-1.png

Redo Byte Address (RBA)

모든 리두 레코드는 10 bytes 길이의 RBA를 가지며, RBA는 리두 레코드의 시작 주소를 의미한다. RBA는 3개의 필드로 구성되어 있으며, 1) 리두 로그 시퀀스 번호 (e.g 0x7)-V$LOG.SEQUENCE#와 동일함 - 2) 리두 로그 안에서의 블록 번호 (e.g 0x23) 3) 블록내의 옵셋 (e.g 0xb0)을 의미한다.

System Change Number (SCN)

System Change Number는 System Commit Number라고도 불리며, 데이터베이스의 COMMITED된 버전을 명시한다. SCN은 읽기 일관성을 위해 사용되며, 리두 로그 레코드 순서화와 복구를 위해서도 사용된다. SCN은 2개의 필드로 구성되어 있으며, 1) SCN Wrap (2bytes, e.g 0x00) 2) SCN Base (4bytes, e.g 000121fd)를 의미한다. SCN Base는 새로운 SCN이 할당될 때 마다 증가하며, SCN Wrap은 SCN Base에 해당되는 숫자가 4bytes의 범위를 넘어설 때 증가하게 된다.

Change Vector

Change Vector는 싱글 데이터 블록에 대한 변경사항을 기록하며, undo header, undo block, data segment header, data block의 변경사항을 기록한다. Change Vector는 데이터 블록 버퍼가 변경되기 전에 PGA 내부에 생성되며 1) header 2)변경된 레코드 길이의 배열 3)변경된 레코드 배열로 구성되어 있다.

Change Vector Header

모든 Change Vector는 하나의 헤더를 가지고 있으며, 표 2.6에 각 필드의 설명을 기술한다.

REDO RECORD - Thread:1 RBA: 0x000007.00000023.00b0 LEN: 0x01e8 VLD: 0x01
SCN: 0x0000.000121fd SUBSCN:  1 01/16/2006 15:40:23
CHANGE #1 TYP:0 CLS:27 AFN:2 DBA:0x00800059 SCN:0x0000.000121dc SEQ:  1 OP:5.2
CHANGE #2 TYP:0 CLS:28 AFN:2 DBA:0x00801ddc SCN:0x0000.000121db SEQ:  1 OP:5.1
CHANGE #3 TYP:0 CLS: 1 AFN:3 DBA:0x00c00012 SCN:0x0000.000121fd SEQ:  1 OP:11.2

그림:48p-2.png

Block Classes

Change Vector Header의 CLS가 의미하는 블록 클래스는 X$BH.CLASS와 동일하며, 표 2.7에 오라클 9i 기준의 각 블록 클래스 번호의 의미를 설명한다. 만일 현재 사용하고 있는 데이터베이스가 오라클 9i 가 아닌 경우에는 V$WAITSTAT 뷰 정의를 이용하여 블록 클래스의 번호에 대한 의미를 확인하는 것이 가능하다.

SELECT view_definition FROM v$fixed_view_definition WHERE view_name=’GV$WAITSTAT’

위 쿼리 결과로 출력되는 decode 문장을 이용하여 블록 클래스 번호의 의미를 확인

SELECT decode( indx , 1 , 'data block' , 2 , 'sort block' , 3 , 'save undo block' , 
                   4 , 'segment header' , 5 , 'save undo header' , 6 , 'free list' ,
                   7 , 'extent map' , 8 , '1st level bmb' , 9 , '2nd level bmb' , 
                  10 , '3rd level bmb' , 11 , 'bitmap block' , 12 , 'bitmap index block' , 
                  13 , 'file header block' , 14 , 'unused' , 15 , 'system undo header' , 
                  16 , 'system undo block' , 17 , 'undo header' , 18 , 'undo block' ) 
FROM   x$kcbwait 
WHERE  indx!=0

그림:49p-1.png

Database Block Address (DBA)

모든 데이터베이스 블록은 상대 파일 번호와 블록 번호로 구성된 4 bytes의 Database Block Address (DBA)를 가진다. DBA의 상위 10 bits는 상대 파일 번호를 의미하며, 하위 22 bits는 블록 번호를 의미한다. 오라클에서 제공하는 DBMS_UTILITY 패키지를 이용하여 16진수로 표시되는 DBA를 손 쉽게 상대 파일 번호와 블록 번호를 계산하는 것이 가능하다.

e.q) DBA가 00C00012인경우
SQL> set serveroutput on
DECLARE
             l_dba   	NUMBER := TO_NUMBER ('00C00012','XXXXXXXX');
             l_file  	NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (l_dba);
             l_block	NUMBER := DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (l_dba);
BEGIN
             DBMS_OUTPUT.PUT_LINE ('File  : '||l_file);
             DBMS_OUTPUT.PUT_LINE ('Block : '||l_block);
END;
File  : 3
Block : 18
PL/SQL procedure successfully completed.

Operation Codes

블록에 대해 수행된 Operation을 표현하며, 오라클 9i 기준으로 130여 개의 OP 가 존재한다. 각각의 Operation은 layer code와 sub code (e.g 11.2)로 구성된다. 표 2.8~표2.10 에서는 row operation 및 index operation에 대한 layer code와 sub code들을 기술한다.

그림:49p-2.png 그림:49p-3.png

Transaction Identifier (XID)

언두 세그먼트 헤더의 트랜잭션 테이블내의 슬롯이 할당되면서부터 명시적으로 트랜잭션이 시작되었다고 볼 수 있다. XID라고 불리는 transaction identifier는 이 위치를 가리키고 있으며, usn.slot.wrap 필드로 구성된다.

(usn=언두 세그먼트 번호, slot=트랜잭션 슬롯 번호, wrap=시퀀스). 

Undo Block Address (UBA)

UBA는 언두 블록내의 변경된 주소를 나타내며 7 bytes의 길이를 갖는다. UBA는 udba.seq.slot 필드로 구성된다.

(udba=언두 블록의 DBA, seq=시퀀스번호, slot=블록내의 레코드 번호).

Transactions

트랜잭션을 시작하는 경우, 가장 먼저 언두 세그먼트 바인딩을 수행하여 언두 세그먼트를 할당 받고, 언두 세그먼트 헤더내에 있는 트랜잭션 테이블에 존재하는 슬롯을 할당 받게 된다(OP: 5.2). 그 이후 transaction identifier (XID)를 해당 트랜잭션에 할당 받은 후에, 해당 XID에 대해 exclusive transaction (TX) enqueue를 획득하게 된다. 그 이후에 언두 블록을 할당 받게 된다(OP: 5.1). 그 이후 데이터 블록에 대한 변경이 발생하게 되고, 커밋을 통해 트랜잭션을 완료하게 된다(OP: 5.4). 이러한 일련의 과정에 대해 리두 로그와 언두 세그먼트 헤더, 언두 블록, 데이터 블록의 변화사항의 순서를 표 2.11을 통해 확인해 보자.

그림:50p-1.png

지금까지의 설명을 통해서, 트랜잭션 수행 시에 발생되는, 리두 로그내의 리두 레코드, Change Vector, Operation Code, 언두 헤더의 트랜잭션 테이블의 슬롯, 언두 블록 및 데이터 블록의 변화사항에 대한 기본적인 개념 정립이 되었다고 생각 된다. 이제 지금까지의 내용을 정리하는 의미에서 인덱스가 1개 있는 테이블에 대한 Update 수행 시 생성되는 리두 로그 파일의 내용을 Dump 하여 어떠한 변화 사항이 발생하는지 살펴보도록 하자.

CREATE TABLE t1 (c1 NUMBER);
CREATE INDEX i1 ON t1 (c1);
INSERT INTO t1 VALUES(1);
COMMIT;
테스트 #1) UPDATE t1 SET c1=2 WHERE c1=1;       (Before 1, After 2) 
테스트 #2) UPDATE t1 SET c1=2 WHERE c1=2;       (Before 2, After 2)
테스트 #3) UPDATE t1 SET c1=NULL WHERE c1=2      (Before 2, After NULL) 
테스트 #4) UPDATE t1 SET c1=2 WHERE c1 IS NULL   (Before NULL, After 2)

위의 테스트(#1~#4)를 수행한 결과, 각 테스트마다 몇 개의 리두 레코드와 Change Vector가 발생하였을 지를 생각한 후에 표 2.12의 리두 로그 덤프 결과를 확인해 보기 바란다.

그림:51p-1.png 그림:51p-2.png

Physiological Logging

데이터베이스의 변경사항을 로깅하는 방법에는 크게 3가지가 가능하다.

1) Logical Logging 2) Physical Logging 3) Physiological Logging. 

여기서 Logical Logging이란 Operational Logging 이라고도 불리며 Insert, Delete, Update, Select for Update 와 같은 Operation을 로깅하는 방법이며, Physical Logging이란 변경된 모든 블록의 이전, 이후 이미지를 로깅하는 방법이며, Physiological Logging 이란 Operation 코드와 블록의 변경사항에 대해서 로깅하는 방법이다. 오라클의 로깅 방법은 Physiological Logging 방법을 사용하고 있으며, 표 2.13은 몇 가지 예를 나타낸다.

그림:51p-3.png

Redo Write Triggers

LGWR 프로세스는 아래의 4가지 경우에, 로그 버퍼의 내용을 리두 로그 파일에 기록하게 된다. 로그 버퍼의 내용을 리두 로그 파일에 기록하는 방식은 “background write”과 “sync write”으로 구분되며, 커밋 수행과 관련된 기록 이외에는 모두 “background write” 방식으로 기록된다. LGWR 프로세스가 “sync write” 방식으로 기록되는 동안에 유저 프로세스는 log file sync 대기 이벤트를 대기하게 된다.

A. LGWR 프로세스가 idle한 경우, LGWR 프로세스는 rdbms ipc message 란 idle 대기이벤트를 대기하며, 해당 대기이벤트의 timeout은 3초이다. 만일 3초(timeout)가 경과한 후에 wake up 해서 리두 로그 버퍼에 리두 로그파일로 기록해야 할 내용이 있으면 “background write”를 수행한다.

B. 오라클 프로세스는 로그 버퍼의 공간을 할당 받을 때 마다, 현재 사용된 로그 버퍼 블록의 수를 계산한다. 만일, 현재 사용된 로그 버퍼 블록 수가 _LOG_IO_SIZE 파라미터에 정의된 블록 수 이상이고, LGWR 프로세스가 idle한 경우에 LGWR 프로세스는 “background write”를 수행하도록 포스트 된다. _LOG_IO_SIZE 의 기본설정 값은 로그버퍼 블록의 1/3 또는 1 Mb 중 적은 값으로 설정된다(단위는 로그 블록 수).

C. 트랜잭션이 커밋될 때, 리두 레코드에는 커밋에 대한 change vector가 생성되며, 해당 로그 레코드를 포함하는 리두 레코드를 디스크로 기록하기 전 까지는 트랜잭션 복구가 불가능하다. 따라서, 커밋을 수행한 프로세스는 LGWR 프로세스를 포스트 한 후에, log file sync 대기 이벤트를 대기하게 된다. 이러한 기록 방식을 “sync write”라고 한다. 따라서, 커밋을 과다하게 발생시키는 오라클 프로세스는 log file sync 대기이벤트의 대기 시간이 길어지게 된다. 하지만 리커시브 콜 (e.g Procedure 코드 안에서 수행되는 콜) 내부에서 수행된 커밋은 최종 결과를 유저에게 리턴 하기 전까지는 해당 리두에 대한 sync를 보장할 필요가 없다. 즉 Procedure 내부에서 FOR LOOP를 이용하여 건건이 커밋을 하는 경우에도 log file sync 대기이벤트는 거의 발생하지 않는다. PRO*C 프로그램 및 PL/SQL을 이용한 테스트를 통해, 이 부분을 자세히 다루도록 한다.

그림:52p-1.png

PRO*C Source 일부 및 테스트 결과

    for (lcnt=0; lcnt<10000; lcnt++) {
         EXEC SQL INSERT INTO t1  VALUES ('C1','C2', 'C3', 'C4');
         EXEC SQL COMMIT;
    }

[10046 Trace Output]

Event	Count	Elapse
----------------------------	-------	-------
log file sync	10,013	59.35
SQL *Net message from client	20,002	43.76
SQL *Net message to client	20,002	0.07
latch free	5	0.05 

[MaxGauge 툴을 통한 세션 성능 지표 추이 분석]

그림:52p-2.png


PL/SQL Source 일부 및 테스트 결과

     FOR  i IN 1..10000 LOOP
          INSERT INTO t1 VALUES('C1','C2','C3','C4');
          COMMIT;
     END LOOP;

[10046 Trace Output]

 Event	Count	Elapse
----------------------------	-------	-------
SQL *Net message to client	13	0.00
log file sync	1	0.80

[MaxGauge 툴을 통한 세션 성능 지표 추이 분석]

그림:52p-3.png

위의 결과를 통해 알 수 있듯이, PRO*C 프로그램으로 수행하였을 경우의 log file sync 대기 이벤트의 대기 시간은 59.35초이며 PL/SQL 프로그램으로 수행하였을 경우의 log file sync 대기 이벤트의 대기시간은 0.8초밖에 소요되지 않았다. 이러한 원인을 리두와 관련된 오라클의 성능 지표를 통해 확인 해 본 결과, PRO*C 프로그램에서는 커밋을 수행하였을 경우 LGWR 프로세스에게 커밋과 관련된 레코드를 디스크로 기록하기를 요청할 때 발생되는 성능 지표인 redo sync writes 의 횟수가 커밋 횟수와 동일하게 발생하고, 이에 따라 redo sync writes를 수행할 때 발생되는 시간을 나타내는 redo sync time(1/100초 단위)이 지속적으로 소요되는 것을 확인할 수 있다. 하지만 PL/SQL 프로그램의 경우 redo sync writes 는 PL/SQL 내부의 LOOP가 완료되는 시점에 1회 발생하였을 뿐이다. 이러한 이유로 인해 PL/SQL 내부에서 수행되는 LOOP INSERT 의 처리 시간은 PRO*C 프로그램에서 수행되는 LOOP INSERT의 처리 시간보다 수십 배 이상 빠르게 된다. 따라서, 현재의 배치 프로그램의 구조가 단순 LOOP INSERT & COMMIT을 수행하는 구조로 된 PRO*C 프로그램의 경우에는 PL/SQL로 변경하는 것이 성능상의 효과를 볼 수 있으며, 만일 PL/SQL로 변경하는 것이 여의치 않은 경우에는 PRO*C 프로그램 내에서 PL/SQL 블록을 사용하여 코딩 하는 것이 바람직하다 (이러한 경우 precompiler compiler 옵션에 SQLCHECK=SEMANTICS userid=username/password를 추가하여야 한다).

EXEC SQL EXECUTE
      BEGIN
          FOR I in 1..10000 LOOP
               INSERT INTO t1 VALUES ('C1','C2', 'C3', 'C4');
               COMMIT;
          END LOOP;
      END;
END-EXEC;

D. 오라클 8i부터는 DBWR 프로세스가 LGWR 프로세스의 on-disk RBA 보다 큰 high-RBA를 가진 블록을 기록하려고 할 때, 해당 블록을 deferred write queue 에 위치시킨 후에 LGWR 프로세스를 포스트 하여 sync를 맞추도록 하며, DBWR 프로세스는 기록 작업을 계속하여 수행하게 된다. 오라클 8i 이전에서는 이러한 경우 DBWR 프로세스가 log file sync 대기 이벤트를 대기하였다.

지금까지 오라클의 트랜잭션 처리를 이해하기 위한 기본적인 지식에 대한 학습이 이루어졌다고 볼 수 있다. 이제부터는 본격적으로 TFM을 설계해보기로 하자.

[편집] TFM(Transaction Flow Model) 설계

일반적인 Transaction 처리 순서

오라클의 트랜잭션 처리 순서는 크게 6단계로 구분된다.

1. 트랜잭션과 관련된 블록들에 대해 buffer lock 을 설정한다.
2. PGA 내부에 change vector들을 생성한다.
3. PGA 내부에 리두 레코드를 생성한다.
4. 로그 버퍼에 필요한 공간을 계산한다.
5. 로그 버퍼에 공간을 할당한다.
6. 버퍼 캐시내의 데이터 블록을 변경한다. 

Transaction 처리와 관련된 리소스

TFM 설계를 위한 트랜잭션 상세 처리 순서에 앞서, 트랜잭션 처리와 연관된 리소스를 정의해 볼 필요가 있다. 이번 호에서는 INSERT Operation 수행 시에 연관된 리소스를 확인해 보자. INSERT Operation이 수행되면, 레코드를 저장할 데이터블록들(1)이 필요하며, 이전 이미지를 저장하기 위한 언두 블록들(2), 트랜잭션에 대한 내용을 저장하기 위한 언두 헤더 블록(3)내의 Undo Transaction Table의 슬롯, 트랜잭션 복구를 위해 리두 및 언두의 내용을 저장하기 위한 로그 버퍼내의 공간이 기본적으로 필요하다.

또한 데이터 블록은 크게 Cache/Transaction/Data Layer로 구분할 수 있으며, 트랜잭션 수행 시에는 데이터 블록내의 Transaction Layer에 존재하는 ITL이 필요하다. (1)~(3)번에 해당되는 블록에 변경사항을 기록하기 위해서는 해당 버퍼(버퍼캐시에 로딩이 되어야 작업이 가능하므로, 버퍼캐시에 로딩된 블록은 버퍼라고 한다)에 대한 buffer lock이 필요하며, 트랜잭션과 관련된 락(Enqueue)이 필요하게 된다. INSERT시에 필요한 Enqueue는 기본적으로 TX, TM, US Enqueue가 필요하며, 경우에 따라서, SQ, HW, ST Type의 Enqueue가 부가적으로 필요할 경우가 있다. 만일 (1)~(3)에 해당되는 블록이 버퍼캐시 내에 존재하지 않을 경우에는 O/S의 I/O 리소스 사용을 통해 버퍼캐시 내에 해당 블록을 로딩하는 작업이 필요하다. 이 경우에 버퍼 캐시와 관련된 버켓, 버켓에서 관리되는 hash chain list, 해당 hash chain list를 관리하기 위한 CBC (Cache Buffers Chains) 래치가 필요하며, 버퍼캐시의 replace management를 관리하기 위한 LRU/LRUW 리스트 및 해당 리스트를 관리하기 위한 cache buffers lru chain 래치가 필요하다. 버퍼캐시와 관련된 리소스 부분은 이번 호의 “Buffer Lock” 기사를 참조하기 바라며, 보다 상세한 내용을 원하는 경우에는 “OWI를 활용한 오라클 진단 & 튜닝” [McGraw-Hill Korea] 의 168p~178p를 참고하기 바란다. 만일 해당 블록을 로딩할 프리 버퍼가 버퍼캐시에 존재하지 않는 경우에는 DBWR 프로세스의 I/O 리소스를 사용하여, LRUW 리스트에 해당되는 버퍼들의 내용을 디스크로 기록하는 작업이 부가적으로 필요하게 된다. 트랜잭션 종료를 위해 커밋을 수행을 하게 되면, LGWR 프로세스의 I/O 리소스를 사용하여, 로그 버퍼의 내용을 리두 로그 파일에 기록하는 작업을 하게 되며, 이 작업이 완료되면, INSERT의 변경사항에 대한 트랜잭션 리커버리가 가능한 상태가 된다.

TFM 설계를 위한 Transaction 상세 처리 순서

앞서 살펴본 일반적인 트랜잭션 처리의 6단계만으로는 TFM을 설계하는 것이 불가능하므로, 보다 상세한 레벨로 세분화 하여, 각 단계 마다 필요한 리소스와 리소스를 획득할 때 발생 가능한 대기이벤트를 연관 지어, 트랜잭션 처리의 “Wait Point” 가 어느 부분인지를 확인해 보도록 하자. 이번 호에서는 INSERT 와 관련된 TFM만을 설계한다.

다른 DML과 마찬가지로 INSERT를 수행하게 될 경우에 오라클은 크게 PARSE와 EXECUTE 단계를 수행하게 된다. PARSE 단계를 정의하는 것도 중요한 부분이지만, 이번 기사의 성격과는 다소 맞지 않는 부분이 있으므로, 이 부분은 생략하고, EXECUTE 단계만을 정의하기로 한다. PARSE 작업이 완료되고 EXECUTE 작업을 시작하게 되면, 트랜잭션과 관련된 데이터블록, 언두 블록, 언두 헤더 블록에 대해 buffer lock을 설정하게 된다. 만일 buffer lock을 설정하는 작업이 실패하는 경우 buffer busy waits 대기 이벤트를 대기하게 된다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

데이터블록에 대한 buffer busy waits

Class#1이 의미하는 데이터 블록은 테이블/인덱스 블록을 통칭하므로, obj#를 이용하여 해당 블록이 테이블에 해당되는 블록인지, 인덱스에 해당되는 블록인지 확인이 가능하다.

WAIT #1: nam='buffer busy waits' ela= 5444 file#=13 block#=13498 class#=1 obj#=54867 
WAIT #1: nam='buffer busy waits' ela= 264 file#=13 block#=13623 class#=1 obj#=54868

언두 헤더 블록에 대한 buffer busy waits

WAIT #1: nam='buffer busy waits' ela= 130 file#=6 block#=937 class#=69 obj#=0

언두 블록에 대한 buffer busy waits

WAIT #1: nam='buffer busy waits' ela= 2041 file#=8 block#=13350 class#=50 obj#=0

10046 Trace 결과를 통해 buffer busy waits 대기이벤트의 발생 순서는 데이터 블록(테이블), 데이터 블록(인덱스), 언두 헤더 블록, 언두 블록 순서로 밝혀졌다. 필자의 생각으로는 언두 바인딩을 먼저 수행하므로, 언두 헤더 블록에 먼저 buffer lock을 설정한다고 생각하였으나, 잘못된 생각이었다. 즉 CLASS#1(data block) -> CLASS#15+2r(Undo Header Block)->CLASS#16+2r(Undo Block) 클래스 순으로 buffer lock을 설정하게 되는 것이다. 여기서 하나 생각해볼 것이 있다. 만일 트랜잭션을 수행하는 프로세스가 동일 블록 클래스에 해당되는 2개 이상의 데이터 블록에 대한 변경이 필요하다면 어떤 식으로 수행이 될까? 하는 것이다. 만일 2개 이상의 데이터 블록에 모두 buffer lock을 설정하려고 시도한다면, 동시 트랜잭션이 많은 환경에서는 필연적으로 데드-락이 발생하는 것은 자명한 일일 것이다.

그림:54p.png

따라서 2개 이상의 데이터 블록에 대한 변경이 필요한 경우에는 첫 번째 블록에 대해 buffer lock을 설정한 후 변경 작업을 완료한 후에 buffer lock을 release 하며, 다음 데이터 블록에 대해 buffer lock을 설정하는 작업을 하게 될 것이다. 즉 한 시점에는 동일 클래스에 대해서는 1개의 buffer lock을 설정한다는 것이다. 만일 동일 오브젝트에 대한 동시 트랜잭션이 과다하게 수행하여 buffer busy waits 대기이벤트가 지속적으로 발생하게 될 경우, buffer deadlock 대기 이벤트가 발생할 수 있으나, 지속시간은 아주 짧은 것이 일반적이므로, 크게 문제 되지 않는다.

buffer deadlock 대기 이벤트 발생 예

WAIT #1: nam='buffer busy waits' ela= 151 file#=10 block#=8384 class#=1 obj#=53765 
WAIT #1: nam='buffer busy waits' ela= 787 file#=10 block#=8428 class#=1 obj#=53766 
WAIT #1: nam='buffer deadlock' ela= 5 dba=41951468 class*10+mode=12 flag=1073750016
obj#=53766 
WAIT #1: nam='buffer busy waits' ela= 97 file#=6 block#=185 class#=49 

그렇다면, INSERT Operation 처리 시에는 위에 언급된 3개의 블록클래스에 대한 buffer lock 만을 설정하면 되는 것일까? 잠시만 생각해보면, INSERT를 하기 위한 프리 블록을 찾기 위해 FREE LIST를 검색할 필요성이 있으며, FREE LIST는 세그먼트 헤더에서 관리되므로, 세그먼트 헤더 블록에 대한 접근이 필요하게 된다. FREE LIST를 검색하는 과정에서 세그먼트 헤더 블록에 대한 buffer lock 설정이 필요하며, INSERT 수행 결과, 해당 프리 블록의 사용공간이 증가하여, PCTUSED (Free List Management -FLM- 방식)로 지정된 임계치를 넘어설 경우에는 FREE LIST의 변경을 위해 세그먼트 헤더 블록에 대한 buffer lock을 설정해야 한다. FREE LIST에 대한 자세한 설명은 이번 호의 “Right Hang Index 현상의 해결과 그 Side Effect” 기사를 참고하기 바란다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

WAIT #1: nam='buffer busy waits' ela= 11 file#=13 block#=17487 class#=1 obj#=54867 
WAIT #1: nam='buffer busy waits' ela= 1721 file#=13 block#=17487 class#=1 obj#=54867 
WAIT #1: nam='buffer busy waits' ela= 452 file#=13 block#=12 class#=4 obj#=54867 

위의 트레이스 결과에서도 알 수 있듯이, buffer busy waits 대기이벤트를 대기하는 순서는 데이터 블록, 세그먼트 헤더 블록 순이다. 지금까지의 결과를 토대로 pin 을 설정하는 순서는 CLASS#1(data block) -> CLASS#4(Segment Header Block) -> CLASS#15+2r(Undo Header Block)->CLASS#16+2r(Undo Block) 클래스 순이라는 것을 확인할 수 있다.

지금까지의 설명의 전제 조건은 트랜잭션과 관련된 모든 블록들이 버퍼캐시에 존재한다는 가정에서였다. 하지만, 만일 트랜잭션과 관련된 블록들이 버퍼캐시에 존재하지 않는다면, 디스크 I/O를 통해 관련 블록들을 버퍼 캐시로 로딩하는 작업이 필요하게 된다. 오라클에서 디스크 I/O를 통해 데이터 블록을 버퍼 캐시로 로딩하는 작업에는 상당히 많은 오퍼레이션이 수행되어야 하지만 여기서는 일반적으로 알려진 주요 오퍼레이션만을 정의하도록 한다.

A. 트랜잭션과 관련된 블록들이 현재 버퍼 캐시에는 존재하지 않지만, 다른 프로세스에 의해, 버퍼캐시로 로딩되고 있을 수가 있으므로, 해당 블록들이 버퍼 캐시로 로딩되고 있는지를 검사한다. 만일 다른 프로세스에 의해 해당 블록이 디스크에서 버퍼캐시로 적재중인 경우에는 로딩이 완료될 때까지 read by other session 대기이벤트를 대기한다. 그렇지 않을 경우에는 B번 작업을 수행한다.

WAIT #1: nam='read by other session' ela= 14681 file#=10 block#=12893 class#=1 obj#=53764

B. LRU 리스트를 Cold End부터 검색하여 버퍼 헤더의 Touch Count가 1 이하인 블록을 검색한다. 만일 LRU 리스트 검색을 위해 필요한 cache buffers lru chains 래치를 획득하지 못할 경우 latch: cache buffers lru chain 대기 이벤트를 대기하게 된다.

WAIT #1: nam='latch: cache buffers lru chain' ela= 88162 address=-4611686018029690888  
number=117 tries=1

만일 LRU 리스트의 Cold End부터 검색을 한 결과, 원하는 프리 버퍼를 찾을 수 없을 경우에는 DBWR 프로세스를 포스트 하여, LRUW 리스트에 해당되는 버퍼들을 디스크로 Flush 하여 프리 버퍼로 만드는 작업을 수행하도록 요청한다. DBWR 프로세스가 해당 작업을 수행하는 동안 트랜잭션을 수행하는 프로세스는 free buffer waits 대기이벤트를 대기한다.

WAIT #1: nam='free buffer waits' ela= 1005576 p1=16 p2=1598 p3=3 

C. B번 작업을 통해, 트랜잭션과 관련된 블록들을 로딩하기 위한 프리 블록을 할당 받았다면, 해당 블록의 DBA(Data Block Address) 와 블록 클래스를 이용하여, 해시 함수를 수행하여, 적절한 해시 버켓을 선정한 후, cache buffers chains 래치를 획득한 후 hash chain에 해당 블록 헤더를 등록하고 buffer lock을 설정한 후, 디스크 I/O 콜을 통해, 관련 블록을 버퍼캐시로 로딩하는 작업을 수행한다. 로딩 작업이 완료될 때까지 프로세스는 싱글 블록 I/O 콜에 대한 대기 이벤트인 db file sequential read 를 대기하게 되며, 이 시점까지, 해당 블록 헤더에 대한 v$bh.state의 값은’read’로 설정된다. 단순 INSERT인 경우 멀티 블록 I/O 콜은 수행하지 않으므로, db file scattered read 대기이벤트가 발생하지는 않는다.

WAIT #1: nam='db file sequential read' ela= 211 file#=10 block#=8316 blocks=1 obj#=53764

여기까지 문제 없이 수행되었다면, 트랜잭션과 관련된 버퍼들에 대한 buffer lock 설정이 모두 수행되었다고 볼 수 있다. 하지만, 지금까지 설명한 내용 중에는 트랜잭션 처리와 관련된 중요한 부분이 한가지 빠져 있다. “Transaction” 섹션에서 설명된 언두 세그먼트 바인딩 부분이다. 트랜잭션 처리를 위해 언두 세그먼트 바인딩을 할 때 어떤 경합이 예상되는가? 언두 세그먼트를 사용하게 될 경우, 오라클은 인스턴스가 기동될 때 단지 10개의 언두 세그먼트 만을 생성하게 된다. 언두 세그먼트는 앞서 언급한 것처럼 트랜잭션과 일대일로 매칭되므로, 만일 11번째 트랜잭션이 발생하게 되면 SMON 프로세스는 새로운 언두 세그먼트를 생성한 후 온라인 시킨 후 UNDO$ 테이블에 신규 생성된 언두 세그먼트를 등록하게 된다. 트랜잭션을 수행하는 프로세스는 UNDO$ 테이블을 검색하여 언두 세그먼트 바인딩을 하게 된다. 만일, 동시 트랜잭션이 폭주하는 경우에는 언두 세그먼트 바인딩 시에 경합이 발생하게 되며, 이러한 경우 트랜잭션을 수행하는 프로세스는 enq: US -contention 대기이벤트를 겪게 된다. 또한 새로 생성된 언두 세그먼트에 대한 블록을 버퍼 캐시로 로딩하기 위해 DISK I/O 와 관련된 대기이벤트인 db file sequential read 가 발생하게 된다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

 PARSING IN CURSOR #6 len=85 dep=2 uid=0 oct=3 lid=0 tim=1856381717140 hv=3573688918 
ad='139a5380'
 select name from undo$ where file#=:1 and block#=:2 and ts#=:3 and status$ != 1
 END OF STMT
PARSE #6:c=0,e=303,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1856381717114
EXEC #6:c=0,e=161,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,tim=1856381717770
FETCH #6:c=0,e=190,p=0,cr=3,cu=0,mis=0,r=1,dep=2,og=4,tim=1856381718099
STAT #6 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS FULL UNDO$ (cr=3 pr=0 pw=0
time=185 us)'
WAIT #1: nam='enq: US - contention' ela= 49840 name|mode=1431502854 undo segment #=25 
WAIT #1: nam='enq: US - contention' ela= 17677 name|mode=1431502854 undo segment #=26 
WAIT #1: nam='db file sequential read' ela= 13921 file#=6 block#=937 blocks=1 obj#=53765 	

위의 트레이스 결과를 해석해보자. 트랜잭션을 수행한 프로세스는 언두 세그먼트 바인딩을 위해 UNDO$ 테이블에서 status$ !=1 (현재 ACTIVE한 트랜잭션이 없는) 인 상태의 언두 세그먼트를 검색한다. 첫 번째로 25번 언두 세그먼트를 바인딩 하려고 했으나, 이미 다른 프로세스에 의해 바인딩 되고 있으므로, enq: US - contention대기이벤트를 대기한다. 따라서 26번 언두 세그먼트를 바인딩 하려고 했으나, 역시 다른 프로세스에 의해 바인딩 되고 있어서, enq: US - contention 대기이벤트를 대기하게 된다. 과연 해당 프로세스는 몇 번 언두 세그먼트를 바인딩 하는데 성공하였을까? 그 대답은 아주 쉽다. db file sequential read 대기 이벤트의 P1, P2 파라미터를 이용하여 DBA_EXTENTS 뷰를 조회해 본 결과 27번 언두 세그먼트인 _SYSSUM27$ 라는 것을 알 수 있다. 그렇다면 해당 블록이 언두 세그먼트 블록인지, 언두 세그먼트 헤더 블록인지 확인해보자. 쿼리 수행 결과로 나타난 FILE_ID, BLOCK_ID가 P1, P2와 동일하므로, 해당 블록은 언두 헤더 블록이라는 것을 알 수 있다.

SELECT segment_id ,segment_name , file_id ,block_id
FROM   dba_rollback_segs
WHERE  segment_id=27
           SEGMENT_ID	SEGMENT_NAME	FILE_IDB	LOCK_ID
             -----------	-------------	--------	--------
                    27 	_SYSSMU27$	6	937

여기까지 진행이 된 후에는 PGA 내부에 change vector들을 생성하고, 관련된 리두 레코드를 생성하게 된다. PGA 내부에서 발생되는 작업은 각 프로세스마다 독립된 메모리 공간에서 수행되는 작업이므로, 경합에 의한 대기현상은 발생하지 않는다. PGA 내부에 리두 레코드 생성이 완료된 후에는 해당 리두 레코드를 로그 버퍼에 기록하기 위한 작업이 수행된다. 리두 레코드를 로그 버퍼에 기록하려는 프로세스는 가장 먼저 redo copy 래치를 획득해야 한다. redo copy 래치의 개수의 기본설정 값은 CPU*2개로 설정되어있으며, V$LATCH_CHILDENT 뷰를 통해 확인이 가능하다. redo copy 래치에 대한 획득은 no-wait mode로 수행되며, 래치 획득 알고리즘은 다음과 같다. 만일 redo copy 래치를 획득하지 못할 경우에는 latch: redo copy 대기이벤트를 대기하게 된다.

redo copy 래치에 대한 획득 작업이 완료된 경우에는 로그 버퍼 내에 사용공간을 할당하기 위해 필요한 redo allocation 래치를 획득해야 한다. 프로세스가 획득한 redo allocation 래치는 로그 버퍼 내에 리두 레코드를 저장할 공간이 할당되자 마자 릴리즈 되며, 그 이후에 리두 레코드를 로그 버퍼 내에 기록하게 된다. 만일 redo allocation 래치를 획득하지 못할 경우에는 latch: redo allocation 대기이벤트를 대기하게 된다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

WAIT #1: nam='latch: redo allocation' ela= 6652 address=-4611686018037399976 number=148  
tries=1 obj#=53765

오라클 8i부터는, 리두 레코드를 로그 버퍼에 기록한 후에 redo copy 래치를 릴리즈 하는 것이 아니라, 리두 레코드 내의 change vector 들을 데이터 블록에 반영할 때까지 redo copy 래치를 홀딩하고 있다. 즉, 로그 버퍼내의 리두 레코드가 데이터 블록에 온전하게 반영이 된 경우에는 해당 리두 레코드를 VALID로 설정하고, 그렇지 않은 경우에는 INVALID로 설정을 한 후에 redo copy 래치를 릴리즈 함으로써, LGWR 프로세스가 리두 레코드를 디스크로 기록하기 전에 리두 레코드에 대한 정합성 여부를 확증해주게 된다. PGA 내의 리두 레코드를 로그 버퍼, 언두 블록에 적용하는 순서는 앞서 언급된 “표 2.11 Update 수행시의 리두 로그, 언두 헤더, 언두 블록, 데이터 블록의 변화 사항”을 참고하기 바란다.

그림:56p.png

오라클 8i 이전 버전에서는 LOG_SMALL_ENTRY_MAX_SIZE 파라미터의 설정 값보다 작은 리두 레코드의 경우에는 redo allocation 래치를 이용하여 PGA내의 리두 레코드를 로그 버퍼에 기록하게 끔 되어있었으며, 일반적으로 이러한 경우는 바람직하지 않으므로, LOG_SMALL_ENTRY_MAX_SIZE 파라미터를 0으로 설정하여, 무조건 redo copy 래치를 이용하여 로그 버퍼에 기록하는 것이 가이드 되었다. 하지만 오라클 8i부터는 해당 파라미터는 없어졌으며, 무조건 redo copy 래치를 통해 로그 버퍼에 기록하게끔 되었다. 표 2.12에 오라클 버전 별로 redo copy 래치와 redo allocation 래치와 관련된 사항을 정리하였다.

그림:57p-1.png

만일 redo allocation 래치를 획득한 후에 로그 버퍼에 공간을 할당하려고 하는 시점에, 로그 버퍼에 가용한 공간이 없을 경우에는 log buffer space 대기이벤트를 대기하게 된다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

WAIT #2: nam='log buffer space' ela= 712508 p1=0 p2=0 p3=0

이제 마지막 단계로 변경사항을 데이터 블록에 반영하는 것이다. 데이터 블록에 변경 사항을 반영하기 위해서는 첫 번째로 가용한 ITL (Interested Transaction List)를 검색한다. ITL은 데이터 블록 내에 있는 트랜잭션 슬롯이다. ITL 슬롯 개수의 초기값은 INITRANS 절에 의해 정의되며, MAXTRANS 절에 의해 제한된다. 기본적으로, 테이블은 1개의 ITL, 인덱스는 2개의 ITL을 가진다. 각각의 ITL은 24bytes이며, ITL 내부에는 XID(Transaction Identifier) 정보를 저장하고 있다. 오라클 9i부터는, 모든 데이터 블록(테이블/인덱스)은 최소 2개의 ITL 슬롯을 가지게 되며, INITRANS를 1로 명시한 경우에도 여전히 2개로 설정된다. 또한 오라클 10g부터는 MAXTRANS를 명시적으로 설정한 경우에도, 최대값이 255로 설정된다. 만일 블록 안에 가용한 모든 ITL이 사용 중이고, 새로운 ITL 슬롯을 동적으로 할당하기 위한 PCTFREE 영역의 공간이 충분하지 않을 경우에는 enq: TX - allocation ITL entry 대기이벤트를 대기하게 된다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

WAIT #1: nam='enq: TX - allocate ITL entry' ela= 9 name|mode=1415053316 usn<<16 |  
slot=917529 sequence=3572 obj#=53766
SELECT owner, object_name, subobject_name, object_type, tablespace_name, value, statistic_name
FROM  v$segment_statistics
WHERE statistic_name=’ITL waits’
ORDER BY value;

그림 2.2는 ITL 을 획득한 후에 데이터 블록과 언두 블록간의 관계를 나타낸 것이다. INSERT의 경우에는 UPDATE나 INSERT와는 달리 row-level의 락 대기 현상은 발생하지 않는다.

그림:57p-2.png


대량의 INSERT 가 발생할 경우에는 인덱스 블록 (root 블록, branch 블록, leaf 블록)에 대한 split이 발생할 가능성이 높다. 만일 인덱스 split이 발생하여 원본 블록의 내용을 split을 통해 새로 추가된 블록에 반영하는 작업을 수행하는 세션은, 작업 수행 시 TX enqueue를 획득하여야 하며, split 작업이 완료될 때까지 다른 세션들은 enq: TX - index contention 대기이벤트를 대기해야 한다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

WAIT #1: nam='enq: TX - index contention' ela= 5121 name|mode=1415053316 usn<<16 | 
slot=851985 sequence=5165 obj#=54868

또한 대량의 INSERT가 발생할 경우에는 HWM(High Water Mark) 이전에 포맷된 블록들이 빠른 순간에 소진 되므로, HWM의 위치를 이동한 후 블록을 포맷하는 작업이 필요하게 된다. HWM의 위치를 이동할 때 필요한 enqueue가 HW enqueue이며, 동시에 여러 프로세스가 INSERT 작업을 하는 경우에는 HW enqueue에 대한 경합이 발생하게 되며, 이러한 경우 enq: HW-contention 대기이벤트를 대기하게 된다. HW enqueue에 대한 자세한 사항은 이번 호의 “HW enqueue” 기사를 참고하기 바란다. 지금까지의 단계가 모두 처리 되었다면, INSERT에 대한 모든 수행이 완료되었다. 그렇다면 트랜잭션을 종료하기 위해 커밋을 수행하자. 트랜잭션 커밋이 발생하게 되면, 첫 번째로 언두 헤더 블록의 트랜잭션 테이블내의 슬롯의 상태를 변경하게 된다(상태코드 9). 트랜잭션에 의해 변경된 데이터 블록들에 대한 상태는 변경하지 않는다. 그런 후에 하나의 change vector(op 5.4)를 가진 커밋 리두 레코드를 생성하여 로그 버퍼에 저장하게 된다. 이후 LGWR 프로세스를 포스트 하여 로그 버퍼의 내용을 리두 로그 파일에 기록하라고 요청한다. 하지만, 이미 LGWR 프로세스가 ACTIVE하게 일을 하고 있는 상태이거나, 다른 프로세스에 의해 이미 포스트 된 경우라면 불필요한 포스트 작업이 반복적으로 발생할 것이다. 오라클은 이러한 일을 방지하게 위해 LGWR 프로세스를 포스트 하기 전에 redo writing 래치를 획득하도록 설계하였다. 만일 redo writing 래치를 획득하는데 경합이 발생할 경우 latch: redo writing 대기이벤트를 대기하게 된다. 일반적으로 커밋 수행 시에 가장 많이 발생되는 대기이벤트는 log file sync 이다.

10046 Trace Output (테스트환경 Oracle version: 10.2.0.1)

WAIT #5: nam='latch: redo writing' ela= 6383 address=-4611686018156675320 number=146  
tries=1 obj#=53765
WAIT #1: nam='log file sync' ela= 6118 buffer#=705 p2=0 p3=0 obj#=0

log file sync 에 대한 내용은 이미 “Redo Write Triggers” 섹션에서 자세히 다룬바 있다. 과다한 INSERT 수행으로 인해 대량의 리두 레코드가 발생하는 경우에, 과다한 로그 스위치로 인해 log file switch completion, log file switch (checkpoint incomplete) 와 같은 대기이벤트가 발생하게 된다. 하지만 이러한 이벤트들은 트랜잭션의 처리 순서와 상관없이 로그 스위치 시점에 발생하는 것이다. 그럼 지금까지 INSERT TFM 설계에 필요한 트랜잭션의 상세 처리 순서를 살펴보았다. 지금까지 살펴본 내용을 골자로 하여 모델을 설계하도록 한다.

[편집] INSERT TFM

표 2.15의 Insert TFM 기본모델은 앞서 살펴본 트랜잭션 수행에 따라 발생되는 대기이벤트의 순서를 기준으로 하여, 관련된 데이터 스트럭처와 리소스를 정리하였다.

그림:58p-1.png 그림:58p-2.png

TFM 설계의 목표는 가장 효과적으로 Root Cause를 분석하는 것이다. 이러한 모델이 수립될 경우에, 가장 적은 노력을 통해 현재 발생하고 있는 성능 저하 문제의 많은 부분을 해결하는데 도움이 되리라 생각한다.

Appendix A: INSERT 트랜잭션 수행 시에 발생 가능한 대기이벤트들 (10gR2) 그림:59p-1.png 그림:59p-2.png

Appendix B: Oracle Dump & Scripts

1) Redo Log 파일 Dump 명령어

alter system dump logfile ‘<log file name>’;

2) ROWID를 이용하여 블록을 Dump 하는 Script

set serveroutput on
declare
   v_rowid_type    number;
   v_object_number number;
   v_relative_fno  number;
   v_block_number  number;
   v_row_number    number;
   v_objname       varchar2(100);
   v_objowner      varchar2(100);
   v_file_number   number;
   v_segname       varchar2(100);
   v_segtype       varchar2(100);
   v_spid          number; 
begin
   dbms_rowid.rowid_info('&row_id',v_rowid_type,
       v_object_number,v_relative_fno,v_block_number,
       v_row_number); 
   SELECT owner,object_name into v_objowner,v_objname
   FROM   dba_objects
   WHERE  object_id=v_object_number;
   v_file_number:=dbms_rowid.rowid_to_absolute_fno('&row_id',
       v_objowner, v_objname);
   execute immediate 'alter system dump datafile ' || 
       v_file_number || ' block ' || v_block_number;
   SELECT p.spid into v_spid
   FROM   v$process p,v$session s
   WHERE  s.audsid = userenv('SESSIONID')
   AND    p.addr = s.paddr;
   dbms_output.put_line('Server Process ID  : ' || v_spid);
end;
/

3) File#, Block#를 알고 있는 경우의 특정 블록 Dump 명령어

alter system dump datafile <file_id> block <block_id>;

4) Index 모든 블록에 대한 Tree dump 명령어

alter session set events ‘immediate trace name treedump level <index object_id> ;

[편집] 6

[편집] Right Hand Index 상황에서 경합 현상의 해결과 그 Side Effect

(주)엑셈 선임컨설턴트 김한도(artdb@ex-em.com)

Right Hand Index 현상이란 다수의 세션이 날짜와 시퀀스 등을 인덱스 키 컬럼으로 사용하면서 계속해서 데이터가 insert되는 현상을 말하여 이러한 작업은 현업에서는 상당히 일반적으로 수행되고 있다. 그러나 Insert가 다수의 세션에서 동시에 발생하는 경우, 그것도 시간과 순서만을 키로 하는 경우는 인덱스와 테이블이 증가할때, B*Tree index의 leaf block이 오른쪽 방향으로 계속 증가하는 것처럼 생각되기 때문에 Right Hand라는 말이 유래되었다. 이러한 작업이 과다하게 진행되는 경우 경합이라는 문제가 발생한다. 일반적으로 이러한 현상을 해결하기 위해서는 Reverse Key Index를 사용하는 것을 권장하고 있다.

이 글에서는 Right Hand Index 현상을 해결하기 위해 Reverse Key index가 얼마나 효율적인지를 살펴보고 만약 다른 해결책이 있다면 어떤 것이 있는지를 살펴보도록 한다. 또한 Insert를 수행한 데이터는 언젠가는 Select를 해야한다. 그렇기 때문에 insert시 입력된 데이터를 열람하는데 있어 발생할 수 밖에 없는 상황들 즉, side effect인 clustering factor라는 부분에 대해 논의해 보도록 할 것이다.

[편집] Reverse Key Index의 경합 상황에서의 효율성 점검

일반적으로 Right Hand Index의 경우에 동시다발적으로 Insert가 수행되는 경우 경합에 대해서는 여러 세션들이 같은 인덱스 leaf 블록에 insert작업을 함으로써 발생한다고 한다. 이는 계속된 insert 작업이 가장 마지막 leaf Block에서 발생하기 때문에 여러 세션이 쇄도할 것이고 이로 인해 경합이 발생할 것으로 생각한다. 그래서 일반적인 해결책으로 제시하는 것이 Reverse Key Index이다. 그렇기 때문에 테스트는 테이블과 날짜와 시퀀스를 키로 하는 일반 테이블을 생성하는 것을 기본으로 하고 똑같은 테이블에 날짜와 시퀀스를 키로 하는 Reverse Key Index를 생성하여 10개의 세션이 한꺼번에 날짜마다 300건씩 31일에 걸쳐 작업이 이루어지는 상황을 가정하였다. 이 테스트를 통해 우리는 경합의 상황을 관찰할 것이고 이를 위해 Buffer Busy Wait이라는 대기 이벤트가 얼마만큼 발생하는 지에 초점을 맞출 것이다. Buffer Busy Wait에 대한 자세한 내용은 본지의 ‘Buffer Lock과 Buffer Busy Wait’을 참고하기 바란다.

테스트 환경과 실제로 테스트를 수행한 스크립트는 다음과 같다.

H/W & OS         : HP-UX hpl2000 B.11.11 64bit
CPU               : 440 Mh * 4 EA
MEM               : 4GB
Oracle Version  : Oracle9i Release 9.2.0.1.0 - 64bit Production
SGA
           Total System Global Area	414149392 bytes  
           Fixed Size	736016 bytes
           Variable Size	285212672 bytes               
           Database Buffers	117440512 bytes           
           Redo Buffers	1323008 bytes                

테스트 ① : 기본 테스트 스크립트

1) Schema

/* Table Create */
create table op_test(
 ins_date date,
 seq number,
 acct_num varchar2(7),
 offset varchar2(4)) pctfree 90 pctused 10;
/* Insert Create */
create index op_test_id on op_test(ins_date, seq);
/* Sequence Create */
create sequence op_seq;

2) Procedure

CREATE OR REPLACE procedure op_ins (v_sess in varchar2)
IS
ins_date date;
BEGIN
for i in 0 .. 30 loop  
   ins_date := trunc(sysdate) + i; 
  for j in 1 .. 300 loop
   insert into op_test values( ins_date, op_seq.nextval, 
                                        round(dbms_random.value(0,10000000),0), v_sess||j);
   commit;
 end loop;
end loop;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( SQLERRM );
END ;

테스트 ② : Reverse Key Index 테스트 스크립트

1) Schema

/* Table Create */
create table op_rvrs(
 ins_date date,
 seq number,
 acct_num varchar2(7),
 offset varchar2(4)) pctfree 90 pctused 10;
/* Insert Create */
create index op_rvrs_id  on op_rvrs(ins_date, seq) reverse;
/* Sequence Create */
create sequence op_seq;

2) Procedure

CREATE OR REPLACE procedure op_ins (v_sess in varchar2)
IS
 ins_date date;
BEGIN
for i in 0 .. 30 loop  
  ins_date := trunc(sysdate) + i; 
 for j in 1 .. 300 loop
   insert into op_rvrs values( ins_date, op_seq.nextval, 
                                         round(dbms_random.value(0,10000000),0), v_sess||j);
   commit;
 end loop;
end loop;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE( SQLERRM );
END ;

이 테스트를 수행하여 다음과 같은 결과를 얻을 수 있었다.

테스트 ① : 기본 테스트 결과

EVENT	SUM(TIME_WAITED)
------------------------------	----------------
Total Wait time	32203
enqueue	7228
latch free	2791
log file sync	9453
buffer deadlock	0
buffer busy waits	12651
wait list latch free	20
db file sequential read	0
SQL*Net message to client	0
SQL*Net message from client	60
SQL*Net break/reset to client	0 
NAME	SUM(VALUE)
------------------------------------	----------
CPU used by this session	16163
CPU used when call started 	16163 

테스트 ② : Reverse Key Test 결과

EVENT	SUM(TIME_WAITED)
------------------------------	----------------
Total Wait time	30248
enqueue	7027
latch free	3059
log file sync	8158
buffer deadlock 	0
buffer busy waits	11895
library cache pin	8
wait list latch free	26
db file sequential read	0
SQL*Net message to client	0
SQL*Net message from client	74
SQL*Net break/reset to client	1 
NAME	SUM(VALUE)
------------------------------------	----------
CPU used by this session	15903
CPU used when call started	15903 

이 결과를 다시 정리해 보면 다음과 같았다.

그림:62p.png

Reverse Key index는 말 그대로 Key를 Reverse하여 인덱스를 생성하는 것이다. 아래의 그림과 같이 Right Hand Index현상에서 Key값을 거꾸로 만들어서 인덱스의 블록을 분산하고자 하는 아이디어이다. 테이블의 조건을 동일하게 고정하고 일반 B*Tree인덱스와 이 Reverse Key Index를 사용하여 테스트를 하면 두 인덱스의 성능을 비교해 볼 수 있다. 테스트 결과 buffer busy wait의 발생 시간의 차이는 Reverse Index의 경우 4%정도 개선된 것으로 나타났다. 그러나 대부분의 Wait이 해소되지 않은 것으로 보아 혹시 경합이 Index자체에서 발생하는 것이 아니라 다른 곳에서 발생하는 것이 아닐까 하는 생각을 할 수 있었다.

그림:63p-1.png

이를 검증하기 위해 두 테스트를 다시 수행하여 10046트레이스의 결과에서 Buffer Busy Wait이 발생한 위치를 찾아보기로 하였다. 10046트레이스에서 레벨 8이상은 Wait에 대한 상세한 정보를 제공한다. 그렇기 때문에 Insert문을 수행할 때 발생하는 Buffer Busy Wait의 정보에서 각각의 파라메터 정보로 분류를 해보기로 했다. Buffer Busy Wait의 P1은 file #, P2는 Block #이기 때문에 이를 거꾸로 역추적 한다면 경합이 발생한 위치를 판가름 할 수 있다.

테스트의 결과를 좀 더 쉽게 파악하기 위해 각각의 테이블스페이스에 데이터 파일 1개씩만 할당하도록 재생성하도록 하겠다. 그리하여 인덱스와 테이블을 file#만으로 쉽게 구분이 갈 수 있도록 하였다. 10046 트레이스는 각각 Insert를 수행한 session의 수만큼 떨어지게 되므로 이 파일들을 모두 취합하여 buffer busy wait에 대한 정보를 가려내어 다음과 같이 정리하였다.

그림:63p-2.png

이 테스트의 결과를 살펴보면 Index Block에서는 Reverse Key Index가 다소 경감되기는 하였으나 실제로 경합은 Data Block에서 심하게 발생하고 있어 이를 경감시켜주지는 못하고 있는 것으로 판단되었다. 그렇기 때문에 Right Hand Index현상에 있어 Reverse Key Index는 경합의 문제를 원천적으로 해결해 주지 못한다고 할 수 있다.

[편집] Data Block의 경합의 해소

Right Hand Index 상황에서 경합이 Index보다는 Data Block에서 더욱 심하게 발생한다는 사실은 또 다른 해결책을 마련해야 한다는 의미와도 같다. 이 상황을 좀더 생각해 보자. Right Hand Index의 상황은 데이터가 증가일변도로 생성되는 환경이다. 이 경우 Insert는 계속된 Free Block을 찾게 된다. 그러나 이미 우리는 PCTFREE를 90까지 주었기 때문에 더 이상의 블록의 활용을 저해하면서 분산을 감행하는 것은 사리에 맞지 않는다고 생각한다. 그렇다면 다른 식의 분산을 생각해 보아야 한다.

현재 Free Block에 Insert가 들어오기 때문에 Free Block을 여러 개 마련한다면 어떨까? 각 세션마다 서로 다른 블록에 데이터를 입력한다면 이 문제는 쉽게 해결 될 것이라는 아이디어이다. Free Block을 세션마다 골고루 나누어 분산하는 것은 테이블의 생성시 freelists 옵션으로 해결이 가능하다. 또한 9i부터 소개된 새로운 기능인 ASSM(Auto Segment Space Management)도 이와 비슷한 원리라고 한다. 그렇다면 이에 대한 검증을 해보도록 하자.

테스트 ③은 인덱스 조건은 테스트 ①과 같고 테이블에 freelists옵션을 주었고 테스트 ④도 역시 인덱스 조건은 고정하고 테이블스페이스를 ASSM으로 새로 만들고 이 테이블 스페이스에 테이블을 생성하여 ASSM에 의해 관리가 되도록 하였다.

테스트 ③ : Freelist 10 테스트 스크립트

1) Schema

/* Table Create */
create table op_fl10(
 ins_date date,
 seq number,
 acct_num varchar2(7),
 offset varchar2(4)) pctfree 90 pctused 10 storage(freelists 10);
/* Insert Create */
create index op_fl10_id  on op_fl10(ins_date, seq);
/* Sequence Create */
create sequence op_seq;

2) Procedure

CREATE OR REPLACE procedure op_ins (v_sess in varchar2)
IS
 ins_date date;
BEGIN
for i in 0 .. 30 loop  
  ins_date := trunc(sysdate) + i; 
  for j in 1 .. 300 loop
    insert into op_fl10 values( ins_date, op_seq.nextval, 
                                          round(dbms_random.value(0,10000000),0), v_sess||j);
    commit;
 end loop;
end loop;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( SQLERRM );
END ;

테스트 ④ : ASSM 테스트 스크립트

1) Schema

/* Tablespace Create */
CREATE TABLESPACE test_assm
DATAFILE '/home/oracle/oradata/9.2.0.64/test_assm01.dbf' SIZE 200m 
SEGMENT SPACE management auto;
/* Table Create */
create table op_assm(
 ins_date date,
 seq number,
 acct_num varchar2(7),
 offset varchar2(4)) pctfree 90 pctused 10 tablespace test_assm;
/* Insert Create */
create index op_assm_id on op_assm(ins_date, seq);
/* Sequence Create */
create sequence op_seq;

2) Procedure

CREATE OR REPLACE procedure op_ins (v_sess in varchar2)
IS
ins_date date;
BEGIN
for i in 0 .. 30 loop  
  ins_date := trunc(sysdate) + i; 
  for j in 1 .. 300 loop
    insert into op_assm values( ins_date, op_seq.nextval, 
                                            round(dbms_random.value(0,10000000),0), v_sess||j);
    commit;
 end loop;
end loop;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( SQLERRM );
END ;

각 테스트를 수행하고 다음의 결과를 얻었다.

테스트 ③ : Freelist 10 테스트 결과

EVENT	SUM(TIME_WAITED)
---------------------------------	----------------
Total Wait time	6810
enqueue	3992
latch free	737
log file sync	394
buffer busy waits	689
library cache pin	1
wait list latch free	62
SQL*Net message to client	0
log file switch completion	841
SQL*Net message from client	94
SQL*Net break/reset to client	0 
NAME                                     SUM(VALUE)
-------------------------------	----------
CPU used by this session	11219
CPU used when call started	11219

테스트 ④ : ASSM 테스트 결과

EVENT	SUM(TIME_WAITED)
--------------------------------- ----------------
Total Wait time	7522
enqueue	2840
latch free	496
L1 validation	6
log file sync	2765
buffer deadlock	0
rdbms ipc reply	6
buffer busy waits	1209
library cache pin	9
wait list latch free	4
db file sequential read	0
SQL*Net message to client	0
log file switch completion	129
SQL*Net message from client	58
SQL*Net break/reset to client	0
NAME	SUM(VALUE)
----------------------------------------	----------
CPU used by this session	11137
CPU used when call started	11137 

위의 결과를 다시 정리해 보면 다음과 같다.

그림:65p-1.png

Freelists옵션을 준 경우 기준이 되는 테스트 ①에 비해 상당히 경합의 요소가 제거됨을 알 수 있었고 또한 전체 수행시간도 상당히 많이 줄어들었음을 알 수 있다. 이는 Right Hand Index의 경합현상이 Data Block에서의 경합이 보다 심각하다는 가정과 함께 단지 freelists옵션만으로 이러한 경합의 문제를 해결 할 수 있다는 결론을 내릴 수 있게 하는데 이를 이해하기 위해 freelist라는 것이 어떤 것인지에 대해 좀 알아볼 필요가 있을 것이다.

Freelist란 insert나 update작업시 가용한 블록의 DBA를 지칭하는 linked list를 말하는데 이것은 segment header에 위치하고 있으며 HWM아래의 free space를 관리한다. Freelist가 관리한 Free Space란 Segment내의 Free Block들을 의미하는데 Free Block이란 block내의 사용 공간이 지정된 PCTFREE보다 밑도는 블록들을 의미한다.

이 Freelist는 세가지 type으로 유지된다. 그것은 각각 Segment Freelist 또는 Master Freelist, Process Freelist, Transaction Freelist이다. 먼저 Master Freelist(MFL)은 모든 프로세스들이 사용할 수 있는 free block들을 관리한다. 이것은 segment를 생성할 때 자동적으로 생성된다.

Process Freelist(PFL)는 freelist라는 옵션에 의해 생성되는데 이것은 free block을 각자의 list로 관리하여 프로세스에 할당된다. 각 프로세스가 Insert 작업을 동시에 수행할 때 각 프로세스의 PID를 가지고 PFL을 할당한다. PFL을 할당하는 방법은 “PFL number = mod (PID, freelist 수) + 1”의 방법을 사용한다. 이러한 방법으로 동시에 Insert가 발생할 경우 여러 세션이 각자의 Freelist를 할당받아 사용하게 되어 경합을 줄일 수 있게 된다.

Transaction Freelist(TFL)은 기본적으로 생성되는 것도 아니고 모든 트랙잭션에 의해 자동적으로 생성되는 것도 아니다. 이는 delete, update작업을 수행하다가 해당 블록이 Free Block이 되었을 때 자동적으로 생성된다. TFL할당 프로세스는 Segment header block에 현재 할당된 모든 TFL을 검색하는 것부터 시작한다. 그 후 XID(transaction Identifier)를 비교하여 해당 트랜잭션에 TFL이 할당되었는지를 결정하고 만약 없다면 새로운 TFL을 할당한다. 만약 TFL을 할당할 수 없다면 이미 커밋된 다른 세션의 TFL을 검색하여 사용한다. 만약 사용할 것이 없다면 해당 세션은 TX Enqueue를 대기한다. 이 TFL은 트랜잭션이 commit을 하지 않을 동안만 사용이 가능하고 커밋이 수행된 이후 TFL의 블록은 MFL이나 PFL로 반환된다.

이 세가지 Freelist는 다음과 같이 동작한다. 현재 insert를 수행하는 와중에 free space가 필요하다고 가정하자. 현재 세션은 TFL을 할당받아 있을 것이고 이 TFL에서 free block을 찾게 되면 사용하지만(①) 그렇지 않을 경우 PFL에게 가서 free block을 요구한다.(②) 그런데 여기서도 찾지 못하게 되면 이미 commit 이 완료된 다른 세션의 TFL에서 free block을 찾게 되는데 만약 찾는다면 이 block을 MFL로 옮기게 된다.(③) 만약 찾지 못하게 되면 MFL로 가서 free block을 요청한다. 여기서 이미 커밋된 TFL에서 free block의 발견 여부를 떠나 그 다음 단계는 MFL에서 free block을 찾게 되는 것이다. MFL에서 free block을 찾게 되면 그 free block은 PFL로 이동하여 사용하게 된다.(④) MFL에서도 찾지 못하게 되면 Extent를 할당 받는다. 이러한 과정은 다음의 그림과 같다.

그림:65p-2.png

이미 앞에서 Freelist를 10으로 할당하는 조건으로 테스트를 수행했다고 하였다. 위의 Freelist의 구조대로 설명을 하면 PFL은 freelists 옵션처럼 10개를 할당 받은 셈이 되고 10개의 세션은 각자의 PID를 10으로 나눈 나머지에 1을 더한 수로 freelist를 각각 할당 받게 된다. 만약 각자의 세션의 PID가 1부터 10이라면 아래의 그림처럼 할당을 받게 될 것이다. 또한 각자의 free block도 아래의 그림처럼 분산하여 할당을 받게 될 것이다. 그렇기 때문에 10개의 세션이 동시에 insert를 수행한다 하더라도 블록단위에서 경합은 발생하지 않게 될 것이다.

그림:65p-3.png

그 결과 전반적인 성능이 향상되었고 buffer busy waits는 95%정도 개선됨을 알 수 있었다. 결국 right hand index의 경우는 인덱스 보다는 오히려 테이블의 경합이 더 심각한 문제이고 이를 freelist를 증가시켜 분산함으로서 데이터 블록의 경합을 피할 수 있어 이는 하나의 좋은 해결책이라 할 수 있다.

그림:66p-1.png

그러나 freelist를 사용할 때 Insert를 수행하는 세션의 수와 freelist와 동일할 때 가장 효율적일 것이다. 다시 말해 현재 수행되는 세션이 설정한 freelist의 수보다 많게 될때는 그만큼 경합이 발생하게 된다는 문제가 발생할 수도 있다는 말이 된다. 아래의 표는 ③의 테스트를 freelists 5로 줄여놓고 수행한 결과를 비교한 것이다. Wait의 총 시간은 줄었음에도 오히려 경합은 2배가량 증가했음을 알 수 있다.

그림:66p-2.png

그렇다고 해서 OLTP의 세션 수에 맞도록 freelist를 무한정 크게 할 수는 없다. Freelist는 block size에 따라 max 값이 각각 달라지기 때문에 상한이 있기 때문이다.(2k-24개, 4k-40개, 8k-101개) 물론 최대로 크게 잡는 것은 큰 부작용은 없을 듯 보이나 Oracle에서는 이러한 고민을 해결해 주기 위해 ASSM이라는 새로운 기능을 선보이고 있다. ASSM은 다수의 세션이 동시에 insert작업을 수행할 때 자동으로 분산의 효과를 누릴 수 있게 해 준다.

ASSM segment는 세가지 형태의 block으로 구성되어 있다. 이것은 각각 Segment Header, bitmap block, data block이다. 아래의 그림은 각각 다른 형태의 블록이 어떻게 존재하는 지를 나타내고 있다. Segment의 가장 처음 블록은 L1 BMB(BitMap Block)이 존재하고 그 다음은 L2 BMB, 세번째 블록은 Segment Header이다. Segment Header Block은 L3 BMB를 포함하고 있다. 그 나머지는 Data Block이다.

그림:66p-3.png

Bitmap Block은 위의 그림처럼 3가지로 구분되는데 이들은 Segment header block을 정점으로 하는 트리구조의 관계를 가진다. Space를 검색하기 위해서는 Segment header를 경유하여 L2 BMB, L1 BMB의 순서로 찾고 그 후에 Data Block으로 찾아간다. 이 트리 구조는 아래의 그림과 같다.

그림:66p-4.png

프로세스가 insert 작업을 수행할 때 L1 BMB에서 적절한 Data Block의 DBA(Data Block Address)를 찾게 된다. L1 BMB는 하나의 extent안에서 연속된 Block들을 Range로 구성하고 이 Range를 관할하게 된다. 하나의 Range는 처음에는 최대 16 block의 DBA로 구성할 수 있으며 하나의 L1 BMB는 Segment Size에 따라 최대 1024의 DBA를 관할할 수 있다.

L1 BMB에서 프로세스는 free DBA를 찾기 위해 다음의 그림과 같은 L1 BMB 그리드를 뒤지게 된다. 이 그리드는 16 * n의 형태의 2차원 배열의 형태를 띄게 되는데 16개의 블록은 고정이고 segment 크기에 따라 n이 결정이 된다. 이 그리드를 스캔하기 위해 우선 프로세스 ID으로 해시값을 구해 L1 BMB의 Starting Point를 무작위로 배정한다. 그림에서는 34번 Block이 배정이 되었는데 여기서부터 Scan이 시작되어 지그재그로 찾게 된다. 찾는 순서는 34번에서 자신의 라인을 모두 뒤진 후 다음 라인은 역으로 찾게 되고 L1 BMB의 끝까지 찾은 후에는 가장 처음 라인으로 바로 올라가 end point인 30번 블록까지 찾게 된다. 이 scan의 목적은 5개의 free block의 candidate를 찾는 것인데 프로세스는 이 5개의 candidate block을 찾아 에 nowait모드로 획득한다. 그러나 만약 이 블록이 다른 프로세스에 의해 사용이 되고 있다면

그림:66p-5.png

다음 번 candidate block으로 넘어간다. 만약 이 candidate block모두가 사용중이라면 그때서야 L1 BMB를 놓고서 exclusive mode로 획득하기 위해 buffer busy wait을 대기하게 된다. 이러한 과정이 buffer busy wait이 경감시키는 작용을 하게 되는 것이다.

그림:67p-1.png

각 테스트의 결과를 비교해 보면 ASSM은 Right Hand Index상황에서 하나의 해결책이 될 수 있을 것으로 보인다. 기본테스트에 비해 ASSM의 buffer busy wait의 발생 시간도 10배 정도 줄었고 이는 Freelist를 10개로 설정한 결과와 큰 차이가 나지 않음을 알 수 있다. 역시 Right Hand Index 현상은 그 명칭은 Index의 문제임을 암시하지만 사실은 Data Block의 경합을 야기하는 것이 실제적인 문제이며 이를 해결하기 위해서는 각 세션마다 insert의 대상이 되는 data block을 최대한 분산하여 할당하는 방법을 생각해 볼 수 있는데 ASSM도 역시 세션마다 L1 BMB를 다르게 할당하여 분산의 효과를 누리게끔 하고 있다.

[편집] 새로운 문제 제기 - Scan시 발생하는 Side Effect

바로 앞에서 Right Hand Index상황에서 성능 저하의 실체는 Data Block의 경합이고 이를 해결하기 위해서는 Index의 분산보다는 Data Block의 분산이 해결책이 될 수 있다고 했다. 그래서 이러한 패턴의 업무를 수행하는 사이트에서는 Oracle 버전이 9i이상인 경우 ASSM을 사용하는 것이 좋고 8i인 경우는 Freelist를 충분히 설정하는 것이 다중 Insert환경에서 유리할 것이다.

그런데 Right Hand Index의 환경의 특징중 하나는 최근에 Insert된 데이터를 주로 쿼리하는 형태의 작업이 많다는 것이다. 또한 이렇게 시간이 지남에 따라 점차로 누적되는 데이터에서 최종 데이터 일부를 가져오기 위해서는 당연히 Index를 이용하여 데이터를 가져와야 한다.

인덱스를 사용하여 데이터를 가져올 때 우리는 옵티마이저가 인덱스를 경유하여 데이터를 가져오는 것을 기대한다. 그러나 옵티마이저가 인덱스를 선택하기 위해서는 인덱스를 경유하는 Access Path의 Cost가 낮아야만 한다. Cost를 계산하는 데 큰 영향을 미치는 것이 바로 클러스터링 팩터(Clustering Factor)인데 이것은 테이블이 얼마나 인덱스처럼 정렬이 되어 있는지를 나타내는 지수로 최소값은 table의 block 수와 같고 최대값은 Table의 Row수와 같다.

그런데 지금껏 Right Hand Index를 해결하기 위해 Data Block을 분산시키는 방법을 사용하여 경합을 최소화 하였는데 Reverse Key Index를 제외하고 모든 인덱스는 날짜와 Sequence 순서로 정렬이 되어 있다. 그렇다면 그 인덱스들의 클러스터링 팩터는 좋을까? Reverse Key 인덱스의 단점은 클러스터링 팩터가 좋지 않게 된다는 것이다. 그것은 인덱스와 테이블의 정렬이 서로 다른 방식으로 되어 있기 때문이다. Freelist를 증가시킨 경우나 ASSM을 사용하는 경우는 각자의 세션이 각자의 Free Block을 따로 할당 받아서 데이터를 입력하는 방식이다. 그렇게 되면 어쩔수 없이 인덱스 키 컬럼의 순서대로 정렬이 되기는 힘들게 될 것이다.

아래는 테스트에 의해 생성된 테이블과 인덱스를 analyze하여 정리한 결과이다. 예상대로 기본 테스트의 경우의 클러스터링 팩터가 최상이고 Reverse Key인덱스의 경우가 row수에 근접하여 가장 좋지 않은 클러스터링 팩터가 나타났다. 또한 freelist를 증가시킨 경우나 ASSM의 경우도 클러스터링 팩터는 상당히 좋지 않게 나타났다.

그림:67p-2.png

이 결과로 미루어 Right Hand Index의 insert시 경합 현상을 해결하기 위해 사용한 방법들은 결국 Scan시 Side Effect를 가지고 있을 수 밖에 없다고 추측할 수 있을까? 아직 결론을 내리기엔 이르다. 이에 대해 과연 옵티마이저는 어떻게 반응을 하는지 그리고 그에 대한 해결책에 대해 한번 생각해 볼 필요가 있다.

[편집] Clustering Factor의 문제

A. Scan 테스트 및 결과

우선 하루 3000건, 전체 31일의 93000건을 각각 가지고 있는 테이블과 인덱스를 대상으로 4일치의 데이터를 scan하는 SQL을 수행해 볼 것이다. 이 SQL을 수행할 때 옵티마이저는 어떤 선택을 할 것인지 그리고 성능은 어떤지에 대한 관찰이 필요할 것이다. 다음은 4가지 경우에 대한 SQL의 수행 결과이다.

① 기본 테스트 결과

SELECT COUNT( offset )
FROM   op_test 
WHERE  ins_date > TRUNC( SYSDATE ) + 26
call	count	cpu	elapsed	disk	query	current	rows	mis	WaitEla
-----	-----	----	-------	----	-----	-------	 -----	------	-------
Parse	1	0.08	0.12	22	45	0	0	1	0.00	
Exec	1	0.00	0.00	0	0	0	0	0	0.00
Fetch	1	0.27	0.28	935	3500	0	1	0	0.07
-----	-----	----	-------	----	-----	-------	 -----	------	-------
Total	3	0.35	0.40	957	3545	0	1	1	0.07 
Event waited on	Count	Zero Ela	Elapse	AVG(Ela)	MAX(Ela)	Blocks
----------------------------	-----	--------	-------	--------	--------	-----	
db file sequential read	957	0	0.07	0.00	0.00	957
SQL*Net message to client	2	0	0.00	0.00	0.00	0
SQL*Net message from client	2	0	0.00	0.00	0.00	0
Rows		Row Source Operation
---------		-----------------------------------------------
          1	SORT AGGREGATE 
   12000	TABLE ACCESS BY INDEX ROWID OBJ#(6439) OP_TEST
   12000	INDEX RANGE SCAN OBJ#(6463) OP_TEST_ID

② Reverse Key Test 결과

SELECT COUNT( offset )
FROM   op_rvrs 
WHERE  ins_date > TRUNC( SYSDATE ) + 26
call	count	cpu	elapsed	disk	query	current	rows	mis	Wait Ela
----	-----	---	-------	----	------	------	-----	----	--------	
Parse	1	0.01	0.00	0	0	0	0	1	0.00
Exec	1	0.00	0.00	0	0	0	0	0	0.00
Fetch	1	0.90	0.89	4154	4669	0	1	0	0.19
----	-----	---	-------	----	------	------	-----	----	--------
Total	3 	0.91	0.90	4154	4669	0	1	1	0.19
Event waited on	Count	Zero Ela	Elapse	AVG(Ela)	MAX(Ela)	Blocks
-----------------------	-----	------	-------	-------	-------	------
db file sequential read	37	0	0.00	0.00	0.00	37
SQL*Net message to client	2	0	0.00	0.00	0.00	0
SQL*Net message from client	2	0	0.00	0.00	0.00	0
db file scattered read	217	0	0.19	0.00	0.01	4632
Rows		Row Source Operation
----------		--------------------------------------------------- 
          1	SORT AGGREGATE 
   12000	TABLE ACCESS FULL OBJ#(6440) OP_RVRS

③ Freelist 10 테스트 결과

SELECT COUNT( offset )
FROM   op_fl10 
WHERE  ins_date > TRUNC( SYSDATE ) + 26
call	count	cpu	elapsed	disk	query	current	rows	mis	Wait Ela
----	-----	----	-------	----	------	-------	-----	----	--------
Parse	1	0.05	0.04	1	41	0	0	1	0.00
Exec	1	0.00	0.00	0	0	0	0	0	0.00
Fetch	1	0.25	0.26	777	7869	0	1	0	0.07
----	-----	----	-------	----	------	-------	-----	----	--------
Total	3	0.30	0.30	778	7910	0	1	1	0.07
Event waited on	Count	Zero Ela	Elapse	AVG(Ela)	MAX(Ela)	Blocks
----------------------	-----	--------	-------	--------	--------	------
db file sequential read	778	0	0.07	0.00	0.00	778
SQL*Net message to client	2	0	0.00	0.00	0.00	0
SQL*Net message from client	2	0	0.00	0.00	0.00	0 
Rows		Row Source Operation
---------		-------------------------------------------------
          1	SORT AGGREGATE 
   12000	TABLE ACCESS BY INDEX ROWID OBJ#(6441) OP_FL10
   12000	INDEX RANGE SCAN OBJ#(6465) OP_FL10_ID

④ ASSM 테스트 결과

SELECT COUNT( offset )
FROM   op_assm 
WHERE  ins_date > TRUNC( SYSDATE ) + 26

call	count	cpu	elapsed	disk	query	current	rows	mis	Wait Ela
----	-----	----	-------	----	-----	-------	----	----	--------
Parse	1	0.04	0.04	0	40	0	0	1	0.00
Exec	1	0.00	0.00	0	0	0	0	0	0.00
Fetch	1	0.23	0.30	790	5532	0	1	0	0.12
----	-----	----	-------	----	-----	-------	----	----	--------
Total	3	0.27	0.33	790	5572	0	1	1	0.12
Event waited on	Count	Zero Ela	Elapse	AVG(Ela)	MAX(Ela)	Blocks
---------------------------	------	--------	------	--------	--------	------
db file sequential read	790	0	0.12	0.00	0.01	790
SQL*Net message to client	2	0	0.00	0.00	0.00	0
SQL*Net message from client	2	0	0.00	0.00	0.00	0
Rows		Row Source Operation
-------	-	------------------------------------------------
          1	SORT AGGREGATE 
   12000	TABLE ACCESS BY INDEX ROWID OBJ#(6442) OP_ASSM
   12000	INDEX RANGE SCAN OBJ#(6466) OP_ASSM_ID

Scan 테스트 결과를 일량과 함께 정리하면 다음과 같다.

그림:69p-1.png

①의 경우는 예상과 같이 인덱스를 사용하였고 ②도 또한 예상을 빗나가지 않았다. 클러스터링 팩터가 굉장히 좋지 않은 경우였기 때문에 Full Table Scan을 수행하였다. 그러나 ③과 ④의 경우는 다소 의외의 결과가 나타났다. 무려 전체 테이블 블록수의 10배가 넘는 클러스터링 팩터가 나타났지만 옵티마이저는 인덱스를 선택하고 있다. 그렇다면 옵티마이저의 선택이 올바른지를 한번 살펴보기로 하자.

전체 수행 시간을 비교해 보면 ③과 ④의 경우는 ①의 경우 보다 빠른 것으로 보아 일단은 옵티마이저가 틀린 선택을 하지는 않은 것으로 보인다. 그러나 워낙 짧은 시간 동안 수행된 작업이기 때문에 이를 0.1초 내외의 차이에 큰 의미를 두지 말고 일량을 보도록 하자.

일량의 경우 물리적인 Disk IO는 physical reads라는 지표로 확인해 볼 수 있는데 이도 또한 ③과 ④의 경우가 ①의 경우 보다는 적다. 또한 Memory IO는 session logical reads라는 지표를 통해 확인해 볼 수 있는데 이 경우는 ①의 경우 보다 ③과 ④의 경우가 다소 많다. Session logical read는 db block gets와 consistent gets의 합으로 나타나는데 db block gets는 current reads, 즉 current block을 읽어들인 총합을 의미한다. Current block은 각 블록의 가장 최근 버전을 의미한다. consistent gets란 consistent read의 총합을 의미한다. Consistent read는 current block이 아닌 consistent block을 읽어들인 횟수 이다. Memory IO를 기준으로 보았을 때 ③과 ④의 경우는 ①의 경우 보다 각각 2배, 1.5배 정도의 차이를 보인다.

처리속도와 일량의 요소들을 가지고 ①의 경우를 1로 놓고 ③과 ④의 경우를 비교해 본다면 아래의 표와 같다. 아래의 표에서는 세가지 경우의 성능에 큰 차이가 없음을 알려준다.(합계의 수치가 클수록 성능이 좋은 것으로 판단)

그림:69p-2.png

조금 더 색다른 비교를 해보자. 위의 테스트 결과 측정 표에서 buffer is not pinned count와 buffer is pinned count라는 지표는 Memory IO의 효율을 알려준다. 이 두가지 지표는 buffer pinning현상에 대한 지표이다. 8.0버전 이후부터 Oracle은 logical IO의 횟수를 줄이기 위해 pinning이라는 것을 소개하였다. 이것은 한 SQL을 수행하는 동안 buffer의 data block을 반복해서 읽게 되면 buffer cache내에 pin을 걸어놓는 것으로 주로 consistent block들을 대상으로 발생된다. 이 pinning이 발생하는 동안에는 session logical reads가 증가하지 않는다.

buffer is pinned count는 이미 이 세션에 의해 pinned된 블록을 읽을 때마다 증가하고 buffer is not pinned count은 pin 되지 않은 블록 그러니까 블록을 처음 방문할 때 증가하고 이는 session logical read와 거의 비슷하게 나타난다. 이 지표 두개를 합하게 되면 총 방문한 블록 수가 된다.

이러한 지표를 토대로 해서 3가지 Ratio를 구할 수 있다.

* 블록당 방문 횟수 
 = (buffer is not pinned count+buffer is pinned count) / physical reads
* 건당 블록 방문 횟수 
 = (buffer is not pinned count+buffer is pinned count) 
     / (table fetch by rowid + index fetch by key)
* 블록당 재방문 횟수 
 = buffer is pinned count / buffer is not pinned count

그림:69p-3.png

블록당 방문 횟수는 IO의 효율과 관련이 있다. 이는 하나의 블록에서 많은 작업을 하는 지를 나타내는데 여기서는 ③과 ④의 경우가 ①의 경우 보다 다소 높은 것을 알 수 있다. 건당 블록 방문 횟수는 1Row를 가져오기 위해서 몇 개의 블록을 방문해야 하는 지를 보여주는데 이는 모두 같다. 이것은 Blevel과 연관이 있을 것으로 보인다. 블록당 재방문 횟수는 1개의 블록을 다시 방문하는 횟수이다. 클러스터링 팩터도 row를 가져올 때 얼마나 방문하는 블록을 몇번 바꾸느냐를 측정한 것이기 때문에 여기서도 재방문하는 횟수가 큰 수록 방문시 블록을 바꾸지 않음을 의미한다. 그러므로 블록당 재방문 횟수가 클수록 클러스터링 팩터가 좋다고 할 수 있다. 이 값들은 각각의 인덱스의 클러스터링 팩터의 순서와 일치함을 알 수 있다.

결국 옵티마이저의 선택이 틀리지 않음을 알 수 있다. 이를 재검증 하기 위해 ②의 경우를 인덱스를 사용하도록 힌트를 주어 옵티마이저의 결정을 번복해 보기로 하자.

⑤ 인덱스 힌트를 사용한 Reverse Key Test 결과

SELECT /*+ index (op_rvrs op_rvrs_id)*/
           COUNT( offset )
FROM   op_rvrs
WHERE  ins_date > TRUNC( SYSDATE ) + 26
call	count	cpu	elapsed	disk	query	current	rows	mis	Wait Ela
----	-----	----	-------	-----	-----	-------	----	----	--------
Parse	1	0.03	0.03	0	41	0	0	1	0.00
Exec	1	0.00	0.00	0	0	0	0	0	0.00
Fetch	1	0.90	0.92	1346	13616	0	1	0	0.13
----	-----	----	-------	-----	-----	-------	----	----	--------
Total	3	0.93	0.95	1346	13657	0	1	1	0.13
Event waited on	Count	Zero Ela	Elapse	AVG(Ela)	MAX(Ela)	Blocks
----------------------	-----	-------	-----	------	------	-----
db file sequential read	1346	0	0.13	0.00	0.02	1346
SQL*Net message to client	2	0	0.00	0.00	0.00	0
SQL*Net message from client	2	0	0.00	0.00	0.00	0 
Rows		Row Source Operation
--------		-----------------------------------------------
          1	SORT AGGREGATE 
   12000	TABLE ACCESS BY INDEX ROWID OBJ#(6440) OP_RVRS
   12000	INDEX FULL SCAN OBJ#(6464) OP_RVRS_ID

그림:70p.png

위의 표는 앞서 사용한 지표와 방법대로 ①과 ⑤를 비교한 표이다. 그 결과 인덱스를 사용하더라도 성능이나 여타 다른 면에서 모두 ①에 미치지 못함을 알 수 있었다. 결국 옵티마이저가 합당한 결과를 제공하고 있음을 알 수 있다.

[편집] 최종 분석

지금까지 Right Hand Index 현상의 insert시 경합 발생에 대한 문제제기를 통해 이 경합은 그 이름과 같이 Index에서 오는 것이 아니라 Data Block에서 발생하는 것임을 밝혀내었고 이를 해결하기 위해서는 일반적으로 알려진 사실처럼 Reverse Key Index를 사용하는 것이 아니라 Freelist를 늘려주거나 9i부터 소개된 ASSM의 기능을 사용하는 것이 경합 현상을 해결하는 것이 좋다는 것을 알아냈다.

또한 이러한 해결책의 Side Effect로 예상할 수 있는 클러스터링 팩터 부분에 있어서는 실제로 동일한 결과를 가져오는 SQL을 수행하여 10046 트레이스와 v$mystat의 결과를 통해 옵티마이저가 선택한 Access Path가 어느 정도의 성능을 보장하고 있으며 또한 그 안에서 클러스터링 팩터는 어떻게 영향을 주는지를 살펴 보았다. 그 결과 Reverse Key Index는 Insert 뿐만 아니라 Select시에도 최악의 성능을 나타내었고 Freelist를 증가시킨 경우나 ASSM을 사용한 경우는 Select시에 성능 저하는 거의 나타나지 않았다. 오히려 수행시간에 있어서는 다소 빠른 듯이 보였다.

요컨데 지금까지 Right Hand Index 현상에 대해서는 Reverse Key Index만이 해결책이라고 하는 오해가 있었던 것으로 보이며 Freelist를 증가시켜주거나 ASSM을 사용하는 것이 오히려 좋은 해결책이 될 수 있었고 Side Effect도 우려할만한 수준은 아니었기 때문에 이러한 환경에서는 이 두가지 해결책을 사용하는 것이 좋을 것이라는 결론을 내릴 수 있게 되었다.

[편집] 7

[편집] INDEX PCTFREE의 이해

테이블과 인덱스를 생성할 때 사용되는 옵션 중에 PCTFREE가 존재한다. PCTFREE는 테이블 스페이스를 관리하는 방식(FLM 또는 ASSM)에 상관없이 세그먼트 생성시에 사용자가 지정하여 사용할 수 있는 옵션이다. 참고로 ASSM의 경우에는 FREELISTS, FREELIST GROUPS, PCTUSED는 오라클에서 자동 관리된다.

테이블에서의 PCTFREE의 용도는 데이터 레코드의 길이가 길어지는 UPDATE에 의한 예약 공간으로 활용된다. 일반적으로 UPDATE가 잦은 테이블의 경우에는 PCTFREE의 수치를 상향 조정하여(기본설정 값 10) UPDATE에 의한 row migration을 최소화시키도록 하는 것이 일반적인 가이드이다. 그렇다면 인덱스의 PCTFREE는 어떠한 용도로 사용될까? 오라클에서 사용되는 B*Tree인덱스에는 UPDATE란 오퍼레이션은 존재하지 않는다. 키 값에 대한 변경이 발생할 경우에는 UPDATE 대신 INSERT & DELETE 방식으로 인덱스 키 값에 대한 처리를 하기 때문이다. 따라서 인덱스의 PCTFREE는 테이블의 PCTFREE와 다른 용도로 사용될 것이라는 것을 직감할 수 있다. 결론부터 말하면, 인덱스의 PCTFREE는 인덱스 leaf 블록의 split 현상을 지연시키기 위해서 사용되며, 인덱스 생성 시점에만 그 의미를 갖는다. 즉, 인덱스 생성시점에 leaf 블록마다 PCTFREE만큼의 공간을 남겨두어, 인덱스 생성 시점 이후에 INSERT되는 레코드에 대한 키 값의 저장을 목적으로 한다는 것이다. 따라서, UPDATE가 발생하지 않는 테이블에 속한 인덱스인 경우에도, 인덱스 생성시에 PCTFREE를 아주 작게, 혹은 0으로 설정하는 것은 leaf 블록의 split 현상을 과다하게 발생시키는 원인이 되므로, 인덱스 PCTFREE의 의미에 대한 정확한 이해를 통해, 해당 옵션을 설정하는 것이 바람직하다.

아래의 예제는 PCTFREE의 수치에 따른 인덱스 leaf 블록 split의 발생 시점을 인덱스 tree dump를 통해 확인해 본 결과이다.

SQL> create table t1 (id number, id2 char(20));
SQL> insert into t1 values (1,'1');                     -- 1 입력
SQL> create sequence t1_sq start with 10;            
-- 10~10000 입력준비
-- 향후 2~9를 입력할 때 블록 split이 발생하는지 여부를 테스트
SQL> begin
         for i in 1..10000 loop
           insert into t1 values (t1_sq.nextval, '1');
         end loop;
      commit;
end;
/ 
SQL> create unique index t1_uk on t1(id) pctfree 0;
SQL> select object_name, object_id from user_objects
         where object_name='T1_UK';
OBJECT_NAME                     	OBJECT_ID
------------------------------	----------
T1_UK                             	46164
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump LEVEL 46164' ;
branch: 0x1c2b7e2 29538274 (0: nrow: 18, level: 1)    /* branch block */
   leaf: 0x1c2b7e3 29538275 (-1: nrow: 576 rrow: 576) /* start leaf block */
   leaf: 0x1c2b7e4 29538276 (0: nrow: 570 rrow: 570)  /* nrow: 저장된 키 수 */
   leaf: 0x1c2b7e5 29538277 (1: nrow: 570 rrow: 570)
…
   leaf: 0x1c2b7f4 29538292 (16: nrow: 305 rrow: 305)

이 상태에서 2를 입력한 후에 leaf 블록의 변화를 살펴보자

SQL> INSERT INTO t1 VALUES(2,'1');
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump LEVEL 46164' ;
branch: 0x1c2b7e2 29538274 (0: nrow: 19, level: 1)
   leaf: 0x1c2b7e3 29538275 (-1: nrow: 292 rrow: 292)
leaf: 0x1c2b7f5 29538293 (0: nrow: 285 rrow: 285)
leaf: 0x1c2b7e4 29538276 (1: nrow: 570 rrow: 570) 
…
leaf: 0x1c2b7f4 29538292 (17: nrow: 305 rrow: 305)

결과에서 확인할 수 있듯이, 1개의 값만을 입력하였을 뿐인데도, 첫 번째 leaf 블록이 2개로 나눠졌고 (50:50 split발생), 결과적으로 1개의 leaf블록이 추가적으로 생성되었다.

그렇다면 인덱스의 PCTFREE를 10으로 설정하여 다시 테스트를 해보자.

SQL> create unique index t1_uk on t1(id) pctfree 10;
SQL> select object_name, object_id from user_objects
         where object_name='T1_UK';
OBJECT_NAME	OBJECT_ID
------------------------------	----------
T1_UK	46167 
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump LEVEL 46167' ;
branch: 0x1c2b7e2 29538274 (0: nrow: 20, level: 1)
    leaf: 0x1c2b7e3 29538275 (-1: nrow: 518 rrow: 518)
    leaf: 0x1c2b7e4 29538276 (0: nrow: 512 rrow: 512)
    leaf: 0x1c2b7e5 29538277 (1: nrow: 512 rrow: 512)
    …
    leaf: 0x1c2b7f6 29538294 (18: nrow: 268 rrow: 268)

이전에 PCTFREE를 0으로 주었을 때와 비교해보면, 하나의 leaf블록에 저장된 키 수가 적어졌음을 알 수 있다.

이 상태에서 2를 입력한 후에 leaf 블록의 변화를 살펴보자

SQL> INSERT INTO t1 VALUES(2,'1');
SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump LEVEL 46167' ;
 branch: 0x1c2b7e2 29538274 (0: nrow: 20, level: 1)
    leaf: 0x1c2b7e3 29538275 (-1: nrow: 519 rrow: 519)
    leaf: 0x1c2b7e4 29538276 (0: nrow: 512 rrow: 512)
    leaf: 0x1c2b7e5 29538277 (1: nrow: 512 rrow: 512)
    …
    leaf: 0x1c2b7f6 29538294 (18: nrow: 268 rrow: 268)

첫 번째 leaf 블록에 저장된 키 수가 518개에서 519개로 증가했음을 알 수 있다. 즉,첫 번째 leaf 블록은 PCTFREE 10의 효과에 의해, 앞으로도 57개(576-519)의 key를 더 저장할 수 있는 공간이 있는 것이다.