tối ưu SQL trong Oracle

Trong các hệ sinh thái tài chính, ngân hàng và viễn thông (Core Banking, Payment Gateways, ERP), Oracle Database luôn là trái tim lưu trữ dữ liệu trọng yếu. Một câu truy vấn SQL viết thiếu kiểm soát có thể kéo sụt chỉ số TPS (Transactions Per Second) của toàn hệ thống, làm cạn kiệt PGA/SGA, hoặc gây ra hiện tượng nghẽn chốt (latch contention) làm tê liệt cụm Oracle RAC.

Nhiều lập trình viên tối ưu hóa SQL theo bản năng: "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".

Với tư cách là một Database Architect hoặc Principal Engineer, tối ưu hóa SQL là một môn khoa học chính xác: bạn phải hiểu cách trình tối ưu hóa Cost-Based Optimizer (CBO) tính toán chi phí, cách động cơ lưu trữ đọc các khối dữ liệu (Data Blocks), và cách phát hiện sự chênh lệch giữa kỳ vọng (Estimates) và thực tế (Actuals).

# cost-based optimizer (cbo) hoạt động như thế nào?

Khi ứng dụng gửi một câu lệnh SQL đến Oracle, câu lệnh không được thực thi ngay mà phải trải qua quy trình biên dịch nghiêm ngặt:

# 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, chuyển đổi truy vấn (Query Transformation), tính toán Cardinality cho mọi tổ hợp Index/Join, và sinh mã thực thi. Quá trình này tiêu tốn nhiều CPU và cần chiếm giữ các chốt độc quyền trong bộ nhớ (latch: shared pool, latch: library cache).
• Soft Parse: Nếu câu lệnh SQL đã từng chạy và có sẵn trong Library Cache, Oracle chỉ cần ánh xạ lại mã hash và tái sử dụng Plan cũ.

[!CRITICAL] Quy tắc vàng số 1: Luôn sử dụng Bind Variables (:param) thay vì nối chuỗi SQL Literal ('WHERE id = ' + id). Nối chuỗi sẽ biến mỗi câu truy vấn thành một chuỗi ký tự duy nhất, ép Oracle phải Hard Parse hàng triệu lần, gây sập Shared Pool vì phân mảnh bộ nhớ.

# công thức tính cost của cbo

CBO lượng hóa chi phí của một Execution Plan dựa trên số lượng I/O dự kiến và chu kỳ CPU cần thiết:

Cost = IO_Cost + (CPU_Cost / CPUSPEED) * Factor

Nếu bảng thống kê (Optimizer Statistics) thu thập sai hoặc quá cũ (DBMS_STATS), giá trị Cardinality (số dòng ước tính) sẽ sai lệch hàng triệu lần, dẫn đến việc CBO chọn sai toàn bộ thuật toán Join và loại Index.

# giải mã 5 phương thức truy cập index

B-Tree Index là cấu trúc dữ liệu chủ lực trong Oracle. Tuy nhiên, cách Oracle duyệt qua cây B-Tree có thể tạo ra sự chênh lệch hàng nghìn lần về hiệu năng I/O:

# index unique scan

Xảy ra khi câu lệnh có điều kiện đẳng thức (=) trên toàn bộ 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 ra con trỏ RowID (Block Address + Row Slot). Đây là phương thức truy cập nhanh nhất với chi phí O(log N).

# index range scan

Xảy ra khi truy vấn trên một cột không duy nhất (Non-unique Index) hoặc sử dụng các toán tử dải (>, <, BETWEEN, LIKE 'ABC%'). Oracle định vị block lá đầu tiên, sau đó duyệt ngang qua con trỏ liên kết kép (doubly-linked list) giữa các block lá để lấy danh sách RowID tương ứng.

# index fast full scan vs index full scan

• Index Full Scan (IFS): Oracle đọc toàn bộ các leaf block theo đúng thứ tự logic của index. Cơ chế này dùng Single Block Read (db file sequential read). Thường được CBO kích hoạt khi truy vấn có mệnh đề ORDER BY trùng với thứ tự cột trong index để tránh 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ỏ và đọc toàn bộ block bằng cơ chế Multiblock Read (db file scattered read), hoàn toàn bỏ qua thứ tự sắp xếp. IFFS nhanh hơn IFS gấp nhiều lần khi chỉ cần tính toán aggregate (COUNT(*), SUM()) mà tất cả các cột đều nằm trọn trong Index.

# index skip scan

Xét một composite index trên 2 cột (GENDER, CITIZEN_ID). Giả sử câu truy vấn chỉ tìm kiếm theo CITIZEN_ID:

SELECT full_name FROM citizens WHERE citizen_id = '0123456789';

Trong các hệ quản trị CSDL thông thường, index này sẽ bị vô hiệu hóa vì vi phạm quy tắc Leftmost Prefix. Tuy nhiên, Oracle có cơ chế Index Skip Scan: vì cột GENDER chỉ có 2 giá trị phân biệt ('M', 'F'), Oracle sẽ tự động chia nhỏ truy vấn thành 2 nhánh con ngầm định:

1. Tìm kiếm trong nhánh GENDER = 'M' AND CITIZEN_ID = '0123456789'
2. Tìm kiếm trong nhánh GENDER = 'F' AND CITIZEN_ID = '0123456789'

# thuật toán JOIN & bộ nhớ PGA

Hiểu rõ cách Oracle kết hợp dữ liệu giữa hai bảng là chìa khóa để xử lý các truy vấn báo cáo tài chính phức tạp:

# hash join spill sang temp tablespace

Trong một Hash Join, Oracle chọn bảng nhỏ hơn làm Build Input, băm toàn bộ khóa join vào một bảng băm trong bộ nhớ PGA (Private Global Area). Sau đó quét bảng lớn (Probe Input) để tìm các bản ghi trùng khớp.

• Nếu kích thước Hash Table vượt quá giới hạn cấp phát của session trong PGA_AGGREGATE_TARGET, Oracle buộc phải phân vùng các hash bucket và đẩy xuống đĩa TEMP tablespace (Multipass Hash Spill).
• Khi điều này xảy ra, thời gian thực thi truy vấn sẽ tăng vọt từ vài trăm mili-giây lên hàng chục phút, kéo theo các sự kiện chờ direct path read temp và direct path write temp.

# đọc kế hoạch thực thi với dbms_xplan allstats last

Đừng bao giờ tin tưởng vào EXPLAIN PLAN FOR thông thường, vì nó chỉ hiển thị kỳ vọng lý thuyết của CBO mà không phản ánh những gì thực sự diễn ra khi chạy qua dữ liệu thật.

Phương pháp chuẩn mực của các chuyên gia Oracle là sử dụng ALLSTATS LAST để so sánh 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'));

# mẫu kế hoạch thực thi & cách đọc chỉ số

------------------------------------------------------------------------------------------------------------------------------------
| 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'))

# quy tắc phân tích

1. Starts: Số lần thao tác đó được lặp lại.
2. E-Rows vs A-Rows (Cardinality Misestimate): Tại bước 5, CBO dự đoán E-Rows = 5000, nhưng thực tế A-Rows = 85000 (sai lệch gấp 17 lần!). Đây chính là nguyên nhân gốc rễ (Root Cause) khiến CBO chọn sai chiến lược join hoặc thứ tự bảng.
3. Buffers (Consistent Gets): Tổng số khối dữ liệu 8KB được đọc từ Buffer Cache. Nếu một truy vấn chỉ lấy 150 dòng mà tốn tới 15420 Buffers (~120 MB memory transfer), đó là dấu hiệu của việc quét chỉ mục diện rộng không hiệu quả.

# kỹ thuật tối ưu hóa oracle sql thực chiến

# keyset pagination thay thế rownum trên bảng trăm triệu dòng

Mẫu hình phân trang truyền thống dùng subquery ROWNUM hoặc OFFSET-FETCH buộc Oracle phải quét qua toàn bộ dữ liệu trước đó rồi mới loại bỏ:

-- ANTI-PATTERN: Quét và sort 1,000,050 dòng 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;

# virtual columns & function-based index

Khi lập trình viên viết điều kiện có bọc hàm, Oracle không thể dùng 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);

# bẫy ngầm implicit type conversion

Nếu kiểu dữ liệu của Bind Variable không khớp với kiểu cột trong CSDL, Oracle sẽ áp dụng hàm chuyển đổi lên chính cột dữ liệu, làm tê liệt index:

-- 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

Với các bảng lịch sử giao dịch phình to qua năm tháng, chia Partition (Range theo ngày/tháng) là bắt buộc. Hãy đảm bảo câu truy vấn kích hoạt được tính năng Partition Pruning (kiểm tra 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 có điều kiện lọc trans_date BETWEEN :start_d AND :end_d, Oracle chỉ đọc đúng partition của tháng đó, bỏ qua 99% các khối dữ liệu còn lại trên đĩa.

# sql plan management (spm)

Trong môi trường ngân hàng, khi DBA chạy lệnh thu thập thống kê hoặc nâng cấp version database, CBO có thể đột ngột thay đổi Execution Plan của một câu truy vấn trọng yếu, biến câu lệnh 5ms thành 30 giây (Plan Regression).

Để ngăn chặn thảm họa này, sử 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          => '9a8b7c6d5e4f',
        plan_hash_value => 123456789,
        fixed           => 'YES',
        enabled         => 'YES'
    );
END;
/

Khi một Baseline được đánh dấu là fixed = 'YES', CBO sẽ bị khóa cứng vào kế hoạch này và không bao giờ tự ý chuyển sang kế hoạch khác ngay cả khi số liệu thống kê của bảng biến động.

# tổng kết

Tối ưu hóa Oracle SQL không phải là công việc sửa đổi bề nổi cú pháp mã nguồn, mà là quá trình kiểm soát tương tác giữa tầng toán học của Cost-Based Optimizer và tầng vật lý của hệ thống lưu trữ dữ liệu.

Một Solution Architect và Database Engineer xuất sắc luôn tuân thủ nguyên tắc:

1. Kiểm soát Parsing: 100% câu truy vấn OLTP phải dùng Bind Variables.
2. Kiểm định Thống Kê: Thường xuyên kiểm tra sự tương quan giữa E-Rows và A-Rows qua DBMS_XPLAN.
3. Hiểu rõ Index Access Paths: Không tạo index dư thừa; tận dụng IFFS, Composite Index và Partition Pruning để tối thiểu hóa số lượng Buffers truy xuất.
4. Bảo vệ Hệ Thống: Dùng SQL Plan Management (SPM) để đóng băng các kế hoạch thực thi cho các giao dịch tài chính cốt lõi.

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