쿼리 튜닝 (Query Profile, 프루닝, 유출, 가속 기능)

순서: 재고, 보고, 고친다

1. 잰다: QUERY_HISTORY 에서 느린 쿼리와 반복 빈도를 봅니다. 하루 한 번 도는 3분짜리보다 1분마다 도는 10초짜리가 더 비쌀 수 있습니다.
2. 본다: Query Profile 에서 시간이 가장 많이 든 연산자를 찾습니다.
3. 고친다: 쿼리 작성, 데이터 배치(클러스터링), 웨어하우스, 가속 기능 순서로 검토합니다. 돈이 드는 기능은 마지막입니다.

-- 최근 하루, 총 실행 시간이 큰 쿼리 패턴
SELECT query_parameterized_hash, ANY_VALUE(query_text) AS sample,
       COUNT(*) AS runs, SUM(total_elapsed_time)/1000 AS total_sec,
       AVG(partitions_scanned / NULLIF(partitions_total, 0)) AS scan_ratio
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
GROUP BY 1 ORDER BY total_sec DESC LIMIT 20;

Query Profile 읽기

Snowsight 의 쿼리 기록에서 쿼리를 열면 연산자 트리가 나옵니다. 오른쪽 통계에서 다음을 봅니다.

SQL 로도 같은 정보를 볼 수 있습니다.

SELECT operator_id, operator_type,
       operator_statistics:output_rows AS out_rows,
       execution_time_breakdown:overall_percentage AS pct,
       operator_statistics:spilling AS spill
FROM TABLE(GET_QUERY_OPERATOR_STATS('01b2c3d4-0000-...'))
ORDER BY pct DESC;

프루닝 살리기

Snowflake 는 마이크로 파티션마다 열의 최솟값, 최댓값을 알고 있어 조건에 맞지 않는 파티션을 건너뜁니다. 원리는 마이크로 파티션 문서에 있습니다.

• 열에 함수를 씌우면 대체로 최솟값, 최댓값 비교를 못 합니다.
• 테이블이 크고 자주 거르는 열이 데이터 적재 순서와 무관하다면 클러스터링 키를 검토합니다. 자동 클러스터링은 서버리스 크레딧이 드니 SYSTEM$CLUSTERING_INFORMATION 으로 효과를 확인하고 켭니다.

유출과 메모리

• 로컬 유출(local spill) 은 노드의 로컬 디스크로, 원격 유출(remote spill) 은 원격 저장소로 데이터가 넘친 것입니다. 원격 유출은 크게 느립니다.
• 처방: 처리할 행과 열을 줄이고(필요한 열만 SELECT, 조건 먼저), 그래도 남으면 웨어하우스를 한 단계 키웁니다. 큰 정렬과 COUNT(DISTINCT) 는 근사 함수(APPROX_COUNT_DISTINCT)를 검토합니다.

조인 폭발

조인 출력 행이 입력보다 훨씬 많다면 조인 키가 고유하지 않은 것입니다.

-- 차원 테이블의 키가 정말 고유한가
SELECT customer_id, COUNT(*) FROM dim_customer GROUP BY 1 HAVING COUNT(*) > 1 LIMIT 10;

• 이력 테이블과 조인할 때는 유효 기간 조건을 함께 겁니다.
• UNION 은 중복 제거를 위해 정렬하므로, 중복이 없음을 안다면 UNION ALL 을 씁니다.
• QUALIFY ROW_NUMBER() OVER (...) = 1 로 먼저 중복을 줄인 뒤 조인합니다.

가속 기능 고르기

-- QAS 후보 찾기
SELECT query_id, eligible_query_acceleration_time
FROM snowflake.account_usage.query_acceleration_eligible
ORDER BY eligible_query_acceleration_time DESC LIMIT 10;

ALTER WAREHOUSE adhoc_wh SET ENABLE_QUERY_ACCELERATION = TRUE
  QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;

-- Search Optimization: 필요한 열과 방식만
ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id), SUBSTRING(url);
SELECT SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('events', 'EQUALITY(user_id)');

팁 Search Optimization 을 테이블 전체(ADD SEARCH OPTIMIZATION;)에 거는 것은 피합니다. 모든 열에 인덱스 성격의 구조를 만들어 저장과 유지 비용이 크게 늘어납니다. 실제로 거르는 열과 방식만 지정하세요.

작성 습관

• SELECT * 대신 필요한 열만. 열 지향 저장이라 읽는 열 수가 그대로 I/O 입니다.
• 큰 IN (서브쿼리) 보다 EXISTS 나 세미 조인이 나은 경우가 많습니다. Profile 로 비교합니다.
• 같은 쿼리를 반복하면 결과 캐시 가 24시간 안에서 재사용합니다. 쿼리에 CURRENT_TIMESTAMP() 같은 비결정 함수를 넣으면 캐시를 못 씁니다.
• 컴파일 시간이 긴 쿼리(수천 줄 SQL, 깊은 뷰 중첩)는 중간 결과를 테이블이나 Dynamic Table 로 끊습니다.

트러블슈팅

• 증상: 같은 쿼리가 어떤 날은 5초, 어떤 날은 2분 걸립니다.

• 원인: 웨어하우스 대기(queued), 캐시 차이(웨어하우스가 막 켜져 로컬 캐시가 비었음), 또는 그날 데이터 양 차이입니다.

• 해결: QUERY_HISTORY 의 queued_overload_time, bytes_scanned, percentage_scanned_from_cache 를 두 실행에서 비교합니다. 대기가 원인이면 멀티 클러스터, 캐시가 원인이면 AUTO_SUSPEND 조정을 검토합니다.

• 증상: 클러스터링 키를 걸었는데 스캔 파티션 수가 그대로입니다.

• 원인: 쿼리 조건이 클러스터링 키 열에 함수를 씌우거나, 클러스터링 키의 카디널리티가 너무 높아 정렬 효과가 없습니다.

• 해결: 조건을 범위 비교로 고치고, 키는 DATE_TRUNC('day', ts) 처럼 카디널리티를 낮춘 식으로 다시 정합니다.

공식 문서

• Analyzing queries using Query Profile
• Search optimization service
• Query Acceleration Service
• Clustering keys and clustered tables