<오라클 성능 고도화 원리와 해법1> Ch06-08 I/O 효율화 원리

오라클 성능 고도화 원리와 해법1 - Ch06-08 I/O 효율화 원리

애플리케이션 측면에서 논리적인 I/O 요청 횟수를 최소화하는 것이 I/O 효율화 튜닝의 핵심 원리라고 했다.

I/O 때문에 시스템 성능이 낮게 측정될 때 하드웨어적인 방법을 통해 I/O 성능을 향상시킬 수도 있다. RAW 디바이스, 비동기 I/O를 사용하거나 스트라이핑 방식을 달리하거나, 고대역폭 인터커넥트를 사용하는 등의 방법이 있고, 이는 분명 I/O 효율을 높이는데 도움이 된다. 하지만 그런 내용은 이 책의 범위를 벗어날 뿐 아니라 애플리케이션 측면의 노력만큼 효과가 크지도 않다. SQL 튜닝을 통해 I/O 발생 횟수 자체를 줄이는 것이 더 확실한 해결 방법이다. 물론 하드웨어를 교체하는 것에 비해 수많은 개발자들의 노력이 수반되어야 하므로 몹시 힘든 작업이지만 그렇게 해야만 비효율을 근본적으로 개선할 수 있다.

애플리케이션 측면에서의 I/O 효율화 원리를 다음과 같이 요약할 수 있다.

• 필요한 최소 블록만 읽도록 쿼리를 작성한다.
• 최적의 옵티마이징 팩터를 제공한다.
• 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.

이를 위한 구체적인 방법은 2권에서 다루지만 기본 원리만큼은 여기서 설명하려고 한다.

(1) 필요한 최소 블록만 읽도록 쿼리 작성

지금까지 반복적으로 강조했듯이 데이터베이스 성능은 I/O 효율에 달려있고, 이를 달성하려면 동일한 레코드를 반복적으로 읽지 않고, 필요한 최소 블록만 읽도록 쿼리를 작성해야 한다.

쿼리 옵티마이저가 나름대로 지능적인 기법들을 동원해 쿼리 최적화를 수행하지만 사용자가 던진 SQL명령의 기본 요구사항과 일량에서 크게 벗어나기는 어렵다. 따라서 SQL 명령을 던지는 사용자 스스로 최소 일량을 요구하는 형태로 논리적인 집합을 정의하고, 효율적인 처리가 가능하도록 쿼리를 작성하는 것이 무엇보다 중요하다.

(2) 최적의 옵티마이징 팩터 제공

앞에서 본 두 개의 사례는, 논리적인 집합 재구성을 통해 읽어야 할 데이터 양을 최소화할 수 있음을 보여주었다. 하지만 오라클이 그것을 처리하는데 있어 사용자 의도대로 블록 액세스를 최소화하면서 효율적인 쿼리 프로세싱을 할 수 있도록 하려면 최적의 옵티마이징 팩터를 제공해주어야 한다.

전략적인 인덱스 구성

옵티마이징(Optimizing)도 ‘최적화’의 뜻을 갖는데, 그 앞에 ‘최적의’라는 수식어가 또 붙었다. 최적화는 옵티마이저가 수행하지만 그렇게 할 수 있도록 적절한 수단을 제공하는 것은 사용자의 몫이라는 뜻이다. 전장에 나가는 병사에게 무기도 주지 않고 싸우라고 할 수는 없지 않은가? 인덱스도 없이 100만 건 중 1건을 0.001초만에 찾아올 방법은 없다. 전략적인 인덱스 구성은 옵티마이저를 돕는 가장 기본적인 옵티마이징 팩터다.

DBMS가 제공하는 다양한 기능 활용

인덱스 외에도 DBMS가 제공하는 기능을 적극적으로 활용해야 한다. 적군은 탱크와 90mm 박격포로 밀고 들어오는데 소총 하나만들고 싸운다면 백전백패다. 1억 건 중 조건 절에 해당하는 100만 건을 읽어 집계된 결과를 출력해야 하는데, 인덱스만 제공해주고 1분만에 결과가 나오기를 바란다면 옵티마이저에게 무리한 요구를 하는 것이다. 파티션, 클러스터, IOT, MV, FBI, 분석 함수 등 DBMS가 제공하는 기능들을 적극 활용한다면 옵티마이저에게 강력한 무기가 된다.

옵티마이저모드설정

아무리 강력한 무기를 갖추었더라도 전략과 목표가 명확하지 않으면 효과적으로 싸우기 어렵다. 중대장은 소대장에게 전쟁의 목적을 분명히 알려줘야한다. 기선만 제압하려 하는 것인지, 적군을 섬멸시키려 하는 것인지, 그것이 명확하지 않으면 싸우는 동안 소대장 또는 개개병사 입장에서 스스로 최적의 판단을 내릴 수가 없다. 기선만 제압하는 게 목적이었는데, 목숨을 내걸고 무모하게 소대원들을 적진으로 돌격시키는 소대장이나 올 수도 있다. 또는 혼자 돌출 행동하는 병사 때문에 심각한 손실을 볼 수도 있다.

옵티마이저에게 작전명령을 내릴 때도 전체 레코드를 다 읽을 생각인지, 아니면 10건 만 읽고 멈출 지 목적(Goal)을 분명히 밝혀야 한다.

옵티마이저모드 외에도 옵티마이저 행동에 영향을 미치는 파라미터들이 몇 가지 있다. 대개는 기본값으로 놔둬도 상관없지만 몇몇 파라미터는 애플리케이션 특성에 따라 조정이 필요할 때가 있다.

통계정보의 중요성

마지막으로, 전쟁에서 효과적으로 싸우려면 정보력이 뒷받침되어야 한다. 전쟁의 목표가 분명하더라도 정보가 불충분하면 적군의 수효가 10만명에 이르는데 불과 20~30명 소대원을 이끌고 무모하게 돌격명령을 내리는 소대장이 또 나올 수 있다. 통계정보의 중요성이 여기에 있다.

바로 앞에서 dbms_stats.gather_table_stats 프로시저를 이용해 T 테이블에 대한 오브젝트 통계를 수집하는 것을 보았다. 오브젝트 통계 수집 전에 시스템 통계도 미리 수집되어 있어야 한다. 아래는 dbms_stats.gather_system_stats 프로시저를 이용해 시스템 통계를 수집했을 때 구해지는 통계 항목들이다.

• CPU 속도
• 평균적인 Single Block 읽기 속도
• 평균적인 Multiblock 읽기 속도
• 평균적인 Multiblock I/O 개수

정리하면, <① 옵티마이저모드를 포함해 적절한 초기화 파라미터를 설정해주고, 적절한 통계 정보를 수집>해주는 것이 무엇보다 중요하다. 이것이 기본이 된 상태에서 <② 전략적인 인덱스 구성이 필수적>으로 뒷받침되어야 한다. 그리고 <③ 기타 다양한 DBMS 기능들을 적극 활용해 옵티마이저가 최적의 선택을 할 수 있도록 수단을 제공>해주어야 한다.

(3) 필요하다면, 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

최적의 옵티마이징 팩터를 제공했다면 가급적 옵티마이저 판단에 맡기는 것이 바람직하지만 옵티마이저가 생각만큼 최적의 실행 계획을 수립하지 못하는 경우가 종종 있다. 그럴 때는 어쩔 수 없이 힌트를 사용해야 한다.

애플리케이션 특성에 따라서는 힌트 사용을 최소화하기보다 적극적으로 사용해야 할 때도 있다. 통계 정보나 실행 환경 변화에 따라 실행 계획이 동적으로 바뀌었을 때 매우 심각한 결과를 초래하는 시스템들이 있기 때문이다. 예를 들어, 주식 시장에서 매도, 매수 주문을 받아 체결을 성사시키고 그 결과를 기준으로 현재의 주가 정보를 제공하는 거래소 시스템이라든지 전국 보건소 업무를 처리하는 중앙 집중화된 의료 시스템 등은 옵티마이저의 작은 실수가 치명적인 결과를 초래한다. 중요한 몇몇 테이블의 통계 정보를 고정시키는 방법도 있지만 그것만으로는 안심할 수가 없다. 이들 시스템이 마비되는 상황을 상상해보라. 실수가 있더라도 옵티마이저의 자율적 판단에 맡기자는 말을 감히 할 수가 없다.

아래는 옵티마이저 힌트를 이용해 실행 계획을 제어하는 방법을 예시하고 있다.

SELECT /*+ leading(a) use_nl(e) index(d dept_loc_idx) */ *
FROM   emp e, dept d
WHERE  e.deptno = d.deptno
AND    d.loc = 'CHICAGO';

또한, 옵티마이저 힌트를 쓰더라도 최적의 실행 계획으로 처리되는지를 반드시 확인해야 한다.

CBO 기술이 고도로 발전하고 있긴 하지만 여러 가지 이유로 옵티마이저 힌트의 사용은 불가피하다. 따라서 데이터베이스 애플리케이션 개발자라면 인덱스, 조인, 옵티마이저의 기본 원리를 이해하고, 그것을 바탕으로 최적의 액세스 경로로 유도할 수 있는 능력을 필수적으로 가져야 한다. 2권에서 그런 원리들을 자세히 설명한다.