쿼리 계획 (Query Plan) 이란?
쿼리 계획 (Query Plan) 이란?특징장점실행 계획실행 계획 이해실행 계획 생성Plan Table 구조실행 계획 해석실행 계획 순서 읽기실행 계획 해석하기사용 예시EXPLAN PLAN마무리참조
쿼리 계획 (Query Plan) 이란?
쿼리 실행 계획(Query Plan)은 데이터베이스 관리 시스템(DBMS)이 SQL 쿼리를 처리하기 위해 사용하는 실행 계획이다.
이것은 DBMS에서 쿼리를 처리할 때 데이터베이스에서 데이터를 검색하고 가져오는 방법을 결정하는데 사용된다.
쿼리 실행에 필요한 단계를 보여주며, 각 단계에서 DBMS가 사용하는 액세스 경로를 보여주고, 쿼리 실행에 필요한 리소스 및 비용 정보를 제공한다.
즉, SQL을 실행하는데 있어 사용되는 cost와 어떠한 방식이 가장 적절한지 판단하고, 올바른 플랜 중 평가하여 어떠한 순서와 방식으로 처리할 것인지 계획을 세우고 필요에 따라 변경하여 성능을 개선하는데 사용된다.
이를 통해 DBMS는 쿼리 실행에 필요한 최소한의 리소스를 사용하면서 최적의 성능을 제공할 수 있다.
특징
- 쿼리 실행에 필요한 단계를 보여준다.
- 각 단계에서 DBMS가 사용하는 액세스 경로를 보여준다.
- 쿼리 실행에 필요한 리소스 및 비용 정보를 제공한다.
장점
- 성능 문제를 식별하는 데 도움이 된다.
- 실행 계획을 변경하여 쿼리 성능을 개선할 수 있다.
- 쿼리 최적화를 위한 정보를 제공한다.
실행 계획
실행 계획 이해
좋은 실행 계획을 보고 있는지 여부를 판단하려면, 최적화 프로그램이 처음에 계획을 결정하는 방법을 이해해야 한다. 또한 실행 계획을 살펴보고 최적화 프로그램이 추정이나 계산에서 잘못되었거나 부적절한 계획으로 이어졌는지를 평가할 수 있어야 한다. 평가해야 할 구성 요소는 다음과 같다.
- Cardinality: 각 작업에서 나오는 행 수의 추정치다.
- Access method: 데이터에 접근하는 방법, 테이블 검색 또는 인덱스 액세스를 통해한다.
- Join method: 테이블을 서로 결합하는 데 사용되는 방법(예: 해시, 정렬-병합 등)이다.
- Join type: 결합 유형(외부, 반대, 반, 등)이다.
- Join order: 테이블이 서로 결합되는 순서다.
- Partition pruning: 쿼리를 해결하기 위해 필요한 파티션만 액세스하고 있는지 여부다.
- Parallel Execution: 병렬 실행의 경우, 계획의 각 작업이 병렬로 수행되고 있다는 것을 확인해야 한다. 적절한 데이터 재분배 방법이 사용되고 있는지도 확인해야 한다.
실행 계획 생성
Oracle에서는
EXPLAIN PLAN
명령어를 사용하여 SQL 쿼리의 실행 계획을 생성할 수 있다.
이를 통해 쿼리가 어떻게 실행되는지, 어떤 인덱스가 사용되는지 등을 확인할 수 있다.EXPLAIN PLAN
명령어는 다음과 같은 형태로 사용된다.EXPLAIN PLAN SET STATEMENT_ID = '계획명' INTO PLAN_TABLE FOR SQL_쿼리;
위의 예시에서
SET STATEMENT_ID
는 생성되는 실행 계획의 이름을 지정하는 옵션이다. INTO PLAN_TABLE
은 실행 계획을 저장할 테이블을 지정한다.
실행 계획을 저장하는 테이블은 미리 생성되어 있어야 한다.
아래와 같이 SQL 문을 사용하여
PLAN_TABLE
을 생성할 수 있다.CREATE TABLE PLAN_TABLE ( STATEMENT_ID VARCHAR2(30), PLAN_ID NUMBER, TIMESTAMP DATE, REMARKS VARCHAR2(4000), OPERATION VARCHAR2(30), OPTIONS VARCHAR2(255), OBJECT_NODE VARCHAR2(128), OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30), OBJECT_ALIAS VARCHAR2(65), OBJECT_INSTANCE NUMBER, OBJECT_TYPE VARCHAR2(30), OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER, ID NUMBER, PARENT_ID NUMBER, DEPTH NUMBER, POSITION NUMBER, COST NUMBER, CARDINALITY NUMBER, BYTES NUMBER, OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255), PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMBER, OTHER LONG, DISTRIBUTION VARCHAR2(30), CPU_COST NUMBER, IO_COST NUMBER, TEMP_SPACE NUMBER, ACCESS_PREDICATES VARCHAR2(4000), FILTER_PREDICATES VARCHAR2(4000), PROJECTION VARCHAR2(4000), TIME NUMBER, QBLOCK_NAME VARCHAR2(30), OTHER_XML CLOB );
Plan Table 구조
펼쳐서 구조 확인하기 🎈
항목 | 설명 |
STATEMENT_ID | 쿼리를 고유하게 식별하는 ID |
PLAN_ID | 실행 계획의 고유한 ID |
TIMESTAMP | 실행 계획이 생성된 시간 |
REMARKS | 실행 계획에 대한 추가 정보 |
OPERATION | 실행 계획에서 수행되는 연산 |
OPTIONS | 연산에 대한 추가 옵션 |
OBJECT_NODE | 연산이 수행되는 노드 |
OBJECT_OWNER | 연산이 수행되는 객체의 소유자 |
OBJECT_NAME | 연산이 수행되는 객체의 이름 |
OBJECT_ALIAS | 연산이 수행되는 객체의 별칭 |
OBJECT_INSTANCE | 연산이 수행되는 객체의 인스턴스 |
OBJECT_TYPE | 연산이 수행되는 객체의 타입 |
OPTIMIZER | 사용된 옵티마이저 |
SEARCH_COLUMNS | 검색된 칼럼 수 |
ID | 노드의 고유 ID |
PARENT_ID | 부모 노드의 ID |
DEPTH | 노드의 깊이 |
POSITION | 노드의 위치 |
COST | 노드의 비용 |
CARDINALITY | 노드가 반환하는 행 수 |
BYTES | 노드가 반환하는 바이트 수 |
OTHER_TAG | 다른 정보 |
PARTITION_START | 파티션 범위의 시작 |
PARTITION_STOP | 파티션 범위의 끝 |
PARTITION_ID | 파티션 ID |
OTHER | 다른 정보 |
DISTRIBUTION | 노드의 분배 방법 |
CPU_COST | 노드의 CPU 비용 |
IO_COST | 노드의 IO 비용 |
TEMP_SPACE | 노드에서 사용하는 임시 공간 |
ACCESS_PREDICATES | 액세스 수행 시 사용되는 조건 |
FILTER_PREDICATES | 필터링 시 사용되는 조건 |
PROJECTION | 노드에서 사용되는 프로젝션 |
TIME | 노드에서 소요된 시간 |
QBLOCK_NAME | 블록 이름 |
OTHER_XML | 다른 정보 |
위의 항목들은 실행 계획에서 수행되는 연산과 함께, 쿼리 성능을 최적화하는 데 도움을 줄 수 있는 정보를 제공한다.
실행 계획 해석
실행 계획에 대해 잘 설명된 글이 있어, 내용을 스크랩 해왔다. 출처: [DB] 데이터베이스 실행 계획에 대하여
실행 계획은 여러 가지 단계로 이루어져 있는데 이것을 스텝이라고 한다. 각각의 스텝에는 그 단계에서 어떤 명령이 수행되었고 총 몇 건의 데이터가 처리되었으며 이 처리를 위해 얼마만큼의 비용과 시간이 소요되었다.
실행 계획 순서 읽기
실행 계획을 읽을 때에는 아래와 같은 규칙이 있다. 이 규칙을 토대로 하나씩 읽어나간다.
- 위에서 아래로 읽어 내려가면서 제일 먼저 읽을 스텝을 찾는다.
- 내려가는 과정에서 같은 들여 쓰기가 존재한다면 무조건 위 ➡ 아래 순으로 읽는다.
- 읽고자 하는 스텝보다 들여 쓰기가 된 하위스텝이 존재한다면, 가장 안쪽으로 들여쓰기 된 스텝을 시작으로 하여 한 단계씩 상위 스텝으로 읽어 나온다.
위의 예제의 경우 이 규칙으로 실행 계획을 읽는 순서를 정한다면 위와 같이 된다.
출력된 실행 계획에서 위쪽에 출력된 결과일수록(ID 칼럼의 값이 작을수록) 쿼리의 바깥(Outer) 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록(ID 칼럼의 값이 클수록) 쿼리의 안쪽(Inner) 부분 또는 나중에 접근한 테이블에 해당된다.
실행 계획 해석하기
실행 계획의 해석 가장 나중에 실행된 것부터 즉 트리의 가장 좌측 아래부터 역순으로 해석한다.
위의 예제를 기준으로 한다면 위와 같은 순서로 해석해간다.
자식들의 좌측부터 차례대로 읽어주고 그 다음에 상위 부모로 올라가는 식으로 반복하면 된다.
위의 예제는
5 ➡ 4 ➡ 6 ➡ 3 ➡ 7 ➡ 2 ➡ 8 ➡ 1 ➡ 0
순으로 진행
위의 실행 계획을 해석하자면 위의 그림과 같다.
5번 : PK_EMP 인덱스를 사용하여 INDEX RANGE SCAN을 하면서 조건에 만족하는 인덱스 블록과 키 값을 검색한 결과를 반환한다.
4번 : 5번에서 읽은 ROWID를 기반으로 EMP 테이블로 이동하여 조건에 부합하는 결과를 반환한다.
6번 : PK_DEPTNO 인덱스에서 INDEX UNIQUE SCAN 방식으로 검색한 결과의 ROWID를 반환한다.
3번 : 4번과 6번에서 반환된 데이터들을 기준으로 NESTED LOOP JOIN 방식으로 4번에서 반환된 데이터의 숫자만큼 반복하여 조인한 결과를 반환한다.
7번 : DEPT 테이블도 4번과 같이 조건에 부합하는 결과를 반환한다.
2번 : NESTED LOOP JOIN 방식으로 3번과 같이 JOIN의 결과를 만들어준다.
8번 : SALGRADE 서브쿼리를 실행한다.
1번 : 서브쿼리를 통해 해당 조건을 만족하는 데이터를 필터링하여 반환한다.
사용 예시
EXPLAN PLAN
실행 계획을 저장할 테이블을 생성한 후에는
EXPLAIN PLAN
명령어를 사용하여 실행 계획을 생성할 수 있다.EXPLAIN PLAN SET STATEMENT_ID = 'plan1' INTO PLAN_TABLE FOR SELECT * FROM employees WHERE salary > 5000;
위의 예시에서는
SET STATEMENT_ID
옵션을 사용하여 실행 계획의 이름을 plan1
로 지정하였다.
실행 계획을 조회하기 위해서는
DBMS_XPLAN.DISPLAY
함수를 사용한다.SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','plan1','ALL'));
위의 예시에서는
DBMS_XPLAN.DISPLAY
함수를 사용하여 PLAN_TABLE
에 저장된 plan1
실행 계획을 모두 조회하였다.이처럼
EXPLAIN PLAN
명령어와 DBMS_XPLAN.DISPLAY
함수를 사용하여 Oracle에서 SQL 쿼리의 실행 계획을 생성하고 조회할 수 있다.출력 예)
마무리
쿼리 플랜(Query Plan)을 적절하게 사용하면, 더욱 나은 서비스와 최적화를 할 수 있다.
모든 SQL을 검토하긴 어려우니 성능이 낮은 SQL을 가지고 사용해 보는 것을 추천한다.
참조
긴 글 읽어주셔서 감사합니다.
오탈자 및 내용 피드백은 언제나 환영합니다.
#DB