SQL 튜닝에 꼭 필요한 옵티마이저와 실행계획

시즌3 Intro.

튜닝의 ㅌ도 몰랐던 비컴공 개발자였던 2020년 가을의 나, 팀내 개선 프로젝트로 우리 팀/회사에서 가장 유명하고 현장 사용 빈도가 높은 화면의 HeavyTR을 줄이기 위해 튜닝을 갑자기 배워야 하는 처지가 되는데....

때맞춰 신청한 외부 교육(어차피 고과 산정에 교육이 필요한 참에, 필요한 걸 들으면 가장 좋잖아?) 3일

+ [친절한 SQL 튜닝] / [실전사례로 알아보는 SQL 튜닝] 2권의 책(기억은 잘 안 나는데 둘 중 하나는 교육 교재였던걸로 기억하고, 하나는 내돈내산)

+ 미친듯한 구글링이 큰 도움이 되었다.

 

 

 

튜닝을 이해하기 위해서는 맨 처음 2가지 개념을 알고 넘어가야 합니다.

1. 옵티마이저 : 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있도록 최적의 데이터 액세스 경로를 선택해 주는 DBMS의 핵심엔진. 자동차의 내비게이션과 비슷한 역할을 한다고 봐도 무방합니다.

 

옵티마이저는 다음과 같은 절차를 통해 최적화된 데이터 액세스 경로를 선택합니다.

1) 사용자가 전달한 쿼리를 수행하는 데에 있어 후보가 될 실행계획 찾기

2) 각 실행계획의 예상 비용 산정: 이 과정에서 데이터 딕셔너리(DB자원을 효율적으로 관리하기 위해, 다양한 정보를 저장한 시스템 테이블)에 미리 수집해둔 오브젝트 통계와 시스템 통계 정보를 활용

3) 최저 비용(cost)을 나타내는 실행 계획을 선택

별표 친 14짜리 실행계획이 선택될 것이다.

아까 자동차 내비게이션 얘기를 잠깐 했는데, 내비게이션은 출발지부터 목적지까지의 예상되는 경로 중 교통 흐름에 따른 최적화된 길을 보여줍니다.

근데 하나만 보여주진 않잖아요?

고속도로 포함 / 국도나 시내 도로만 타는 길 등 여러 가지 옵션을 보여주고, 모의주행을 통해 어떤 길로 진입해서 어디서 좌회전해야 하는지 같은 것도 보여줍니다.

그걸 보고 사용자들은 마음에 드는 옵션을 선택할 수 있습니다.

 

2. 실행계획

실행계획이 내비게이션의 예상경로 조회와 비슷한 역할을 합니다.

 

1)실행계획이란? : SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있도록 트리구조로 표현

2)알아낼 수 있는 것: 조인의 순서와 기법, 테이블 액세스 방법, 최적화 정보(cost/byte 기준), 연산 등

 

실행계획을 사용하고 해석하는 목적은, 어떤 플랜이 가장 효과적인 지 판단하는 데에 있습니다.

이를 판단하기 위해 cost(%CPU), bytes, cardinality(rows)에 대해 알아야 합니다.

SQLGate 환경에서는 실행계획 이나 실행계획(DBMS_XPLAN) 탭을 열고, 실행 버튼을 눌러서 생성할 수 있습니다.

혹은 명령어도 있습니다.

EXPLAIN PLAN FOR ~~~~~ SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

그러면 이런 식의 결과물을 볼 수 있다.

그런데 이 실행계획은 어떻게 읽어야 하지? 갑자기 머리가 아파온다면 3가지를 기억하면 읽을 수 있어요.

1. 위에서 아래로 읽는다.

2. 들여쓰기된 하위 스텝이 존재한다면, 가장 안쪽으로 들여쓴 스텝부터 읽는다.

3. 같은 들여쓰기 스텝이 여러개 존재하면, 같은 단계를 위에서 아래로 읽는다.

 

위 표에서 보면 3,4가 맨 안쪽에 있으니 3->4 로 시작합니다.

그리고 읽고보니 2가 윗단계에 있는데 2와 5가 같은 스텝이고, 그 위로 1 0 순으로 단계가 올라가네?

 

3->4->2->5->1->0 순서로 읽으면 됩니다.

다음시간에는 스캔 이야기를 좀 해볼까 합니다.