tối ưu SQL trong Oracle

Ở mảng tài chính, ngân hàng, viễn thông (Core Banking, Payment Gateways, ERP), Oracle Database gần như luôn là nơi giữ dữ liệu quan trọng nhất. Một câu SQL viết ẩu có thể kéo tụt TPS (Transactions Per Second) của cả hệ thống, vắt cạn PGA/SGA, hoặc gây nghẽn latch (latch contention) làm đứng cả cụm Oracle RAC.

Nhiều dev tối ưu SQL theo cảm tính: "thấy chậm thì gắn thêm index", "thêm hint bừa bãi", hoặc "đổ lỗi cho dữ liệu phình to".

Ở vai Database Architect hay Principal Engineer, tối ưu SQL phải làm 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à biết cách phát hiện chỗ lệch giữa ước lượng (Estimates) với thực tế (Actuals).

# 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

• Hard Parse: Oracle phải phân tích cú pháp, kiểm tra quyền, biến đổi truy vấn (Query Transformation), tính Cardinality cho mọi tổ hợp Index/Join rồi sinh mã thực thi. Việc này ngốn CPU và phải tranh nhau các cơ chế khoá trong Shared Pool (latch: shared pool; library cache từ 11g dùng mutex nên wait event là library cache: mutex X thay cho latch: library cache thời trước).
• Soft Parse: câu SQL đã từng chạy và còn trong Library Cache thì Oracle chỉ việc khớp lại mã hash và dùng lại Plan cũ.

[!CRITICAL] 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

• Index Full Scan (IFS): Oracle đọc hết các leaf block theo đúng thứ tự logic của index, dùng Single Block Read (db file sequential read). CBO hay chọn cách này khi truy vấn có ORDER BY trùng thứ tự cột trong index, để khỏi phải chạy bước SORT ORDER BY tốn kém trong PGA.
• Index Fast Full Scan (IFFS): Oracle coi index như một bảng thu nhỏ, đọc toàn bộ block bằng Multiblock Read (db file scattered read) và không quan tâm thứ tự sắp xếp. Khi chỉ cần tính aggregate (COUNT(*), SUM()) và mọi cột cần dùng đều nằm trong Index, IFFS thường nhanh hơn IFS nhiều, và cũng chạy song song (parallel query) được 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:

1. Tìm trong nhánh GENDER = 'M' AND CITIZEN_ID = '0123456789'
2. 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 lấy bảng nhỏ hơn làm Build Input, băm hết khoá join vào một bảng băm trong bộ nhớ PGA (Program Global Area). Sau đó quét bảng lớn (Probe Input) để tìm các bản ghi khớp.

• Nếu Hash Table to hơn work area mà session được cấp (do PGA_AGGREGATE_TARGET điều phối), Oracle buộc phải chia dữ liệu thành các partition và đẩy phần không vừa xuống TEMP tablespace. Ghi ra đĩa rồi đọc lại một lượt gọi là one-pass, vẫn chấp nhận được; work area quá nhỏ đến mức phải đọc đi đọc lại nhiều lượt là multipass, đây mới là thứ đáng sợ.
• Dính multipass thì thời gian chạy truy vấn có thể tăng hàng chục lần, kèm theo các wait event direct path read temp và direct path write temp. Cột Used-Mem trong DBMS_XPLAN ghi số lượt ở trong ngoặc: (0) là optimal (chạy hoàn toàn trong bộ nhớ), (1) là one-pass, lớn hơn là 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

1. Starts: số lần thao tác đó được chạy lặp lại.
2. 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.
3. 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 scan ORDERS hoặ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!

# 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

Tối ưu Oracle SQL không phải chuyện sửa vài chỗ cú pháp cho đẹp. Nó là việc nắm được cách phần tính toán của Cost-Based Optimizer làm việc với tầng lưu trữ vật lý bên dưới.

Mấy nguyên tắc mình luôn giữ:

1. Kiểm soát Parsing: 100% câu truy vấn OLTP phải dùng Bind Variables.
2. Soát thống kê: thường xuyên so E-Rows với A-Rows qua DBMS_XPLAN.
3. Hiểu rõ Index Access Paths: không tạo index thừa; tận dụng IFFS, Composite Index và Partition Pruning để đọc càng ít Buffers càng tốt.
4. Giữ hệ thống ổn định: dùng SQL Plan Management (SPM) để khoá kế hoạch thực thi cho các giao dịch tài chính quan trọng.

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 😎 👍🏻 🚀 🔥.