조회 화면이 느리다는 이야기가 들어왔다. 특정 고객을 검색하면 결과가 3초쯤 뒤에 뜬다는 것이다. 다른 고객은 즉시 뜬다고 했다.
쿼리를 받아서 실행계획부터 떴다. INDEX RANGE SCAN이 찍혀 있었다. 인덱스도 있고 타기도 한다. 여기서 한 번 막혔다. 인덱스를 타는데 왜 느린가.
증상
조건 값을 바꿔 가며 재현해 봤다. 거래가 적은 고객은 0.02초, 거래가 많은 고객은 3.1초가 나왔다. 실행계획은 두 경우 모두 동일했다. 같은 인덱스, 같은 operation. 그런데 소요 시간은 150배 차이가 난다.
여기서 알 수 있는 건 하나다. 실행계획의 operation 이름만으로는 아무것도 판단할 수 없다. 인덱스를 탔다는 건 시작점이지 결론이 아니다.
원인 분석
실행계획을 제대로 보려면 예상치가 아니라 실측치를 봐야 한다. 세션에 통계 수집을 켜고 실제로 돌린 뒤 계획을 뜬다.
alter session set statistics_level = all;
select /*+ gather_plan_statistics */ o.ord_no, o.ord_dt, o.amt, o.status
from orders o
where o.cust_id = :b1
and o.ord_dt between :b2 and :b3
and o.status = 'C';
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PEEKED_BINDS'));여기서 봐야 할 게 두 곳이다.
첫째, A-Rows와 E-Rows. E-Rows는 옵티마이저가 예상한 행 수, A-Rows는 실제로 나온 행 수다. 문제의 쿼리는 E-Rows 120에 A-Rows 84,000이었다. 옵티마이저가 120건 나올 거라 보고 세운 계획인데 실제로는 8만 4천 건이 나왔다. 700배 틀렸다. 이 정도로 벗어나면 그 아래 모든 선택이 잘못된 전제 위에 서 있다.
둘째, Predicate Information 섹션. 실행계획 아래 붙는 이 부분이 진짜 본문이다.
2 - access("O"."CUST_ID"=:B1)
2 - filter("O"."ORD_DT">=:B2 AND "O"."ORD_DT"<=:B3)
1 - filter("O"."STATUS"='C')access와 filter는 완전히 다른 의미다.
- access — 인덱스에서 읽을 범위를 좁히는 데 쓰인 조건. 진짜 일하는 조건이다.
- filter — 일단 다 읽어 온 다음 버리는 조건. 읽는 양은 전혀 줄이지 않는다.
이 쿼리에서 범위를 좁혀 준 건 cust_id 하나뿐이었다. 날짜 조건과 상태 조건은 전부 읽은 뒤 버리는 역할이었다. 거래가 많은 고객은 cust_id 하나로 8만 건이 걸리고, 그 8만 건을 전부 테이블에서 읽어 온 다음 날짜와 상태로 걸러서 200건을 남기고 있었다.
select index_name, column_name, column_position
from user_ind_columns
where table_name = 'ORDERS'
order by index_name, column_position;IX_ORDERS_01은 CUST_ID 단일 컬럼이었다. 예상대로였다. 한 가지 더 확인했다. 인덱스로 찾은 행을 테이블에서 꺼내오는 비용이다.
select index_name, num_rows, leaf_blocks, clustering_factor
from user_indexes
where table_name = 'ORDERS';clustering_factor가 num_rows에 거의 근접해 있었다. 인덱스 순서와 테이블의 물리적 저장 순서가 완전히 따로 논다는 뜻이고, 인덱스에서 찾은 행 하나마다 서로 다른 블록을 랜덤하게 읽어야 한다는 뜻이다. 8만 번의 랜덤 블록 액세스. 3초의 정체가 이것이었다.
해결
복합 인덱스를 새로 만들되, 컬럼 순서를 access 조건이 최대한 많이 붙도록 잡았다.
create index ix_orders_02 on orders (cust_id, status, ord_dt);순서가 중요하다. 등치 조건을 앞에, 범위 조건을 뒤에 둔다. 범위 조건이 중간에 끼면 그 뒤 컬럼들은 access로 쓰이지 못하고 filter로 밀려난다. status를 ord_dt 앞에 둔 이유가 이것이다.
2 - access("O"."CUST_ID"=:B1 AND "O"."STATUS"='C' AND "O"."ORD_DT">=:B2 AND "O"."ORD_DT"<=:B3)세 조건이 전부 access로 올라왔다. A-Rows는 84,000에서 210으로 떨어졌고 소요 시간은 0.03초가 됐다.
조회 컬럼이 몇 개 안 되는 화면이었다면 한 발 더 갈 수도 있었다. 필요한 컬럼까지 인덱스에 포함시키면 테이블 액세스 자체가 사라진다.
create index ix_orders_03 on orders (cust_id, status, ord_dt, amt, ord_no);이 경우 실행계획에서 TABLE ACCESS BY INDEX ROWID 줄이 통째로 없어진다. 다만 인덱스가 뚱뚱해지고 DML이 느려지므로 호출 빈도가 정말 높은 쿼리에만 쓴다.
남은 교훈
실행계획을 본다는 건 operation 목록을 본다는 뜻이 아니다. FULL SCAN이라는 단어를 보고 놀라고 INDEX SCAN이라는 단어를 보고 안심하는 습관이 오히려 문제를 못 찾게 만든다. 8만 건을 랜덤 액세스하는 인덱스 스캔보다 잘 정리된 풀 스캔이 빠른 경우는 흔하다.
볼 것은 두 개다. A-Rows가 E-Rows와 얼마나 벌어졌는가, 그리고 내 조건들이 access에 있는가 filter에 있는가. 이 두 줄만 봐도 대부분의 느린 쿼리는 원인이 드러난다.
그리고 인덱스를 걸었는데 안 빨라졌다는 말을 들으면, 인덱스가 없어서가 아니라 컬럼 순서가 조건 모양과 안 맞아서인 경우가 대부분이다. 인덱스는 만드는 것보다 순서를 정하는 게 일이다.