एसक्यूएल संगरोध

यह ब्लॉग पोस्ट SQL संगरोध की अवधारणा का परिचय देता है। Oracle® संसाधन प्रबंधक का उपयोग करके, आप CPU और I/O जैसे अन्य संसाधनों के उपयोग को विनियमित और सीमित कर सकते हैं। सबसे दिलचस्प तथ्य यह है कि आप किसी भी लंबे समय से चल रही क्वेरी के निष्पादन को रोक सकते हैं जो एक निर्धारित सीमा से अधिक है।

एसक्यूएल क्वारंटाइन क्या है?

क्वारंटाइन का मतलब आइसोलेशन होता है। SQL संगरोध एक संस्करण 19c सुविधा है जिसका उपयोग आप भगोड़ा प्रश्नों के ऊपरी भाग को समाप्त करने के लिए कर सकते हैं। भगोड़ा क्वेरी वे हैं जो संसाधन प्रबंधक द्वारा समाप्त कर दी जाती हैं क्योंकि वे संसाधन या रनटाइम सीमा से अधिक हो जाती हैं और संसाधनों, सीपीयू और आईओ का उपयोग करती हैं।

यह सुविधा केवल Exadata (Oracle Database Enterprise Edition onEngineered Systems) और DBCS/ExaCS (Oracle Database Exadata Cloud Service) के लिए उपलब्ध है। इसका परीक्षण करने के लिए, मैंने अंडरस्कोर पैरामीटर सेट किया और निम्नलिखित कमांड का उपयोग करके डेटाबेस को बाउंस किया:

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

लंबे समय तक चलने वाली क्वेरी के बारे में क्या?

डाटाबेस रिसोर्स मैनेजर (डीबीआरएम) एक पृष्ठभूमि प्रक्रिया है जो आपको एक एसक्यूएल स्टेटमेंट को समाप्त करने देती है जो आईओ और सीपीयू जैसे कुछ संसाधन उपयोग थ्रेसहोल्ड से अधिक है। यह किसी भी क्वेरी को समाप्त कर सकता है जो अधिकतम रन-टाइम थ्रेशोल्ड से अधिक है।

कोई भी चिह्नित SQL निष्पादन योजना और SQL कथन जो सीमा से अधिक है, संगरोध है, जिसका अर्थ है कि यदि वही SQL फिर से चलता है, उसी निष्पादन योजना का उपयोग करते हुए, SQLstatement को समाप्त कर दिया जाएगा और निम्न त्रुटि को फ्लैश करेगा:

  error: ORA-56955: quarantined plan used.

जब ये त्रुटियां होती हैं, तो ऑब्जेक्ट क्वारंटाइन उस ऑब्जेक्ट को अलग कर देता है जिसके कारण त्रुटि हुई और फिर बाकी डेटाबेस पर इसके प्रभाव को देखने के लिए इसकी निगरानी करता है।

मैं टेबल और इंडेक्स की बात नहीं कर रहा हूं, बल्कि उन ऑब्जेक्ट्स की बात कर रहा हूं, जिन्हें Oracle क्वारंटाइन कर सकता है, जिसमें सेशन, प्रोसेस, SGA ट्रांजैक्शन और लाइब्रेरी कैश शामिल हैं।

अब आप किसी भी SQL क्वेरी को समाप्त या रद्द कर सकते हैं जो किसी दिए गए समय सीमा से अधिक समय तक चलती है, जैसे कि निम्न उदाहरण:

चित्र 1:भगोड़ा 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:रनवे एसक्यूएल द्वारा सीपीयू की खपत छवि स्रोत:https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

एसक्यूएल क्वारंटाइन

भगोड़ा प्रश्नों के ऊपरी हिस्से को खत्म करने के लिए आप SQL संगरोध का उपयोग कर सकते हैं। जब रिसोर्स मैनेजर किसी संसाधन या रन-टाइम सीमा से अधिक SQL स्टेटमेंट का पता लगाता है, तो स्टेटमेंट द्वारा उपयोग किया जाने वाला SQL एक्ज़ीक्यूशनप्लान क्वारंटाइन हो जाता है।

यदि समान SQL कथन समान SQL योजना का उपयोग करके निष्पादित होता है, तो यह तुरंत समाप्त हो जाता है। यह सिस्टम संसाधन उपयोग को महत्वपूर्ण रूप से कम करने में मदद करता है। निम्न आकृति में, जब कुछ प्रश्न निष्पादित होते हैं, तो आप उच्च उपयोग देख सकते हैं। हालांकि, निष्पादन से पहले उन्हें समाप्त करके, वे क्वारंटाइन होने के बाद सिस्टम संसाधनों का उपभोग नहीं करते हैं।

चित्र 3:SQL क्वारंटाइन द्वारा सहेजा गया CPU छवि स्रोत: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

ध्यान दें कि इस मामले में, संसाधन प्रबंधक ने ORA-00040 . के साथ निष्पादन को समाप्त कर दिया है त्रुटि।

क्या आप कथन का SQL_ID ढूंढ सकते हैं? यह 3hdkutq4krg4c है ।

SQL संगरोध बनाएं

आप 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 संगरोध के साथ, यदि आप समान 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 औरपरहेज_निष्पादन :

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 संगरोध सुविधा प्रदर्शन को बेहतर बनाने में मदद करती है क्योंकि यह एक महंगे, संगरोधित SQL कथन के भविष्य के निष्पादन को रोकती है।

कोई टिप्पणी करने या प्रश्न पूछने के लिए प्रतिक्रिया टैब का उपयोग करें। आप विक्रय चैट . पर भी क्लिक कर सकते हैं अभी चैट करने और बातचीत शुरू करने के लिए।