4.1 SQL문의 분석과 공유 풀은 왜 배워야 하는가?
아무리 힘이 센 사람이라고 해도 작업을 효율적으로 하지 않으면, 힘이 약한 사람보다 작업량이 적을 수도 있다. RDBMS 역시 마찬가지이다. 아무리 디스크를 늘리고 클럭이 빠른 CPU를 추가하더라도 처리 방법 자체가 나쁘다면, 보잘겂없는 장비에서 동작하지만 처리 방법이 더 효율적인 RDBMS에게 질 수 밖에 없다. 또한, SQL을 처리하는 방법을 생성하는 데는 CPU를 오래 사용하므로, 처리 방법을 생성하는 횟수를 줄이면 데이터베이스 전체의 성능을 높일 수도 있다. 이번 장에서는 '분석(parse, 파스)'이라 부르는 처리 방법의 생성과 생성한 처리 방법을 캐시하는 장소인 '공유 풀(shared pool)'에 관해서 설명한다.
4.2 SQL문과 일반적인 프로그래밍 언어의 차이
일반적인 프로그래밍 언어와 SQL의 차이는 무엇일까? 여러 가지가 있을 거지만, SQL은 '처리 방법(절차)'을 기술하지 않는다는 점이 갖아 큰 차이라고 말할 수 있다. 프로그래밍 언어는 객체지향 언어, 스크립트 언어, 어셈블러에 상관없이 처리 방법을 기술해야 한다. 여러분도 프로그래밍할 떄는 '여기에서 데이털르 꺼내 와서 반복문을 수행하고 조건문을 사용해 분기를 시킨다'와 같이 처리 방법을 기술했을 거다. 이에 비해 SQL문에서는 '데이터의 조건이나 관계'만을 기술한다.(기본적으로 '데이터가 만족하는 조건이나 관계'를 기술하지만, 오라클은 힌트(hint)라는 기능을 이용해서 오라클의 처리 순서를 어느 정도는 지시할 수 있다. 예를 들어, 'SELECT /*+ index(A B)*/ FROM A WHERE C = 1'이라는 SQL문은 테이블 A의 B라고 하는 인덱스를 사용해서 처리하도록 지시한 것이 된다. 또한, 여러 개의 테이블을 조인하여 검색하는 SQL문에서는 ordered라고 하는 힌트를 사용해서 테이블의 조인 순서를 지시할 수 있다.)
예를 들어, 'SELECT A FROM B WHERE C = 1'과 같은 SELECT문은 테이블 B에서 'C = 1'이라는 조건을 만족하는 데이터에서 A라고 하는 속성(컬럼)의 정보를 꺼내오라는 SQL문이다. 어디에도 '인덱스를 사용해서'나 '풀 스캔을 하라'와 같은 처리 방법을 기술하지 않았다는 점에 주목하자.
어떤 작업을 할 때 누군가가 처리 방법을 생각하지 않으면 작업을 처리할 수 없다. RDBMS에서는 사람이 아니라 '옵티마이저(파서, parser)'라고 불리는 기능이 처리 방법을 생각해준다. 옵티마이저(파서)가 SQL문을 분석하고 '실행 계획(plan, 플랜)'이라고 하는 처리 방법을 생성해준다.(오라클의 실행 계획은 일반적으로 '액세스 패스(access path)'라 불리는 것과 거의 같다고 생각하면 된다. 실행 계획에는 인덱스 사용 여부 및 테이블에 접근하는 방법, 테이블의 검색 순서, 테이블의 조인 방법과 같은 내용이 포함되어 있다.)
오라클에서의 분석이란, SQL문을 분해해서 어떤 요소(테이블이나 컬럼 등)로 구성되어 있는지를 조사하는 것뿐만 아니라 어떤 식으로 처리할지까지 생각하는 것을 의미한다. 여기서 말한 '생각한다'라는 것은 결국 컴퓨터가 하는 것이므로 알고리즘을 기반으로 처리하는 것일 뿐이다. 오라클은 '규칙 기반(rule base)'과 '비용 기반(cost base)'이라는 알고리즘을 가지고 있다. 단, 오라클 10g부터 규칙 기반을 지원하지 않으므로 여기서는 비용 기반에 관한 이야기만 하겠다.
비용 기반이라는 것은 간략히 이야기해보자면, '처리 시간이나 I/O 횟수가 가장 작다고 생각되는 처리 방법이 최상이다'라는 알고리즘이다. 오라클에서는 처리 시간이나 I/O 횟수를 예측하기 위해서 '비용(cost)'이라고 불리는 수치를 이용한다. 비용을 단순하게 이야기하면, '처리에 필요하다고 생각되는 시간 또는 자원 사용량'이다. 즉, 인간이라면 작업을 가장 빠르게 처리할 수 있다고 생각되는 방법을 선택하는 것과 마찬가지이다.
4.3.1 비용을 계산하기 위한 기초 수치, '통계 정보'
그런데 창고 회사 사원의 입장에서 생각해보면, '이런 데이터가 필요해'라는 것만으로는 처리하는 데 시간이 얼마나 걸릴지 예측할 수 없다. 따라서 시간을 예측하기 위한 기초 수치(데이터양 등)가 필요하다. 실제로 비용은 '통계 정보'라 불리는 기초 수치를 기반으로 계산된다. 통계 정보는 '이 테이블에는 데이터가 몆 로우(행)가 존재하고 있으며, 데이터의 양은 이 정도다. 칼럼의 데이터 최댓값과 최솟값은 이런 값이다. 해당 테이블의 인덱스는....'과 같은 테이블이나 인덱스에 관한 기초 수치를 말한다. 창고 회사에서도 '이 물건은 몆 개 정도 있고...'와 같은 기초 수치를 사전에 파악 해둘 사원이 있다면, 보다 정확한 작업 시간을 예측을 할 수 있을 것이다. 즉, 의뢰를 받은 후에 기초 수치를 조사한다면 이미 늦은거다. 오라클에서는 통계 정보를 '애널라이즈(Analyze)'라고 불리는 작업을 통해 얻는다.
애널라이즈는 관리자가 수행하지 않더라도 오라클이 자동으로 수행한다. 이러한 비용의 계산에 필요한 정보를 정리한 것이 그림 4.2다.
그림 4.2 비용 계산에서는 여러 정보가 필요
SQL문의 정보
- 어떤 테이블의 어떤 데이터인지
- 어떤 조건(WHERE절)
- 어떤 관계(어떤 테이블과 어떤 테이블이 조인하는지) 등
초기화 파라미터
- 단일 I/O로 읽어올 수 있는 블록 수
- 세션에서 이용할 수 있는 메모리 크기 등
옵티마이저 통계
- 테이블 통계(로우 및 블록의 정보)
- 컬럼 통계(컬럼 값 및 데이터 분포 정보)
- 인덱스 통계(인덱스의 깊이, 인덱스 블록의 수 및 인덱스와 데이터 블록의 관계 정보)
- 시스템 통계(I/O, CPU의 성능 및 사용률 정보) 등
----> 옵티마이저 : 옵티마이저는 주로 이 세개의 정보로 비용을 계싼하여 최적의 실행 계획을 생성한다.
4.4 실행 계획이 최적이라는 것을 판단하기 위해서는?
실행 계획이 최적이 아닐 경우 어떤 일이 발생하게 될까? 리스트 4.1과 같은 SQL문을 수행한다고 가정해보자.
리스트 4.1 테이블 A와 테이블 B를 조인하는 SQL문
SELECT * FROM A, B WHERE A.ID = B.ID AND A.value =1 AND B.value = 1;테이블 A의 ID, value, 그리고 테이블 B의 ID, value의 네 가지 컬럼에 인덱스가 생성되어 있다고 가정해보자. 우선은 테이블 A에서 검색한 후에 테이블 B를 검색하는 방법과 테이블 B에서 검색한 후 테이블 A를 검색하는 방법이 바로 떠오를 것으로 생각한다. 하지만 테이블 B는 데이터가 100건만 들어가 있고, 테이블 A는 1,000만 건의 데이터를 가지고 있다고 가정해보자. 더욱이 테이블 A의 value 컬럼의 값은 대부분 1이라고 가정한다면 어떨까?
테이블 A -> 테이블 B 순으로 검색하는 처리가 상당히 무겁지만, 테이블 B -> 테이블 A 순으로 처리하는것은 데이터를 즉시 찾아오기 떄문에 처리가 매우 가볍다. 이렇게 DBMS는 실행 계획의 좋고 나쁨으로 인해 매우 큰 성능 차이가 발생한다.
현장 용어
- 풀차다
'가득 차다'라는 의미이다. 한계까지 도달했을 떄 '풀 찼다'라는 말을 사용한다. 예를 들어 '테이블스페이스가 풀차서 장애가 발생했어'처럼 이야기하며, 여기서 '풀'은 영문 FULL의 발음을 그대로 표기한 것이다.
- 행걸리다
프로그램 수행 중 멈춰서 더 이상 진행이 되지 않는 상황(hang)을 의미한다. 예를 들어 '세션이 행에 걸려서 멈춰있다'와 같이 성능 이슈나 여러가지 장애로 인해 작업이 진행되지 않고 멈춰 있는 상태를 의미한다.
그러면 대체 무슨 수로 '어떤 처리 방법이 가장 좋은지(비용이 가장 적을 것 같은지)'를 판단하는 것일까? 기본적으로는 모든 처리 방법의 비용을 계산해서 비교하는 것 이외에는 방법이 없다. 하지만 실제로 '기본적으로는 모든 처리 방법의 비용을 계산한다'면 큰일이다. 만약 사람이 실행 계획을 생각하는 입장이라고 한ㄴ다면 짜증이 날 정도로 많은 개수라는 것을 알 수 있다. 그렇다면 간혹 DBMS가 좋지 않은 실행 계획을 선택하는 이유(원인)는 무엇일까? 대표적으로는 '선택할 수 있는 실행 계획의 수가 많다는 점'과 이런 실행 계획들이 '어디까지나 예측에 지나지 않는다는 점' 때문이다.
칼럼
- 어댑티브 쿼리 최적화의 장점과 단점
오라클의 주 기능이라고 부를 수 있는 옵티마이저는 버전이 올라갈수록 발전해왔다. 오라클 12c부터는 어댑티브 쿼리 최적화(Adaptive Query Optimization)라는 옵티마이저 통계를 보정하는 기능이 추가되었고, 덕분에 사람이 수고하지 않더라도 좀 더 성능이 좋은 실행 계획을 선택할 수 있게 되었다. 그러나 시스템에 따라서는 사용 방법에 주의가 필요하다.
오라클에서는 실행 계획을 결정할 때 사전에 수집해둔 통계 정보를 참고해서 처리 대상 테이블의 추세를 파악한다. 이 추세를 기반으로 가장 처리 비용이 적다고 예측되는 액세스 패스, 조인 순서, 조인 방법 등을 판단한다.
주목해야 되는 부분은 '통계 정보는 사전에 수집된다'라는 점이다. 이 부분은 SQL문을 분석할 떄 발생하는 부하를 줄이기 위한 것이지만, SQL문을 실행할 때 통계 정보와의 괴리가 발생했을 때는 최적의 실행 계획이 선택되지 않을 가능성이 있다. 이를 방지하기 위해서는 DBA가 적절한 수집 시점이나 빈도, 수준으로 통계 정보를 수집하는 작업을 수행해야 한다.
오라클 12c에서 추가된 어댑티브 쿼리 최적화 기능으로 인해 SQL문을 실행할 때 추가로 통계 정보를 동적으로 수집한 후 최종적으로 실행 계획을 선택할 수 있게 되었다. 이로 인해 사전에 수집한 통계 정보의 부족한 부분을 보정하고 좀 더 좋은 실행 계획을 실시간으로 선택하는 것이 가능해졌다.
단, 통계 정보의 동적 수집이 동작하는 만큼 분석하는 시간이 길어지게 된다는 점에 주의해야 한다. 실제로 '오라클 12c에서 SQL문 속도가 느려졌다'라는 사례도 있다. OLTP같이 실행 시간이 짧은 SQL문이 다수 실행되는 시스템에서는 동적 통계 수집으로 인한 분석 시간이 길어지는 경향이 있다. 이때는 정적 통계 수집을 검토하는 것이 좋다.
또한, 시스템에 따라서는 옵티마이저 통계를 정기적으로 수집하지 않거나 통계에 Lock을 걸어 '옵티마이저에 의한 실행 계획의 변동'을 제어하는 경우도 있다. 또한, 초기화 파라미터를 설정하여 실행 계획이 변경되는 것을 방지할 수도 있다.
- 참고 : 오라클 공식 매뉴얼 'SQL Tuning Guide'의 4.4 About Adaptive Query Optimization
4.5 공유 풀의 동작과 구조
분석 처리가 CPU 자원을 얼마나 소비하는지를 설명해보자. 인덱스를 사용해서 한 건의 데이터를 가져오는 간단한 SQL문을 수행했다고 가정하면, 실제 데이터를 처리하는데 필요한 CPU 자원보다 데이터를 처리하기 위한 과정을 생성하는 SQL문의 분석 단계에서 더 많은 CPU를 소모할 수도 있다.(데이터의 처리에 시간이 걸리는 SL문이라면 분석에 사용하는 CPU 자원은 상대적으로 적으므로(분석에 소모되는 자원에) 신경을 덜 쓰게 된다. 데이터의 처리에 시간이 걸리는 대표적인 SQL문의 예로는 데이터 웨어하우스에서 대량의 데이터를 가져오는 SELECT문이다.)
공유 풀은 실행 계획을 재사용하여 분석 작업을 줄이기 위한 존재라고 봐도 좋다. 따라서 공유 풀을 잘 활용하면 CPU 자원의 절약으로도 이어진다. 그러면 공유 풀의 위치와 동작에 대해서 설명하겠다.
공유 풀도 프로세스 간에 공유되어야 하므로 버퍼 캐시와 마찬가지로 공유 메모리에 있다. 공유 메모리의 많은 부분이 버퍼 캐시로 사용되고, 거기서 남은 일부가 공유 풀로 사용된다. 더욱이 공유 풀은 라이브러리 캐시(여기에 실행 계획이 존재한다)나 딕셔너리 캐시 같은 영역으로 나누어진다.
공유 풀
| 딕셔너리 캐시(Dictionary Cache) | 통계 정보의 캐시 등 주로 SQL의 실행에 필요한 메타 정보를 보관함 |
| 라이브러리 캐시(Library Cache) | 공유 풀의 중요한 역활인 실행 계획 등의 SQL 정보는 여기에 캐시됨 |
| 그 외 | 공유 서버라고 불리는 구성일 때는 여기에서 정렬 처리기가 수행되기도함. 공유 풀은 이외에도 여러 군데에 사용되고 있음 |
오라클은 어떻게 SQL문이 같다고 판단할까? 오라클은 해시 알고리즘(빠른 검색을 위한 알고리즘이다. 값이나 문자열을 해시 함수에 입력하면 해시 값이라고 불리는 ID를 생성한다. 생성한 해시 값을 사용하면 빠른 데이터 접근(데이터 검색)을 구현할 수 있다.)을 사용해서 SQL문마다 ID를 생성한다. 좀 더 정확히 설명하면, SQL문을 문자열(데이터)로 해시 함수에 입력하고 함수에서 출력된 해시 값을 SQL문의 ID로 사용한다. 해시 함수에서 대문자와 소문자는 다른 문자이므로 쿼리의 대문자와 소문자가 다르면 받는 해시 값도 달라진다. 사람이 보면 같은 SQL문으로 보이지만, 오라클에서 보면 전혀 다른 SQL문이 된다.
리스트 4.2 사람이 보면 같은 SQL문이지만, 오라클은 그렇게 생각하지 않음
Select id, cust_name, tel_no from cust where id = 1;
select id, cust_name, tel_no from cust where id = 1;이와는 다르게 검색 조건의 값이 다른 SQL문에 바인드 변수를 사용하면 같은 SQL문으로 판단한다(리스트 4.3). 바인드 변수는 프로그램의 변수를 SQL문에서 사용하는 기능이라고 생각하면 된다. 리스트 4.3의 내용을 보면 ':A'라는 변수에는 여러 값이 저장되어 있지만, SQL문의 문자열로 보면 같으므로 SQL문의 해시 값은 변하지 않는다. 즉, 같은 SQL문으로 취급한다.
리스트 4.3 값이 다른 SQL문을 반복해서 실행하고 싶을 떄는 바인드 변수를 사용
-- 실행하고 싶은 SQL문
SELECT id, cust_name, tel_no FROM cust WHERE id = 1;
SELECT id, cust_name, tel_no FROM cust WHERE id = 2;
변수 부분에 차이가 있으므로 실행 계획이 같아도 상관없지만, 매번 분석 작업을 수행하지 않으면 안됨
-- 바인드 변수를 사용해서 수정한 SQL문
SELECT id, cust_name, tel_no FROM cust WHERE id = :A;
SELECT id, cust_name, tel_no FROM cust WHERE id = :A;
':A라는 변수에 1이나 2처럼 값을 넣은 후에 SQL문을 실행하면 위와 같은 작업을 수행함. 오라클은 같은 SQL문이라고
인식하기 떄문에 분석했던 결과가 캐시에 남아 있으면 분석 작업을 수행하지 않음분석(파스)에는 하드 파스(hard parse)와 소프트 파스(soft parse)가 있다. 하드 파스는 지금까지 설명해왔던 분석을 말하는데, 공유 풀에 실행 계획이 없으므로 실행 계획을 생성하는 경우를 의미한다. 소프트 파스는 해시 값을 요청한 결과, 공유 풀에 캐시되어 있는 실행 계획을 찾았기 때문에 재사용하는 경우를 의미한다. 즉, 지금까지 분석을 수행하지 않는다고 설명했던 부분이다.
4.6 수치로 알아보는 분석과 공유 풀의 정보
수치를 사용해서 '분석(파스)'과 공유 풀을 살펴보자. 스태츠팩(Statspack, 오라클의 성능 진단 도구)의 보고서를 확인하는 방법이 간단하고 편리하다. 예를 들어, '전체의 CPU 사용량 중에 분석을 위해 사용하는 CPU가 절반이나 차지하고 있네? 바인드 변수를 사용하도록 하면 성능이 더 좋아질 것 같은데?'와 같은 발견을 할 수 있을 것이다.
칼럼
- 오라클의 성능 진단 도구
스태츠팩은 오라클 표준 성능 진단 도구이다. 스태츠팩은 오라클 8i 이상 버전에서 설치 할 수 있으며, 성능 정보를 수집한다. 예를 들어, 자원을 대량으로 소비하는 SQL문을 특정하거나 캐시 히트율을 확인하는 것으로 병목 현상을 구분할 수 있다.
또한, 오라클 10g 이상부터는 스태츠팩을 진화시킨 AWR(Automatic Workload Repository)이라는 기능을 사용할 수 있다. AWR은 설치할 필요가 없으며, 스태츠팩으로는 수집할 수 없는 통계 정보와 관련된 분석도 수행된다. 다만, AWR을 사용하기 위해서는 Diagnostics Pack 라이선스가 필요하다.
- 참고 : Database Performance Tuning Guide 의 6 Garhering Database Statistics
리스트 4.4 스태츠팩 보고서로 알아보는 분석과 공유 풀의 상황(발췌)
Snap Id Snap Time Sessions Curs/Sess Comment 주: 일부는 더미 데이터
------- ------------------ -------- --------- ---------------
Begin Snap: 11 13-May-05 10:00:00 128 4.0
End Snap: 12 13-May-05 10:30:00 138 4.2
Elapsed: 30.00 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~~
Buffer Cache: 1,000M Std Block Size: 8K
Shared Pool Size: 352M Log Buffer: 10,000K
Load Profile
~~~~~~~~~~~~
Per Second Per Transaction
--------------- ---------------
Redo size: 625,128.42 6,182.12
Logical reads: 50,112.51 4902.04
Block changes: 4,743.25 446.38
Physical reads: 2,350.09 203.48
Physical writes: 335.65 31.20
User calls: 3,725.42 343.22
Parses: 407.24 38.95 -- 영어로 '분석'을 Parse라고 부르며, 'Parse'는 하드 파스와 소프트
Hard parses: 92.25 9.01 -- 파스의 실행 횟수 합계임. 이 환경에서는 1초간 407번 수행됨
Sorts: 1,231.32 112.11
Logons: 3.38 0.30
Executes: 1,789.33 165.21
Trajsactions: 100.10
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 99.23 In-memory Sort %: 99.99
Library Hit %: 95.12 Soft Parse %: 77.34
Execute to Parse %: 77.24 Latch Hit %: 98.12
Parse CPU to Parse Elapsd %: 78.45 % Non-Parse CPU: 54.28 -- Parse 이외에 사용된 CPU양이 54%밖에 안 됨. 즉, 46%가 Parse라는 말임
.
.
.
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ----------
CPU used by this session 364,125 202.3 20.0
CPU used when call started 364,104 202.3 20.0
. -- CPU used by this session(거의 SQL을 처리하는 데 사용한 CPU양)보다
. -- parse time cpu(분석에 사용한 CPU양)가 많음. 원인은 parse count(hard)가 많은 것으로, 하드 파스가 많이 발생했다는 것을 의미함.
. -- 또한, parse count(total)에서 parse count(hard)를 뺀 값이 소프트 파스의 횟수이며, 소프트 파스는 횟수가 많아도 CPU를 크게 소비하지는 않음
parse count (hard) 166,051 92.3 9.1
parse count (total) 733,032 407.2 40.4
parse time cpu 166,478 92.5 9.0
parse time elapsed 212,209 117.9 11.4
.
.
.
SGA breakdown difference for DB: XXXX Instance: XXXX Snaps: 11 - 12
Pool Name Begin value End value % Diff
------ ------------------------------- --------------- ---------------- --------
shared dictionary cache 3,229,952 3,229,952 0.00
.
.
.
shared free memory 2,327,024 2,328,680 0.01
.
. -- 여기의 값은 메모리의 크기임. sql area가 크고, free memory는
. -- 작지만 통틀어서 보면 부족하다고는 말할 수 없음
shared sql area 304,057,200 304,148,417 0.03
-- 'shared'는 공유 풀을 의미하며, 공유 풀의 free
-- memory와 sql area(라이브러리 캐시 내의 실행
-- 계획)의 크기임4.7 요약
- SQL문에는 처리 방법이 적혀 있지 않기 떄문에 오라클이 처리 방법(실행 계획)을 생성할 필요가 있다는 점
- 실행 계획의 좋고 나쁨에 따라 성능이 크게 변한다는 점
- 실행 계획을 생성하기 위해서는 많은 양의 CPU를 사용하기 때문에 공유 풀(라이브러리 캐시)에 실행 계획을 캐시해두고 재활용한다는 점
칼럼
- 통계 정보는 언제 수집해야 하나요?
오라클이 비용을 계산할 때 사용하는 기본 정보가 통계 정보이다. 적절한 실행 계획은 적절한 통계 정보가 있기 때문에 도출해낼 수 있다. 반대로 말하면 실제와 다른 통계 정보를 기반으로 도출되는 실행 계획은 성능에 문제를 일으키는 원인이 될 수 있다.
그러면 적절한 통계 정보를 수집하기 위해 고려해야 하는것은 무엇일까? 답은 바로 '시점'이다.
통계 정보를 단순히 정기적으로 수집하는 것만으로는 충분하지 않다. 가장 중요한 점은 시스템의 특성을 고려한 '적절한 시점'에 수집하는 것이다.
예를 들어, 통계 정보를 자동 통계 수집(기본 설정으로는 평일 22:00부터 다음날 2:00까지 사이)으로 수집하는 상황에 매일 밤 21시에 수행되는 배치가 있는 경우를 생각해보자. 그리고 배치 처리로 인해, 테이블의 데이터양이 낮시간 대비 1/1000 정도로 줄어든다고 가정해보자.
데이터양이 줄어든 시점에 수집된 통계 정보를 기반으로 만들어진 실행 계획을 데이터양이 많은 낮시간에 사용한다면 어떤 상황이 발생할까?
적절한 실행 계획은 데이터의 양에 따라 다르다. 이 경우에는 본래 사용해야 할 인덱스 스캔(INDEX SCAN)이 아니라 풀 스캔을 선택해버리는 문제가 발생할 수 있으며, 치명적인 성능 문제로 이어질 가능성이 높다.
이런 예처럼 일정 주기로 데이터양의 증감이 반복되는 상황에서 데이터양이 많은 낮시간대의 처리를 대비하기 위해서는 일반적으로 데이터양이 많은 시점에 통계 정보를 수집하면 적절한 실행 계획이 만들어질 것이라 예측할 수 있다. 대신에 데이터양이 적은 밤시간에 수행되는 배치 처리는 효율이 떨어질 수 있다.
따라서 데이터베이스 관리자는 통계 정보의 수집 시접, 통계 정보의 고정, 힌트 등을 조합하여 의도한 실행 계획이 도출될 수 있도록 운영해야 한다.
칼럼
- 4장의 지식은 현장에서 어떻게 활용되는가?
현장에서 튜닝할 때는 분석이나 실행 계획에 관한 지식을 사용하므로 관련 내용을 이번 칼럼에서 간략하게 소개하겠다. 다만 여기서 소개하는 내용은 개요 정도이므로 자세하게 알고 싶은 분은 해당 매뉴얼을 참고하자.
- 실행 계획이 나쁘고 SQL문의 성능이 좋지 않을 때
우선은 비용 계산의 기초 정보가 되는 통계 정보가 제대로 수집되고 있는지 데이터베이스 관리자에게 문의해보자. 수집하고 있지 않다면 dbms_stats 패키지(또는 analyze 명령어)를 사용해서 최신 통계 정보를 수집하면 좀 더 좋은 실행 계획이 만들어질 가능성이 높아진다. 단, 관리자의 운영 정책으로 통계 정보를 수집하지 않는 경우도 있으므로 관리자와 협의 없이 수집해서는 안 된다. 통계 정보를 제대로 수집해도 만들어진 실행 계획이 적합치 않을 때는 옵티마이저의 판단이 좋지 않다는 의미이므로, 힌트나 플랜 스태빌리티(Plan Stablity)라고 불리는 기능을 사용해서 오라클에 지시를 내린다.
- 하드 파스가 많으며, 분석에 사용하는 CPU 양이 많을 때
바인드 변수를 사용할 수 있도록 SQL문을 변경할 수 있는지를 검토한다. 하지만 실제 프로젝트에서는 애플리케이션을 수정할 수 없는 경우도 많다. 그럴 떄는 CURSOR SHARING(바인드 변수를 사용하지 않은 SQL문을 오라클 내부에서 바인드 변수를 사용하는 것처럼 변경함으로써 바인드 변수의 장점을 누릴 수 있도록 하는 기능을 설정하는 파라미터)이라고 하는 초기화 파라미터를 설정함으로써 바인드 변수를 적용한 것과 거의 같은 효과를 얻을 수 있다. 단, 이 기능을 사용하기 전에 PSR(Patch Set Release, 오라클 제품에 대한 패치 파일)을 적용하여 혹시 모를 버그 등에 대응할 것을 권고한다.
- 크기에 관한 튜닝
공유 풀의 크기에 관한 튜닝은 아쉽게도 그리 간단하지 않다. 왜냐하면 free memory(미사용 메모리)가 없어질 떄까지는 '사용 빈도가 적은 데이터'인데도 버리려고 하지 않기 때문이다. 다시 말해 'free memory가 적으니까 크기를 늘리지 않으면 안 되겠군'이라고 말 할 수 없다는 것이다. 바인드 변수를 사용하여 소프트 파스로 처리되도록 SQL문을 작성했음에도, SQL문이 캐시에서 자주 밀려나 하드 파싱이 일어나는 경우가 있다. 그럴 때는 공유 풀의 크기를 늘려주면 효과가 있는 경우도 있다.
'DB > Oracle' 카테고리의 다른 글
| [Oracle] Chapter 6. 커넥션과 서버 프로세스의 생성 (0) | 2022.11.05 |
|---|---|
| [Oracle] Chapter 5. 오라클의 기동과 정지 (2) | 2022.10.30 |
| [Oracle] Chapter 3. 캐시와 공유메모리 (0) | 2022.10.23 |
| [Oracle] Oracle Data Pump 와 SQL*Loader (0) | 2022.10.17 |
| [Oracle] 원격 데이터베이스 Application Architecture (0) | 2022.10.14 |