본 문서에서는 개요 및 도구 설명, 사례를 통해 Tibero SQL Tuning을 설명합니다.
SQL Tuning 개요
SQL Tuning은 빠른 시간내에 원하는 결과값을 얻기 위해 SQL문을 작성하고 개선하는 작업 입니다.
SQL Tuning함으로써 시스템이 최적의 성능을 낼 수 있도록 합니다.
1. SQL Tuning 필요성
인덱스는 테이블의 데이터를 빠르게 접근하기 위한 색인 입니다.
- DB I/O는 시간 소요가 많은 작업이므로 통계정보와 SQL이 수행 속도 및 시스템 성능에 많은 영향을 미칩니다.
- 데이터베이스에서 데이터를 가져오는 방법은 SQL가 유일하며 다양한 방법으로 SQL을 작성해 동일한 결과를 얻을 수 있습니다.
Note!
옵티마이저는 사용자가 원하는 데이터를 가져오기 위한 다양한 실행 계획을 생성하고 최적의 실행계획을 선택합니다.
따라서 SQL 별로 상이한 실행계획이 생성되어 선택됩니다.통계정보가 변경되면 동일한 SQL에 대해서도 변경된 통계정보를 바탕으로 다른 최적의 실행 계획을 선택하고
이에 따라 데이터 SQL처리 응답시간의 차이가 발생합니다.
SQL의 수행 속도를 빠르게 하기 위해서는 옵티마이저에 대한 이해도가 중요합니다.
2. 처리 과정
2.1. 파싱
2.1.1. 소프트 파싱
문자열데이터를 분석하고 분해해 목적한 패턴에 맞게 문자열의 구조를 결정하는 것으로 실행된 SQL스트링을 해시 함수를 통해 분석하여 같은 SQL 문장이 Shared Cache의 SQL cache에 존재하는 경우 소프트 파싱을 수행합니다.
2.1.2. 하드 파싱
새로 파싱된 문장으로, Library cache에 이전에 실행했던 쿼리가 없는 경우 진행합니다.
실행된 SQL이 SQL cache에 존재하지 않아 parsing/optimization 후 정보를 저장하기 위해 Shared Cache에서 메모리를 할당 받고 저장하므로 소프트 파싱 보다 비용이 많이 듭니다.
그림1. SQL 파싱 처리 과정
2.1.3. 옵티마이저
옵티마이저는 각 SQL에 대해 가능한 모든 데이터 액세스 경로 중 가장 효율적으로 객체에 접근할 수 있는실행계획을 결정합니다.
비용기반 방식(Cost Based Optimizer, CBO)
해당SQL 문장이 참조하고 있는 객체들(테이블, 인덱스 등)에 대한 수집된 통계정보(statistics)의 값과 데이터베이스 파라미터($TB_SID.tip) 설정 값을 기초로 가장 적은 비용(cost)이 발생되는 실행계획을 결정합니다.
- ALL_ROWS: 전체 결과를 응답하는 데 가장 적은 비용이 드는 방법을 수행_기본값
- FIRST_ROWS_n: 최초의 n개의 결과를 빠르게 응답하기 위한 최적화 수행
Note!
비용기반 방식 내 통계정보는 실시간 관리되지 않습니다.
옵티마이저 모드 설정
- Instance 레벨 : $TB_SID.tip 파일에 설정
OPTIMIZER_MODE = { ALL_ROWS | FIRST_ROWS_[1|10|100|1000] } - Session 레벨 : 해당 세션에서 “Alter Session Set …”
SQL> ALTER SESSION SET OPTIMIZER_MODE = { ALL_ROWS | FIRST_ROWS_[1|10|100|1000] } - SQL에서 hint 처리
ALL_ROWS : SELECT /*+ ALL_ROWS */ …
FIRST_ROWS_n : SELECT /*+ FIRST_ROWS (n) */ … - 레벨별 우선순위 : SQL hint > Session > Instance
