1. 실행계획이란?
데이터베이스 컨텍스트에서 실행 계획은 데이터베이스 엔진이 특정 쿼리에 대한 데이터를 처리하고 검색하는 데 사용하는 세부 전략을 나타낸다. 즉 "SQL을 어떻게 수행하는가"를 알려주는 기능이다.
실행계획은 내부적으로 쿼리 실행 프로세스를 최적하기 위해 인덱스, 통계 및 시스템 리소스와 같은 요소를 고려하여 결과를 나타내므로 적절히 활용한다면 좋은 SQL를 구성하는데 도움이 될 것이다.
필자는 일반적으로 DB tool에서 사용하는 실행계획을 사용해왔는데, 이번에 실행계획의 분류에 대해 알게되면서 글을 작성하려고 한다.
2. 실행계획 분류
dbeaver나 querybox같은 툴을 사용해, 내부 기능인 실행계획을 실행하는 것을 일반적으로 예상 실행계획이라고 한다.
이와 달리 실제 실행계획이라고 부르는 다른 방법이 존재하는데 두가지에 대해서 알아보도록 하자.
길고 긴 내용이지만,, 자세한 확인을 위해 두 분류의 실행순서를 따라 설명을 작성해보았다.
2.1 예상 실행계획
- 구문 분석: 실행 계획의 첫 번째 단계는 구문 분석이다. 여기서 데이터베이스 관리 시스템(DBMS)은 구문의 정확성을 확인하기 위해 SQL 쿼리 또는 명령문을 분석하고 데이터베이스 스키마에 대해 쿼리의 유효성을 한다.
- 권한 부여: 구문 분석이 끝나고 작업 수행을 위한 사용자 권한 및 권한을 확인한다. 여기에는 요청된 데이터에 액세스하거나 요청된 작업을 수행하는 데 필요한 권한이 있는지 확인하기 위해 사용자의 권한, 역할 및 사용 권한을 확인하는 것이 포함된다.
- 쿼리 최적화: 인증 후 DBMS가 쿼리 최적화를 수행한다. 이 단계에는 쿼리를 분석하고 최적화된 실행 계획을 생성하여 데이터를 효율적으로 검색하는 작업이 포함된다. DBMS는 쿼리를 실행하는 최선의 방법을 결정하기 위해 인덱스, 통계 및 사용 가능한 시스템 리소스와 같은 다양한 요소를 고려한다.
- 쿼리 실행: 최적화된 실행 계획이 생성되면 DBMS가 쿼리 실행을 시작한다. 여기에는 데이터베이스에서 필요한 데이터에 액세스하고, 필터 또는 조인을 적용하고, 쿼리에 지정된 다른 작업을 수행하는 작업이 포함된다.
- 잠금 및 동시성 제어: 쿼리 실행 중에 DBMS는 데이터 무결성을 보장하고 여러 동시 트랜잭션 간의 충돌을 방지하기 위한 메커니즘을 사용한다. 잠금 및 동시성 제어 메커니즘은 데이터베이스에 대한 동시 액세스를 관리하고 여러 트랜잭션이 서로 간섭하지 않도록 하는 데 사용된다.
- 결과처리 : 질의가 수행되면 DBMS가 결과를 처리한다. 여기에는 데이터를 원하는 결과 형식(예: 표 형식)으로 형식화하고 정렬 또는 집계와 같은 사후 처리 작업을 적용하는 것이 포함된다.
- 트랜잭션 관리: 쿼리가 트랜잭션의 일부로 실행된 경우 DBMS는 쿼리 결과에 따라 트랜잭션 커밋 또는 롤백과 같은 트랜잭션 관리 작업을 수행한다.
2.2 실제 실행계획
- 구문 분석: 예상 실행계획과 동일하게 첫 시작은 구문 분석이다.
- 컴파일: 쿼리가 구문 분석되면 DBMS는 이를 쿼리 최적화 프로그램에서 처리할 수 있는 내부 표현으로 컴파일한다. 이 단계에는 AST(추상 구문 트리) 생성, 쿼리 계획 트리 생성 또는 추가 최적화에 적합한 형식으로 쿼리를 나타내는 다른 데이터 구조 사용이 포함될 수 있다.
- 쿼리 최적화: 예상 실행계획의 쿼리 최적화와 동일한 동작을 한다.
- Plan 생성: 쿼리 옵티마이저가 최적화된 실행 계획을 결정하면 DBMS는 gather_plan_statistics 기능을 사용하여 실제 실행 계획을 생성한다. 이 단계에는 데이터베이스 및 시스템 리소스의 현재 상태를 기반으로 카디널리티, 선택도 추정치 및 비용 추정치와 같은 실제 통계로 계획을 채우는 작업이 포함된다.
- 계획 표현: 실제 실행 계획은 쿼리를 실행하기 위해 데이터베이스가 수행할 작업의 순서를 반영하는 트리 또는 그래프와 같은 적절한 형식으로 표현된다. 계획의 각 노드는 검색, 조인 또는 정렬과 같은 작업을 나타내고 가장자리는 작업 간의 데이터 흐름을 나타낸다.
- 계획 실행: DBMS는 계획에 정의된 작업 순서에 따라 실제 실행 계획을 실행한다. 여기에는 데이터베이스에서 데이터에 액세스하고, 필터 또는 조인을 적용하고, 계획에 지정된 다른 작업을 수행하고, 결과를 사용자에게 반환하는 작업이 포함된다.
- 통계 수집: 계획 실행 중에 DBMS는 실제 행 수, I/O 비용 및 CPU 사용량과 같은 추가 통계를 수집하여 쿼리 성능에 대한 보다 정확한 정보로 계획을 업데이트할 수 있다.
- 결과 처리: 쿼리가 실행되면 DBMS는 데이터를 원하는 결과 형식으로 포맷하고, 후처리 작업을 적용하고, 최종 결과를 사용자에게 반환하는 등 계획에 따라 결과를 처리한다.
2.3 차이점
예상 실행계획과 실제 실행계획의 주요 차이점은 계획 생성의 타이밍과 정확성이다. 예상 실행계획은 쿼리 최적화 중에 사용 가능한 통계 및 비용 추정에 기반하여 생성되며, 실제 실행계획은 실시간 통계 및 시스템 리소스를 사용하여 쿼리 실행 중에 생성된다.
실제 실행계획은 쿼리 성능에 영향을 미칠 수 있는 동적 요인을 고려하며, 예상 실행계획은 쿼리 최적화 중에 사용 가능한 정적 정보에 기반한다. (그러나 쿼리 옵티마이저가 사용하는 쿼리 최적화 방법은 두 경우 모두 동일하다)
즉, 실제 실행계획을 사용하면 더 정확한 비용 추정과 최적화된 실행계획이 생성되어 쿼리 성능을 향상시킬 수 있다.
3. 실행계획 수행
실행계획을 수행하는 것은 간단한 힌트만을 추가하면 된다. 다만 필자의 근무환경에서는 추가적인 쿼리를 수행해야 결과를 얻을 수 있었는데, 이를 작성해두려고 한다.
/*+ gather_plan_statistics */힌트를 추가하여 쿼리 수행SELECT /*+ gather_plan_statistics */ * FROM {TABLE} WHERE {CONDITION};- (Optional) 사용된 쿼리 목록을 조회하여,
SQL_ID획득. 아래의 쿼리를 사용하면 LIKE 문법과 같이 작성된 쿼리의 이력을 보여준다. 이력에서 수행한 쿼리의SQL_ID를 찾아서 다음 단계에서 사용한다.SELECT * FROM v$sql WHERE SQL_TEXT LIKE '%SELECT /*+ gather_plan_statistics */ * FROM {테이블명}%';
DBMS_XPLAN.DISPLAY_CURSOR()의 첫번째 파라미터에 2번째 단계에서 조회한SQL_ID를 넣어 아래의 쿼리를 수행하면 실제 실행계획을 확인 할 수 있다.SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('cdkw3uxs6qvw6', NULL, 'ADVANCED ALLSTATS LAST'));
