Oracle 19c SQL क्वारंटाइन के साथ रनअवे क्वेरी को रोकें

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

SQL क्वारंटाइन क्या है?

क्वारंटाइन का अर्थ है अलगाव। SQL क्वारंटाइन एक संस्करण 19c सुविधा है जिसका उपयोग आप रनअवे क्वेरी के ओवरहेड को खत्म करने के लिए कर सकते हैं। रनअवे क्वेरी वे हैं जो Resource Manager द्वारा समाप्त हो जाती हैं क्योंकि वे संसाधन या रनटाइम सीमा से अधिक हो जाती हैं और बहुत सारे संसाधनों, CPU, और I/O का उपयोग करती हैं।

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

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

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

डेटाबेस रिसोर्स मैनेजर (DBRM) एक बैकग्राउंड प्रक्रिया है जो आपको एक SQL स्टेटमेंट को समाप्त करने देती है जो IO और CPU जैसे कुछ संसाधन उपयोग सीमा से अधिक हो जाता है। यह उन क्वेरी को भी समाप्त कर सकता है जो अधिकतम रन-टाइम सीमा से अधिक हो जाती हैं।

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

 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: रनअवे SQL द्वारा उपभोग CPU छवि स्रोत: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf

SQL क्वारंटाइन

आप रनअवे क्वेरी के ओवरहेड को खत्म करने के लिए 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; 

फिर, आपको Resource Manager को सेटअप करने के लिए निम्नलिखित चरणों को पूरा करने की आवश्यकता है:

1. एक पेंडिंग क्षेत्र बनाना:

    begin dbms_resource_manager.create_pending_area(); end; / 

2. एक या अधिक रिसोर्स कंज्यूमर ग्रुप बनाएं:

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

3. रिसोर्स प्लान बनाएं:

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

4. रिसोर्स प्लान निर्देश बनाएं। 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; / 

5. प्लान, कंज्यूमर ग्रुप, और निर्देशों के लिए पेंडिंग क्षेत्र को मान्य और सबमिट करें:

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

यहां आपको विशेषाधिकार प्रदान करने और उपयोगकर्ताओं को कंज्यूमर ग्रुप असाइन करने की आवश्यकता है।

6. विशेषाधिकारों, भूमिकाओं, और असाइन किए गए उपयोगकर्ताओं के लिए एक पेंडिंग क्षेत्र बनाएं:

    begin dbms_resource_manager.create_pending_area(); end; / 

7. उपयोगकर्ताओं या भूमिकाओं को रिसोर्स कंज्यूमर ग्रुप के लिए स्विच विशेषाधिकार प्रदान करें।

    begin dbms_resource_manager_privs.grant_switch_consumer_group('DBA1','SQL_LIMIT',false); end; / 

8. उपयोगकर्ताओं को रिसोर्स कंज्यूमर ग्रुप असाइन करें।

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

9. पेंडिंग क्षेत्रों को मान्य और सबमिट करें।

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

10. अब, आपको प्लान को अपडेट करना होगा और फिर से पेंडिंग क्षेत्र सबमिट करना होगा।

    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 सेटअप को पूरा करते हैं। उन्हें पूरा करने के बाद, आपको बस इस प्लान को एक इंस्टेंस को असाइन करना होगा:

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 क्वारंटाइन बनाएं

आप एक SQL स्टेटमेंट के निष्पादन योजना के लिए क्वारंटाइन कॉन्फ़िगरेशन बनाने के लिए DBMS_SQLQ पैकेज का उपयोग कर सकते हैं जिसे क्वारंटाइन करने की आवश्यकता है।

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

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