느린 쿼리 트러블슈팅 (대기, 유출, 프루닝, 잠금, 컴파일)

먼저 볼 것: 시간이 어디서 쓰였나

느리다는 신고가 오면 먼저 쿼리 ID 를 받아 총 시간을 쪼갭니다.

SELECT query_id, warehouse_name, warehouse_size,
       total_elapsed_time/1000      AS total_s,
       compilation_time/1000        AS compile_s,
       queued_provisioning_time/1000 AS q_provision_s,
       queued_overload_time/1000    AS q_overload_s,
       transaction_blocked_time/1000 AS blocked_s,
       execution_time/1000          AS exec_s,
       bytes_spilled_to_remote_storage,
       partitions_scanned, partitions_total,
       percentage_scanned_from_cache
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(USER_NAME => 'KIM', RESULT_LIMIT => 50))
WHERE query_id = '01b2c3d4-0000-...';

사례 1. 아침마다 대시보드가 느립니다

• 증상: 오전 9시대에만 대시보드 응답이 수십 초로 늘고, 쿼리 자체 실행 시간은 짧습니다.
• 원인: 출근 시간에 동시 쿼리가 몰려 queued_overload_time 이 큽니다. 웨어하우스가 한 클러스터라 차례를 기다립니다.
• 해결: BI 웨어하우스를 멀티 클러스터(MAX_CLUSTER_COUNT 2 이상, SCALING_POLICY = 'STANDARD')로 바꿉니다. 크기를 키우는 것보다 효과적입니다. 자세한 설정은 웨어하우스와 크레딧 에 있습니다.

사례 2. 첫 쿼리만 느립니다

• 증상: 한동안 쓰지 않다가 처음 돌린 쿼리만 느리고, 바로 다시 돌리면 빠릅니다.
• 원인: 웨어하우스가 일시 중지 상태에서 재개되며 프로비저닝을 기다렸고, 로컬 디스크 캐시가 비어 원격 저장소에서 읽었습니다.
• 해결: 대개 정상입니다. 응답이 중요한 BI 웨어하우스는 AUTO_SUSPEND 를 조금 길게 두거나, 같은 쿼리라면 결과 캐시를 활용합니다. 캐시 문서를 참고합니다.

사례 3. 원격 디스크 유출

• 증상: Query Profile 에 "Bytes spilled to remote storage" 가 GB 단위로 보이고, 실행 시간 대부분이 집계나 정렬 연산자입니다.
• 원인: 처리할 데이터가 웨어하우스 메모리와 로컬 디스크를 넘었습니다.
• 해결:
  1. 필요한 열만 선택하고 조건을 먼저 걸어 처리량을 줄입니다.
  2. 큰 COUNT(DISTINCT) 는 APPROX_COUNT_DISTINCT 를 검토합니다.
  3. 그래도 남으면 웨어하우스를 한 단계 키웁니다. 유출이 사라지면 시간이 절반 이하로 줄어 총 크레딧이 비슷하거나 줄어드는 경우가 많습니다.

사례 4. 거의 전체를 스캔합니다

• 증상: partitions_scanned 가 partitions_total 과 거의 같고, 조건으로 몇 행만 고르는데도 느립니다.
• 원인: 조건 열에 함수를 씌웠거나(DATE(ts) = ...), 형 변환이 일어나거나, 조건 열과 데이터 적재 순서가 무관해 값이 모든 파티션에 퍼져 있습니다.
• 해결: 조건을 열 그대로의 범위 비교로 고칩니다. 그래도 넓다면 클러스터링 키, 등호 조건이 대부분이라면 Search Optimization 을 검토합니다. 쿼리 튜닝 의 표를 참고합니다.

사례 5. 조인 뒤 행이 폭발합니다

• 증상: Profile 에서 조인 연산자의 출력 행이 입력 행의 수십 배이고, 결과가 이상하게 많습니다.
• 원인: 조인 키가 한쪽 또는 양쪽에서 고유하지 않습니다. 이력 테이블을 기간 조건 없이 조인한 경우가 흔합니다.
• 해결: 키의 고유성을 GROUP BY ... HAVING COUNT(*) > 1 로 확인하고, 유효 기간 조건이나 QUALIFY ROW_NUMBER() ... = 1 로 한 행만 남긴 뒤 조인합니다.

사례 6. 잠금 대기

• 증상: UPDATE, MERGE, DELETE 가 실행 시간은 짧은데 총 시간이 깁니다. transaction_blocked_time 이 큽니다.
• 원인: 같은 테이블에 다른 트랜잭션이 쓰기를 하고 있어 기다립니다. 같은 테이블에 대한 동시 DML 은 대부분 차례로 실행됩니다. 열린 채 남은 명시적 트랜잭션도 원인입니다.
• 해결:

SHOW LOCKS IN ACCOUNT;
SHOW TRANSACTIONS IN ACCOUNT;
SELECT SYSTEM$ABORT_TRANSACTION(<transaction_id>);   -- 정말 필요한 경우만

같은 테이블에 여러 작업이 동시에 쓰지 않게 태스크 그래프로 순서를 정하고, 작은 MERGE 여러 번보다 모아서 한 번에 처리합니다. 세션의 LOCK_TIMEOUT 매개변수로 기다릴 최대 시간을 정할 수 있습니다.

사례 7. 컴파일이 오래 걸립니다

• 증상: compilation_time 이 수십 초이고 실행은 금방 끝납니다.
• 원인: 수천 줄짜리 생성 SQL, 깊게 중첩된 뷰, 아주 긴 IN 목록, 수많은 UNION ALL, 또는 파티션 수가 매우 많은 테이블의 메타데이터 처리입니다.
• 해결: 중간 결과를 테이블이나 Dynamic Table 로 끊고, 긴 IN 목록은 임시 테이블과의 조인으로 바꿉니다. 같은 패턴이 반복되면 결과 캐시가 쓰이는지 확인합니다.

사례 8. 외부 함수, AI 호출이 느립니다

• 증상: AI_COMPLETE 나 외부 함수가 들어간 쿼리가 오래 걸리고, 웨어하우스를 키워도 빨라지지 않습니다.
• 원인: 시간 대부분이 모델이나 외부 API 응답을 기다리는 데 쓰입니다. 웨어하우스 크기는 이 대기를 줄이지 못합니다.
• 해결: AI_FILTER 나 일반 조건으로 대상 행을 먼저 줄이고, 작은 웨어하우스를 씁니다. 배치 작업은 업무 시간 밖에 태스크로 돌립니다. Cortex AI Functions 의 비용 부분을 참고합니다.

사례 9. 어제까지 빨랐는데 오늘 느립니다

• 증상: 코드 변경이 없는데 갑자기 느려졌습니다.
• 원인: 데이터 양 급증, 클러스터링 상태 악화(대량 적재나 업데이트 뒤), 웨어하우스 크기나 세대 변경, 통계가 다른 실행 계획 선택입니다.
• 해결: 빠르던 날과 느린 날의 쿼리 ID 로 bytes_scanned, partitions_scanned, warehouse_size 를 비교하고, Profile 두 개를 나란히 봅니다. 계획이 달라졌다면 조인 순서와 필터 위치를 확인합니다.

신고 받을 때 묻는 것

• 쿼리 ID (텍스트보다 ID 가 정확합니다)
• 언제부터 느렸는지, 원래 얼마나 걸렸는지
• 어떤 도구로 실행했는지 (BI 도구라면 도구 쪽 지연일 수도 있음)

공식 문서

• Monitor query activity with Query History
• GET_QUERY_OPERATOR_STATS
• Optimizing warehouses for performance