All about Oracle Technologies

Saturday 10 January 2015

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.

0 comments :

Post a Comment