এই ব্লগ পোস্টে SQL কোয়ারান্টাইন কনসেপ্ট পরিচয় করানো হয়েছে। Oracle® Resource Manager ব্যবহার করে, আপনি CPU এবং I/O সহ রিসোর্স ব্যবহারের নিয়ন্ত্রণ এবং সীমাবদ্ধতা করতে পারেন। সবচেয়ে আকর্ষণীয় বিষয় হলো আপনি যেকোনো দীর্ঘ-চলমান কুয়েরি এক্সিকিউশন রোধ করতে পারেন যা একটি সংজ্ঞায়িত থ্রেশহোল্ড অতিক্রম করে।
SQL কোয়ারান্টাইন কী?
কোয়ারান্টাইন মানে বিচ্ছিন্নতা। SQL কোয়ারান্টাইন হলো একটি ১৯c ফিচার যা আপনি রানওয়ে কুয়েরির ওভারহেড দূর করতে ব্যবহার করতে পারেন। রানওয়ে কুয়েরি হলো যেগুলো Resource Manager দ্বারা টার্মিনেট হয় কারণ সেগুলো রিসোর্স বা রান-টাইম লিমিট অতিক্রম করে এবং প্রচুর রিসোর্স, CPU, এবং IO ব্যবহার করে।
এই ফিচার শুধু 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. যখন এই এররগুলো ঘটে, Object Quarantine এরর ঘটানো অবজেক্টকে বিচ্ছিন্ন করে এবং তারপর ডেটাবেসের বাকি অংশে এর প্রভাব মনিটর করে।
আমি টেবিল এবং ইন্ডেক্সের কথা বলছি না, বরং Oracle যে অবজেক্টগুলো কোয়ারান্টাইন করতে পারে সেগুলোর কথা বলছি, যার মধ্যে সেশন, প্রসেস, SGA ট্রানজ্যাকশন, এবং লাইব্রেরি ক্যাশ রয়েছে।
এখন আপনি যেকোনো SQL কুয়েরি টার্মিনেট বা ক্যানসেল করতে পারেন যা একটি নির্দিষ্ট টাইম থ্রেশহোল্ডের চেয়ে বেশি সময় চলে, যেমন নিচের উদাহরণ:
Figure 1: Runaway Sql Statement Image Source: 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-এর প্রায় ১০০% ক্যাপাসিটি ইউটিলাইজ করে।
Figure 2: CPU consumed by Runaway SQL Image Source: https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-19c-5324206.pdf
SQL কোয়ারান্টাইন
আপনি SQL কোয়ারান্টাইন ব্যবহার করতে পারেন রানওয়ে কুয়েরির ওভারহেড দূর করতে। যখন Resource Manager একটি SQL স্টেটমেন্ট ডিটেক্ট করে যা রিসোর্স বা রান-টাইম লিমিট অতিক্রম করেছে, তখন স্টেটমেন্ট দ্বারা ব্যবহৃত SQL এক্সিকিউশন প্ল্যান কোয়ারান্টাইন হয়ে যায়।
যদি একই SQL স্টেটমেন্ট একই SQL প্ল্যান ব্যবহার করে এক্সিকিউট হয়, তবে সেটি তৎক্ষণাৎ টার্মিনেট হয়ে যায়। এটি সিস্টেম রিসোর্স ব্যবহার উল্লেখযোগ্যভাবে কমাতে সাহায্য করে। নিচের ফিগারে, আপনি দেখতে পাবেন কিছু কুয়েরি এক্সিকিউট হলে হাই ইউটিলাইজেশন। যাহোক, এক্সিকিউশন-এর আগে সেগুলো টার্মিনেট করলে, কোয়ারান্টাইন হওয়ার পর সেগুলো আর সিস্টেম রিসোর্স খরচ করে না।
Figure 3: CPU saved by SQL quarantine Image Source: 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 সেট আপ করতে নিচের ধাপগুলো সম্পন্ন করতে হবে:
-
একটি পেন্ডিং এরিয়া তৈরি করুন:
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; /
পরবর্তী ধাপ
উপরোক্ত ধাপগুলো Resource Manager সেটআপ पूरा করে। সেগুলো পুরা হলে, আপনার যা করতে হবে সেটি হলো এই প্ল্যানটিকে একটি ইনস্ট্যান্সে অ্যাসাইন করা:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=NEW_PLAN; DBA1 ইউজার দিয়ে লগ ইন করুন এবং একটি কুয়েরি ইস্যু করুন যা রিসোর্স প্ল্যানে সংজ্ঞায়িত ১০ সেকেন্ডের এলাপসড টাইম থ্রেশহোল্ড অতিক্রম করবে।
আপনাকে স্টেটমেন্টটি 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 কোয়ারান্টাইন তৈরি করুন
আপনি 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 এবং 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 স্টেটমেন্টের ভবিষ্যতে এক্সিকিউশন রোধ করে।
যেকোনো মন্তব্য বা প্রশ্নের জন্য Feedback ট্যাব ব্যবহার করুন। আপনি Sales Chat-এ ক্লিক করেও এখনই চ্যাট শুরু করতে পারেন।
