[친절한 SQL 튜닝] 7. SQL 옵티마이저 [2]

 

7.2 옵티마이저에 대한 이해

 

7.2.1 옵티마이저 종류

• 비용기반(Cost-Based) 옵티마이저(CBO)
  • 사용자 쿼리를 위해 후보군이 될만한 실행계획을 도출
    • 데이터 딕셔너리에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저
    • 데이터량, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 CBO가 사용하는 통계정보
• 규칙기반(Rule-Based) 옵티마이저(RBO)
  • 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 옵티마이저
  • 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대량 데이터를 처리하는데 부적합함

규칙기반 옵티마이저의 규칙

 

 

7.2.2 옵티마이저 모드

최적화 목표를 설정하는 기능으로서 아래 세 가지 옵티마이저 모드 중 하나를 선택하면 된다.

• ALL_ROWS : 전체 처리속도 최적화
  • 전체를 읽는 것을 전제로 하여 시스템 리소스(I/O, CPU, 메모리 등)를 가장 적게 사용하는 실행계획 선택
• FIRST_ROWS : 최초 응답속도 최적화
  • 앞쪽 일부만 읽다가 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택
  • ALL_ROWS와 비교하면, Table Full Scan보다 인덱스를 더 많이 선택하고, 해시 조인, 소트 머지 조인보다 NL 조인을 더 많이 선택하는 경향을 보인다.
  • 곧 사라질 옵티마이저 모드이기 때문에, FIRST_ROWS_N을 사용해야 함
• FIRST_ROWS_N : 최초 N건 응답속도 최적화
  • 앞쪽 N개 로우만 읽고 멈추는 것을 전제로 응답 속도가 가장 빠른 실행계획을 선택
  • ALTER SYSTEM 또는 ALTER SESSION 명령어로 설정할 때 N으로 지정할 수 있는 값은 1, 10, 100, 100 네 가지이다.

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_100;
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1000;

 

FIRST_ROWS는 사용자가 데이터를 어느 정도 읽다가 멈출지를 지정하지 않았으므로 정확한 비용 산정이 어렵지만, FIRST_ROWS_N은 읽을 데이터 건수를 지정하였으므로 정확한 비용 산정이 가능하다!

 

 

 

7.2.3 옵티마이저에 영향을 미치는 요소

 

(1) SQL과 연산자 형태

 

결과가 같더라도 SQL을 어떤 형태로 작성했는지 또는 어떤 연산자(=, IN, LIKE, BETWEEN, 부등호 등)를 사용했는지에 따라 옵티마이저가 다른 선택을 할 수 있고, 궁극적으로 쿼리 성능에 영향을 미침

 

(2) 인덱스, IOT, 클러스터, 파티션, MV 등 옵티마이징 팩터

 

쿼리를 똑같이 작성해도 인덱스, IOT, 클러스터, 파티션, MV 등을 구성했는지, 그리고 어떤 식으로 구성했는지에 따라 실행계획과 성능이 크게 달라짐

 

(3) 제약 설정

 

DBMS에 설정한 PK, FK, Check, Not Null 같은 제약들은 데이터 무결성을 보장해 줄뿐만 아니라 옵티마이저가 쿼리 성능을 최적화하는 데 매우 중요한 메타 정보로 활용됨

 

(4) 통계정보

 

통계정보는 옵티마이저에 매우 강력한 영향을 미침. SQL 옵티마이저가 사용하는 통계정보에 문제가 생기면 애플리케이션 성능이 갑자기 느려지고 심할 땐 장애 상황으로 이어지기도 함

 

[시스템 장애가 발생하는 다양한 원인]

 

• 특정 테이블 통계정보를 갑자기 삭제한다.
• 대량 데이터를 지웠다가 다시 입력하기 직전, 데이터가 없는 상태에서 자동으로 통계정보가 수집된다.
• 3년간 갱신하지 않던 특정 테이블 통계정보를 어느 날 갑자기 재수집한다.
• 통계정보 없이 관리하던 테이블에 인덱스를 재생성한다.
• 테이블이나 인덱스를 재생성하면서 파티션 단위로만 통계정보를 수집한다.

(5) 옵티마이저 힌트

 

옵티마이저에게 가장 절대적인 영향을 미치는 요소. 옵티마이저는 힌트를 명령어로 인식하고 그대로 따름. 만약 힌트가 잘 작동하지 않는다면, 아래 경우에 해당할 가능성이 높음

 

• 문법적으로 맞지 않게 힌트를 기술
• 잘못된 참조 사용
• 의미적으로 맞지 않게 힌트를 기술
• 논리적으로 불가능한 액세스 경로
• 버그

(6) 옵티마이저 관련 파라미터

 

옵티마이저의 행동 변화에 영향을 미치는 파라미터 목록을 볼 수 있는 쿼리는 아래와 같다.

 

SELECT NAME, VALUE, ISDEFAULT, DEFAULT_VALUE
  FROM V$SYS_OPTIMIZER_ENV

 

 

7.2.4 옵티마이저의 한계

• 내비게이션이 실수를 하듯, 옵티마이저도 완벽하지 않고 한계가 존재한다.
• 옵티마이저 행동에 가장 큰 영향을 미치는 통계정보를 '필요한 만큼 충분히' 확보하는 것은 불가능한 일이다.
• 통계정보를 아무리 완벽히 수집해도 바인드 변수를 사용한 SQL에 컬럼 히스토그램을 활용할 수 없다는 치명적인 담정도 존재한다.
• 기본적으로 비용기반으로 작동하지만, 내부적으로 여러 가정과 정해진 규칙을 이용해 기계적인 선택을 한다는 것도 한계이다.

 

7.2.5 개발자의 역할

 

옵티마이저에만 의존할 것이 아니라 개발자 스스로 옵티마이저가 되어야 한다. 결과물이 올바른지 실행계획을 통해 늘 점검하고, 더 개선할 여지는 없는지 찾으려고 노력해야 한다.

또한 고성능, 고효율 DB 애플리케이션을 구축하려면, 소수 DBA나 튜너보다 다수 개발자 역할이 더 중요하다. 따라서 SQL

수행원리와 튜닝방법을 익히는 데도 많은 노력과 시간을 투자해야 한다.

 

[SQL 성능을 높이기 위해 개발자가 할 일]

 

• 필요한 최소 블록만 읽도록 쿼리를 작성한다.
  • 데이터베이스 성능은 I/O 효율에 달려있으므로 동일한 레코드를 반복적으로 읽지 않고, 필요한 최소 블록만 읽도록 해야 한다.
• 최적의 옵티마이징 팩터를 제공한다.
  • 옵티마이저는 주어진 환경에서 가장 빠른 처리경로를 찾아줄 뿐, 없는 길을 스스로 만들어 내지 못한다. 따라서 옵티마이저가 기능들을 활용할 수 있도록 물리적으로 DB를 구성해야 한다.
    • 전략적인 인덱스 구성 : 옵티마이저를 돕는 가장 기본적인 옵티마이징 팩터
    • DBMS가 제공하는 다양한 기능 활용 : 파티션, 클러스터, IOT, MV, Result Cache 등
    • 옵티마이저 모드 설정 : 옵티마이저 모드를 포함해 각종 파라미터를 적절한 값으로 설정해야 함
    • 정확하고 안정적인 통계정보 : 통계정보는 중요하다(잘 수집하자).
• 필요하다면 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도한다.
  • 옵티마이저가 최적의 실행계획을 수립하지 못할 때, 개발자가 힌트를 이용해 직접 데이터 액세스 경로를 선택할 수 있으므로, 개발자는 최적의 액세스 경로를 찾아내고, 실행계획을 그 방식으로 유도할 수 있는 능력을 반드시 갖춰야 한다.