플랜이란 무엇인가?
CBO 방식에서 옵티마이저는 주어진 환경(통계정보, SQL문) 하에서 최적의 실행계획(PLAN)을 우리에게 제공한다. 옵티마이저는 우리에게 어떤 경로로 테이블을 접근하는지, 어떤 방식으로 조인하는지, 어떤 인덱스 자원을 사용하는지 등에 대한 최적화한 계획(플랜)을 알려준다.
만약 옵티마이저가 제공하는 플랜이 완벽하다면 오라클은 우리에게 플랜을 제공할 필요가 없으며, 쿼리문에 힌트절을 허용할 이유도 없을 것이다. 물론 개발자들인 우리도 플랜에 대해 알아야 할 이유가 전혀 없을 것이다. 하지만 플랜은 완벽하지 않다. 오히려 간혹 잘못된 정보를 주곤 한다.
체스 챔피언과의 시합에서는 수많은 기보에 대한 충분한 분석이 이루어져서 우승하였지만, 수많은 개발자들이 작성한 무수히 많은 쿼리문에 대해 케이스별로 올바르게 분석하고 대응한다는 것은 불가능에 가깝다. 또한 퀴즈 대결에서는 방대한 데이터 축적과 빅데이터 처리에 최적화한 강력한 성능의 슈퍼 컴퓨터로 우승하였지만, 오라클 서버는 슈퍼컴퓨터가 아니라 그냥 약간 좋은 컴퓨터일 뿐이다. 그리고 쿼리를 분석하는 데 충분한 시간도 주어지지 않는다.
이와 같이 한정된 분석자료, 한정된 서버자원, 한정된 처리시간 하에서 오라클 옵티마이저가 우리에게 완벽한 플랜을 제공하리라는 어떠한 희망도 근거도 없다. 단지 대체로 괜찮은 플랜을 제공할 뿐이다. 수시로 올바르지 않은 플랜을 제공하기도 한다. 이와 같은 이유 때문에 우리는 플랜을 볼 줄 알아야 하고, 이해할 줄 알아야 하고, 비교할 줄 알아야 하며, 변경할 줄 알아야 한다.
이번 연재 목표는 플랜을 쉽게 보고 쉽게 이해하는 것이다. 또한 플랜을 비교하는 것이다. 비교하기 위해서는 오라클 옵티마이저가 제공하는 플랜 외에 개발자 자신이 생각하는 플랜이 있어야 한다. 플랜을 비교할 때는 개발자가 생각하는 플랜이 항상 올바른 것이고 옵티마이저가 제공하는 플랜은 틀렸다고 전제하고 시작해야 한다. 대부분은 일치하겠지만 만약 서로 다르다면 개발자 플랜을 우선해야 한다. 옵티마이저의 플랜은 의심스러운 눈길로 바라봐야 한다.
연재의 말미에는 개발자가 생각하는 플랜을 필자는 어떤 방법으로 쉽게 표현하는지에 대한 내용도 있으며, 쿼리 제작 시 개발자의 생각(플랜)을 어떤 방법으로 쿼리에 포함 시키는지에 대한 내용도 있다.
오라클 플랜 보는 법
플랜에 대한 내용은 기본적인 내용이라 일반적인 DB 도서에서는 자세한 내용을 설명하지 않는다. 하지만 필자가 경험하기로는 상당수 개발자들이 의외로 모르고 있는 경우를 많이 보았다. 심지어 쿼리 작성 후 데이터 결과만 확인할 뿐 플랜을 아예 보지 않는 개발자도 많았다. 쿼리 제작 후에는 반드시 플랜을 확인하는 습관을 가져야 한다. 아래 플랜을 보면서 하나씩 배워 보자.
위 플랜을 해석하면 다음과 같다.
ACCESS FULL : 고객 테이블 FULL SCAN(전체 접근)
Cost=633K : 633,000 비용발생(논리적 비용 = IO + MEM + CPU + NET + …)
Card=42M : 42,000,000건(접근하는 레코드 수)
Bytes=15G : 15,000,000,000(42,000,000 * 1 ROW의 총 길이)
ACCESS FULL은 고객 테이블을 FULL SCAN하겠다는 의미다. 어떠한 인덱스도 통하지 않고 테이블을 직접 접근해 전체 데이터를 읽는다는 의미다. 플랜에서 이 용어가 보인다는 것은 다음의 3가지 경우에 해당한다.
첫째, 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로써 필요한 인덱스를 생성함으로써 해결 가능하다.
둘째, 인덱스는 존재하지만 부정확한 통계정보로 인하여 인덱스를 타지 않는 경우로써, 최신의 통계 정보를 구성하거나 힌트절을 사용해 해결할 수 있다.
셋째, 테이블 FULL SCAN하는 것이 인덱스를 통한 랜덤 엑세스보다 유리한 경우로써, 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별로 효용성이 없을 때다. 위의 쿼리는 조회조건이 없으므로 바로 이 경우에 해당한다.
Cost는 비용을 의미하는데 해당 쿼리가 동작되었을 때 소요하는 비용을 말한다. 비용이 크면 클수록 오라클이 많은 일을 하고 있다고 생각하면 된다. 즉 무거운 쿼리인 것이다. 여기서 비용은 물리적 비용이 아니라, 논리적 비용을 의미한다. 논리적 비용이란 직접적이고 구체적인 수치에 의해 명확하게 알 수 있는 비용이 아니다. 오라클 옵티마이저가 산출한 비용에 대해 우리는 왜 그러한 비용값이 계산되었는지 이해할 필요까지는 없다. 단지 플랜 비교 시 비교 기준 값으로는 삼을 수 있다.
Card(Cardinality)는 쿼리 조건에 맞는 레코드 건수를 의미한다. 참고로 우리는 K는 10의 3승을 의미하고, M은 10의 6승을 의미하고, G는 10의 9승을 의미함을 이미 알고 있다. 위의 플랜에서 Card 값은 42M이므로 고객 테이블의 데이터 건수가 4,200만임을 알 수 있다.
Bytes는 쿼리 실행,시 발생하는 네트워크 트레픽을 의미한다. 즉 I/O 발생량이다. 1Row를 구성하는 컬럼의 길이 총 합을 구한 후 Card 값을 곱하면 된다. 결국 위의 플랜에서는 15,000,000,000 Byte라는 어마어마한 네트워크 트레픽이 발생함을 알 수 있다.
위의 플랜을 종합하면, 해당 쿼리는 고객 테이블을 FULL SCAN으로 접근해 4,200만 건의 데이터를 읽어오며, 이때 15,000,000,000Byte의 네트워크 트래픽을 유발한다. 그리고 이때 비용은 633K가 발생함을 오라클 옵티마이저가 알려 주고 있다.
그럼, 계속해서 다음 플랜을 살펴보자. 아래 플랜에서 우리는 플랜을 해석하는 순서를 알아볼 것이다. 또한 ORDER BY 절이 있는 쿼리의 비용(Cost) 부분에 주목해서 살펴볼 것이다.
플랜을 해석하는 순서는 다음과 같다.
첫째, 레벨(깊이)이 다른 경우에는 안쪽 레벨부터 해석한다.
둘째, 레벨(깊이)이 같은 경우에는 위에서 아래로 해석한다.
따라서 위 플랜은 1번(가져와서), 2번(SORT해서), 3번(보여준다)순으로 해석하면 된다.
플랜을 자세히 보면, 고객 테이블을 FULL SCAN하는 1번의 비용(Cost)은 633K인데 반해, SORT하는 2번의 비용(Cost)은 6M임을 알 수 있다. 결국 4,200만 건의 데이터를 가져오는 비용보다, 가져온 데이터를 SORT하는 비용이 10배 가량 높음을 알 수 있다. 대부분의 개발자들은 튜닝 시 조건절 컬럼의 인덱스 유무에 관심을 갖지만, SORT를 없애는 것도 튜닝에서 중요한 부분임을 알 수 있다.
계속해서 다음 2개의 플랜을 동시에 살펴보자.
위 2개의 플랜에서 쿼리의 차이점은 ROWNUM < = 1 혹은 2 부분이다. 테이블을 FULL SCAN하기 위해 접근하지만 COUNT(STOPKEY) 부분에서 레코드 건수가 1 혹은 2가 되었을 때 SCAN을 중지하고 빠져 나옴을 알 수 있다. 2개의 플랜에서 ROWNUM 값이 1 혹은 2에 따라서 Card 값과 Bytes 값이 배수가 됨을 알 수 있다. 그런데 비용(Cost) 값은 왜 같을까? 그것은 고객 테이블의 첫 번째 레코드와 두 번째 레코드가 동일 블록에 저장돼 있기 때문일 것이다. 참고로 오라클은 최소 운반 단위인 블록 단위로 데이터를 운반한다.
이번엔 좀더 복잡한 플랜을 인덱스 생성도와 같이 비교해 살펴보자.
플랜의 해석 순서는, 깊이가 다른 경우에는 안쪽에서 바깥쪽으로, 깊이가 같은 경우에는 위에서 밑으로 해석한다고 이미 설명하였다. 따라서 위 플랜은 1 → 2 → 3 → 4 → 5 → 6순으로 해석한다. 그리고 플랜의 내용에서 스캔은 UNIQUE SCAN임을 알 수 있고, 두 테이블의 조인 방식은 NESTED LOOP JOIN임을 알 수 있다. 즉, 순차적 루프에 의한 접근 방식이다. 조인 방식에 대한 더 자세한 내용은 지난 연재인 ‘오라클 조인의 방식’에서 확인할 수 있다.
플랜의 해석 순서를 그림으로 변환하면 인덱스 생성도와 동일하다는 것을 우리는 알 수 있다.
(주문번호 인덱스 → 주문 테이블 → 고객번호 인덱스 → 고객 테이블)
이번에는 고객 테이블에 인덱스가 없는 경우를 가정해 보았다. 인덱스 생성도와 같이 살펴보자. 존재하지 않는 이미지입니다.
고객 테이블의 고객번호 컬럼에 인덱스가 없어서 고객 테이블에서 FULL SCAN이 발생하고 있다. 실제 리턴 결과 건수는 1건이지만, 인덱스가 존재하지 않음에 따라 고객 테이블의 전체 데이터를 FULL SCAN하고 있는 것이다. 여기에서 우리는 Card=1이라는 것에 주목할 필요가 있다. 비록 인덱스는 없지만 고객 테이블에 통계정보가 구성돼 있음을 유추할 수 있고, 고객번호는 UNIQUE함을 추정할 수 있다. 따라서 고객번호 컬럼을 인덱스로 생성해야 함을 알 수 있다. 플랜에서는 인덱스를 생성해야 할 컬럼을 직관적으로 바로 알기는 어려우나, 인덱스 생성도를 함께 이용하면 어떤 위치에 어떤 인덱스를 생성해야 하는지 한눈에 알 수 있다.
덧붙여, 테이블을 FULL SCAN한다는 의미는 다음의 3가지 경우에 해당함을 다시 한번 강조한다.
첫째, 해당 쿼리에 대한 적절한 인덱스가 존재하지 않는 경우로서 필요한 인덱스를 생성해 해결 가능하다. 둘째, 인덱스는 존재하나 부정확한 통계정보로 인하여 인덱스를 타지 않는 경우로서 최신의 통계 정보를 구성하거나 힌트절을 사용해서 해결 가능하다. 셋째, 테이블을 FULL SCAN하는 것이 인덱스를 통한 랜덤 엑세스보다 유리한 경우로써 데이터 조회 범위가 커서 인덱스를 사용하는 것이 별 효용성이 없을 때다.
이번에는 조인절 양방향 모두에 인덱스가 없는 경우를 가정해 보았다.
첫째, 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후 고객번호 순으로 정렬한다(SORT).
둘째, 주문 테이블에서 주문일자가 ‘20150112’인 주문을 구한 후 고객번호 순으로 정렬한다(SORT).
셋째, 정렬된 고객 정보와 주문 정보를 고객번호 컬럼으로 결합한다(MERGE).
하지만 Sort Merge Join 방식은 성능상 문제가 많은 조인 방식이다. 해결 방안은 조인절 컬럼에 인덱스를 생성하여 Nested Loop Join 방식으로 플랜이 풀리게 해야 한다. 만약에 고객 테이블의 고객번호를 인덱스로 생성한다면 주문 테이블에서 고객 테이블로 순차적으로 접근할 것이고, 주문 테이블의 고객번호를 인덱스로 생성한다면 고객 테이블에서 주문 테이블로 순차적으로 접근할 것이다.
만약, 업무 로직상 조인절에 인덱스를 생성하기 곤란한 상황이라면 힌트절을 추가해 Hash Join 방식으로 접근하는 것도 좋은 방법이다. 대부분 Hash Join 방식이 Sort Merge Join 방식보다 성능이 더 좋다. 그래서 요즘 Sort Merge Join 방식은 거의 볼 수 없고 Hash Join 방식을 많이 사용한다.
아래의 경우가 힌트절을 추가해 Hash Join 방식을 설명한 것이다.
Hash Join 방식은 해시 함수를 이용한 접근 방식인데, 대량의 데이터 처리에 효율적인 조인 방식이다. Nested Loop Join 방식에서 처리 범위가 부담스럽거나, Sort Merge Join 방식에서 정렬(Sort)이 부담스러울 때 사용한다. Hash Join 방식의 처리 순서는 다음과 같다.
첫째, 고객 테이블에서 고객명이 ‘홍길동’인 고객을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 분류해해시 테이블을 생성한다(해시 함수를 이용해 해시 테이블 생성).
둘째, 주문 테이블에서 주문일자가 ‘20150112’인 주문을 구한 후, 조인절 컬럼인 고객번호를 해시 함수로 변환해 해시 테이블로 순차적으로 접근한다(해시 함수를 통해 해시 테이블 탐색).
메모리에 해시 테이블을 생성하고 해시 함수를 이용하여 연산 조인을 하기 때문에 CPU 사용이 증가할 수 있으므로 조회 빈도가 높은 온라인 프로그램에는 적합하지 않는 조인 방식이다.
끝으로, UNION ALL과 UNION 관련하여 다음 2개 플랜을 동시에 살펴보자.
쿼리에서 UNION ALL 구문을 사용하면 중복되는 데이터를 있는 그대로 모두 보여준다. 하지만 UNION 구문을 사용하면 중복되는 데이터를 제거하고 UNIQUE하게 보여준다는 것을 우리는 이미 알고 있다. 위의 2개 플랜의 차이점은 SORT (UNIQUE) 부분이다. 이것의 의미는 데이터를 정렬한 후에 중복된 데이터를 제거하고 UNIQUE하게 보여 준다는 의미다.
그런데 SORT는 왜 하는가? 왜냐하면 SORT는 중복된 데이터를 제거하는 가장 단순하고 쉬운 방법이기 때문이다. SORT 이후에 동일한 값이 연속 되어 있으면 제거하면 되기 때문이다.
예전에는 화투 1~2세트는 갖고 있는 집이 많았다. 화투는 기본적으로 1월(솔)부터 12월(비)까지 총 48장으로 구성되는데, 만약 화투 개수가 48장 이상이라면 중복된 화투장을 찾아 빼야 할 것이다. 이때 화투를 모두 펼쳐놓고 같은 모양을 찾아서 빼기란 쉬운 일이 아니다. 아마도 대부분의 사람들은 1월부터 12월까지 화투를 정렬(SORT)한 후, 중복되는 화투장을 찾아내는 방법을 쓸 것이다. UNION 구문 사용 시에 오라클 옵티마이저도 우리와 동일한 방법으로 중복된 데이터를 제거한다. 바로 SORT(정렬)을 이용하고 있는 것이다.
오라클 옵티마이저의 실행계획과 개발자의 실행계획
오라클 옵티마이저의 실행계획(Plan)은 통계정보(과거+현재)를 기반으로 한다. 그에 반해 개발자의 실행계획(Plan)은 과거 + 현재 + 미래 정보를 기반으로 한다. 당연히 개발자의 실행 계획이 더 옳을 것이다. 개발자들은 향후 데이터 증감 추이가 어떻게 될 것인지, 생성 예정인 인덱스는 무엇인지, 또 프로그램을 누가 어떤 용도로 얼마나 빈번하게 사용하는지 등, 오라클 옵티마이저보다 더 많은 정보를 얻을 수 있기 때문에 더 좋은 플랜을 계획할 수 있다.
오라클 옵티마이저 실행계획과 개발자의 실행계획이 일치하면 좋겠지만 항상 일치하진 않는다. 지금까지의 경험상으로 90% 이상은 일치했고, 10% 미만은 일치하지 않았다. 그렇다면 왜 일치하지 않는 걸까? 다음과 같은 여러 가지 이유로 인하여 일치하지 않았다.
첫째, 통계정보 구성이 실제 데이터를 반영하지 못하거나 없는 경우
둘째, 적절한 인덱스가 존재하지 않거나 부적절한 경우
셋째, 쿼리가 최적화 되어 있지 않는 경우나 잘못 사용된 경우
넷째, 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 현실적인 문제
실행계획이 일치하지 않는 경우에는 통계정보를 재구성하거나 필요한 인덱스 생성 및 쿼리 최적화로 어느 정도 해결할 수 있다. 하지만 옵티마이저의 알고리즘 기술수준 문제에 있어서는 해결 방법이 없다. 이때에는 힌트절을 추가하여 인위적으로 플랜을 변경해야 한다. 오라클이 힌트절 기능을 제공한다는 의미는 곧 오라클 옵티마이저의 알고리즘이 완벽하지 않다는 의미다. 향후 오라클 버전에서 성능 개선이 점차 이루어진다면 힌트절 기능이 축소되거나 동작에 제한이 걸릴 수도 있다. 이는 개발자 중심의 실행계획에서 오라클 중심의 실행계획으로 주도권이 넘어간다는 의미다. 만약 그런 상황이 실제로 온다면 개발자들에게 좋은 상황인지 나쁜 상황인지 고민스럽다.
오라클 옵티마이저의 실행계획은 어떤 내용인지 이미 알고 있다. 그렇다면 개발자의 실행계획은 무엇인가? 개발자의 실행계획은 개발자의 마음속에 있는 실행 계획이다. 쿼리를 제작할 때 대부분의 개발자들은 이미 마음속에 실행계획(접근순서, 접근방법, 자원선택)을 갖고 있다. 하지만 그러한 실행계획은 어느 순간이 지나면 잊혀지는 찰나적인 생각일 뿐이다. 이러한 고민을 해결하고자 필자는 쿼리를 제작할 때 실행계획의 내용을 쿼리에 포함시키는 방법을 사용한다. 바로 공정 쿼리다.
우리는 인덱스 생성도에서 테이블 접근 순서 및 인덱스 생성 위치를 알 수 있다. 또한 공정쿼리 작성법에 의해 작성된 쿼리에서도 테이블 접근 순서 및 인덱스 생성 위치를 쉽게 알 수 있다. 작성된 쿼리에 개발자가 생각하는 실행 계획이 내포되어 있어서, 오라클 옵티마이저가 제시하는 플랜과의 비교 작업이 한결 쉬워졌고 언제든지 가능해졌다.
공정쿼리 방식으로 제작한 쿼리는 다른 개발자가 처음 보더라도 쉽게 플랜을 유추해 낼 수 있고 인덱스 생성 위치를 바로 알 수 있어서 좋다. 더 자세한 내용은 지난 연재인 ‘공정쿼리 작성법’을 참고하기 바란다. 결론적으로 인덱스 생성도와 공정쿼리와 플랜은 형태는 틀리지만 본질적으로 동일한 의미를 지니고 있으므로 삼위일체라 생각할 수 있다.
바인드 변수와 하드 파싱
대부분의 개발자들은 튜닝시 플랜을 상수값으로 테스트하지만, 실제로 바인드 변수로 운영되는 경우에는 플랜이 다를 수 있다. 프로그램에 바인드 변수로 되어 있다면 반드시 바인드 변수로 플랜을 확인해야 한다. 아래 두 개의 쿼리를 살펴보자.
SELECT * FROM 고객 WHERE 고객명 = ‘홍길동’ → 상수값
SELECT * FROM 고객 WHERE 고객명 = :NAME → 바인드 변수
위의 두 개의 쿼리는 동일한 결과를 조회하지만 플랜은 다른 수 있다. 이와 같은 쿼리들은 대부분의 경우 플랜이 동일하지만 간혹 다른 경우도 있으므로, 쿼리가 어떤 방식으로 운영되는지에 따라 플랜을 구분-확인해야 한다.
그럼, 바인드 변수를 사용하는 이유는 무엇일까? 바인드 변수 사용은 하드 파싱을 줄이기 위한 수단이다. 오라클 옵티마이저는 상수값이 다르면 서로 다른 쿼리로 인식해 파싱을 새로 한다. 특히 실행 횟수가 많고 컬럼의 distinct 값이 크다면, 하드 파싱이 자주 발생해 시스템 전반적으로 많은 메모리를 사용할 것이고 CPU 부하도 높을 것이다. 반면에 바인드 변수를 사용한다면 오라클 옵티마이저는 동일한 쿼리로 인식하기 때문에 파싱을 매번 하지는 않는다. OLTP 프로그램은 데이터베이스 성능을 고려해 바인드 변수 사용을 권고한다.
이번 연재에서는 오라클 옵티마이저가 제공하는 플랜과 개발자가 생각하는 플랜을 어떠한 방법으로 쿼리문에 구현해야 하는지에 대해 배웠다. 다음 연재에서는 개발자들의 영원한 숙제인 ‘NULL’에 대해 자세히 알아본다.
|
용기를 갖자 오라클 DB뿐 아니라 대부분의 DB 구성 알고리즘은 어느 날 ‘하늘에서 뚝 떨어져 새로 만들어진 것’이 아니라 실생활에서 이용되는 혹은 이미 상식 수준에서 인지되는 그런 보편적인 원리를 바탕으로 만들어졌으므로 쉽게 접근하고 이해할 수 있다. 서두에서 말했듯이 ‘레몬시장이론’을 상기하며 DB를 지레짐작으로 어려워하지 말고 용기를 내고 하나씩 터득해 나가기를 바란다. 이 글은 DB 전문가 수준의 이해를 요구하지는 않는다. 단지 DB에 대해서 더 친숙하고 더 쉽게 이해하고 접근하길 바랄 뿐이다. 이 글을 읽으면서 궁금하거나 의문 나는 점이 있으면, 댓글을 달아주실 것을 적극 바란다. 아무리 어렵고 힘든 일이더라고 ‘관계’와 ‘소통’으로 풀어나갈 수 있음을 다시 한 번 믿으며...
|
[지난 문제의 정답과 풀이]
원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제
지난 연재에 출제한 ‘원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제’에 대한 정답과 해설은 아래와 같다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다.
[이번 호 문제]
원리를 이해하고 논리로 풀어가는, 쉬어가는 DB 문제
각 연재의 말미에 간단하면서도 재미있고 생각해 보는 문제를 출제하려 한다. 모든 문제는 DB의 원리를 이해할 수 있는 문제로 출제할 예정이다. 문제를 풀면서 DB 원리를 하나씩 배우고 이해할 수 있다. 정답과 그에 대한 설명은 다음 연재에서 한다.
'프로그래밍 > 데이터베이스' 카테고리의 다른 글
| [MSSQL] 테이블 정보 조회 (0) | 2023.03.10 |
|---|---|
| [DB] DBeaver 쿼리 조회 결과창 필드명에 주석 표시하기 (0) | 2023.03.07 |
| [오라클] Protocol Violation (0) | 2023.03.07 |
| [MSSQL] DB LOCK 관련 (0) | 2023.03.07 |
| [MSSQL] 특정 컬럼이 포함된 테이블 찾는 쿼리 (0) | 2023.03.07 |
댓글