Cách sử dụng Execution Plan trong Oracle: Đọc gì trước khi tối ưu SQL?

Một câu SQL chạy chậm không có nghĩa là câu SQL đó viết sai.

Có thể Oracle đang chọn cách truy xuất dữ liệu không phù hợp, đọc quá nhiều dòng, không sử dụng Index hoặc thực hiện một phép Join tốn nhiều chi phí.

Thay vì đoán, bạn có thể xem Execution Plan để biết Oracle đang thực sự làm gì khi chạy câu SQL.

1. Execution Plan là gì?

Execution Plan là kế hoạch mà Oracle Optimizer lựa chọn để thực thi một câu SQL.

Ví dụ:

SELECT *
FROM employees
WHERE email = '[email protected]';

Oracle có thể lựa chọn:

TABLE ACCESS FULL

hoặc:

INDEX RANGE SCAN
    ↓
TABLE ACCESS BY INDEX ROWID

Có thể hiểu đơn giản:

SQL nói bạn muốn gì, Execution Plan cho bạn thấy Oracle dự định làm như thế nào.

Vì vậy, khi query chậm, đừng chỉ nhìn vào câu SQL. Hãy nhìn cả Execution Plan.

2. Xem Execution Plan trong Oracle như thế nào?

Cách đơn giản:

EXPLAIN PLAN FOR
SELECT *
FROM employees
WHERE email = '[email protected]';

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);

Oracle sẽ trả về một kế hoạch thực thi.

Ví dụ:

--------------------------------------------------------------------------------
| Id | Operation                   | Name             |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |                  |
|  1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES        |
|  2 | INDEX RANGE SCAN            | IDX_EMP_EMAIL    |
--------------------------------------------------------------------------------

Ở đây có thể đọc theo hướng:

INDEX RANGE SCAN
       ↓
Tìm ROWID của bản ghi
       ↓
TABLE ACCESS BY INDEX ROWID
       ↓
Lấy dữ liệu từ bảng

Điều này cho thấy Oracle đang sử dụng Index để tìm dữ liệu thay vì quét toàn bộ bảng.

3. Những Operation nào nên biết?

Bạn không cần học thuộc toàn bộ Execution Plan ngay từ đầu. Với các query thông thường, hãy làm quen với một số operation phổ biến.

TABLE ACCESS FULL

TABLE ACCESS FULL

Oracle đọc toàn bộ bảng.

Điều này không tự động có nghĩa là query sai hoặc chậm.

Ví dụ bảng chỉ có vài trăm dòng thì đọc toàn bảng có thể hợp lý hơn việc sử dụng Index.

INDEX RANGE SCAN

INDEX RANGE SCAN

Oracle tìm một phạm vi giá trị trong Index.

Ví dụ:

WHERE salary BETWEEN 1000 AND 3000

hoặc các điều kiện có thể trả về nhiều bản ghi.

INDEX UNIQUE SCAN

INDEX UNIQUE SCAN

Oracle tìm một giá trị duy nhất trong Unique Index hoặc cấu trúc tương ứng.

Ví dụ thường gặp:

WHERE employee_id = 1001

khi employee_id được đảm bảo là duy nhất.

TABLE ACCESS BY INDEX ROWID

Sau khi Index giúp Oracle tìm được vị trí của bản ghi, Oracle sử dụng ROWID để lấy dữ liệu từ bảng.

Có thể hình dung:

Index
  ↓
Tìm vị trí dòng
  ↓
ROWID
  ↓
Lấy dữ liệu từ table

4. Đọc Execution Plan để tìm vấn đề như thế nào?

Đừng nhìn vào một dòng rồi kết luận ngay.

Hãy bắt đầu bằng 3 câu hỏi:

① Oracle đang đọc bao nhiêu dữ liệu?

Ví dụ:

TABLE ACCESS FULL
EMPLOYEES

Nếu bảng có hàng triệu dòng nhưng query chỉ cần vài dòng, đây là điểm đáng kiểm tra.

Có thể cần xem:

• Có Index phù hợp không?
• Điều kiện WHERE có phù hợp với Index không?
• Optimizer có lý do để chọn Full Table Scan không?

② Có operation nào xử lý quá nhiều rows không?

Ví dụ:

TABLE ACCESS FULL
  10,000,000 rows

nhưng cuối cùng query chỉ trả về:

10 rows

Có nghĩa là Oracle đã phải xử lý một lượng dữ liệu rất lớn để tìm ra một lượng kết quả nhỏ.

Đây là dấu hiệu đáng để điều tra.

③ JOIN đang xử lý dữ liệu như thế nào?

Ví dụ Execution Plan có:

HASH JOIN
  TABLE ACCESS FULL
  TABLE ACCESS FULL

Không nên nhìn thấy HASH JOIN rồi kết luận ngay rằng query đang có vấn đề.

Oracle có nhiều phương pháp Join như:

NESTED LOOPS
HASH JOIN
MERGE JOIN

Optimizer lựa chọn dựa trên thống kê và chi phí ước tính.

Điều cần quan tâm là:

Với lượng dữ liệu thực tế, kế hoạch này có đang xử lý quá nhiều dữ liệu hay không?

5. Một số hiểu lầm khi đọc Execution Plan

❌ “Có TABLE ACCESS FULL nghĩa là query sai”

Không đúng.

Full Table Scan đôi khi là lựa chọn tốt nhất, đặc biệt khi query cần đọc phần lớn bảng.

❌ “Có INDEX nghĩa là query chắc chắn nhanh”

Không đúng.

Oracle có thể có Index nhưng vẫn chọn Full Table Scan nếu Optimizer đánh giá cách đó có chi phí thấp hơn.

❌ “Cost càng thấp thì query chắc chắn nhanh hơn”

Cũng không nên hiểu như vậy.

Cost là giá trị ước tính được Optimizer sử dụng để so sánh các kế hoạch, không phải số mili-giây mà query sẽ chạy.

❌ “Chỉ cần EXPLAIN PLAN là biết chính xác query chạy thực tế thế nào”

Đây là điểm quan trọng.

EXPLAIN PLAN cho biết kế hoạch mà Oracle dự kiến sử dụng.

Khi cần phân tích sâu hơn, bạn nên xem actual execution statistics của lần chạy thực tế, chẳng hạn thông qua DBMS_XPLAN.DISPLAY_CURSOR khi đã thu thập runtime statistics phù hợp.

6. Quy trình thực tế khi query chậm

Thay vì sửa SQL theo cảm tính, có thể đi theo quy trình:

Query chậm
    ↓
Xem Execution Plan
    ↓
Oracle đang đọc dữ liệu như thế nào?
    ↓
Có Full Table Scan / Join lớn / xử lý nhiều rows?
    ↓
Kiểm tra WHERE, JOIN, INDEX và statistics
    ↓
Thử thay đổi
    ↓
Đo lại

Ví dụ bạn có:

SELECT *
FROM employees
WHERE email = '[email protected]';

và bảng có hàng triệu dòng.

Đừng ngay lập tức tạo Index chỉ vì nhìn thấy:

TABLE ACCESS FULL

Hãy đặt câu hỏi:

email có tính chọn lọc cao không?

Query thực sự cần bao nhiêu dòng?

Oracle có statistics phù hợp không?

Index hiện tại có phù hợp với điều kiện truy vấn không?

Sau đó mới quyết định có cần thay đổi SQL hoặc Index hay không.

Tóm lại

Khi làm việc với SQL, đừng tối ưu bằng cảm giác.

Hãy hình thành thói quen:

SQL
 ↓
Execution Plan
 ↓
Oracle đang làm gì?
 ↓
Đọc quá nhiều dữ liệu ở đâu?
 ↓
Tối ưu
 ↓
Đo lại

Execution Plan không phải thứ chỉ dùng khi query đã chậm. Nó là công cụ giúp bạn hiểu Oracle đang thực sự lựa chọn cách nào để truy xuất dữ liệu.