Bài viết này sẽ giải thích nguyên lý của RESULT CACHE HINT trong tối ưu câu lệnh SQL một cách chi tiết, bạn sẽ biết một trong các phương án tối ưu vô cùng “bá đạo” – có thể đưa thời gian thực hiện của câu lệnh ngay lập tức về ~0s.

 

1. Sử dụng RESULT CACHE mang lại lợi ích gì?

Bạn nghĩ sao nếu câu lệnh SQL của bạn trả ra kết quả với thời gian 0s NGAY LẬP TỨC?

Nếu điều này xảy ra thì hết sức tuyệt vời đúng không nào?

Nếu bạn hiểu và biết cách sử dụng RESULT CACHE, câu lệnh SQL của bạn sẽ có thời gian thưc thi là 0s !!!

Đây chính là lợi ích lớn nhất của RESULT CACHE.

Tuy nhiên, trước khi bắt đầu sử dụng phương án tối ưu này trong các dự án thực tế, bạn cần tự hỏi một số điều sau

• Thứ nhất: RESULT CACHE là gì, và tại sao nó có thể tăng tốc câu lệnh một cách khủng khiếp như vậy?
• Thứ hai: Có phải mọi câu lệnh SQL, trong mọi tình huống đều trả ra kết quả với thời gian 0s hay không?
• Thứ ba: Chúng ta có nên lạm dụng phương án RESULT CACHE này không?
• Thứ tư: Trong dự án thực tế thì người ta có sử dụng RESULT CACHE hay không?

2. Demo Result Cache và kết quả câu lệnh SQL giảm còn 0s ngay lập tức.

Toàn bộ Video Demo bạn có thể xem tại đây:

Vắn tắt kết quả Demo như sau

• Thực hiện tối ưu câu lệnh SQL

select count(*) from emp;

Bảng emp có số lượng bản ghi là 1 triệu 100K bản ghi.

Câu lệnh này nếu tối thực hiện 3 lần liên tiếp nhau thì có thời gian thực hiện là:

Lần	Thời gian (s)
1	4.84
2	4.66
3	6.96

• Áp dụng phương án RESULT CACHE để tối ưu câu lệnh trên

select /*+ RESULT_CACHE */ count(*) from emp;

Khi thêm HINT /*+ RESULT_CACHE */ thời gian thực hiện của câu lệnh lúc này như sau

Lần	Thời gian (s)
1	4.82
2	0
3	0

Các lần thực hiện từ thứ 2 trở đi kết quả đều trả ra với thời gian 0s (gần như ngay lập tức!)

2. RESULT CACHE là gì ?

Dưới đây là một luồng cơ bản khi câu lệnh SQL thực thi.

 

Ban đầu các dữ liệu cần thiết cho câu lệnh SQL thực hiện có ở trong những Datafile (các file trên hệ điều hành).

Nếu câu lệnh lần đầu tiên thực hiện, hệ thống sẽ thực hiện: lấy dữ liệu từ Datafile và đưa lên phần bộ nhớ. Cụ thể những block dữ liệu đó sẽ được đưa vào một vùng bộ nhớ có tên gọi là Database Buffer Cache.

Với các câu lệnh muốn truy xuất cùng những block dữ liệu, hệ thống sẽ không cần phải truy cập vào file trên hệ điều hành nữa, thay vào đó chỉ cần vào vùng Buffer Cache trên Memory là có thể lấy được dữ liệu ra.

Thông thường việc đọc dữ liệu trên Memory sẽ có lợi về thời gian rất nhiều so với đọc ghi trực tiếp từ ổ cứng (lấy từ Data File trên hệ điều hành), do đó với các loại database đều có chung nguyên lý thực thi câu lệnh SQL như thế này.

Tại đây có 1 vấn đề mà bạn cần hiểu rõ:

• Hệ thống KHÔNG LƯU KẾT QUẢ của câu lệnh trong Buffer Cache.
• Buffer Cache chỉ lưu các BLOCK DỮ LIỆU. Trong trường hợp câu lệnh cần lấy thực hiện lại một câu lệnh SQL thì các Block dữ liệu này sẽ được đem ra tính toán (theo điều kiện có trong câu lệnh), do đó thời gian thực thi có thể giảm so với lần đầu tiên, tuy nhiên không thể nào đưa câu lệnh “TỤT HUYẾT ÁP” ngay lập tức về 0s được. Điều này là RÕ RÀNG từ phía kiến trúc.

Khi chúng ta sử dụng kỹ thuật RESULT CACHE, hệ thống sẽ sử dụng thêm 1 vùng bộ nhớ nữa – vùng bộ nhớ có tên là RESULT CACHE.

Bản chất của vùng bộ nhớ RESULT CACHE này sẽ lưu KẾT QUẢ thực hiện của câu lệnh SQL.

Khi sử dụng HINT /*+ RESULT_CACHE */ hê thống sẽ biết rằng chúng ta muốn lưu kết quả của câu lệnh vòa trong vùng RESULT CACHE MEMORY.

Bây giờ ta sẽ xem xét khi thực hiện 3 câu lệnh SQL trong ví dụ trên

select /*+ RESULT_CACHE */ count(*) from emp;

Lần đầu tiên thực hiện:

• Lúc này hệ thống phải lấy các block dữ liệu từ DATA FILE để đưa lên BUFFER CACHE. (nếu như trong BUFFER CACHE không chứa thông tin).
• Trong RESULT CACHE chưa có KẾT QUẢ lưu trước của câu lệnh (vì là lần đầu tiên thực hiện).
• Lần đầu thực hiện, câu lệnh thường chạy với thời gian lâu nhất.
• Sau khi thực hiện xong, kết quả được lưu lại trong RESULT CACHE MEMORY

Lần thứ hai thực hiện:

• Câu lệnh KHÔNG CẦN SỬ DỤNG BUFFER CACHE để tính toán gì cả.
• Câu lệnh lấy ngay kết quả trong RESULT CACHE MEMORY và TRẢ RA NGAY LẬP TỨC cho người dùng
• Thời gian thực hiện ~ 0s

Lần thứ ba thực hiện

• Câu lệnh KHÔNG CẦN SỬ DỤNG BUFFER CACHE để tính toán gì cả.
• Câu lệnh lấy ngay kết quả trong RESULT CACHE MEMORY và TRẢ RA NGAY LẬP TỨC cho người dùng
• Thời gian thực hiện ~ 0s

 

3. Giải đáp một số câu hỏi liên quan đến RESULT CACHE

3.1. Result Cache có áp dụng được với những câu lệnh truy vấn phức tạp không?

Chúng ta hoàn toàn có thể áp dụng kỹ thuật Result Cache trong những câu lệnh phức tạp

Ví dụ: áp dụng trong các câu lệnh JOIN

select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400

Các câu lệnh truy vấn lồng cũng mang lại kết quả tương tự khi áp dụng RESULT CACHE

3.2. Nếu dữ liệu trong bảng bị thay đổi thì giải pháp sử dụng RESULT_CACHE còn hiệu quả hay không?

Giả sử chúng ta đang xét câu lệnh sau

select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400

Nếu một trong các bảng emp hoặc dept bị thay đổi dữ liệu (bởi các câu lệnh DML), hệ thống sẽ tự động phát hiện được việc này và đánh dấu kết quả trong RESULT_CACHE MEMORY là không còn đúng nữa.

Chính vì thế sau khi dữ liệu của bảng bị thay đổi, hệ thống sẽ cần TÍNH TOÁN LẠI chứ không lấy ngay kết quả trong RESULT CACHE MEMORY để trả ra cho người dùng được.

Cụ thể nếu chúng ta xét tình huống sau

Lần 1: Thực hiện câu lệnh

select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400

• Đây là lần đầu tiên chạy câu lệnh, hệ thống phải đưa các dữ liệu vào BUFFER CACHE
• Sau khi tính toán xong, kết quả được đưa vào RESULT_CACHE

Lần 2: Chưa có bất kỳ thay đổi dữ liệu nào, người dùng thực hiện lại câu lệnh

select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400

• Hệ thống lấy ngay lập tức dữ liệu trong RESULT_CACHE để trả cho người dùng

Lần 3: Bảng EMP được thêm mới 1 bản ghi. Sau khi dữ liệu đã được thêm mới, người dùng thực hiện lại câu lệnh thống kê

select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400

• Do dữ liệu trong bảng EMP bị thay đổi, hệ thống đánh dấu thông tin trong RESULT_CACHE sẽ không được sử dụng.
• Hệ thống phải tính toán lại các dữ liệu trong BUFFER CACHE
• Sau khi tính toán xong, kết quả được đưa vào RESULT_CACHE

Lần 4: Dữ liệu không có bất kỳ thay đổi nào, người dùng lại thực hiện câu lệnh

select /*+ RESULT_CACHE */ * from emp e, dept d where e.deptno=d.deptno and e.salary=400

• Dữ liệu được lấy ngay lập tức từ RESULT_CACHE trả cho người dùng

3.3. Nếu câu lệnh có thay đổi về cách viết thì RESULT CACHE còn tác dụng không?

Có một số bạn gửi câu hỏi đến tôi rằng:

• Nếu câu lệnh lần thứ 2 chỉ khác biệt so với câu lệnh lần thứ nhất ở “dấu cách” hoặc ở cách viết IN HOA thì RESULT CACHE có tác dụng không (về mặt bản chất thì 2 câu lệnh này chẳng khác gì nhau cả)

Trả lời:

• Các câu lệnh khác nhau ở cách viết (so sánh đơn thuần theo STRING) thì được tính là 2 câu lệnh KHÁC NHAU (có SQL_ID khác nhau).
• Do đó hệ thống sẽ không sử dụng RESULT CACHE của 2 câu lệnh này cho nhau.

3.4. Khuyến cáo từ kinh nghiệm dự án của Wecommit

• Không nên áp dụng tụy tiện kỹ thuật RESULT CACHE.
• Thường kỹ thuật RESULT CACHE không phù hợp với các hệ thống OLTP
• Chỉ áp dụng RESULT CACHE với các tình huống dữ liệu ít thay đổi
• Việc lạm dụng RESULT CACHE đối với các object có dữ liệu thay đổi liên tục sẽ khiến hệ thống bị Wait do liên tục phải kiểm tra và lưu lại thông tin trong Result Cache Memory.

4. Truy cập vào KHO HỌC LIỆU TỐI ƯU của chúng tôi (Không cần đăng ký tài khoản)

Truy cập tại đây: https://wecommit.com.vn/courses/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-cao-cap/

 

5. Nếu bạn muốn biết về những giá trị hoàn toàn khác biệt của chương trình đào tạo tối ưu cơ sở dữ liệu cao cấp (CÓ PHÍ) của chúng tôi

Xem nội dung tại đây: https://wecommit.com.vn/chuong-trinh-dao-tao-toi-uu-co-so-du-lieu-wecommit/

6. Thông tin tác giả

Trần Quốc Huy – CEO & Founder Wecommit

Zalo: 0888549190