Bài đăng trên blog này giới thiệu khái niệm 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 các tài nguyên như CPU và I / O, trong số những tài nguyên khác. Thực tế thú vị là bạn có thể ngăn chặn việc thực thi bất kỳ truy vấn chạy dài nào vượt quá ngưỡng đã xác định.
Cách ly SQL là gì?
Quarantine có nghĩa là cách ly. Cách ly SQL là một tính năng của 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. Truy vấn chạy là những truy vấn cuối cùng bị Trình quản lý tài nguyên kết thúc bởi 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 tài nguyên, CPU và IO.
Tính năng này chỉ có sẵn cho Exadata (Oracle Database Enterprise Edition onEngineered Systems) và DBCS / ExaCS (Oracle Database Exadata Cloud Service). Để kiểm tra tài liệu này, tôi đặt tham số gạch dưới và trả lại cơ sở dữ liệu bằng cách sử dụng lệnh sau:
Alter system set"_exadata_feature_on"=true scope=spfile;
Còn các truy vấn chạy dài thì sao?
Trình quản lý tài nguyên cơ sở dữ liệu (DBRM) là một quy trình nền cho phép bạn chấm dứt câu lệnh anSQL vượt quá ngưỡng sử dụng tài nguyên nhất định như IO và CPU. Italso 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 nào được đánh dấu và câu lệnh SQL vượt quá giới hạn sẽ bị cách ly, đ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à xuất hiện lỗi sau:
Lỗi error: ORA-56955: quarantined plan used.
Khi những lỗi này xảy ra, Đối tượng Cách ly sẽ cô lập đối tượng gây ra lỗi và sau đó theo dõi đối tượng để xem tác động của nó đối với phần còn lại của Cơ sở dữ liệu.
Tôi không đề cập đến các bảng và chỉ mục mà đề cập đến các đối tượng mà Oracle có thể cách ly, bao gồm các phiên, quy trình, giao dịch SGA và bộ đệm thư viện.
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 một ngưỡng thời gian nhất định, chẳng hạn như ví dụ sau:
Hình 1:Câu lệnh Runaway Sql Nguồn hình ảnh:https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf
Tìm hiểu về các đối tượng đã được cách ly
Theohttps://dbaparadise.com/2020/01/everything-you-need-to-know-about-quarantined-objects/:Để nhận thông tin về các đối tượng bị cách ly, bạn có thể truy vấn V $ QUARANTINE > và V $ QUARANTINE_SUMMARY các chế độ xem, cho bạn biết loại đối tượng, địa chỉ bộ nhớ của đối tượng, lỗi ORA thực tế và ngày giờ của lỗi.
Như được hiển thị trong ví dụ sau, bạn có thể giả định rằng việc sử dụng CPU trong máy chủ giống như phản hồi của truy vấn chạy khi được thực thi. Chúng ta có thể thấy ba truy vấn đang chạy đồng thời, sử dụng CPU đến gần 100% công suất của nó.
Hình 2:CPU được sử dụng bởi Runaway SQL Nguồn hình ảnh:https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf
Cách ly SQL
Bạn có thể sử dụng SQL Quarantine để loại bỏ chi phí của các truy vấn chạy. Khi ResourceManager phát hiện một 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 sử dụng bởi câu lệnh 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 gói 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 cấu hình sau, bạn có thể thấy hiệu suất 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 sử dụng tài nguyên hệ thống sau khi bị cách ly.
Hình 3:CPU được lưu bằng vùng cách ly SQL Nguồn hình ả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.
Trước tiên, bạn phải thiết lập cơ sở dữ liệu khi 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 Trình quản lý tài nguyên:
-
Tạo một khu vực đang chờ xử lý:
begin dbms_resource_manager.create_pending_area(); end; / -
Tạo một hoặc nhiều nhóm người tiêu dùng tài nguyên:
begin dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'. SQL_LIMIT',COMMENT=>'consumer group'); end; / -
Tạo kế hoạch tài nguyên:
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 chỉ thị kế hoạch tài nguyên. Nhóm CANCEL_SQL đã tồn tại theo 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 và gửi khu vực đang chờ xử lý cho kế hoạch, nhóm người tiêu dùng và chỉ thị:
begin dbms_resource_manager.validate_pending_area(); end; / begin dbms_resource_manager.submit_pending_area(); end; /
Tại đây, bạn cần chỉ định các khoản tài trợ và chỉ định nhóm người tiêu dùng cho người dùng.
-
Tạo khu vực đang chờ xử lý cho các đặc quyền, vai trò và người dùng được chỉ định:
begin dbms_resource_manager.create_pending_area(); end; / -
Cấp đặc quyền chuyển đổi cho các nhóm khách hàng tài nguyên cho người dùng hoặc vai trò.
begin dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false); end; / -
Chỉ định người dùng vào các nhóm người tiêu dùng tài nguyên.
begin dbms_resource_manager.set_initial_consumer_group('DBA1','SQL_LIMIT'); end; / -
Xác thực và gửi các khu vực đang chờ xử lý.
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 Kế hoạch và một lần nữa cần gửi khu vực đang chờ xử lý.
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 Trình quản lý Tài nguyên. Sau khi hoàn thành chúng, tất cả những gì bạn cần làm là gán Kế hoạch này cho một phiên bản:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;
Đăng nhập với người dùng DBA1 và đưa ra một truy vấn sẽ vượt quá ngưỡng thời gian đã trôi qua là 10 giây được xác định trong kế hoạch tài nguyên.
Bạn phải thực thi câu lệnh với tư cách là người dùng DBA1 và DBA1 phải có quyền truy cập vào các dạng xem DBA.
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, Trình quản lý tài nguyên đã kết thúc việc thực thi bằng ORA-00040 lỗi.
Bạn có thể tìm thấy SQL_ID của câu lệnh không? Nó là 3hdkutq4krg4c .
Tạo Vùng cách ly SQL
Bạn có thể sử dụng DBMS_SQLQ gói để tạo cấu hình cách ly cho một kế hoạch thực thi của một câu lệnh SQL cần được cách ly.
Bạn có thể sử dụng văn bản SQL hoặc SQL_ID của câu lệnh được cách ly, như được hiển thị trong 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 vùng cách ly này, bạn có thể chỉ định ngưỡng cách ly bằng cách sử dụng DBMS_SQLQ.ALTER_QUARANTINE thủ tục.
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 DBA_SQL_QUARANTINE xem câu lệnh SQL nào bị cách ly.
Với SQL Quarantine tại chỗ, nó không thực thi nếu bạn cố gắng chạy 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 rằng kế hoạch được sử dụng cho tuyên bố này là một phần của kế hoạch cách ly. Nó đã hủy truy vấn vì nó đã vượt qua giới hạn ngưỡng.
Nếu bạn chọn V $ SQL xem, bạn thấy hai cột mới: sql_quarantine và tránh_xác_định :
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 Cách ly SQL giúp cải thiện hiệu suất vì nó ngăn chặn việc thực thi một câu lệnh SQL cách ly, tốn kém trong tương lai.
Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào. Bạn cũng có thể nhấp vào Trò chuyện bán hàng để trò chuyện ngay bây giờ và bắt đầu cuộc trò chuyện.