BigQuery 12 — Query plan & Execution Details

Vì sao phải đọc được query plan

Bạn viết một câu SQL, bấm Run, kết quả trả về. Nhưng giữa hai khoảnh khắc đó BigQuery đã làm rất nhiều việc: biên dịch SQL thành một kế hoạch thực thi (query plan) rồi chạy nó trên hàng trăm/nghìn worker. Khi query chậm hoặc tốn tài nguyên bất thường, bạn không sửa được nếu không nhìn thấy kế hoạch đó.

Ở bài slots & mô hình thực thi ta đã dựng mô hình tinh thần "slot + stage + shuffle". Bài này là bước tiếp theo: mở tab Execution details ra và đọc từng dòng stage. Nắm được nó rồi, ta mới đi tiếp tới đọc execution graph trực quan và phân tích thông số từng pha stage.

Mục tiêu bài này rất cụ thể:

1. Query plan là gì và SQL biến thành cây STAGE ra sao.
2. Mở tab Execution details (dạng bảng) trong console ở đâu, mỗi dòng nghĩa là gì.
3. Lấy cùng dữ liệu đó qua INFORMATION_SCHEMA khi cần tự động hoá.
4. Nhận diện các loại stage điển hình (Input, Repartition, Join, Aggregate, Sort, Output) và các step kind bên trong.

Query plan là gì

Query plan là bản mô tả BigQuery sẽ thực thi truy vấn của bạn như thế nào — được sinh ra bởi bộ tối ưu (query optimizer) trước khi chạy. Nó không phải câu SQL của bạn viết lại; nó là một cây các STAGE (execution tree / DAG), trong đó:

• Mỗi stage là một nhóm thao tác chạy được song song trên nhiều worker.
• Các stage phụ thuộc nhau: một stage chỉ chạy khi các stage đầu vào của nó đã sinh đủ dữ liệu.
• Dữ liệu chảy giữa các stage qua shuffle (lớp bộ nhớ/mạng phân tán), không ghi ra bảng trung gian trên đĩa.

Cây này đọc từ lá lên gốc: các stage lá đọc dữ liệu thô từ lưu trữ cột Capacitor/Colossus, các stage giữa tổng hợp dần, stage gốc ghi kết quả cuối trả về client.

Điểm mấu chốt: plan chỉ có ý nghĩa đầy đủ sau khi job chạy xong, vì lúc đó mỗi stage mới có số liệu thật (records, thời gian, slot time). Đó chính là thứ tab Execution details trình bày.

Từ SQL đến cây stage: một ví dụ ngân hàng

Xét truy vấn: với mỗi khách hàng VIP, đếm số giao dịch và tổng tiền tháng 6, sắp theo tổng tiền giảm dần.

SELECT
  c.customer_id,
  c.segment,
  COUNT(*)        AS so_giao_dich,
  SUM(t.amount)   AS tong_tien
FROM `ncb-dwh.core.transactions` AS t
JOIN `ncb-dwh.core.customers`    AS c
  ON c.customer_id = t.customer_id
WHERE t.txn_date BETWEEN '2026-06-01' AND '2026-06-30'
  AND c.segment = 'VIP'
GROUP BY c.customer_id, c.segment
ORDER BY tong_tien DESC
LIMIT 100;

BigQuery không chạy khối này liền mạch. Nó dựng đại khái cây stage sau (tên S00, S01, … là thứ tự stage trong plan):

Đọc sơ đồ:

• S00 và S01 là hai Input stage độc lập — mỗi cái đọc một bảng, lọc sớm, rồi ghi ra shuffle chia theo khoá join (customer_id). Chúng chạy song song vì không phụ thuộc nhau.
• S02 có inputStages = [S00, S01]: nó đọc lại hai luồng shuffle, JOIN theo customer_id, rồi làm AGGREGATE partial trên từng mảnh cục bộ.
• S03 có inputStages = [S02]: gom các partial cùng khoá thành kết quả final, SORT, LIMIT rồi ghi ra.

Đây là hình dạng "hai aggregate → nối bằng shuffle" rất kinh điển đã nhắc ở bài slots: việc gom nhóm/join hầu như luôn bị tách thành partial (trước shuffle) và final (sau shuffle) để chạy song song rồi mới hợp nhất.

Mở tab Execution details ở đâu

Trong BigQuery console (Google Cloud), sau khi một query chạy xong:

1. Vào BigQuery Studio → chạy query (hoặc mở Job history / Personal history).
2. Bấm vào job để mở Job details.
3. Trong Job details có các tab; chọn:
   • Execution details — trình bày plan dạng bảng, mỗi stage một dòng (đây là trọng tâm bài này).
   • Execution graph — trình bày cùng plan dạng đồ thị trực quan (bàn ở bq-13).

Ngoài console, bạn còn hai cách khác lấy chính dữ liệu plan đó:

• Trường queryPlan trong response của Jobs API (jobs.get).
• Cột/mảng job_stages trong INFORMATION_SCHEMA.JOBS — tiện cho phân tích hàng loạt bằng SQL (xem cuối bài).

Đọc panel Execution details dạng bảng

Mỗi dòng là một stage; các cột chính cho biết stage đó đọc/ghi bao nhiêu bản ghi, thời gian tương đối trải qua các pha, và tiêu tốn bao nhiêu slot time. Dưới đây là mô phỏng có chú thích của panel (số liệu minh hoạ cho query VIP ở trên):

EXECUTION DETAILS ─ Job: bquxjob_ab12_...  (elapsed 4.7s · slot time 38.9s · bytes shuffled 1.2 GB)
┌───────┬──────────────────────┬───────────┬───────────┬──────────────────────────────┬───────────┐
│ Stage │ Loại (name)          │ Records   │ Records   │ Timing tương đối các pha      │ Slot time │
│       │                      │  read     │  written  │ wait│read│compute│write     │  (ms)     │
├───────┼──────────────────────┼───────────┼───────────┼──────────────────────────────┼───────────┤
│ S00   │ Input                │ 42,000,000│  5,120,000│  ▓░│▓▓▓▓│▓▓░░░░░│▓░         │   14,800  │
│ S01   │ Input                │  1,800,000│     96,000│  ░░│▓▓░░│▓░░░░░░│░░         │    2,100  │
│ S02   │ Join+                │  5,216,000│    340,000│  ▓▓│▓▓░░│▓▓▓▓▓░░│▓░         │   18,300  │
│ S03   │ Aggregate+ / Sort    │    340,000│        100│  ▓░│▓░░░│▓▓░░░░░│░░         │    3,700  │
└───────┴──────────────────────┴───────────┴───────────┴──────────────────────────────┴───────────┘
                    ▲            ▲           ▲            ▲                              ▲
                    │            │           │            │                              └ slotMs: compute đã tiêu (slot × ms)
                    │            │           │            └ 4 pha/worker: wait|read|compute|write, vẽ TƯƠNG ĐỐI (avg vs max)
                    │            │           └ recordsWritten: số dòng ghi ra shuffle/kết quả
                    │            └ recordsRead: số dòng stage này đọc vào (từ storage hoặc shuffle)
                    └ Stage id theo thứ tự plan: S00 chạy sớm, id lớn hơn phụ thuộc id nhỏ hơn

Cách đọc nhanh từng dòng:

• Stage id (S00, S01, …): chỉ là nhãn thứ tự trong plan, không phải thứ tự thời gian tuyệt đối. Stage id lớn thường phụ thuộc stage id nhỏ (inputStages), nhưng nhiều stage độc lập (như S00, S01) chạy song song.
• Loại (name): BigQuery tự đặt tên phản ánh việc stage làm — Input, Repartition, Join+, Aggregate+, Sort+, Output… Dấu + ám chỉ stage gộp nhiều bước.
• Records read / written: chính là recordsRead / recordsWritten. So sánh chúng cho biết stage bung nở (join sinh nhiều dòng) hay co lại (filter/aggregate cắt bớt). S00 đọc 42M nhưng chỉ ghi 5.1M → filter tháng 6 loại phần lớn dữ liệu.
• Timing tương đối các pha: mỗi worker của stage trải qua 4 pha wait → read → compute → write. Panel vẽ chúng theo tỷ lệ tương đối (thanh màu), gồm cả avg và max theo worker. Pha nào dài bất thường là manh mối: read dài = quét/shuffle nặng; compute dài = join/sort/hàm nặng; wait dài = đói slot. Chi tiết field waitMsAvg/Max, readMsAvg/Max, computeMsAvg/Max, writeMsAvg/Max để dành cho bq-14.
• Slot time (slotMs): tổng compute stage tiêu (slot × mili-giây). S02 (join + partial aggregate) tốn nhất — đúng như kỳ vọng.

Mẹo đọc skew: nếu thanh max của một pha dài hơn avg rất nhiều, một số worker phải làm nặng hơn hẳn phần còn lại → data skew (khoá lệch). Đây là một trong những thứ bq-15 chẩn đoán & tinh chỉnh xử lý.

Các loại stage điển hình

BigQuery đặt tên stage theo vai trò. Bảng các loại thường gặp:

Một stage thực tế thường gộp nhiều vai trò (ví dụ Join+ = join rồi partial aggregate ngay), nên tên có dấu +.

Step kind bên trong mỗi stage

Bên trong một stage là chuỗi step, mỗi step có một kind. Đây là các kind thật hay gặp:

Nhìn chuỗi step giúp bạn biết stage đang làm gì: một stage READ → FILTER → AGGREGATE → WRITE chính là một Input stage lọc rồi partial-aggregate; một stage READ → JOIN → WRITE là join thuần.

Lấy plan bằng INFORMATION_SCHEMA (tự động hoá)

Khi cần phân tích nhiều job (không click từng cái trong console), truy vào mảng job_stages của INFORMATION_SCHEMA.JOBS. Query dưới đây chạy được, "trải" (UNNEST) từng stage của các job nặng gần đây thành từng dòng — đúng như panel Execution details nhưng bằng SQL:

SELECT
  j.job_id,
  s.id                         AS stage_id,       -- S00, S01... (dạng số)
  s.name                       AS stage_name,     -- Input / Join+ / Aggregate+ ...
  s.records_read,
  s.records_written,
  s.parallel_inputs,                              -- số mảnh song song
  s.shuffle_output_bytes,
  s.shuffle_output_bytes_spilled,                 -- >0 = shuffle tràn đĩa, cần chú ý
  s.wait_ms_avg,   s.wait_ms_max,                 -- pha chờ slot (avg vs max = skew?)
  s.read_ms_avg,   s.read_ms_max,
  s.compute_ms_avg, s.compute_ms_max,
  s.write_ms_avg,  s.write_ms_max,
  s.slot_ms,                                      -- compute stage này tiêu
  s.input_stages                                  -- MẢNG id các stage phụ thuộc
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT AS j,
  UNNEST(j.job_stages) AS s
WHERE j.creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND j.job_type = 'QUERY'
  AND j.state    = 'DONE'
  AND j.total_slot_ms > 60000        -- chỉ soi job tốn compute
ORDER BY j.total_slot_ms DESC, s.id ASC;

Vài cột đắt giá khi đọc:

• input_stages — mảng id các stage đầu vào; đây là thứ dựng lại DAG phụ thuộc (nền cho đọc execution graph).
• parallel_inputs / completed_parallel_inputs — mức song song stage đạt được.
• shuffle_output_bytes_spilled > 0 — shuffle không đủ RAM phải tràn đĩa, dấu hiệu cần tối ưu.
• Cặp *_ms_avg vs *_ms_max — chênh lớn = skew.

Use case thực tế

Đội Data NCB có một report rủi ro tín dụng chạy mỗi sáng, gần đây chậm gấp 3 (từ ~5s lên ~16s) dù dữ liệu không tăng nhiều. Không ai biết vì sao cho tới khi mở Execution details của job.

Bảng stage chỉ ra ngay thủ phạm: stage Join+ (nối transactions với customers) có records_read phình lên bất thường và thanh compute max dài gấp ~8 lần avg — tức một số ít worker gánh phần lớn công việc. Soi thêm shuffle_output_bytes_spilled > 0: shuffle của stage đó tràn đĩa.

Nguyên nhân: một nhóm nhỏ customer_id "rác" (giá trị mặc định 0 do lỗi nhập liệu ở nguồn) chiếm hàng chục triệu giao dịch, dồn hết về một khoá join → data skew kinh điển. Sau khi lọc bỏ customer_id = 0 ở tầng nguồn và thêm điều kiện WHERE, stage Join+ giảm records_read ~40%, thanh compute max/avg về gần nhau, spilled về 0, và job trở lại ~5s. Toàn bộ chẩn đoán bắt đầu từ việc đọc được panel Execution details — không cần thử-sai mù. Quy trình chẩn đoán đầy đủ nằm ở bq-15.

Ghi nhớ

• Query plan = cây STAGE (DAG) do optimizer sinh ra; đọc từ lá lên gốc (Input → Join/Aggregate → Output).
• Mỗi stage phụ thuộc stage khác qua inputStages; id S00, S01, … là thứ tự plan, không phải thứ tự thời gian — stage độc lập chạy song song.
• Execution details (Job details → Execution details) trình bày plan dạng bảng: mỗi dòng một stage với records_read/written, timing tương đối 4 pha, và slot_ms.
• Bốn pha mỗi worker: wait → read → compute → write; chênh avg vs max lớn = data skew.
• Loại stage điển hình: Input, Repartition, Join, Aggregate, Sort, Output; step kind: READ, FILTER, COMPUTE, AGGREGATE, JOIN, SORT, WRITE…
• So records_read với records_written để biết stage nở (join) hay co (filter/aggregate); shuffle_output_bytes_spilled > 0 = cần chú ý.
• Cùng dữ liệu lấy được qua INFORMATION_SCHEMA.JOBS → job_stages để phân tích hàng loạt bằng SQL.
• Đây là nền cho đọc execution graph và soi thông số từng pha.

Nguồn tham khảo

• BigQuery — Query plan and timeline (execution details, stages, phases, slot time): cloud.google.com/bigquery/docs/query-plan-explanation
• BigQuery — INFORMATION_SCHEMA JOBS (job_stages, total_slot_ms, records_read/written): cloud.google.com/bigquery/docs/information-schema-jobs
• BigQuery — Best practices for performance overview: cloud.google.com/bigquery/docs/best-practices-performance-overview
• BigQuery documentation (tổng quan): cloud.google.com/bigquery/docs
• "Dremel: Interactive Analysis of Web-Scale Datasets", Melnik et al., VLDB 2010
• "Google BigQuery: The Definitive Guide", Lakshmanan & Tigani, O'Reilly