Tối ưu SQL trong Oracle: đọc execution plan, E-Rows vs A-Rows và SPM

- Published on
- 17 mins read
Câu query báo cáo hôm qua chạy vài giây, hôm nay chạy cả chục phút. Không ai sửa code, không ai đụng vào schema. Người thì đòi thêm index, người thì đòi gắn hint, người bảo tại bảng to quá.
Ở các hệ thống tài chính, ngân hàng hay viễn thông (Core Banking, Payment Gateway, ERP), Oracle Database thường gánh phần dữ liệu lõi nhạy cảm nhất. Chỉ một câu SQL viết ẩu lọt lên production cũng đủ kéo tụt TPS (Transactions Per Second), vắt cạn bộ nhớ PGA/SGA, hoặc gây tranh chấp khoá nội bộ (latch/mutex contention) làm đơ cả cụm Oracle RAC.
Đánh index theo cảm tính hay gắn hint ép plan bừa bãi đôi khi cũng chữa được, nhưng là chữa mò. Muốn xử lý triệt để thì phải có phương pháp: hiểu Cost-Based Optimizer (CBO) tính chi phí ra sao, storage engine đọc các khối dữ liệu (Data Blocks) thế nào, và dùng DBMS_XPLAN để bắt tận tay chỗ lệch giữa ước lượng (E-Rows) với thực tế (A-Rows).
# cost-based optimizer (cbo) chạy như thế nào?
Ứng dụng gửi một câu SQL tới Oracle thì nó chưa chạy ngay, mà phải qua cả một quy trình biên dịch:
# soft parse vs hard parse: bài toán shared pool contention
Khi một câu SQL chưa từng xuất hiện trong Library Cache, Oracle buộc phải thực hiện Hard Parse: phân tích cú pháp, kiểm tra quyền truy cập đối tượng, biến đổi truy vấn (Query Transformation), ước tính số dòng (Cardinality) cho nhiều tổ hợp Index/Join rồi mới sinh ra Execution Plan. Quá trình này rất ngốn CPU và phải xin cấp khoá độc quyền trong vùng nhớ Shared Pool (gây ra các wait event như latch: shared pool hoặc library cache: mutex X; từ 11g library cache dùng mutex thay cho latch: library cache thời trước). Ngược lại, nếu câu SQL đã từng chạy và bản kế hoạch thực thi vẫn còn nằm trong Library Cache, Oracle chỉ việc băm chuỗi SQL để đối chiếu mã hash rồi lấy luôn Plan cũ ra chạy, gọi là Soft Parse.
IMPORTANT
Nguyên tắc số 1: luôn dùng Bind Variables (:param), đừng nối chuỗi SQL Literal ('WHERE id = ' + id). Nối chuỗi thì mỗi câu truy vấn thành một chuỗi khác nhau, Oracle phải Hard Parse hàng triệu lần, Shared Pool bị lấp đầy bởi những cursor dùng đúng một lần, phân mảnh dần và có thể dính ORA-04031 (không cấp phát được bộ nhớ shared pool). Nối chuỗi còn mở cửa cho SQL injection. CURSOR_SHARING=FORCE có thể chữa cháy ở mức database, nhưng sửa ở code vẫn là cách đúng.
# công thức tính cost của cbo
CBO tính chi phí của một Execution Plan dựa trên số I/O dự kiến và số chu kỳ CPU cần dùng. Công thức Oracle công bố (từ 9i, khi có System Statistics) quy mọi thứ về đơn vị "thời gian của một lần đọc single block":
Cost = ( #SRds * sreadtim + #MRds * mreadtim + #CPUCycles / cpuspeed ) / sreadtim
#SRds, #MRds : số lần đọc single block / multiblock
sreadtim, mreadtim : thời gian trung bình một lần đọc single block / multiblock (ms)
cpuspeed : số chu kỳ CPU mỗi giây (từ system statistics)Nói cách khác, Cost là một con số tương đối để so các plan với nhau, không phải số mili-giây hay số block thật.
Nếu thống kê (Optimizer Statistics) thu thập sai hoặc quá cũ (DBMS_STATS), Cardinality (số dòng ước tính) có thể lệch cả triệu lần, kéo theo CBO chọn sai hết thuật toán Join lẫn loại Index.
# 5 cách truy cập index
B-Tree Index là cấu trúc dữ liệu được dùng nhiều nhất trong Oracle. Nhưng Oracle duyệt cây B-Tree theo cách nào thì hiệu năng I/O có thể chênh nhau cả nghìn lần:
# index unique scan
Dùng khi câu lệnh có điều kiện bằng (=) trên tất cả các cột của một Primary Key hoặc Unique Constraint. Oracle chỉ cần đi từ Root Node qua Branch Nodes xuống đúng 1 Leaf Block để lấy con trỏ RowID (Block Address + Row Slot). Đây là cách truy cập nhanh nhất, chi phí O(log N).
# index range scan
Dùng khi truy vấn trên cột không duy nhất (Non-unique Index) hoặc dùng toán tử khoảng (>, <, BETWEEN, LIKE 'ABC%'). Oracle tìm block lá đầu tiên, rồi đi ngang qua danh sách liên kết kép (doubly-linked list) giữa các block lá để gom các RowID tương ứng.
# index fast full scan vs index full scan
Hai kiểu quét toàn bộ index này nghe tên gần giống nhau nhưng cơ chế đọc đĩa bên dưới khác hẳn. Với Index Full Scan (IFS), Oracle đi từ gốc xuống nhánh trái ngoài cùng rồi đọc lần lượt từng leaf block theo đúng thứ tự sắp xếp logic của index bằng Single Block Read (db file sequential read). CBO thường chọn IFS khi câu truy vấn có mệnh đề ORDER BY khớp thứ tự cột trong index, nhờ đó lấy được dữ liệu đã sắp sẵn mà không tốn thêm bước SORT ORDER BY trong bộ nhớ PGA.
Trong khi đó, Index Fast Full Scan (IFFS) coi bản thân cây index như một bảng dữ liệu thu nhỏ (skinny table): nó quét càn qua toàn bộ các block của index trên đĩa bằng Multiblock Read (db file scattered read) và bỏ qua thứ tự liên kết giữa các block lá. Khi câu truy vấn chỉ cần tính toán gộp (COUNT(*), SUM()) và mọi cột cần lấy đều đã nằm sẵn trong index (Covering Index), IFFS chạy nhanh hơn IFS rất nhiều và còn tận dụng được cơ chế chạy song song (Parallel Query) giống hệt như Full Table Scan.
# index skip scan
Lấy ví dụ một composite index trên 2 cột (GENDER, CITIZEN_ID), và câu truy vấn chỉ tìm theo CITIZEN_ID:
SELECT full_name FROM citizens WHERE citizen_id = '0123456789';Ở nhiều hệ quản trị CSDL khác, index này coi như vô dụng vì phạm quy tắc Leftmost Prefix (MySQL có skip scan từ 8.0.13, PostgreSQL thì từ bản 18). Nhưng Oracle có Index Skip Scan: cột GENDER chỉ có 2 giá trị ('M', 'F'), nên Oracle tự ngầm tách truy vấn thành 2 nhánh con:
- Tìm trong nhánh
GENDER = 'M' AND CITIZEN_ID = '0123456789' - Tìm trong nhánh
GENDER = 'F' AND CITIZEN_ID = '0123456789'
Skip scan chỉ có lợi khi cột dẫn đầu có rất ít giá trị phân biệt. Cột dẫn đầu nhiều giá trị thì số "nhánh con" lớn, CBO sẽ bỏ qua và chọn full scan. Nếu truy vấn theo CITIZEN_ID là chính thì tạo index riêng cho nó vẫn tốt hơn trông vào skip scan.
# thuật toán JOIN & bộ nhớ PGA
Muốn xử lý được mấy câu truy vấn báo cáo tài chính phức tạp thì phải hiểu Oracle ghép dữ liệu giữa hai bảng ra sao:
# hash join spill sang temp tablespace
Với Hash Join, Oracle chọn bảng có kích thước dữ liệu sau khi lọc nhỏ hơn làm Build Input, đọc lên và băm toàn bộ khoá join vào một bảng băm nằm trong vùng nhớ PGA (Program Global Area) của session. Tiếp đó, nó quét tuần tự bảng lớn hơn (Probe Input) và dò vào bảng băm trong RAM để ghép các bản ghi khớp nhau.
Rắc rối phát sinh khi bảng băm của Build Input phình to vượt quá hạn mức work area mà session được cấp phát (do tham số PGA_AGGREGATE_TARGET điều phối). Lúc này, Oracle buộc phải chia nhỏ dữ liệu thành các partition và đẩy phần tràn xuống đĩa ở TEMP tablespace. Nếu chỉ phải ghi tạm ra đĩa rồi đọc lại đúng một lượt (gọi là one-pass), hiệu năng giảm nhẹ nhưng vẫn trong tầm kiểm soát. Đáng sợ nhất là khi vùng nhớ PGA quá chật khiến dữ liệu phải ghi xuống và đọc lên từ TEMP lặp đi lặp lại nhiều vòng (multipass): thời gian chạy truy vấn có thể vọt lên gấp hàng chục lần, kéo theo hàng loạt wait event direct path read temp và direct path write temp. Khi kiểm tra cột Used-Mem trong kết quả DBMS_XPLAN, con số nằm trong ngoặc đơn sẽ báo hiệu tình trạng này: (0) là chạy hoàn toàn trong RAM (optimal), (1) là bị tràn một lượt (one-pass), còn từ (2) trở lên là đã rơi vào multipass.
# đọc kế hoạch thực thi với dbms_xplan allstats last
Đừng tin EXPLAIN PLAN FOR thông thường, vì nó chỉ cho thấy CBO dự tính thế nào trên lý thuyết, chứ không phản ánh chuyện gì thật sự xảy ra khi chạy trên dữ liệu thật.
Cách dân Oracle lâu năm hay dùng là ALLSTATS LAST, để so số dòng ước lượng (E-Rows) với số dòng thực tế (A-Rows):
-- 1. Chạy câu lệnh SQL với hint GATHER_PLAN_STATISTICS
SELECT /*+ GATHER_PLAN_STATISTICS */
c.customer_name, COUNT(o.order_id) as total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country_code = 'VN' AND o.order_date >= DATE '2025-01-01'
GROUP BY c.customer_name;
-- 2. Truy xuất Execution Plan từ Library Cache
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST +COST +BYTES'));DISPLAY_CURSOR(null, null, ...) lấy câu SQL vừa chạy gần nhất trong cùng session, nên phải chạy ngay sau câu cần đo. Trong SQL*Plus/SQLcl nhớ SET SERVEROUTPUT OFF, nếu không câu "gần nhất" sẽ là lời gọi DBMS_OUTPUT chứ không phải câu của bạn.
# một kế hoạch thực thi mẫu & cách đọc
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem|
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 150 |00:00:01.20 | 15420 | 1200 | | | |
| 1 | HASH GROUP BY | | 1 | 120 | 150 |00:00:01.20 | 15420 | 1200 | 1024K| 1024K| 1250K (0)|
|* 2 | HASH JOIN | | 1 | 5000 | 85000 |00:00:00.85 | 15420 | 1200 | 5M | 2M | 6M (0)|
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 1 | 200 | 210 |00:00:00.05 | 450 | 0 | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 5000 | 85000 |00:00:00.65 | 14970 | 1200 | | | |
|* 5 | INDEX RANGE SCAN | IDX_ORD_DATE| 1 | 5000 | 85000 |00:00:00.12 | 320 | 50 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
3 - filter("C"."COUNTRY_CODE"='VN')
5 - access("O"."ORDER_DATE">=TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))# đọc thế nào
- Starts: số lần thao tác đó được chạy lặp lại.
- E-Rows vs A-Rows (Cardinality Misestimate): ở bước 5, CBO đoán
E-Rows = 5000, nhưng thực tếA-Rows = 85000(lệch tới 17 lần!). Đây là nguyên nhân gốc (Root Cause) làm CBO chọn sai cách join hoặc sai thứ tự bảng. - Buffers (logical reads): số khối dữ liệu (mặc định 8KB) đọc từ Buffer Cache, cộng dồn từ các bước con lên bước cha. Truy vấn chỉ trả 150 dòng mà tốn tới
15420 Buffers(~120 MB) là quá nhiều. Nhìn từng bước sẽ thấy index ở bước 5 chỉ tốn 320 buffers, còn bước 4 (TABLE ACCESS BY INDEX ROWID) ngốn gần 15.000 buffers vì phải nhảy vào bảng 85.000 lần theo RowID. Với số dòng thật như vậy, full scanORDERShoặc một index chứa sẵn(order_date, customer_id)có khi còn rẻ hơn. CBO không chọn được vì nó tưởng chỉ có 5.000 dòng.
# mấy kỹ thuật tối ưu oracle sql hay dùng
# keyset pagination thay cho rownum trên bảng trăm triệu dòng
Kiểu phân trang truyền thống bằng subquery ROWNUM hoặc OFFSET-FETCH bắt Oracle quét qua toàn bộ dữ liệu phía trước rồi mới bỏ đi:
-- ANTI-PATTERN: Đọc 1,000,050 dòng (theo index, hoặc sort nếu không có index) rồi mới bỏ 1,000,000 dòng đầu!
SELECT * FROM (
SELECT a.*, ROWNUM rnum FROM (
SELECT order_id, order_date, amount FROM orders ORDER BY order_id DESC
) a WHERE ROWNUM <= 1000050
) WHERE rnum > 1000000;
-- ARCHITECTURAL PATTERN: Keyset Seek Pagination (Độ phức tạp O(log N) bất kể trang sâu)
SELECT order_id, order_date, amount
FROM orders
WHERE order_id < :last_seen_order_id
ORDER BY order_id DESC
FETCH FIRST 50 ROWS ONLY;Keyset cần index trên cột sắp xếp, và cột đó (hoặc tổ hợp cột) phải duy nhất, không thì dòng trùng giá trị ở ranh giới trang sẽ bị lặp hoặc mất. Đổi lại, bạn không nhảy thẳng tới "trang 500" được, chỉ đi tới/lùi từ trang hiện tại. OFFSET ... FETCH có từ Oracle 12c.
# virtual columns & function-based index
Điều kiện mà bọc cột trong hàm thì Oracle không dùng được B-Tree Index thông thường:
-- B-Tree Index trên TRANSACTION_DATE bị vô hiệu hóa hoàn toàn
SELECT * FROM transactions WHERE TRUNC(transaction_date) = DATE '2025-05-01';
-- GIẢI PHÁP: Tạo Function-Based Index khớp chính xác biểu thức
CREATE INDEX idx_trx_date_trunc ON transactions(TRUNC(transaction_date));
-- HOẶC DÙNG VIRTUAL COLUMN (Chuẩn thiết kế hiện đại)
ALTER TABLE transactions ADD (trx_day AS (TRUNC(transaction_date)));
CREATE INDEX idx_trx_day ON transactions(trx_day);
-- HOẶC ĐƠN GIẢN NHẤT: viết lại điều kiện thành khoảng, dùng luôn index sẵn có trên TRANSACTION_DATE
SELECT * FROM transactions
WHERE transaction_date >= DATE '2025-05-01'
AND transaction_date < DATE '2025-05-02';# bẫy ngầm implicit type conversion
Nếu kiểu của Bind Variable không khớp kiểu cột trong CSDL, Oracle sẽ áp hàm chuyển đổi lên chính cột đó, và index coi như bỏ:
-- Giả sử CITIZEN_ID là VARCHAR2(20), nhưng lập trình viên truyền vào số Long (NUMBER)
SELECT * FROM citizens WHERE citizen_id = 123456789;
-- Oracle ngầm biến đổi câu lệnh thành:
-- SELECT * FROM citizens WHERE TO_NUMBER(citizen_id) = 123456789;
-- -> TOÀN BỘ INDEX TRÊN CITIZEN_ID BỊ VÔ HIỆU HÓA -> FULL TABLE SCAN HÀNG CHỤC TRIỆU DÒNG!WARNING
Cẩn thận với cột chuỗi chứa số: Luôn bảo đảm tham số truyền từ Java (ví dụ @Param trong Spring Data JPA) đúng kiểu với schema của bảng. Một biến String bị truyền nhầm thành Long là đủ biến một truy vấn vài mili-giây thành Full Table Scan cả chục giây!
Một biến thể hay gặp nữa: cột kiểu DATE nhưng Java bind java.sql.Timestamp (kiểu TIMESTAMP), Oracle phải nâng cột lên INTERNAL_FUNCTION("TRANSACTION_DATE") để so sánh và index cũng mất tác dụng. Thấy INTERNAL_FUNCTION bọc cột trong phần Predicate Information là nên nghi ngay.
# partition pruning trong dữ liệu lớn
Bảng lịch sử giao dịch phình ra theo năm tháng thì chia Partition (Range theo ngày/tháng) là bắt buộc. Nhớ kiểm tra câu truy vấn có ăn Partition Pruning hay không (xem cột Pstart và Pstop trong Execution Plan):
CREATE TABLE transaction_ledger (
trans_id NUMBER,
trans_date DATE,
amount NUMBER
)
PARTITION BY RANGE (trans_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION p_init VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);Khi câu lệnh lọc trans_date BETWEEN :start_d AND :end_d, Oracle chỉ đọc đúng những partition nằm trong khoảng đó, bỏ qua phần lớn khối dữ liệu còn lại trên đĩa. Vì dùng bind variable nên lúc parse Oracle chưa biết partition nào, cột Pstart/Pstop sẽ hiện KEY (pruning lúc chạy), đó vẫn là pruning. Thứ cần tránh là 1/1048575 (quét mọi partition), thường do bọc cột partition trong hàm hoặc lệch kiểu như ở mục trên.
# sql plan management (spm)
Ở ngân hàng, mỗi lần DBA thu thập lại thống kê hay nâng version database, CBO có thể bất ngờ đổi Execution Plan của một câu truy vấn quan trọng, câu 5ms thành 30 giây (Plan Regression).
Muốn chặn chuyện này thì dùng SQL Plan Baselines:
-- Cố định Plan hiện tại vào Baseline đã được kiểm định (Verified)
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '9a8b7c6d5e4f1',
plan_hash_value => 123456789,
fixed => 'YES',
enabled => 'YES'
);
END;
/Baseline nào được đánh dấu fixed = 'YES' thì CBO ưu tiên tuyệt đối kế hoạch đó, không tự đổi sang kế hoạch khác kể cả khi thống kê của bảng thay đổi, và plan mới tìm được cũng không được tự động đưa vào baseline. Có một ngoại lệ: nếu plan cố định không còn tái tạo được (ví dụ index nó dùng đã bị drop), Oracle đành bỏ qua baseline và tự tính plan mới. Nên sau mỗi lần đổi schema, đáng kiểm tra lại DBA_SQL_PLAN_BASELINES.
# tổng kết
Lần tới gặp câu SQL chậm, thử làm đúng một việc trước khi bàn tới index hay hint: chạy lại nó với hint GATHER_PLAN_STATISTICS, gọi DBMS_XPLAN với ALLSTATS LAST, rồi tìm dòng có E-Rows và A-Rows lệch nhau nhiều nhất. Phần lớn câu trả lời nằm quanh dòng đó.
Ngoài ra có ba thói quen đáng giữ khi vận hành:
- Dùng Bind Variables 100% cho các truy vấn OLTP để tránh vắt kiệt Shared Pool vì Hard Parse.
- Chọn đúng kiểu Index Access Path (tận dụng IFFS, Covering Index, Partition Pruning) để giảm tối đa số
Buffersphải đọc. - Khoá kế hoạch thực thi bằng SQL Plan Management (SPM) cho những giao dịch lõi không được phép biến động độ trễ.
Chỉ là những ghi chép cá nhân với hy vọng mang lại chút giá trị. Nếu thấy hữu ích, đừng ngại chia sẻ cho bạn bè & đồng nghiệp nhé!
Happy coding 😎 👍🏻 🚀 🔥.
On this page
- # cost-based optimizer (cbo) chạy như thế nào?
- # soft parse vs hard parse: bài toán shared pool contention
- # công thức tính cost của cbo
- # 5 cách truy cập index
- # index unique scan
- # index range scan
- # index fast full scan vs index full scan
- # index skip scan
- # thuật toán JOIN & bộ nhớ PGA
- # hash join spill sang temp tablespace
- # đọc kế hoạch thực thi với dbms_xplan allstats last
- # một kế hoạch thực thi mẫu & cách đọc
- # đọc thế nào
- # mấy kỹ thuật tối ưu oracle sql hay dùng
- # keyset pagination thay cho rownum trên bảng trăm triệu dòng
- # virtual columns & function-based index
- # bẫy ngầm implicit type conversion
- # partition pruning trong dữ liệu lớn
- # sql plan management (spm)
- # tổng kết