BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'CG1',
COMMENT => 'test');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER,
VALUE => 'TEST',
CONSUMER_GROUP => 'CG1');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'PLAN1',
COMMENT => 'test');
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'PLAN1',
GROUP_OR_SUBPLAN => 'CG1',
COMMENT => 'test',
MGMT_P1 => 75);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'PLAN1',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'test',
MGMT_P1 => 10);
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/
BEGIN
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'PLAN1';
select * from DBA_RSRC_CONSUMER_GROUPS;
select * from DBA_RSRC_GROUP_MAPPINGS;
select * from DBA_RSRC_PLANS;
select * from DBA_RSRC_PLAN_DIRECTIVES;
CREATE USER 'francis'@'localhost' IDENTIFIED BY 'francis';
GRANT ALL ON test.* TO 'francis'@'localhost'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;
(8.0)
-- CAP_SYS_NICEケイパビリティの設定
systemctl edit mysqld
[Service]
AmbientCapabilities=CAP_SYS_NICE
systemctl restart mysqld
-- リソースグループの作成
create resource group rg1 type = user vcpu = 0 thread_priority = 19;
-- リソースグループの確認
select * from information_schema.resource_groups\G
-- リソースグループの割当
set resource group rg1;
-- リソースグループの有効化
alter resource group rg1 enable;
-- リソースグループの無効化
alter resource group rg1 disable;
-- リソースグループの削除
drop resource group rg1 force;
CREATE DATABASE test3 with owner = user1
CONNECTION LIMIT = 100;
--- Create a resource pool for production processing
--- and set limits.
USE master;
GO
CREATE RESOURCE POOL pProductionProcessing
WITH
(
MAX_CPU_PERCENT = 100,
MIN_CPU_PERCENT = 50
);
GO
--- Create a workload group for production processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gProductionProcessing
WITH
(
IMPORTANCE = MEDIUM
)
USING pProductionProcessing;
GO
--- Create a resource pool for off-hours processing
--- and set limits.
CREATE RESOURCE POOL pOffHoursProcessing
WITH
(
MAX_CPU_PERCENT = 50,
MIN_CPU_PERCENT = 0
);
GO
--- Create a workload group for off-hours processing
--- and configure the relative importance.
CREATE WORKLOAD GROUP gOffHoursProcessing
WITH
(
IMPORTANCE = LOW
)
USING pOffHoursProcessing;
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
USE master;
GO
CREATE TABLE tblClassificationTimeTable
(
strGroupName sysname not null,
tStartTime time not null,
tEndTime time not null
);
GO
--- Add time values that the classifier will use to
--- determine the workload group for a session.
INSERT into tblClassificationTimeTable VALUES('gProductionProcessing', '6:35 AM', '6:15 PM');
go
CREATE FUNCTION fnTimeClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
/* We recommend running the classifier function code under
snapshot isolation level OR using NOLOCK hint to avoid blocking on
lookup table. In this example, we are using NOLOCK hint. */
DECLARE @strGroup sysname
DECLARE @loginTime time
SET @loginTime = CONVERT(time,GETDATE())
SELECT TOP 1 @strGroup = strGroupName
FROM dbo.tblClassificationTimeTable WITH(NOLOCK)
WHERE tStartTime <= @loginTime and tEndTime >= @loginTime
IF(@strGroup is not null)
BEGIN
RETURN @strGroup
END
--- Use the default workload group if there is no match
--- on the lookup.
RETURN N'gOffHoursProcessing'
END;
GO
ALTER RESOURCE GOVERNOR with (CLASSIFIER_FUNCTION = dbo.fnTimeClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
--リソース プールとワークロード グループの構成を取得
USE master;
SELECT * FROM sys.resource_governor_resource_pools;
SELECT * FROM sys.resource_governor_workload_groups;
GO
--分類子関数が存在し、有効になっていることを確認
--- Get the classifier function Id and state (enabled).
SELECT * FROM sys.resource_governor_configuration;
GO
--- Get the classifer function name and the name of the schema
--- that it is bound to.
SELECT
object_schema_name(classifier_function_id) AS [schema_name],
object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration;
--リソース プールとワークロード グループの現在のランタイム データを取得
SELECT * FROM sys.dm_resource_governor_resource_pools;
SELECT * FROM sys.dm_resource_governor_workload_groups;
GO
--各グループにあるセッションを確認
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time,
CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS g
ON g.group_id = s.group_id
ORDER BY g.name;
GO
--各グループにある要求を確認
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id,
r.start_time, r.command, r.sql_handle, t.text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_resource_governor_workload_groups AS g
ON g.group_id = r.group_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name;
GO
--分類子関数で実行されている要求を確認
SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS g
ON g.group_id = s.group_id
AND 'preconnect' = s.status
ORDER BY g.name;
GO
SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time,
r.command, r.sql_handle, t.text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_resource_governor_workload_groups AS g
ON g.group_id = r.group_id
AND 'preconnect' = r.status
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY g.name;
GO
