Tối ưu câu lệnh Oracle SQL – phần 1: nền tảng cơ bản

Tối ưu câu lệnh là chủ đề nâng cao, vì vậy để có thể hiểu được các kỹ thuật tối ưu câu lệnh Oracle SQL, chúng ta cần tìm hiểu một số khái niệm cơ bản liên quan đến lĩnh vực này, bài viết này không nhằm mục đích chuyển tải toàn bộ kiến thức liên quan đến tối ưu câu lệnh SQL, bài viết chỉ trình bày những khái niệm quan trọng nhất, đủ giúp những đọc giả đã am hiểu kiến trúc Oracle có thể hiểu được.

Nền tảng cơ bản

Trình tự xử lý câu lệnh SQL

Oracle Server xử lý câu lệnh SQL theo trình tự các bước sau:

1. Open
   • Ngầm định khai báo và khởi tạo Cursor cho câu lệnh SQL
2. Parse
   • Kiểm tra câu lệnh đã thực hiện trước đó chưa, nếu có thì chuyển sang thực hiện bước 3
   • Phân tích và kiểm tra cú pháp câu lệnh SQL
   • Kiểm tra tính hợp lệ và quyền truy cập đến các đối tượng dữ liệu câu lệnh tham chiếu tới
   • Xác định “sơ đổ thực thi câu lệnh SQL” tối ưu nhất
3. Bind
   • Tìm và gán giá trị cho các bind-variable nếu có
4. Execute
   • Thực thi các bước mô tả trong “sơ đồ thực thi câu lệnh SQL”
5. Fetch
   • Chuyển kết quả về nơi gọi thực thi lệnh
6. Close
   • Ngầm định đóng Cursor cho câu lệnh

 Trong các giai đoạn xử lý trên, giai đoạn Parse chiếm nhiều thời gian nhất.

Công cụ tối ưu câu lệnh SQL*Plus AUTOTRACE

Chức năng

Thu thập thông tin mô tả quá trình thực thi câu lệnh SQL, thường được dùng để tối ưu câu lệnh.

Cài đặt

Để sử dụng đầy đủ chức năng của công cụ này, cần tạo Table tên PLAN_TABLE và gán nhóm quyền PLUSTRACE cho tài khoản muốn sử dụng công cụ:

1. Dùng $ORACLE_HOME/rdbms/admin/utlxplan.sql để tạo PLAN_TABLE
2. Dùng tài khoản DBA chạy mã lệnh $ORACLE_HOME/sqlplus/admin/plustrce.sql để tạo nhóm quyền PLUSTRACE.

Ví dụ

Quan sát kết quả từ mã dẫn trên, ta thấy công cụ AUTOTRACE gửi về ba nhóm thông tin sau:

1. Kết quả câu lệnh
2. Sơ đồ trình tự thực thi câu lệnh
3. Thông tin thống kê chi phí tài nguyên cần dùng để thực thi câu lệnh

Cách sử dụng

Sơ đồ thực thi câu lệnh SQL

Sơ đồ thực thi câu lệnh SQL (Execution Plan - EP) mô tả thứ tự các bước Oracle cần thực thi để có được kết quả câu lệnh nhanh nhất.

Ví dụ về EP của câu lệnh xem dữ liệu trên hai Table CUSTOMERS và COUNTRIES:

Oracle qui định thứ tự xử lý các bước của EP như sau:

1. Bắt đầu xử lý từ bước nằm thụt về phía bên phải nhất, tiếp theo là các bước đứng kế trước nó
2. Nếu hai bước có cùng thứ tự thì sẽ xử lý bước nằm phía trên trước

Như vậy, ta thấy EP trên được xử lý theo thứ tự được đánh số bên phải.

Một EP đầy đủ bao gồm ba thành tố sau: phương thức truy cập dữ liệu, phương pháp JOIN và thứ tự JOIN giữa hai hay nhiều tập dữ liệu.

Phương thức truy cập dữ liệu

Xác định cách thức truy cập vật lý đến từng dòng dữ liệu của Table, ví du một số phương pháp truy cập dữ liệu Table phổ biến:

• Truy cập theo cách đọc từng dòng của Table ( TABLE ACCESS (FULL) ): đọc toàn bộ dòng dữ liệu của Table để đối sánh dữ liệu, vì vậy nếu Table có kích thước lớn thì phương thức truy cập này sẽ chiếm nhiều chi phí IO. Tuy nhiên, cách truy cập này cho phép chúng ta có thể cấu hình đọc nhiều khối dữ liệu (Block) cho một thao tác đọc và nhiều tiến trình cùng đọc một lúc.
  


• Truy cập dữ liệu cây Index (INDEX SCAN): duyệt cây Index theo giá trị khóa cần tìm, kết quả tìm được có lưu giá trị ROWID của dòng dữ liệu chứa giá trị khóa
  


• Truy cập dữ liệu Table theo Index ( TABLE ACCESS (BY INDEX ROWID)): phương thức truy cập này bao gồm hai bước tách biệt:
  
  
  
  1. Duyệt cây Index để tìm ROWID tương ứng với giá trị khóa
  2. Đọc dòng dữ liệu của Table theo ROWID tìm được một cách nhanh nhất

Với cách này, ta thấy để đọc được một dòng dữ liệu của Table thì phải tốn ít nhất hai thao tác IO; một thao tác IO đọc trên cây Index và một thao tác IO đọc trên Table. Theo luật chung thì cách này thích hợp khi kết quả dữ liệu cần tìm ít hơn 5% khối lượng dữ liệu của Table.

Phương pháp JOIN

Xác định phương pháp kết hợp dữ liệu giữa hai hay nhiều Table với nhau, ví dụ về phương pháp JOIN giữa hai Table T1 và T2:

1. NESTED LOOP JOIN: với T1 là outer-table, T2 là inner-table thì từng dòng dữ liệu của Table T1 sẽ kết hợp so sánh với tất cả dòng dữ liệu của Table T2, kết quả trả về là tất cả các dòng dữ liệu thỏa điều kiện so sánh.
2. SORT MERGE JOIN: tập dòng dữ liệu của hai Table được sắp theo thứ tự trước khi ứng dụng thuật toán trộn trên chúng.

Thứ tự JOIN

Xác định thứ tự JOIN khi câu lệnh SQL có nhiều hơn hai Table kết hợp với nhau, thứ tự JOIN hợp lý giúp giảm thiểu dữ liệu cần kết hợp với nhau mà vẫn đạt được kết quả đúng.

Sau khi tìm hiểu qua các khái niệm cơ bản, chúng ta thử quay lại tìm hiểu ý nghĩa EP của câu lệnh SQL trên:

1. Oracle chọn phương pháp JOIN là NESTED LOOP để thực hiện phép JOIN giữa hai Table.
2. Oracle chọn CUSTOMERS đóng vai trò là outer-table, COUNTRIES là inner-table
3. Bắt đầu đọc từng dòng dữ liệu của CUSTOMERS rồi so trùng với tất cả dòng của COUNTRIES, điểm lưu ý là giá trị COUNTRIES.COUNTRY_ID này được lấy từ cây Index COUNTRY_PK
4. Kết quả trả về là tất cả những dòng dữ liệu thỏa điều kiện so trùng

Đến đây có thể các bạn thắc mắc là tại sao Oracle lại chọn phương pháp Join là NESTED LOOP ? tại sao CUSTOMERS lại được chọn làm outer-table ?, quyết định chọn này được thực hiện bởi Trình tối ưu câu lệnh Oracle.

Trình tối ưu câu lệnh Oracle

Trình tối ưu giúp Oracle xác định được một EP tốt nhất cho một câu lệnh SQL. Trình tối ưu Oracle 9i hỗ trợ phương pháp tối ưu dựa vào cú pháp lệnh (Rule Based Optimizer – RBO) và phương pháp tối ưu dựa vào chi phí ước tính cần dùng để thực thi câu lệnh (Cost Based Optimizer – CBO). Mặc định Oracle9i dùng RBO.

RBO

RBO có từ phiên bản Oracle6, dựa vào cấu trúc câu lệnh SQL để xác định EP tốt nhất. RBO sử dụng một lược đồ phân hạng các phương thức truy cập dữ liệu để chọn phương thức truy cập cho EP, phương thức nào có số hạng thấp sẽ được ưu tiên chọn. Lược đồ phân hạng các phương thức truy cập dữ liệu được qui định như sau:

Ta thấy truy cập theo ROWID có số hạng thấp nhất là 1; nghĩa là phương thức truy cập đến dữ liệu nhanh nhất, và truy cập theo Index có số hạng là 4 sẽ nhanh hơn nhiều so với truy cập theo Full-Table-Scan có số hạng cao nhất là 15.

Ví dụ sau cho thấy RBO sử dụng luật phân hạng để chọn phương thức truy cập dữ liệu cho EP. Do cột CUST_ID của điều kiện WHERE có UNIQUE INDEX lên RBO chọn phương thức truy cập theo Index:

SQL> DROP TABLE new_table;

Table dropped.

SQL> CREATE TABLE new_table AS

        SELECT object_id, object_name, object_type

        FROM all_objects

        WHERE rownum <= 5000;  2    3    4

Table created.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT * FROM new_table WHERE object_type = 'TABLE';

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (FULL) OF 'NEW_TABLE'

SQL> SET AUTOTRACE OFF

SQL> CREATE INDEX idx_object_type ON new_table(object_type);

Index created.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL> SELECT * FROM new_table WHERE object_type = 'TABLE';

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'NEW_TABLE'

   2    1     INDEX (RANGE SCAN) OF 'IDX_OBJECT_TYPE' (NON-UNIQUE)

CBO

CBO có từ phiên bản Oracle7, CBO dựa vào “thông tin tổng hợp” được để ước tính chi phí của EP, EP tốt nhất là EP có chi phí ước tính nhỏ nhất. Oracle9i chỉ dùng CBO để tối ưu nếu đối tượng tham chiếu đến có thông tin mô tả.

“Thông tin tổng hợp” trong ngữ cảnh này bao gồm:

1. Thông tin về hệ điều hành máy chủ Oracle: số CPU, tốc độ đọc ghi đĩa cứng, kích thước khối dữ liệu, cơ chế quản lý đĩa …
2. Thông tin mô tả các đối tượng dữ liệu: số dòng, cột và khối dữ liệu của Table, kích thước dòng, chiều cao và số nút lá của cây Index …
3. Thông tin về các thông số liên quan: optimizer_mode, db_file_multiblock_read_count, parallel_automatic_tuning …

Nếu cung cấp đầy đủ và chính xác các thông tin trên, trình tối ưu CBO có thể xác định được một EP tốt nhất.

Tiếp theo ví dụ trong phần RBO, chúng ta dùng lệnh ANALYZE TABLE để thu thập thông tin của NEW_TABLE cho trình tối ưu:

SQL> SET AUTOTRACE OFF

SQL> @li NEW_TABLE

indexes on table NEW_TABLE%:

TABLE_NAME           INDEX_TYPE INDEX_NAME

-------------------- ---------- ------------------------------

NEW_TABLE            NONUNIQUE  IDX_OBJECT_TYPE

SQL> ANALYZE TABLE new_table COMPUTE STATISTICS;

Table analyzed.

SQL> SET AUTOTRACE TRACEONLY EXPLAIN

SQL>SELECT * FROM new_table WHERE object_type = 'TABLE';

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=2500 Bytes=107500)

   1    0   TABLE ACCESS (FULL) OF 'NEW_TABLE' (Cost=5 Card=2500 Bytes=107500)

Ta thấy kết quả của EP có một số điểm khác biệt so với thời điểm trước khi thu thập thông tin:

1. Có thêm thông tin về chi phí thực hiện của mỗi bước lệnh: (Cost=5 Card=2500 Bytes=107500), điều này chứng tỏ Oracle hiện đang dùng trình tối ưu CBO
2. CBO chọn phương thức truy cập TABLE ACCESS (FULL) mặc dù có tồn tại Index trên cột object_type.

Phụ lục

Giải nghĩa từ

Bind-variable: kỹ thuật viết mã không gán giá trị cố định, kỹ thuật này giúp tăng tính sẵn sàng, hiệu năng và bảo mật cho hệ thống Oracle. Ví dụ:

Mã không dùng Bind-Variable

BEGIN

UPDATE employees SET SALARY=SALARY + 100 WHERE employee_id = 100;

END;

Mã dùng Bind-Variable

DECLARE

   bind_var NUMBER := 100;

BEGIN

UPDATE employees SET SALARY=SALARY + 100 WHERE employee_id = bind_var;

END;

Outer-table: từng dòng dữ liệu của Table này sẽ đối sánh với tất cả dòng của inner-table, còn gọi là driving-table, được đề cập đến trong phương pháp NESTED LOOP JOIN, thường đi chung với thuật ngữ inner-table

Inner-table: tất cả dòng dữ liệu của Table này sẽ được đối sánh với từng dòng của outer-table.

Mã lệnh

Một số File mã lệnh tiện ích sử dụng trong bài viết:

Lịch sử thay đổi

1. 10/02/2009 - Giới thiệu bài viết
2. 20/02/2009 - Bổ sung phần "Công cụ tối ưu câu lệnh: SQL*Plus AUTOTRACE

Tham khảo

1. Cách sử dụng "Bind Variable" khi lập trình Java với Oracle
2. Tài liệu chuẩn của Oracle Database 9i
3. Tài liệu khóa học Oracle Database 9i: SQL Tuning

Mời các bạn đọc phần tiếp theo của chủ đề này:

Phần 2 – Tối ưu câu lệnh SQL có Table và có một điều kiện

Để hiểu rõ hơn về chủ đề này, các bạn có thể tham gia các khóa học sau tại tại Trung tâm VietPace:

• Oracle Database 9i: SQL Tuning Workshop

Copyright 2009 www.vietpace.com, All rights reserved.