Category: Performance
Oracle DB Version: 19.3.0.0
با استفاده از Resource Manager، میتوانیم SQLها را از نظر مصرف منابع، محدود کنیم. مثلا به این صورت که SQLهایی که منابع بیشتری نسبت به thresholdهای ست شده استفاده کنند، توسط Resource Manger خاتمه یابند. تا قبل از نسخه 19c، وقتی یک SQL توسط Resource Manger متوقف میشد، از اجرای مجدد آن ممانعت به عمل نمی آمد؛ فقط اگر مانند دفعه قبل، منابع بیشتری نسبت به thresholdهای ست شده استفاده میکرد، باز هم توسط Resource Manger متوقف میشد. که این موضوع باعث میشود که منابع سیستم هدر رود، چون آن دستور SQL مجددا اجرا شده و برای اجرا از منابع سیستم استفاده کرده است. ولی از اوراکل 19c ، میتوانیم با استفاده از SQL Quarantine ، از اجرای مجدد SQLهایی که توسط Resource Manger خاتمه یافته اند، ممانعت به عمل آوریم.
به طور مثال فرض کنید در Resource Manger یک threshold ست شده است به این صورت که اگر زمان اجرای یک دستور SQL برای یک یوزر بیشتر از 3 ثانیه شد، آن SQL متوقف شود.
یک دستور SQL با نام SQL1 اجرا میشود. اگر SQL1 بیشتر از 3 ثانیه طول بکشد، توسط Resource Manger خاتمه می یابد.
بعد از آن SQL Quarantine یک quarantine با نام Quarantine1 برای SQL1 ایجاد میکند و execution time مساوی 3 ثانیه را به عنوان threshold برای Quarantine1 ست میکند.
اگر SQL1 مجددا با همان Execution Plan اجرا شود و threshold ست شده در Resource Manager نیز همچنان 3 ثانیه باشد، Quarantine1 اجازه نمیدهد که SQL1 اجرا شود، زیرا تشخیص داده است که SQL1 در نهایت توسط Resource Manager متوقف میشود.
اگر در Resource Manager، مقدار Execution Time به 2 ثانیه تغییر پیدا کند. وقتی SQL1 دوباره با همان execution plan اجرا شود، مجددا Quarantine1 اجازه اجرا به آن نمیدهد، زیرا تشخیص داده که SQL1 در نهایت توسط Resource Manager متوقف میشود، چون اجرای آن حداقل 3 ثانیه طول میکشد.
اگر در Resource Manager، مقدار Execution Time به 10 ثانیه تغییر پیدا کند. وقتی SQL1 دوباره با همان Execution Plan اجرا شود، Quarantine1 از اجرای آن ممانعتی به عمل نمی آورد، زیرا تشخیص داده است که SQL1 حداقل 3 ثانیه طول میکشد که اجرا شود، اما این احتمال وجود دارد که تا قبل از 10 ثانیه تمام شود.
مثالی از ایجاد شدن Quarantine براساس thresholdهای ست شده در Resource Manager
مثال زیر نشان میدهد که چگونه execution plan یک دستور SQL وقتی از thresholdهای ست شده در Resource Manger فراتر برود، قرنطینه میشود:
1. با استفاده از Resource Manager مشخص میکنیم که Execution Time برای SQLهایی که با استفاده از یوزر HR اجرا شوند 3 ثانیه باشد. برای انجام این کار مراحل زیر را طی کنید:
1.1. ایجاد یک consumer group با نام TEST_RUNAWAY_GROUP
1.2. اختصاص دادن یوزر HR به TEST_RUNAWAY_GROUP
1.3. ایجاد یک resource plan با نام LIMIT_RESOURCE که حداکثر زمان اجرا را برای SQLهای اجرا شده 3 ثانیه در نظر بگیرد.
1.4. تخصیص LIMIT_RESOURCE به TEST_RUNAWAY_GROUP
|
connect / as sysdba
begin
-- Create a pending area
dbms_resource_manager.create_pending_area();
-- Create a consumer group 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.create_consumer_group (
consumer_group => 'TEST_RUNAWAY_GROUP',
comment => 'This consumer group limits execution time for SQL statements'
);
-- Map the sessions of the user 'HR' to the consumer group 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.set_consumer_group_mapping(
attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
value => 'HR',
consumer_group => 'TEST_RUNAWAY_GROUP'
);
-- Create a resource plan 'LIMIT_RESOURCE'
dbms_resource_manager.create_plan(
plan => 'LIMIT_RESOURCE',
comment => 'Terminate SQL statements after exceeding total execution time'
);
-- Create a resource plan directive by assigning the 'LIMIT_RESOURCE' plan to
-- the 'TEST_RUNAWAY_GROUP' consumer group
-- Specify the execution time limit of 3 seconds for SQL statements belonging to
-- the 'TEST_RUNAWAY_GROUP' group
dbms_resource_manager.create_plan_directive(
plan => 'LIMIT_RESOURCE',
group_or_subplan => 'TEST_RUNAWAY_GROUP',
comment => 'Terminate SQL statements when they exceed the' ||
'execution time of 3 seconds',
switch_group => 'CANCEL_SQL',
switch_time => 3,
switch_estimate => false
);
-- Allocate resources to the sessions not covered by the currently active plan
-- according to the OTHER_GROUPS directive
dbms_resource_Manager.create_plan_directive(
plan => 'LIMIT_RESOURCE',
group_or_subplan => 'OTHER_GROUPS',
comment => 'Ignore'
);
-- Validate and submit the pending area
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
-- Grant switch privilege to the 'HR' user to switch to the 'TEST_RUNAWAY_GROUP'
-- consumer group
dbms_resource_manager_privs.grant_switch_consumer_group('HR',
'TEST_RUNAWAY_GROUP',
false);
-- Set the initial consumer group of the 'HR' user to 'TEST_RUNAWAY_GROUP'
dbms_resource_manager.set_initial_consumer_group('HR',
'TEST_RUNAWAY_GROUP');
end;
/
-- Set the 'LIMIT_RESOURCE' plan as the top plan for the Resource Manager
alter system set RESOURCE_MANAGER_PLAN = 'LIMIT_RESOURCE' scope = memory;
|
2. با یوزر HR به دیتابیس متصل شوید و دستور زیر را اجرا کنید. زمان اجرای این دستور بیشتر از 3 ثانیه است.
|
select count(*)
from employees emp1, employees emp2,
employees emp3, employees emp4,
employees emp5, employees emp6,
employees emp7, employees emp8,
employees emp9, employees emp10
where rownum <= 100000000;
|
این SQL توسط Resource Manger متوقف شده و خطای زیر به کاربر نمایش داده میشود:
ORA-00040: active time limit exceeded - call aborted |
3. وقتی این SQL قرنطینه شد، اگر مجددا با همان plan اجرا شود، با خطای زیر مواجه میشوید:
ORA-56955: quarantined plan used |
4. در viewهای زیر میتوانید آماری را در رابطه با SQLهای قرنطینه شده مشاهده کنید:
|
select sql_text, plan_hash_value, avoided_executions, sql_quarantine
from v$sql
where sql_quarantine is not null;
|
خروجی کوئری بالا به شکل زیر است:
|
SQL_TEXT PLAN_HASH_VALUE AVOIDED_EXECUTIONS SQL_QUARANTINE
---------------------------- -------------------------- ------------------------------ ---------------------------------
select count(*) 3719017987 1 SQL_QUARANTINE_3uuhv1u5day0yf6ed7f0c
from employees emp1,
employees emp2, employees emp3,
employees emp4, employees emp5,
employees emp6, employees emp7,
employees emp8, employees emp9,
employees emp10
where rownum <= 100000000;
|
-
dba_sql_quarantine: این view اطلاعاتی درباره quarantine configurationهای ایجاد شده به ما میدهد:
|
select sql_text, name, plan_hash_value, last_executed, enabled
from dba_sql_quarantine;
|
خروجی کوئری بالا به شکل زیر است:
|
SQL_TEXT NAME PLAN_HASH_VALUE LAST_EXECUTE ENABLED
------------------- ----------------------------------- --------------- ---------------------------- -------
select count(*) SQL_QUARANTINE_3uuhv1u5day0yf6ed7f0c 3719017987 14-JAN-19 02.19.01.000000 AM YES
from employees emp1,
employees emp2,
employees emp3,
employees emp4,
employees emp5,
employees emp6,
employees emp7,
employees emp8,
employees emp9,
employees emp10
where rownum <= 100000000;
|
ایجاد Quarantine برای یک دستور SQL
با استفاده از پکیج DBMS_SQLQ میتوانیم برای execution plan یک دستور SQL یک Quarantine ایجاد کنیم و برای آن thresholdهایی مشابه thresholdهای Resource Manager ست کنیم.
در مثال زیر برای یکی از Execution planهای یک دستور SQL یک quarantine ایجاد شده است:
|
DECLARE
quarantine_config VARCHAR2 (40);
BEGIN
quarantine_config :=
DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID (
SQL_ID => '28bu8g33sbm4v',
PLAN_HASH_VALUE => '3347434651');
DBMS_OUTPUT.put_line (quarantine_config);
END;
|
اگر execution plan را مشخص نکرده یا NULL ست کنیم، quarantine برای همه Execution Planهای آن SQL اعمال میشود، بجز آنهایی که به صورت جداگانه برایشان یک quarantine تعریف شده باشد. در مثال زیر برای همه execution planهای یک SQL که SQL ID آن مساوی 28bu8g33sbm4v است، یک quarantine ایجاد شده است.
|
DECLARE
quarantine_config VARCHAR2 (40);
BEGIN
quarantine_config :=
DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID (SQL_ID => '28bu8g33sbm4v');
END;
|
در مثال زیر هم برای همه execution planهای دستور 'select count(*) from emp' یک quarantine ایجاد شده است.
|
DECLARE
quarantine_config VARCHAR2 (40);
BEGIN
quarantine_config :=
DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT (
SQL_TEXT => TO_CLOB ('select count(*) from emp'));
END;
|
نتیجه اجرای دستورات بالا نام Quarantine که ایجاد شده است، میباشد. که در مراحل بعدی موقع ست کردن threshold ، به آن نام نیاز میباشد.
Result: SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b |
مشخص کردن Threshold برای Quarantine ایجاد شده
بعد از ایجاد یکQuarantine برای execution plan یک دستور SQL، میتوانیم با استفاده از پروسیجر DBMS_SQLQ.ALTER_QUARANTINE برای آن quarantine، threshold تعیین کنیم. وقتی هریک از thresholdهای ست شده در Resource Manager کوچکتر یا مساوی thresholdهای مشخص شده برای یک Quarantine باشند، به آن SQL اجازه اجرا داده نمیشود.
میتوانیم برای Resourceهای زیر در quarantine configuration ، threshold تعریف کنیم:
- CPU time
- Elapsed time
- I/O in megabytes
- Number of physical I/O requests
- Number of logical I/O requests
در مثال زیر برای یک Quarantine که در مرحله قبل ایجاد کردیم ، thresholdهایی برای CPU time و Elapsed time ست می کنیم:
|
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE (
QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b',
PARAMETER_NAME => 'CPU_TIME',
PARAMETER_VALUE => '5');
DBMS_SQLQ.ALTER_QUARANTINE (
QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b',
PARAMETER_NAME => 'ELAPSED_TIME',
PARAMETER_VALUE => '4');
END;
|
وقتی دستور SQL با همان plan مشخص شده در SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b اجرا شود. اگر thresholdهای ست شده در Resource Manager، برای CPU time ، 5 ثانیه یا کمتر و برای Elapse time، 4 ثانیه یا کمتر باشد، از اجرای آن SQL ممانعت به عمل می آید.
بدست آوردن اطلاعات در مورد Thresholdهای ست شده برای یک Quarantine
با استفاده از فانکشن DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE میتوانیم از یک Quarantine که قبلا ایجاد شده است، کوئری بگیریم.
با استفاده از مثال زیر میتوانیم متوجه شویم که مقدار threshold ست شده برای CPU time در SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b چقدر است:
|
DECLARE
quarantine_config_setting_value VARCHAR2 (30);
BEGIN
quarantine_config_setting_value :=
DBMS_SQLQ.GET_PARAM_VALUE_QUARANTINE (
QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b',
PARAMETER_NAME => 'CPU_TIME');
DBMS_OUTPUT.put_line (quarantine_config_setting_value);
END;
Result: 5
|
پاک کردن thresholdهای ست شده برای یک Quarantine
در مثال زیر threshold ست شده برای CPU time، از SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b حذف شده است:
|
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE (
QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b',
PARAMETER_NAME => 'CPU_TIME',
PARAMETER_VALUE => DBMS_SQLQ.DROP_THRESHOLD);
END;
|
فعال یا غیرفعال کردن یک Quarantine
یک Quarantine به صورت پیش فرض وقتی ایجاد میشود enable است. در مثال زیر یک قرنطینه با نام SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b که قبلا ایجاد شده بود، disable شده است:
|
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE (
QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b',
PARAMETER_NAME => 'ENABLED',
PARAMETER_VALUE => 'NO');
END;
|
پاک کردن یک Quarantine
Quarantineهایی که مورد استفاده قرار نگرفته اند، به صورت اتومات بعد از 53 هفته پاک میشوند، که میتوانیم با استفاده از پروسیجر زیر این تنظیم اتومات را disable کنیم.
|
BEGIN
DBMS_SQLQ.ALTER_QUARANTINE (
QUARANTINE_NAME => 'SQL_QUARANTINE_3u0anzbpmfkmdc785cc9b',
PARAMETER_NAME => 'AUTOPURGE',
PARAMETER_VALUE => 'NO');
END;
|
همچنین میتوانیم با استفاده از پروسیجر زیر یک Quarantine را به صورت دستی پاک کنیم.
|
BEGIN
DBMS_SQLQ.DROP_QUARANTINE ('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');
END;
|
انتقال یک Quarantine از یک دیتابیس به دیتابیس دیگر
با استفاده از پکیج DBMS_SQLQ میتوانیم یک Quarantine را از یک دیتابیس به دیتابیس دیگر انتقال دهیم. مثلا در یک دیتابیس تستی، یک Quarantine ایجاد و تستهای لازم انجام شده است. با توجه به اینکه عملکرد خوبی داشته است، میخواهیم آن را به دیتابیس اصلی منتقل کنیم. مثال زیر مراحل انجام این کار را نشان میدهد:
1. با استفاده از SQL*Plus با یک یوزر که دسترسی admin دارد، به دیتابیس مبدا متصل میشویم و با اجرای دستور زیر یک staging table ایجاد میکنیم:
|
BEGIN
DBMS_SQLQ.CREATE_STGTAB_QUARANTINE (
staging_table_name => 'TBL_STG_QUARANTINE');
END;
|
2. کانفیگ های مربوط به SQL Quarantine را که میخواهیم به دیتابیس مقصد منتقل کنیم با استفاده از دستور زیر به جدولی که در مرحله قبل ساخته ایم منتقل میکنیم. در مثال زیر همه quarantine هایی که نام آنها با QUARANTINE_CONFIG شروع شده است به جدول TBL_STG_QUARANTINE که در مرحله قبل ایجاد شده است اضافه میشوند:
|
DECLARE
quarantine_configs NUMBER;
BEGIN
quarantine_configs :=
DBMS_SQLQ.PACK_STGTAB_QUARANTINE (
staging_table_name => 'TBL_STG_QUARANTINE',
name => 'QUARANTINE_CONFIG_%');
END;
|
3. از جدول TBL_STG_QUARANTINE با استفاه از Data Pump اکسپورت بگیرید.
4. Dump File را از دیتابیس مبدا به مقصد انتقال دهید.
5. در دیتابیس مقصد dump file مربوطه را ایمپورت کنید.
6. با استفاده از SQL*Plus با یک یوزر که دسترسی admin دارد، به دیتابیس مقصد متصل شوید و با اجرای دستور زیر بر اساس staging table که ایمپورت کرده اید، یک quarantine ایجاد کنید:
|
DECLARE
quarantine_configs NUMBER;
BEGIN
quarantine_configs :=
DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE (
staging_table_name => 'TBL_STG_QUARANTINE');
DBMS_OUTPUT.put_line (quarantine_configs);
END;
|
فانکشن DBMS_SQLQ.UNPACK_STGTAB_QUARANTINE به عنوان خروجی تعداد quarantine هایی که در دیتابیس مقصد ایجاد شده است را برمیگرداند.
