쿼리 구조 및 실행으로는 아무 문제가 없으나, 특정 계층 쿼리가 DB버퍼를 많이 사용하는 현상이 관찰되었다. 실행계획을 떠본 결과 튜닝포인트가 있음을 확인할 수 있었다. 이 참에 계층쿼리 기본부터 정리하며 짚어보았다.
👉 계층쿼리란
테이블의 데이터가 계층적인 구조로 되어있고, 해당 데이터를 쿼리하고자 할 때 사용. (계층쿼리 구조도)
오라클 계층쿼리 주요 특징 : 아래의 절을 포함한다.
-
START WITH : 쿼리할 계층 구조의 루트 행을 지정한다. 생략될 경우 모든 ROW가 루트노드로 간주된다.
-
CONNECT BY : 계층 구조의 상위 행과 하위 행 간의 관계를 지정한다. 자식노드가 될 컬럼 앞에 PRIOR 키워드를 붙여 부모노드를 탐색한다.
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
;
EMPLOYEE_ID LAST_NAME MANAGER_ID
----------- ------------------------- ----------
101 Kochhar 100
108 Greenberg 101
109 Faviet 108
110 Chen 108
111 Sciarra 108
...
-- employee_id가 100인 노드의 매니저 하위 직원들의 ID와 성을 모두 추출한다.👉 계층쿼리 실행순서
계층쿼리 관련 오라클 공식문서를 참조하여 정리해보았다.
-
START WITH 조건절을 만족하는 행(들)을 최상위 루트 노드(들)로 선택한다.
(manager_id=100인 row를 선택) -
1에서 선택된 행의 자식노드를 찾는다. 모든 자식 행들은 CONNECT BY 절에서 지정한 조건에 따라 부모와 연결되어야 한다.
(1에서 선택된 행의employee_id를manager_id로 갖는 자식 행들을 찾는다) -
위와 같은 방식으로 이어지는 자식행들을 모두 찾는다. Depth-first 방식으로, 먼저 방문한 행의 자식을 모두 찾아낸 다음, 다 찾은 이후에는 옆의 노드로 이동하며 아래로 탐색을 계속한다. (아래 이미지 참조)
- 쿼리에 조인 없이 WHERE 절이 포함된 경우, 계층구조에서 WHERE 절의 조건을 충족하지 않는 모든 행을 제거(필터아웃)한다.
즉, START WITH > CONNECT BY > 테이블 WHERE 절 순으로 실행된다고 정리할 수 있다. 그러나 조인이 있을 경우에는 조인이 먼저 실행된다.
👉 튜닝대상 계층쿼리 실행계획 확인하기
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.06 | 11977 | | | |
| 1 | SORT ORDER BY | | 1 | 3 |00:00:00.06 | 11977 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 3 |00:00:00.05 | 11977 | 1075K| 1075K| 424K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 3 |00:00:00.01 | 7 | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE_B | 1 | 3 |00:00:00.01 | 7 | | | |
|* 5 | INDEX RANGE SCAN | IF2_TABLE_B | 1 | 3 |00:00:00.01 | 4 | | | |
| 6 | VIEW | | 1 | 4 |00:00:00.05 | 11970 | | | |
|* 7 | FILTER | | 1 | 4 |00:00:00.05 | 11970 | | | |
| 8 | JOIN FILTER USE | :BF0000 | 1 | 4 |00:00:00.05 | 11970 | | | |
|* 9 | CONNECT BY WITH FILTERING ◀️◀️◀️ | | 1 | 11608 |00:00:00.06 | 11970 | 2533K| 726K| 2251K (0)|
|* 10 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE_A | 1 | 4 |00:00:00.01 | 7 | | | |
|* 11 | INDEX RANGE SCAN | IX1_TABLE_A | 1 | 5 |00:00:00.01 | 2 | | | |
| 12 | NESTED LOOPS | | 6 | 11604 |00:00:00.03 | 11963 | | | |
| 13 | CONNECT BY PUMP | | 6 | 11608 |00:00:00.01 | 0 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_A | 11608 | 11604 |00:00:00.02 | 11963 | | | |
|* 15 | INDEX RANGE SCAN | IX1_TABLE_A | 11608 | 13148 |00:00:00.01 | 1452 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------◀️◀️◀️ 화살표 부분에서 CONNECT BY(WITH FILTERING) 실행계획이 수행될 예정임을 확인하였다. 이는 CONNECT BY 작업을 수행할 때 데이터를 필터링하도록 SQL 실행 프로그램에 지시한다. TABLE_A START WITH 시 한번에 액세스하지 않고 RANGE SCAN한 다음, 해당 데이터를 가지고 CONNECT BY 할때 다시 TABLE_A 액세스하며 대조하여 조건에 맞는 것만 찾는 것으로 보인다.
👉 /*+ NO_CONNECT_BY_FILTERING */ 힌트절
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.03 | 632 | | | |
| 1 | SORT ORDER BY | | 1 | 3 |00:00:00.03 | 632 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 3 |00:00:00.03 | 632 | 1075K| 1075K| 424K (0)|
| 3 | JOIN FILTER CREATE | :BF0000 | 1 | 3 |00:00:00.01 | 7 | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID BATCHED | TABLE_B | 1 | 3 |00:00:00.01 | 7 | | | |
|* 5 | INDEX RANGE SCAN | IF2_TABLE_B | 1 | 3 |00:00:00.01 | 4 | | | |
| 6 | VIEW | | 1 | 4 |00:00:00.02 | 625 | | | |
|* 7 | FILTER | | 1 | 4 |00:00:00.02 | 625 | | | |
| 8 | JOIN FILTER USE | :BF0000 | 1 | 4 |00:00:00.02 | 625 | | | |
|* 9 | CONNECT BY NO FILTERING WITH START-WITH|◀️◀️◀️ | 1 | 11608 |00:00:00.03 | 625 | 2675K| 740K| 2377K (0)|
| 10 | TABLE ACCESS FULL | TABLE_A | 1 | 13518 |00:00:00.01 | 625 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------/*+ NO_CONNECT_BY_FILTERING */ 힌트절을 추가하고나니 실행이 위처럼 변경되었다. TABLE A에 한번만 접근하고 따라서 Buffers(해당 단계까지 메모리 버퍼에서 읽은 블록 수. 논리적 IO 횟수) 부분에서 크게 향상된 것을 확인할 수 있었다.
힌트 관련해서 이 블로그를 참고하였는데 읽어도 잘 이해가 안되는 부분이 많아 쿼리를 이거저거 바꿔가며 Oracle 테스트결과로 효용성을 파악하였다. 추후 공식문서 등을 찾고 공부를 더 해서 업데이트를 해야겠다.
