오라클(oracle) 실행계획 보는 방법 (EXPLAIN PLAN, SET AUTORACE, SQL TRACE) / MSA / 인덱스 생성 전략 / 인덱스 리빌드 / HINT
MSA개발방법이 적용되면서 SQL에 대한 의존도가 예전에 비해 많이 없어지기는 했다. MSA란 마이크로 서비스 아키텍처(Micro Service Architecture)의 약자로 단일 프로그램을 각 컴포넌트 별로 나누어 작은 서비스의 조합으로 구축하는 방법이다. 마이크로서비스란 작고, 독립적으로 배포 가능한 각각의 기능을 수행하는 서비스로 구성된 프레임워크라고 할 수 있다.
MSA는 Netflix가 이 개념을 실제 서비스에 도입하여 성공적인 결과를 이끌어냈기 때문에 유명해진 것으로 생각된다. MSA는 API를 통해서만 상호작용할 수 있다. 즉, 마이크로 서비스는 서비스의 end-point를 API의 형태로 외부에 노출하고, 실질적인 세부사항들은 모두 추상화한다. 내부의 구현 로직, 아키텍쳐, 프로그래밍 언어, 데이터베이스, 품질 유지 체계와 같은 기술적인 사항들은 서비스 API에 의해 철저히 가려진다. 따라서 *SOA(Service Oriented Architecture)의 특징을 다수 공통으로 가진다. MSA는 각각의 서비스가 모듈화되어 있기 때문에 개별 서비스 개발을 빠르게 진행할 수 있다.
MSA 소개 마이크로서비스 개발 MSA 배포 및 운영
SQL*PLUS에서 Explain plan for 를 이용하여 실행계획 보는 방법
SQL> EXPLAIN plan FOR
2 SELECT * FROM EMP WHERE EMPNO > 7600 AND JOB = 'MANAGER'; -- 확인 할 쿼리를 plan table로 저장
SELECT *
FROM TABLE(dbms_xplan.display); -- Plan table 출력
SQL> SET LINESIZE 200 --편하게보기위해 넉넉하게 잡아줌
SQL> @?/rdbms/admin/utlxpls --오라클이 제공하는 ultxpls.sql 스크립트로 실행계획 확인DB Tool을 사용한 실행계획 보는 방법
Orange 실행계획 보는 단축키 : ctrl + E 또는 쿼리 블록 drag + Ctrl + E
Orange 테이블 스크립트 및 관련 object 보기 : emp 블럭잡고 F4
Orange 테이블에 생성된 index 보기 : emp 블럭잡고 F4 후, Description 의 Index 탭 선택
분석 : EXEC dbms_stats.gather_schema_stats(‘SCOTT’);
– 분석 후, 실행계획 수행 후, 해당 실행계획 더블클릭 !
Toad 실행계획 보는 단축키 : 쿼리 블록 drag + Ctrl + E
sql developer : F10
SQLGate: F7
실행계획 순서
- 상하 레벨 간에는 ID가 낮은 거 부터 먼저
- 부모자식 중에는 자식을 우선 분석
- 형제간(동급 레벨)에는 형을 우선 분석(ID가 높은거)
## 세션 유지하는동안 수행된 SQL에 대한 실행계획 보는 방법
ALTER SESSION SET STATISTICS_LEVEL = ALL;oracle 실행계획 보는 방법
쿼리문의 실행 계획을 확인하는 세 가지 방법은 EXPLAIN PLAN과 SET AUTORACE, SQL TRACE이다.
1.EXPLAIN PLAN
EXPLAIN PLAN의 사용법은 SELECT문 앞에 실행 계획을 저장하라는 명시를 해주고 실행할 SQL에 임의의 ID를 부여한 다음 FOR를 사용한다. 쿼리문의 실행계획을 PLAN_TABLE에 저장한 후 직접 조회하시거나 오라클에서 제공하는 DBMS_XPLAN 패키지를 사용하여 조회하면 된다. EXPLAIN PLAN은 SQL의 실행 계획만을 확인할 수 있고 명령을 사용할 때 데이터를 처리하지 않음으로 데이터베이스에 그 어떤 부하도 걸리지는 않는다.
(단점) EXPLAIN PLAN은 한번에 하나의 쿼리문의 실행계획만을 확인할 수 있고 그 실행계획을 또 확인하기 위해 별도의 SELECT문을 작성해야 하는 번거로움과 데이터를 읽지 않기 때문에 소요시간을 추정한다거나 데이터 관련 I/O정보를 확인할 수 없다.
EXPLAIN PLAN -- EXPLANIN PLAN 선언부
SET STATEMENT_ID = 'PLAN1' INTO PLAN_TABLE -- SQL에 PLAN1이라는 ID 부여
FOR
SELECT * FROM REGIONS A --SQL 입력부
LEFT OUTER JOIN COUNTRIES B ON A.REGION_ID = B.REGION_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY -- PLAN_TABLE에 저장된 실행계획 조회
('PLAN_TABLE','PLAN1','ALL'));
PLAN_TABLE의 구조
| 항목 | 설명 |
| STATEMENT_ID | EXPLAIN PLAN 명령을 수행할 떄 사용자가 정의한 ID |
| PLAN_ID | 데이터베이스 내에서 실행 계획이 갖게 되는 단일 속성 |
| TIMESTAMP | EXPLAIN PLAN 명령을 수행한 날짜, 시간 정보 |
| REMARKS | EXPLAIN PLAN 명령을 수행할 떄 사용자가 임의로 넣는 주석 |
| OPERATION | INSERT, SELECT, UPDATE, DELETE 가운데 하나가 수행될 때 단계 별 작동형태를 정의 |
| OPTIONS | OPERATION의 상세 옵션을 설명 |
| OBJECT_NODE | 참조하는 객체의 데이터베이스 링크 이름 |
| OBJECT_OWNER | 테이블 또는 인덱스의 소유주 |
| OBJECT_NAME | 테이블 또는 인덱스의 이름 |
| OBJECT_ALIAS | SQL에서 정의된 테이블 또는 뷰의 유일한 별칭 |
| OBJECT_INSTANCE | FROM절에 기술된 객체에 부여하는 번호 |
| OJBECT_TYPE | 객체의 유형 |
| OPTIMIZER | 옵티마이저 모드 정보 |
| ID | 수립된 각 실행 단계의 일련번호 (오퍼레이션ID) |
| PARENT_ID | ID에 해당하는 부모 ID |
| POSITION | 동일 PARENT_ID를 가지는 ID 사이의 처리 순서 |
| COST | 각 처리 단계별 비용(해당 단계까지 사용될 예상 비용(누적)), 적을 수록 쿼리가 더 효율적이다. 단, 어쩔수 없이 Full Scan을 해야 하는 쿼리는 의미가 없을 수도 있습니다. |
| CARDINALITY | 행 집합에서 행의 수를 표시 합니다. 행 집합은 기본 테이블, 뷰, 조인이나, GROUP BY의 결과 입니다. 행 집합을 의미하니 적게 나타날수록 SQL이 빠를 수 있습니다. |
| BYTES | 해당 단계까지 사용될 예상 데이터 양(누적) |
| TIME | 해당 단계까지 사용될 예상 시간(누적) |
비용 계산 방법
| Full table scan | Cost = 전체 블록 수 / DB_FILE_MULTIBLOCK_READ_COUNT의 보정 값 |
| Unique index scan | Cost = blevel + 1 |
| Fast Full Index Scan | Cost = leaf_blocks / db_block_size |
| Index Range Scan | Cost = blevel + (Selectivity X leaf_blocks) + (Selectivity X Cluster Factor) |
| Sort-Merge Join | Cost = (Outer 테이블의 Sort Cost +Inner 테이블의 Sort Cost) -1 |
| Nest-Loop Join | Cost = Outer 테이블의 Cost + (Inner 테이블의 Cost * Outer 테이블의 Cardinality) |
| Hash Join | Cost = (Outer 테이블의 Cost × #Hash 파티선수 +Inner 테이블의 Cost) + 2 |
DBMS_XPLAN 패키지
DBMX_XPLAN 패키지에는 아래와 같이 여러 기능의 function 들이 존재한다. 이 중 가장 많이 사용하는 display 와 display_cursor function에 대해 설명이다.
| Subprogram | Description |
| DIFF_PLAN | 플랜 비교 |
| DISPLAY | plan_table 내용을 출력 |
| DISPLAY_AWR | AWR에 저정되어 있는 실행계획을 표시 |
| DISPLAY_CURSOR | cursor cache에 있는 cursor의 실행계획을 표시 |
| DISPLAY_PLAN | 다양한 형식으로 plan_table의 내용을 표시 – format : BASIC, TYPICAL, SERIAL, ALL, ADPTIVE – type : ‘TEXT’, ‘ACTIVE’, ‘HTML’, ‘XML’ |
| DISPLAY_SQL_PLAN_BASELINE | SQL plan baseline의 지정된 SQL 핸들에 대해 하나 이상의 실행 계획을 표시 |
| DISPLAY_SQLSET | SQL tuning set 에 저장된 실행 계획을 표시합니다. |
2. SET AUTOTRACE
EXPLAIN PLAN 명령과는 달리 한번의 명령으로 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있다는 편리함 측면에서의 장점이 있고 또 AUTOTRACE의 다양한 옵션을 활용하여 데이터를 읽어 들일 수도 있고 데이터를 읽지 않게 할 수도 있으며 데이터를 읽게 된다면 I/O정보나 SORT 정보 등의 여러 가지 정보를 추가적으로 확인하실 수도 있다는 장점이 있다.
SET AUTOTRACE ON; -- 자동 추적 사용
SELECT * FROM REGIONS A -- 쿼리문 실행
LEFT OUTER JOIN COUNTRIES B ON A.REGION_ID = B.REGION_ID;
[예외처리]
PLAN_TABLE SYNONYM 이 없다면 위 명령어를 통해 오라클에서 제공하는 스크립트를 실행해서 생성할 수 있다. 추가로 오라클 10g부터는 별도의 PLAN_TABLE을 생성하지 않아도 SYS.PLAN_TABLE$ 테이블을 사용하여 실행 계획을 저장하는 것도 가능해졌다.
@$ORACLE_HOME/rdbms/admin/utlxplanAUTOTRACE도 마찬가지로 PLAN_TABLE 테이블이 생성되어 있어야 한다. 없다면 앞서 EXPLAIN PLAN에서 설명한대로 스크립트를 실행해서 만들어야한다. 그리고 PLUSTRACE라는 롤을 받아야 하는데 아래 스크립트를 실행후 PLUSTRACE 권한에 GRANT를 활용하여 권한을 부여한다.
$ORACLE_HOME/sqlplus/admin/plustrace.sqlSQL> conn /as system
Connected.
SQL> grant plustrance to hr;
Grant plustrace to hrAUTOTRACE 주요 명령어
| 명령 | 수행 결과 | 실행 계획 | 실행 통계 | plustrace 권한 여부 |
| set autotrace on | O | O | O | O |
| set autotrace on explain | O | O | X | X |
| set autotrace on statistics | O | X | O | O |
| set autotrace traceonly | X | O | O | O |
| set autotrace traceonly explain | X | O | X | X |
| set autotrace traceonly statistics | X | X | O | O |
3. SQL TRACE
SQL TRACE는 실행 계획뿐만이 아니라 여러 세션에서 수행한 SQL의 통계 정보, 수행 시간, 결과 등을 TRAE로 기록하여 이를 파일 형태로 저장하는 방법을 말한다. SQL Trace는 실행되는 SQL 문의 실행 통계를 세션별로 모아서 Trace 파일을 만든다. 만들어지는 파일은 .trc의 확장자를 갖으며, .trc 파일들은 바이너리 형태로 저장되기에 읽기 편한 형태로 파일을 변환하는 과정이 필요한데 TKPROF 유틸리티를 이용하면 쉽게 변환할 수 있다. 또한 SQL Trace는 세션과 인스턴스 레벨에서 SQL 문장들을 분석할 수 있다. 인스턴스 레벨로 Trace를 수행시키면 전체적인 수행능력이 20~30% 정도 감소하므로, 될 수 있으면 세션 레벨로 Trace 파일을 생성해야 한다.
SQL> conn /as system
Connected.
SQL> grant alter session to hr;
Grant succeeded.
-- session level 실행 방법
SQL> alter session set sql_trace = true;
SQL> execute dbms_system.set_sql_trace_in_session(true);
SQL> execute dbms_system.set_sql_trace_in_session(session_id, serial_id, true);
session altered.
-- session level로 종료 방법
SQL> alter session set sql_trace = false;
session altered.
저장경로 확인하기
SQL> show parameter user_dump_dest; --경로 출력
$ cd admin/testdb/udump/
$ ls
파일이름.trc
$ vi 파일이름.trc -- 내용 분석
TKPROF 사용하여 확인 할수 있는 형식으로 바꾸기
tkprof trace_file.trc new_file.txt sys=no$ tkprof trace_test.trc output_file.txt sys=no
## 위의 trace 파일을 tkprof 로 분석
$ tkprof ORCL_ora_22987.trc output_file.txt sys=no
## tkprof 로 분석한 내용을 확인(vi or vim)
$ vi output_file.txt
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 14 0.00 0.00 0 0 0 0
Fetch 14 0.00 0.00 0 28 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 32 0.00 0.00 0 28 0 2
Misses in library cache during parse: 3
Misses in library cache during execute: 3위의 명령문을 터미널에서 실행시키면 텍스트 파일로 변환하는 것이 가능하다. sys=no라는 옵션을 사용하면 시스템에서 내부적으로 수행되는 SQL 문장에 대해서는 변환을 제외해주므로 같이 사용하도록 한다.
SCAN의 종류
데이터 양이 엄청 많다면 FULL TABLE SCAN을 하는 것이 유리할 수 있고 반대로 많은 데이터가 있는 테이블에서 내가 원하는 데이터를 추출해야 하는 상황이라면 INDEX SCAN을 하는것이 좋다.
FULL TABLE SCAN : 테이블의 전체 데이터를 읽어 조건에 맞는 데이터를 추출하는 방식.
ROWID SCAN : ROWID를 기준으로 데이터를 추출하며 단일 행에 접근하는 방식 중에서 가장 빠름.
INDEX SCAN : 말 그대로 인덱스를 활용하여 원하는 데이터를 추출하는 방식
FULL TABLE SCAN을 타는 상황
1. 조건절에서 비교한 칼럼에 인덱스가 없는 경우
2. 조건절에서 비교한 컬럼에 최적화된 인덱스는 있지만 조건에 만족하는 데이터가 테이블의 많은 양을 차지하여 FULL TABLE SCAN이 낫다고 옵티마이저가 판단하는 경우
3. 인덱스는 있으나, 테이블의 데이터 자체가 적어 FULL TABLE SCAN이 낫다고 옵티마이저가 판단하는 상황
4. 테이블 생성 시 DEGREE 속성 값이 크게 설정되어 있는 경우
옵티마이저가 판단 후 TABLE FULL SCAN을 탄다면 그냥 두는 것이 좋고 INDEX SCAN이 더 유리한데도 불구하고 INDEX가 없어 부득이하게 FULL TABLE SCAN을 한다면 INDEX를 하나 만드는 것이 좋다. INDEX를 필요할 때마다 만드는 것도 UPDATE와, DELETE 등의 속도를 저해한다.
ROWID SCAN을 타는 상황
1. 조건절에 ROWID를 직접 명시할 경우
2. INDEX SCAN을 통해 ROWID를 추출한 후 테이블에 접근할 경우
ROWID SCAN은 단일 행 접근이 매우 빠르기 때문에 ROWID SCAN가 유리하다고 판단되면서 ROWID SCAN을 탈 수 있는 상황이라면 유도해주는 것이 좋다.
INDEX SCAN을 타는 상황
| 인덱스 종류 | 상황 |
| INDEX UNIQUE SCAN | UNIQUE INDEX를 구성하는 모든 컬럼이 조건에 “=”로 명시된 경우 |
| INDEX RANGE SCAN | 1. UNIQUE 성격의 결합 인덱스의 선두 컬럼이 WHERE절에 사용되는 경우 2. 일반 인덱스의 컬럼이 WHERE절에 존재하는 경우 |
| INDEX RANGE SCAN DESCENDING | INDEX RANGE SCAN을 수행함과 동시에 ORDER BY DESC절을 만족하는 경우 |
| INDEX SKIP SCAN | 1. 결합 인덱스의 선행 컬럼이 WHERE절는 경우 2. 옵티마이저가 INDEX SKIP SCAN이 FULL TABLE SCAN보다 낫다고 판단하는 경우 |
| INDEX FULL SCAN | 1. ORDER BY / GROUP BY의 모든 컬럼이 인덱스의 전체 또는 일부로 정의된 경우 2. 정렬이 필요한 명령에서 INDEX ENTRY를 순차적으로 읽는 방식으로 처리된 경우 |
| INDEX FULL SCAN DESCENDING | INDEX FULL SCAN을 수행함과 동시에 ORDER BY DESC절을 만족하는 경우 |
| INDEX FAST FULL SCAN | FULL TABLE SCAN을 하지 않고도 INDEX FAST FULL SCAN으로 원하는 데이터를 추출할 수 있고 추출된 데이터의 정렬이 필요 없으며 결합 인덱스를 구성하는 컬럼 중에 최소 한개 이상은 NOT NULL인 경우 |
| INDEX JOIN | 추출하고자 하는 데이터가 조인하는 인덱스에 모두 포함되어 있고 추출하는 데이터의 정렬이 필요없는 경우 |
문제가 되는 쿼리 조회문
운영 환경에선 그동안 수행된 쿼리에 대한 통계데이터가 쌓이게 됩니다. 해당 통계 중 문제되는 쿼리를 확인할 때 사용할 수 있는 쿼리문들이다.
## 프로세스 목록
SHOW PROCESSLIST;
## 느린쿼리 확인
SELECT query, exec_count, sys.format_time(avg_latency) AS "avg latency", rows_sent_avg, rows_examined_avg, last_seen
FROM sys.x$statement_analysis
ORDER BY avg_latency DESC;
## 성능 개선 대상 식별
SELECT DIGEST_TEXT AS query,
IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
COUNT_STAR AS exec_count,
SUM_ERRORS AS err_count,
SUM_WARNINGS AS warn_count,
SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000) AS exec_time_total,
SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000) AS exec_time_max,
SEC_TO_TIME(AVG_TIMER_WAIT/1000000000000) AS exec_time_avg_ms, SUM_ROWS_SENT AS rows_sent,
ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg, SUM_ROWS_EXAMINED AS rows_scanned,
DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC
## I/O 요청이 많은 테이블 목록
SELECT * FROM sys.io_global_by_file_by_bytes WHERE file LIKE '%ibd';
## 테이블별 작업량 통계
SELECT table_schema, table_name, rows_fetched, rows_inserted, rows_updated, rows_deleted, io_read, io_write
FROM sys.schema_table_statistics
WHERE table_schema NOT IN ('mysql', 'performance_schema', 'sys');
## 최근 실행된 쿼리 이력 기능 활성화
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history'
UPDATE performance_schema.setup_consumers SET ENABLED = 'yes' WHERE NAME = 'events_statements_history_long'
## 최근 실행된 쿼리 이력 확인
SELECT * FROM performance_schema.events_statements_history인덱스 생성 전략
생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋습니다. 인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 Index 테이블이 생성됩니다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋습니다. 가장 최선은 PK로 인덱스를 거는것이겠죠. 중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고. 반대로 모든 값이 같은 컬럼이 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것입니다.
1. 조건절에 자주 등장하는 컬럼
2. 항상 = 으로 비교되는 컬럼
3. 중복되는 데이터가 최소한인 컬럼 (분포도가 좋은) 컬럼
4. ORDER BY 절에서 자주 사용되는 컬럼
5. 조인 조건으로 자주 사용되는 컬럼
인덱스 생성 방법
기본 문법
CREATE INDEX [인덱스명] ON [테이블명](컬럼1, 컬럼2, 컬럼3.......)
--예제
CREATE INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
--예제 컬럼 중복 X (UNIQUE 키워드를 붙이면 컬럼값에 중복값을 허용하지 않는다)
CREATE[UNIQUE] INDEX EX_INDEX ON CUSTOMERS(NAME,ADDRESS);
인덱스 조회
SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'CUSTOMERS';
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'CUSTOMERS';
인덱스 수정
인덱스를 변경하기 위해서는 삭제 DROP을 한 후 새로 생성 CREATE를 해주어야 한다.
--문법
DROP INDEX [기존 인덱스 명] TO [바뀔 인덱스 명]
--예제
ALTER INDEX EX_INDEX RENAME TO EX_INDEX_NEW
인덱스 삭제
인덱스는 조회성능을 극대화하기 위해 만든 객체인데 너무 많이 만들면 insert, delete, update시에 부하가 발생해 전체적인 데이터베이스 성능을 저하된다. 그럼으로 사용하지 않는 인덱스는 삭제하는 것이 좋다.
--문법
DROP INDEX [인덱스 명]
DROP INDEX EX_INDEX;
인덱스(Index) 리빌드(Rebuild)가 필요한 이유는?
인덱스 파일은 생성 후 insert, update, delete등을 반복하다보면 성능이 저하된다. 생성된 인덱스는 트리구조를 가지는데 삽입,수정,삭제등이 오랫동안 일어나다보면 트리의 한쪽이 무거워져 전체적으로 트리의 깊이가 깊어진다. 이러한 현상으로 인해 인덱스의 검색속도가 떨어지므로 주기적으로 리빌딩하는 작업 해주어야 한다.
인덱스 리빌드 할 대상 조회쿼리
해당쿼리는 index 트리의 깊이가 4이상인 index를 조회하는 쿼리이다. 해당 쿼리문을 실행하여 검색되는 index가 있다면 리빌딩을 하는것이 좋다.
SELECT I.TABLESPACE_NAME,I.TABLE_NAME,I.INDEX_NAME, I.BLEVEL,
DECODE(SIGN(NVL(I.BLEVEL,99)-3),1,DECODE(NVL(I.BLEVEL,99),99,'?','Rebuild'),'Check') CNF
FROM USER_INDEXES I
WHERE I.BLEVEL > 4
ORDER BY I.BLEVEL DESC
인덱스 리빌드
--문법
ALTER INDEX [인덱스명] REBUILD;
--예제
ALTER INDEX EX_INDEX REBUILD;
--전체 인덱스 리빌드 쿼리문 생성
SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD; 'FROM USER_INDEXES;
인덱스(Index)를 남발하면 안되는 이유
개발서버에는 잘 동작하던 쿼리들이 운영서버의 많은량의 데이터들을 처리하다보면 성능이슈가 많이 발생한다. 그 성능이슈의 주요원인은 바로 데이터베이스에 있다. 데이터베이스 관리자는 성능문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성이다. 하지만 문제를 그저 쉽게 해결하기 위해 쿼리 속도 문제가 날때마다 인덱스를 추가하는것은 바람직하지 못하다. 성능 이슈가 발생했을 때 인덱스를 만들고 또 다른 SQL에서문에서 성능이슈가 발생하여 또 인덱스를 만들게 되면, 생성된 인덱스를 참조하는 하나의 쿼리문을 빠르게는 만들 수 있지만 전체적인 데이터베이스의 성능 부하를 초래한다. 그럼으로 인덱스를 생성하는것 보다는 SQL문을 좀 더 효율적으로 작성하는 방향으로 나가야한다. 인덱스생성은 꼭 마지막 수단으로 강구해야 할 문제이다.
옵티마이저의 종류
옵티마이저는 사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 엑세스 경로를 선택해주는 DBMS 핵심 엔진이다.
옵티마이저는 실행 계획을 세우는 방식에 따라 규칙 기반 옵티마이저와 비용 기반 옵티마이저로 나뉜다.
| 항목 | 규칙 기반 옵티마이저 | 비용 기반 옵티마이저 |
| 개념 | 사전에 정의된 규칙 기반 | 최소비용 계산 실행계획 수립 |
| 기준 | 실행우선 순위(Ranking) | 액세스 비용(Cost) |
| 인덱스 | 인덱스 존재 시 가장 우선시 사용 | Cost에 의한 결정 |
| 성능 | 사용자 SQL작성 숙련도 | 옵티마이저 예측 성능 |
| 장점 | 판단이 매우 규칙적 실행 예상 가능 | 통계 정보를 통한 현실 요소 적용 |
| 단점 | 예측 통계정보 요소 무시 | 최소 성능 보장 계획의 예측 제어 어려움 |
| 사례 | AND 중심 양쪽 ‘=’ 시 Index Merge 사용 | AND 중심 양쪽 ‘=’ 시 분포도별 Index 선택 |
규칙 기반 옵티마이저 (RBO)
규칙 기반 옵티마이저는 말 그대로 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택하는 것이다. 과거에는 옵티마이저의 비용을 예측하는 능력이 그다지 좋지 않아 이러한 방식을 사용하였다. (오라클8 이하의 버전에서 기본으로 설정된 옵티마이저가 바로 규칙 기반 옵티마이저)
규칙의 우선순위표
| 순위 | 설명 |
| 1 | ROWID를 사용한 단일 행인 경우 |
| 2 | 클러스터 조인에 의한 단일 행인 경우 |
| 3 | 유일하거나 기본키(Primary Key)를 가진 해시 클러스터 키에 의한 단일 행인 경우 |
| 4 | 유일하거나 기본키(Primary Key)에 의한 단일 행인 경우 |
| 5 | 클러스터 조인인 경우 |
| 6 | 해시 클러스터 조인인 경우 |
| 7 | 인덱스 클러스터 키인 경우 |
| 8 | 복합 칼럼 인덱스인 경우 |
| 9 | 단일 칼럼 인덱스인 경우 |
| 10 | 인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우 |
| 11 | 인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우 |
| 12 | 정렬-병합(Sort-Merge) 조인인 경우 |
| 13 | 인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우 |
| 14 | 인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우 |
| 15 | 전체 테이블을 스캔(FULL TABLE SCAN)하는 경우 |
이 우선순위는 잘 공부해두시면 쿼리문을 효율적으로 작성하는데 큰 도움이 된다. 이렇게 규칙 기반 옵티마이저는 우선순위에 순위가 매겨져 있기 때문에 옵티마이저에서 실행계획이 세워지는 것을 미리 예측할 수 있는 장점이 있고 조금 응용하면 내가 원하는 대로 실행 계획이 세워지도록 유도할 수도 있다.
그러나 주의할점은 테이블에 데이터가 별로 없을 경우 전체 조회를 했을 경우 FULL TABLE SCAN이 더 빠를 수 있는데 INDEX를 타버리게 되면 비효율적인 실행 계획이 도출될 수 있다. 옵티마이저의 실행계획을 유도할 수 있는 힌트와 HASH JOIN의 경우에는 규칙 기반 이후에 나온 개념들이므로 여기서는 사용하실 수 없다는 단점이 있다.
비용 기반 옵티마이저
비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤(최대 2천개까지) 비용이 최소한으로 나온 실행 계획을 수행한다. 비용기반 옵티마이저는 비용을 예측하기 위해서 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다. 통계정보가 없는 경우 비효율적인 실행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다. 오라클 10 이후 버전부터는 공식적으로 비용 기반 옵티마이저만 사용을 하는 것으로 알고 있다.
비용 기반 옵티마이저의 모드
비용 기반 옵티마이저는 여러 가지 모드가 있는데, 모드에 따라 최적의 비용을 구하는 방식이 조금씩 다르다.
- CHOOSE : 현재는 잘 사용하지 않지만 SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저로 그렇지 않다면 규칙 기반 옵티마이저로 작동시키는 모드.
- FIRST_ROWS : 옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화할 수 있는 실행 계획을 세우는 모드.
- FIRST_ROWS_n : SQL의 실행 결과를 출력하는데까지 걸리는 응답속도를 최적화하는 모드.
- ALL_ROWS : SQL 실행 결과 전체를 빠르게 처리하는데 최적화 된 실행계획을 세우는 모드. 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 오라클 10g 이후로는 이 모드가 기본값으로 설정되어 있음.
통계 정보
비용 기반 옵티마이저에서는 실행 계획 도출 시 DBMS에서 제공하는 통계 정보를 사용한다. 이러한 통계정보는 꾸준히 갱신되고 있는 것이 좋다. 통계 정보의 생성 주기 및 수행 시간을 스케줄러에 일괄적으로 등록하여 일정 주기마다 통계 정보를 수집하고 종료하도록 자동 통계 정보를 생성할 수도 있고 사용자가 직접 수동으로 통계 정보를 생성할수도 있다. DBMS_STATS 패키지를 사용하면 데이터베이스, 스키마 및 계정, 테이블 인덱스 단위로 구분하여 통계 정보를 수집할 수 있다.
주요 통계 정보들
| 구분 | 세부 통계 정보 |
| 테이블 | 테이블의 전체 행의 갯수 |
| 테이블이 차지하고 있는 전체 블록 갯수 | |
| 테이블의 행들이 가지고 있는 평균 길이 | |
| 컬럼 | 컬럼 값의 종류 |
| 컬럼 내부 NULL 값의 분포도 | |
| 컬럼 값의 평균 길이 | |
| 컬럼 내부 데이터 분포의 추정치 | |
| 인덱스 | LEAF BOLOCK 수 : 데이터를 보관하는 블록 수 |
| LEVELS : 인덱스 트리의 LEVEL 정보 | |
| CLUSTERING FACTOR : 접근하고자 하는 데이터가 모여 있는 밀집도 | |
| 시스템 | I/O 성능 및 사용률 |
| CPU 성능 및 사용률 |
오라클 힌트 사용 방법
명령문 블록에는 힌트가 포함된 주석이 하나만 있을 수 있으며, 주석은 SELECT, UPDATE, INSERT, MERGE 또는 DELETE 키워드 뒤에 와야 한다. Oracle은 뷰(또는 하위 쿼리) 내부나에서 힌트 사용을 권장하지 않는다.
힌트 종류
1. Types of Hints
- Single-table : 단일 테이블 힌트는 하나의 테이블 또는 뷰에 지정됩니다. INDEX 및 USE_NL은 단일 테이블 힌트의 예입니다.
- Multi-table :다중 테이블 힌트는 힌트가 하나 이상의 테이블이나 뷰를 지정할 수 있다는 점을 제외하면 단일 테이블 힌트와 비슷합니다. LEADING은 다중 테이블 힌트의 예입니다. USE_NL(table1 table2)은 USE_NL(table1) 및 USE_NL(table2)의 형태와 동일하므로 다중 테이블 힌트로 간주되지 않습니다.
- Query block : 쿼리 블록 힌트는 단일 쿼리 블록에서 작동합니다. STAR_TRANSFORMATION 및 UNNEST는 쿼리 블록 힌트의 예입니다.
- Statement : 구문 힌트는 전체 SQL 문에 적용됩니다. ALL_ROWS는 구문 힌트의 예입니다.
2. Hints by Category
Optimizer 힌트는 다음 범주로 그룹화됩니다.
- Hints for Optimization Approaches and Goals
- Hints for Enabling Optimizer Features
- Hints for Access Paths
- Hints for Join Orders
- Hints for Join Operations
- Hints for Online Application Upgrade
- Hints for Parallel Execution
- Hints for Query Transformations
- Additional Hints
2.1 Hints for Optimization Approaches and Goals
ALL_ROWS 및 FIRST_ROWS(n) 힌트를 사용하면 최적화 접근 방식과 목표 중에서 선택할 수 있다. SQL 문에 최적화 접근 방식과 목표를 지정하는 힌트가 있는 경우 옵티마이저는 통계 유무, OPTIMIZER_MODE 초기화 매개변수 값, ALTER SESSION 문의 OPTIMIZER_MODE 매개변수에 관계없이 지정된 접근 방식을 사용한다.
- ALL_ROWS
- FIRST_ROWS(n)
2.2 Hints for Enabling Optimizer Features
OPTIMIZER_FEATURES_ENABLE 힌트는 Oracle Database 릴리스 번호를 기반으로 최적화 기능을 활성화하기 위한 역할을 한다. 이 힌트는 데이터베이스 업그레이드 후 계획 회귀를 확인할 때 유용한 방법이다.
힌트에 대한 인수로 릴리스 번호를 지정한다. 다음 예는 Oracle Database 11g Release 1(11.1.0.6)의 옵티마이저 기능으로 쿼리를 실행한다.
SELECT /*+ optimizer_features_enable('11.1.0.6') */ employee_id, last_name
FROM employees
ORDER BY employee_id;2.3 Hints for Access Paths
다음 힌트는 테이블에 대한 특정 액세스 경로를 사용하도록 지시한다.
- FULL
- CLUSTER
- HASH
- INDEX and NO_INDEX
- INDEX_ASC and INDEX_DESC
- INDEX_COMBINE and INDEX_JOIN
- INDEX_JOIN
- INDEX_FFS and NO_INDEX_FFS
- INDEX_SS and NO_INDEX_SS
- INDEX_SS_ASC and INDEX_SS_DESC
2.3.1 FULL Hint
FULL 힌트는 옵티마이저가 지정된 테이블에 대해 전체 테이블 스캔을 수행하도록 지시한다.
SELECT /*+ FULL(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :b1;Oracle Database는 WHERE 절의 조건에 의해 사용 가능한 last_name 열에 인덱스가 있는 경우에도 직원 테이블에 대해 전체 테이블 스캔을 수행하여 이 문을 실행한다. 직원 테이블의 FROM 절에 별칭 e가 있으므로 힌트는 이름이 아닌 별칭으로 테이블을 참조해야 한다.
2.3.2 CLUSTER Hint
CLUSTER 힌트는 지정된 테이블에 액세스하기 위해 클러스터 스캔을 명시적으로 선택한다. 클러스터링된 개체에만 적용된다.
SELECT /*+ CLUSTER(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :b1;2.3.3 HASH Hint
HASH 힌트는 명시적으로 해시 스캔을 선택하여 지정된 테이블에 액세스한다. 클러스터에 저장된 테이블에만 적용된다.
SELECT /*+ HASH(e) */ employee_id, last_name
FROM hr.employees e
WHERE last_name LIKE :b1;2.3.4 INDEX and NO_INDEX Hint
INDEX hint
INDEX 힌트는 지정된 테이블에 대한 인덱스 스캔을 선택한다. 도메인, B-트리, 비트맵 및 비트맵 조인 인덱스에 대해 INDEX 힌트를 사용할 수 있다. 그러나 Oracle은 여러 인덱스 조합에 INDEX보다 INDEX_COMBINE을 사용할 것을 권장한다.
SELECT /*+ INDEX (employees emp_department_ix)*/
employee_id, department_id
FROM employees
WHERE department_id > 50;NO_INDEX hint
NO_INDEX 힌트는 지정된 테이블에 대한 인덱스 집합을 허용하지 않는다.
SELECT /*+ NO_INDEX(employees emp_empid) */ employee_id
FROM employees
WHERE employee_id > 200; 2.4 Hints for Join Orders
2.5 Hints for Join Operations
다음 힌트는 테이블에 대한 특정 조인 작업을 사용하도록 지시한다.
- USE_NL and NO_USE_NL
- USE_NL_WITH_INDEX
- USE_MERGE and NO_USE_MERGE
- USE_HASH and NO_USE_HASH
- NO_USE_HASH
2.6 Hints for Online Application Upgrade
온라인 애플리케이션 업그레이드 힌트는 에디션 기반 재정의를 사용하여 온라인 애플리케이션 업그레이드를 수행할 때 충돌하는 INSERT 및 UPDATE 작업을 처리하는 방법을 제안한다.
2.7 Hints for Parallel Execution
병렬 실행 힌트는 작업을 병렬화할지 여부와 방법에 대해 지시합니다. 다음 병렬 힌트를 사용할 수 있다.
2.8 Hints for Query Transformations
다음 힌트는 옵티마이저가 특정 SQL 쿼리 변환을 사용하도록 지시한다.
- NO_QUERY_TRANSFORMATION
- USE_CONCAT
- NO_EXPAND
- REWRITE and NO_REWRITE
- MERGE and NO_MERGE
- STAR_TRANSFORMATION and NO_STAR_TRANSFORMATION
- FACT and NO_FACT
- UNNEST and NO_UNNEST
2.9 추가 힌트(Additional Hints)
- APPEND, APPEND_VALUES, and NOAPPEND
- CACHE and NOCACHE
- PUSH_PRED and NO_PUSH_PRED
- PUSH_SUBQ and NO_PUSH_SUBQ
- QB_NAME
- CURSOR_SHARING_EXACT
- DRIVING_SITE
- DYNAMIC_SAMPLING
- MODEL_MIN_ANALYSIS
