Bài viết blog này giới thiệu khái niệm SQL Quarantine (Cách ly SQL). Bằng cách sử dụng Oracle® Resource Manager, bạn có thể điều chỉnh và giới hạn việc sử dụng tài nguyên như CPU và I/O, trong số những tài nguyên khác. Thú vị nhất là bạn có thể ngăn chặn việc thực thi bất kỳ truy vấn chạy lâu nào vượt quá ngưỡng được định nghĩa.
SQL Quarantine là gì?
Quarantine nghĩa là cách ly. SQL Quarantine là tính năng phiên bản 19c mà bạn có thể sử dụng để loại bỏ chi phí của các truy vấn chạy mất kiểm soát (runaway queries). Runaway queries là những truy vấn cuối cùng bị chấm dứt bởi Resource Manager vì chúng vượt quá giới hạn tài nguyên hoặc thời gian chạy và sử dụng rất nhiều tài nguyên, CPU, và I/O.
Tính năng này chỉ khả dụng cho Exadata (Oracle Database Enterprise Edition trên Engineered Systems) và DBCS/ExaCS (Oracle Database Exadata Cloud Service). Để kiểm tra tính năng này, tôi đặt tham số underscore và khởi động lại database bằng lệnh sau:
Alter system set"_exadata_feature_on"=true scope=spfile;
Về những truy vấn chạy lâu (Long-running queries)
Database Resource Manager (DBRM) là một tiến trình nền cho phép bạn chấm dứt câu lệnh SQL vượt quá các ngưỡng sử dụng tài nguyên nhất định như I/O và CPU. Nó cũng có thể chấm dứt bất kỳ truy vấn nào vượt quá ngưỡng thời gian chạy tối đa.
Bất kỳ kế hoạch thực thi SQL (SQL execution plan) và câu lệnh SQL bị đánh dấu vượt quá giới hạn sẽ bị cách ly (quarantined), điều này ngụ ý rằng nếu cùng một SQL chạy lại, sử dụng cùng một kế hoạch thực thi, câu lệnh SQL sẽ bị chấm dứt và hiển thị lỗi sau:
error: ORA-56955: quarantined plan used.
Khi các lỗi này xảy ra, Object Quarantine (Cách ly đối tượng) sẽ cách ly đối tượng gây ra lỗi và sau đó giám sát nó để xem tác động của nó đến phần còn lại của Database.
Tôi không đề cập đến các bảng và chỉ mục mà là các đối tượng mà Oracle có thể cách ly, bao gồm sessions (phiên), processes (tiến trình), SGA transactions, và library cache.
Bây giờ bạn có thể chấm dứt hoặc hủy bỏ bất kỳ truy vấn SQL nào chạy lâu hơn ngưỡng thời gian cho trước, chẳng hạn như ví dụ sau:
Hình 1: Câu lệnh SQL chạy mất kiểm soát Nguồn ảnh: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf
Tìm hiểu về đối tượng bị cách ly
Theo https://dbaparadise.com/2020/01/everything-you-need-to-know-about-quarantined-objects/: Để lấy thông tin về các đối tượng bị cách ly, bạn có thể truy vấn view V$QUARANTINE và V$QUARANTINE_SUMMARY, cho biết loại đối tượng, địa chỉ bộ nhớ của đối tượng, mã lỗi ORA- thực tế, và ngày giờ của lỗi.
Như ví dụ sau, bạn có thể giả định việc sử dụng CPU trong server trông giống như phản hồi của các runaway queries khi thực thi. Chúng ta có thể thấy ba truy vấn chạy đồng thời, sử dụng CPU gần 100% công suất.
Hình 2: CPU bị tiêu thụ bởi Runaway SQL Nguồn ảnh: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf
SQL Quarantine (Chi tiết)
Bạn có thể sử dụng SQL Quarantine để loại bỏ chi phí của các truy vấn chạy mất kiểm soát. Khi Resource Manager phát hiện câu lệnh SQL vượt quá giới hạn tài nguyên hoặc thời gian chạy, kế hoạch thực thi SQL được câu lệnh sử dụng sẽ bị cách ly.
Nếu cùng một câu lệnh SQL thực thi bằng cách sử dụng cùng một kế hoạch SQL, nó sẽ bị chấm dứt ngay lập tức. Điều này giúp giảm đáng kể việc sử dụng tài nguyên hệ thống. Trong hình sau, bạn có thể thấy sử dụng cao khi một vài truy vấn thực thi. Tuy nhiên, bằng cách chấm dứt chúng trước khi thực thi, chúng không còn tiêu thụ tài nguyên hệ thống sau khi bị cách ly.
Hình 3: CPU được tiết kiệm nhờ SQL Quarantine Nguồn ảnh: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf
Các bước để sử dụng tính năng cách ly
Hãy kiểm tra tính năng và cách nó hoạt động.
Đầu tiên, bạn phải thiết lập database như thể bạn đang làm việc trên Exadata.
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
Sau đó, bạn cần hoàn thành các bước sau để thiết lập Resource Manager:
-
Tạo pending area:
begin dbms_resource_manager.create_pending_area(); end; / -
Tạo một hoặc nhiều resource consumer group:
begin dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'. SQL_LIMIT',COMMENT=>'consumer group'); end; / -
Tạo resource plans:
begin dbms_resource_manager.set_consumer_group_mapping(attribute => 'ORACLE_USER',value => 'DBA1',consumer_group =>'SQL_LIMIT' ); dbms_resource_manager.create_plan(PLAN=> 'NEW_PLAN',COMMENT=>'Kill statement after exceeding total execution time'); end; / -
Tạo resource plan directives. Nhóm CANCEL_SQL đã tồn tại mặc định:
begin dbms_resource_manager.create_plan_directive( plan => 'NEW_PLAN', group_or_subplan => 'SQL_LIMIT', comment => 'Kill statement after exceeding total execution time', switch_group=>'CANCEL_SQL', switch_time => 10, switch_estimate=>false); end; / begin dbms_resource_manager.create_plan_directive( PLAN=> 'NEW_PLAN', GROUP_OR_SUBPLAN=>'OTHER_GROUPS', COMMENT=>'leave others alone', CPU_P1=>100 ); end; / -
Xác thực (validate) và submit pending area cho plan, consumer group, và directives:
begin dbms_resource_manager.validate_pending_area(); end; / begin dbms_resource_manager.submit_pending_area(); end; /
Ở đây bạn cần cấp quyền (grants) và gán consumer group cho người dùng.
-
Tạo pending area cho privileges, roles, và assigned users:
begin dbms_resource_manager.create_pending_area(); end; / -
Cấp switch privilege cho resource consumer group cho users hoặc roles.
begin dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false); end; / -
Gán users cho resource consumer groups.
begin dbms_resource_manager.set_initial_consumer_group('DBA1','SQL_LIMIT'); end; / -
Xác thực và submit pending areas.
begin dbms_resource_manager.validate_pending_area(); end; / begin dbms_resource_manager.submit_pending_area(); end; / -
Bây giờ, bạn phải cập nhật Plan và lại cần submit pending area.
begin dbms_resource_manager.clear_pending_area; dbms_resource_manager_create_pending_area; end; / begin dbms_resource_manager.update_plan_directive( plan=>'NEW_PLAN', group_or_subplan=>'SQL_LIMIT', new_switch_elapsed_time=>10, new_switch_for_call=>TRUE, new_switch_group=>'CANCEL_SQL'); end; / begin dbms_resource_manager.validate_pending_area(); dbms_resource_manager.submit_pending_area; end; /
Các bước tiếp theo
Các bước trước hoàn thành thiết lập Resource Manager. Sau khi hoàn thành chúng, tất cả những gì bạn cần làm là gán Plan này cho một instance:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;
Đăng nhập với user DBA1 và thực thi truy vấn sẽ vượt quá ngưỡng thời gian elapsed 10 giây được định nghĩa trong resource plan.
Bạn phải thực thi câu lệnh với user DBA1, và DBA1 phải có quyền truy cập vào DBA views.
select a.owner_name,b.product_name,c.location,d.country_code
from import_pr_table a, item_table b, locate_dealer_table c,country_table d;
ERROR at line 1:
ORA-00040: active time limit exceeded - call aborted
Lưu ý rằng trong trường hợp này, Resource Manager chấm dứt thực thi với lỗi ORA-00040.
Bạn có thể tìm thấy SQL_ID của câu lệnh không? Nó là 3hdkutq4krg4c.
Tạo SQL Quarantine
Bạn có thể sử dụng gói DBMS_SQLQ để tạo cấu hình cách ly cho kế hoạch thực thi của câu lệnh SQL cần bị cách ly.
Bạn có thể sử dụng SQL text hoặc SQL_ID của câu lệnh cần cách ly, như ví dụ sau:
CREATE_QUARANTINE_BY_SQL_ID or CREATE_QUARANTINE_BY_SQL_TEXT
DECLARE quarantine_sql VARCHAR2(30);
BEGIN
quarantine_sql := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => '3hdkutq4krg4c');
END; /
Sau khi tạo cấu hình cách ly này, bạn có thể chỉ định ngưỡng cách ly bằng thủ tục DBMS_SQLQ.ALTER_QUARANTINE.
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE(
QUARANTINE_NAME => 'SQL_QUARANTINE_3hdkutq4krg4c',
PARAMETER_NAME => 'ELAPSED_TIME',
PARAMETER_VALUE => '10');
END; /
Bây giờ bạn có thể truy vấn view DBA_SQL_QUARANTINE để xem câu lệnh SQL nào bị cách ly.
Với SQL Quarantine đã có sẵn, nó sẽ không thực thi nếu bạn thử chạy lại cùng một câu lệnh SQL.
select a.owner_name,b.product_name,c.location,d.country_code
from import_pr_table a, item_table b, locate_dealer_table c,country_table d;
ERROR at line 1:
ORA-56955: quarantined plan used
Thông báo lỗi trước cho biết kế hoạch được sử dụng cho câu lệnh này là một phần của kế hoạch bị cách ly. Nó hủy bỏ truy vấn vì đã vượt quá ngưỡng giới hạn.
Nếu bạn kiểm tra view V$SQL, bạn sẽ thấy hai cột mới: sql_quarantine và avoided_executions:
select sql_quarantine,avoided_executions from v$sql where sql_id='3hdkutq4krg4c';
SQL> select sql_quarantine,avoided_executions
2 from v$sql where sql_id='3hdkutq4krg4c';
SQL_QUARANTINE AVOIDED_EXECUTIONS
--------------- ---------------
SQL_QUARANTINE_3hdkutq4krg4c 1
Kết luận
Tính năng SQL Quarantine giúp cải thiện hiệu suất vì nó ngăn chặn việc thực thi trong tương lai của một câu lệnh SQL tốn kém, bị cách ly.
Sử dụng tab Feedback để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi. Bạn cũng có thể nhấp vào Sales Chat để trò chuyện ngay và bắt đầu cuộc hội thoại.