Tối ưu câu lệnh bằng RESULT CACHE (Phần 2)

Để hiểu rõ hơn về cách thức hoạt động của Result Cache, ta xét 1 ví dụ đơn giản sau:
1. Thiết lập môi trường test:

Tạo bảng và insert dữ liệu mẫu

1 2 3 4 5 6 7

CREATE TABLE tbl_cache (id NUMBER); INSERT INTO tbl_cache VALUES(1); INSERT INTO tbl_cache VALUES(2); INSERT INTO tbl_cache VALUES(3); INSERT INTO tbl_cache VALUES(4); INSERT INTO tbl_cache VALUES(5); COMMIT;

Tạo 1 hàm xử lý dữ liệu trên bảng tbl_cache, và giả lập hàm này xử lý rất lâu.

1 2 3 4 5 6 7

CREATE OR REPLACE FUNCTION slow_function (p_id IN tbl_cache.id%TYPE) RETURN NUMBER AS BEGIN DBMS_LOCK.sleep (1); RETURN p_id; END;

* Lưu ý: user dùng để tạo hàm này phải được gán quyền thực thi câu lệnh DBMS_LOCK.sleep, nếu chưa được gán quyền ta sẽ thực hiện như sau:

• Đăng nhập bằng quyền DBA.
  Ví dụ: sqlplus / as sysdba
• Thực hiện gán quyền cho user
  Ví dụ: grant execute on sys.dbms_lock to orcl;

---

 2. Thực hiện test

SQL> set timing on; SQL> SELECT slow_function(id) FROM tbl_cache; SLOW_FUNCTION(ID) -----------------                 1                 2                 3                 4                 5 Elapsed: 00:00:05.03

Ta thấy rằng câu lệnh trên thực thi mất hơn 5 giây :(
Sau đây ta sẽ áp dụng RESULT_CACHE vào trong câu lệnh trên:

SQL> SELECT /*+ result_cache */ slow_function (id) FROM tbl_cache; SLOW_FUNCTION(ID) -----------------                 1                 2                 3                 4                 5 Elapsed: 00:00:10.02 SQL> SELECT /*+ result_cache */ slow_function (id) FROM tbl_cache; SLOW_FUNCTION(ID) -----------------                 1                 2                 3                 4                 5 Elapsed: 00:00:00.00

Với câu lệnh ban đầu thì thời gian thực thi mất hơn 10 giây. :(( Câu thứ 2 thì không mất giây nào :))
Trong bài 1, bạn đã được lưu ý một số trường hợp câu lệnh không được CACHE (xem lại Bài 1). Ở đây câu 1 chạy lâu vì là chạy lần đầu.
Ta xét tiếp trường hợp CACHE bị flush (có thể là vùng nhớ RESULT_CACHE đã hết hoặc DBA thực hiện flush bằng tay)

SQL> exec dbms_result_cache.flush; PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 SQL> SELECT /*+ result_cache */ slow_function (id) FROM tbl_cache; SLOW_FUNCTION(ID) -----------------                 1                 2                 3                 4                 5 Elapsed: 00:00:10.03 SQL> SELECT /*+ result_cache */ slow_function (id) FROM tbl_cache; SLOW_FUNCTION(ID) -----------------                 1                 2                 3                 4                 5 Elapsed: 00:00:00.01

Khi vùng nhớ RESULT_CACHE bị flush (do nhiều nguyên nhân) thì đương nhiên kết quả của lần chạy trước sẽ không được lưu trong SGA và ở lần chạy tiếp theo thời gian truy vấn của bạn sẽ dài hơn.