ป้องกัน Runaway Queries ด้วย Oracle 19c SQL Quarantine

บทความบล็อกนี้แนะนำแนวคิดของ SQL Quarantine ด้วยการใช้ Oracle® Resource Manager คุณสามารถควบคุมและจำกัดการใช้ทรัพยากรอย่าง CPU และ I/O และสิ่งน่าสนใจที่สุดคือ คุณสามารถป้องกันการดำเนินการของ Long-running Queries ใดๆ ที่เกินเกณฑ์ที่กำหนดได้

SQL Quarantine คืออะไร?

Quarantine หมายถึงการแยกแยก (Isolation) SQL Quarantine เป็นฟีเจอร์ของเวอร์ชัน 19c ที่คุณสามารถใช้เพื่อกำจัดโอเวอร์เฮดของ Runaway Queries Runaway Queries คือคิวรีที่ถูกยกเลิกโดย Resource Manager เพราะเกินขีดจำกัดทรัพยากรหรือเวลารัน (Runtime limit) และใช้ทรัพยากร CPU และ I/O มากมาย

ฟีเจอร์นี้พร้อมใช้งานสำหรับ Exadata (Oracle Database Enterprise Edition on Engineered Systems) และ DBCS/ExaCS (Oracle Database Exadata Cloud Service) เท่านั้น เพื่อทดสอบ ผมตั้งค่าพารามิเตอร์ Underscore และรีสตาร์ทฐานข้อมูลโดยใช้คำสั่งต่อไปนี้:

Alter system set "_exadata_feature_on"=true scope=spfile;

Long-running Queries เกี่ยวกับอย่างไร?

Database Resource Manager (DBRM) เป็นกระบวนการพื้นหลัง (Background process) ที่ให้คุณยกเลิกคำสั่ง SQL ที่เกินเกณฑ์การใช้ทรัพยากรบางอย่าง เช่น I/O และ CPU มันยังสามารถยกเลิกคิวรีใดๆ ที่เกินเกณฑ์เวลารันสูงสุดได้

แผนการดำเนินการ SQL (SQL Execution Plan) และคำสั่ง SQL ที่ถูกทำเครื่องหมายและเกินขีดจำกัด จะถูก Quarantine (แยกแยก) ซึ่งหมายความว่า หาก SQL เดียวกันรันอีกครั้ง โดยใช้แผนการดำเนินการเดียวกัน คำสั่ง SQL จะถูกยกเลิกและแสดงข้อผิดพลาดต่อไปนี้:

error: ORA-56955: quarantined plan used.

เมื่อข้อผิดพลาดเหล่านี้เกิดขึ้น Object Quarantine จะแยก Object ที่ทำให้เกิดข้อผิดพลาด และติดตามเพื่อดูผลกระทบต่อส่วนที่เหลือของฐานข้อมูล

ผมไม่ได้พูดถึง Tables และ Indexes แต่ถึง Objects ที่ Oracle สามารถ Quarantine ได้ รวมถึง Sessions, Processes, SGA Transactions และ Library Cache

ตอนนี้คุณสามารถยกเลิกหรือยกเลิกคิวรี SQL ใดๆ ที่รันนานกว่าเกณฑ์เวลาที่กำหนด ได้แล้ว เช่นตัวอย่างต่อไปนี้:

ป้องกัน Runaway Queries ด้วย Oracle 19c SQL Quarantine

รูปที่ 1: Runaway SQL Statement แหล่งภาพ: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

ค้นหาเกี่ยวกับ Quarantined Objects

ตาม https://dbaparadise.com/2020/01/everything-you-need-to-know-about-quarantined-objects/: เพื่อรับข้อมูลเกี่ยวกับ Quarantined Objects คุณสามารถสืบค้นมุมมอง V$QUARANTINE และ V$QUARANTINE_SUMMARY ซึ่งบอกประเภทของ Object, ที่อยู่หน่วยความจำของ Object, ORA- error จริง และวันที่เวลาของ Error

ตามตัวอย่างต่อไปนี้ คุณสามารถสมมติได้ว่า การใช้งาน CPU ในเซิร์ฟเวอร์ดูเหมือนการตอบสนองของ Runaway Queries เมื่อรัน เราสามารถเห็นได้ว่ามี 3 คิวรีรันพร้อมกัน ใช้ CPU เกือบ 100% ของความจุ

รูปที่ 2: CPU ที่ถูกใช้โดย Runaway SQL แหล่งภาพ: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

SQL Quarantine

คุณสามารถใช้ SQL Quarantine เพื่อกำจัดโอเวอร์เฮดของ Runaway Queries เมื่อ Resource Manager ตรวจพบคำสั่ง SQL ที่เกินขีดจำกัดทรัพยากรหรือเวลารัน แผนการดำเนินการ SQL ที่ใช้โดยคำสั่งนั้นจะถูก Quarantine

หากคำสั่ง SQL เดียวกันดำเนินการโดยใช้แผน SQL เดียวกัน มันจะถูกยกเลิกทันที นี่ช่วยลดการใช้ทรัพยากรระบบอย่างมีนัยสำคัญ ในรูปต่อไปนี้ คุณสามารถเห็นการใช้งานสูงเมื่อคิวรีไม่กี่ตัวรัน อย่างไรก็ตาม ด้วยการยกเลิกก่อนการดำเนินการ พวกมันไม่ได้ใช้ทรัพยากรระบบอีกต่อไปหลังจากถูก Quarantine

รูปที่ 3: CPU ที่ประหยัดได้จาก SQL Quarantine แหล่งภาพ: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

ขั้นตอนการใช้ฟีเจอร์ Quarantine

มาเช็คฟีเจอร์และวิธีการทำงานกัน

ขั้นแรก คุณต้องตั้งค่าฐานข้อมูลเหมือนคุณทำงานบน Exadata

alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

จากนั้น คุณต้องทำตามขั้นตอนต่อไปนี้เพื่อตั้งค่า Resource Manager:

สร้าง Pending Area:

begin dbms_resource_manager.create_pending_area(); end; /

สร้าง Resource Consumer Group หนึ่งหรือหลายกลุ่ม:

begin dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'. SQL_LIMIT',COMMENT=>'consumer group'); end; /

สร้าง 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; /

สร้าง Resource Plan Directives กลุ่ม CANCEL_SQL มีอยู่แล้วโดยค่าเริ่มต้น:

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; /

ตรวจสอบและส่ง Pending Area สำหรับ Plan, Consumer Group และ Directives:

begin dbms_resource_manager.validate_pending_area(); end; /
begin dbms_resource_manager.submit_pending_area(); end; /

ที่นี่คุณต้องมอบสิทธิ์ (Grants) และมอบ Consumer Groups ให้ผู้ใช้

สร้าง Pending Area สำหรับ Privileges, Roles และ Assigned Users:
```
begin dbms_resource_manager.create_pending_area(); end; /
```
มอบสิทธิ์ Switch สำหรับ Resource Consumer Groups ให้ Users หรือ Roles
```
begin dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false); end; /
```

มอบ Users ให้ Resource Consumer Groups

begin dbms_resource_manager.set_initial_consumer_group('DBA1','SQL_LIMIT'); end; /

ตรวจสอบและส่ง Pending Areas

begin dbms_resource_manager.validate_pending_area(); end; /
begin dbms_resource_manager.submit_pending_area(); end; /

ตอนนี้ คุณต้องอัปเดต Plan และส่ง 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; /

ขั้นตอนต่อไป

ขั้นตอนข้างต้นเสร็จสิ้นการตั้งค่า Resource Manager หลังจากเสร็จสิ้น ทั้งสิ่งที่คุณต้องทำคือมอบ Plan นี้ให้อินสแตนซ์:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;

ล็อกอินด้วยผู้ใช้ DBA1 และออกคิวรีที่จะเกินเกณฑ์เวลาที่ผ่านไป (Elapsed time) 10 วินาที ที่กำหนดใน Resource Plan

คุณต้องรันคำสั่งเป็นผู้ใช้ DBA1 และ DBA1 ต้องมีสิทธิ์เข้าถึง 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

สังเกตว่าในกรณีนี้ Resource Manager ยกเลิกการดำเนินการด้วยข้อผิดพลาด ORA-00040

คุณหา SQL_ID ของคำสั่งได้ไหม? มันคือ 3hdkutq4krg4c

สร้าง SQL Quarantine

คุณสามารถใช้แพ็กเกจ DBMS_SQLQ เพื่อสร้างการตั้งค่า Quarantine สำหรับแผนการดำเนินการของคำสั่ง SQL ที่ต้องการ Quarantine

คุณสามารถใช้ SQL Text หรือ SQL_ID ของคำสั่งที่จะ Quarantine ตามตัวอย่างต่อไปนี้:

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; /

หลังจากสร้างการตั้งค่า Quarantine นี้แล้ว คุณสามารถระบุเกณฑ์ Quarantine ได้โดยใช้กระบวนการ DBMS_SQLQ.ALTER_QUARANTINE

BEGIN DBMS_SQLQ.ALTER_QUARANTINE(
  QUARANTINE_NAME => 'SQL_QUARANTINE_3hdkutq4krg4c',
  PARAMETER_NAME => 'ELAPSED_TIME',
  PARAMETER_VALUE => '10'); END; /

ตอนนี้คุณสามารถสืบค้นมุมมอง DBA_SQL_QUARANTINE เพื่อดูว่าคำสั่ง SQL 哪个ถูก Quarantine

ด้วย SQL Quarantine ที่มีอยู่ มันจะไม่ดำเนินการหากคุณพยายามรันคำสั่ง 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

ข้อความข้อผิดพลาดข้างต้นระบุว่าแผนที่ใช้สำหรับคำสั่งนี้เป็นส่วนหนึ่งของแผนที่ถูก Quarantine มันยกเลิกคิวรีเพราะข้ามเกณฑ์จำกัด

หากคุณตรวจสอบมุมมอง V$SQL คุณจะเห็นคอลัมน์ใหม่สองคอลัมน์: sql_quarantine และ 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

สรุป

ฟีเจอร์ SQL Quarantine ช่วยปรับปรุงประสิทธิภาพเพราะมันป้องกันการดำเนินการในอนาคตของคำสั่ง SQL ที่มีค่าใช้จ่ายสูงและถูก Quarantine

ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม คุณยังสามารถคลิก Sales Chat เพื่อแชททันทีและเริ่มการสนทนา