กักกัน SQL

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

การกักกัน SQL คืออะไร

การกักกันหมายถึงการแยกตัว การกักกัน SQL เป็นคุณลักษณะเวอร์ชัน 19c ที่คุณสามารถใช้เพื่อขจัดโอเวอร์เฮดของการสืบค้นแบบหนีไม่พ้น คิวรีแบบรันอะเวย์คือคิวรีที่สิ้นสุดโดยตัวจัดการทรัพยากรเนื่องจากเกินทรัพยากรหรือขีดจำกัดรันไทม์และทรัพยากร CPU และ IO จำนวนมากที่ใช้ไป

คุณลักษณะนี้มีให้เฉพาะ Exadata (Oracle Database Enterprise Edition บนระบบวิศวกรรม) และ DBCS/ExaCS (Oracle Database Exadata Cloud Service) เพื่อทดสอบสิ่งนี้ ฉันตั้งค่าพารามิเตอร์ขีดล่างและตีกลับฐานข้อมูลโดยใช้คำสั่งต่อไปนี้:

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

แล้วการสืบค้นที่ใช้เวลานานล่ะ

ตัวจัดการทรัพยากรฐานข้อมูล (DBRM) เป็นกระบวนการพื้นหลังที่ให้คุณยุติคำสั่ง anSQL ที่เกินเกณฑ์การใช้ทรัพยากรบางอย่าง เช่น IO และ CPU นอกจากนี้ยังสามารถยุติการสืบค้นใดๆ ที่เกินขีดจำกัดรันไทม์สูงสุดได้

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

  error: ORA-56955: quarantined plan used.

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

ฉันไม่ได้หมายถึงตารางและดัชนีแต่หมายถึงออบเจ็กต์ที่ Oracle สามารถกักบริเวณได้ ซึ่งรวมถึงเซสชัน กระบวนการ ธุรกรรม SGA และแคชของไลบรารี

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

รูปที่ 1:คำสั่ง Runaway SQL ที่มาของรูปภาพ:https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

ค้นหาเกี่ยวกับวัตถุที่ถูกกักกัน

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

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

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

การกักกัน SQL

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

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

รูปที่ 3:CPU ถูกบันทึกโดย SQL quarantine ที่มาของรูปภาพ:https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

ขั้นตอนในการใช้ฟีเจอร์กักบริเวณ

มาตรวจสอบคุณสมบัติและวิธีการทำงานกัน

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

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

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

การสร้างพื้นที่รอดำเนินการ:
```
  begin
  dbms_resource_manager.create_pending_area();
  end;
  /
```
สร้างกลุ่มผู้บริโภคทรัพยากรอย่างน้อยหนึ่งกลุ่ม:
```
 begin
 dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'.   SQL_LIMIT',COMMENT=>'consumer group');
 end;
 /
```

สร้างแผนทรัพยากร:

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

สร้างคำสั่งแผนทรัพยากร กลุ่ม 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;
 /

ตรวจสอบและส่งพื้นที่รอดำเนินการสำหรับแผน กลุ่มผู้บริโภค และคำสั่ง:
```
 begin
 dbms_resource_manager.validate_pending_area();
 end;
 /
 begin
 dbms_resource_manager.submit_pending_area();
 end;
 /
```

ที่นี่คุณต้องมอบหมายทุนและกำหนดกลุ่มผู้บริโภคให้กับผู้ใช้

สร้างพื้นที่รอดำเนินการสำหรับสิทธิ์ บทบาท และผู้ใช้ที่ได้รับมอบหมาย:
```
  begin
  dbms_resource_manager.create_pending_area();
  end;
  /
```
ให้สิทธิ์ในการเปลี่ยนสำหรับกลุ่มผู้ใช้ทรัพยากรให้กับผู้ใช้หรือบทบาท
```
   begin
   dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false);
   end;
   /
```
กำหนดผู้ใช้ให้กับกลุ่มผู้บริโภคทรัพยากร
```
 begin
 dbms_resource_manager.set_initial_consumer_group('DBA1','SQL_LIMIT');
 end;
 /
```

ตรวจสอบและส่งพื้นที่ที่รอดำเนินการ

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

ตอนนี้ คุณต้องอัปเดตแผนและต้องส่งพื้นที่รอดำเนินการอีกครั้ง

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

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

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

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN;

เข้าสู่ระบบด้วยผู้ใช้ DBA1 และออกแบบสอบถามที่จะเกินขีดจำกัดเวลาที่ผ่านไป 10 วินาทีที่กำหนดไว้ในแผนทรัพยากร

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

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

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

สร้าง SQL Quarantine

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

คุณสามารถใช้ข้อความ SQL หรือ SQL_ID ของคำสั่งเพื่อกักกัน ดังแสดงในตัวอย่างต่อไปนี้:

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

หลังจากที่คุณสร้างการกำหนดค่าการกักกันนี้ คุณสามารถระบุเกณฑ์การกักเก็บได้โดยใช้ 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 ใดถูกกักกัน

ด้วย 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

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

หากคุณเลือก V$SQL มุมมอง คุณเห็นสองคอลัมน์ใหม่:sql_quarantine และหลีกเลี่ยง_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 ที่ถูกกักกันซึ่งมีค่าใช้จ่ายสูง

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