达梦数据库参数优化

达梦数据库初始化成功或集群搭建成功之后，调整实例参数可以避免很多使用问题，执行成功需重启数据库才能生效

如下报错 

不能修改或删除聚集索引的列和超过最大连接限制及句句柄个数超上限或系统内存不足

 

 

 

declare
exec_mode int:= 0; --0表示直接执行脚本修改参数，1表示不直接修改参数，打印设置参数的语句，设置为1后，必须调整v_mem_mb和v_cpus
mem_per int:=80; --默认所有的内存归达梦数据库使用，如实际不能100%可用，可以调整此参数
v_mem_mb int:= 16000; --根据机器实际内存调整此参数
v_cpus int:= 8; --根据机器CPU核数调整此参数

tname varchar(100);
MEMORY_POOL int;
MEMORY_N_POOLS int;
MEMORY_TARGET int;
BUFFER INT;
MAX_BUFFER INT;
RECYCLE int;
CACHE_POOL_SIZE int;
BUFFER_POOLS int;
RECYCLE_POOLS int;
SORT_BUF_SIZE int;
SORT_BUF_GLOBAL_SIZE INT;
DICT_BUF_SIZE INT;
HJ_BUF_SIZE INT;
HAGR_BUF_SIZE INT;
HJ_BUF_GLOBAL_SIZE INT;
HAGR_BUF_GLOBAL_SIZE INT;
--SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;
TASK_THREADS INT;
IO_THR_GROUPS INT;
FAST_POOL_PAGES INT :=3000;
FAST_ROLL_PAGES INT :=1000;
CNT INT;

begin
CNT :=0;
if exec_mode=0 then
SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;
end if;

v_mem_mb := v_mem_mb * (mem_per/100.0);

v_mem_mb=round(v_mem_mb,-3);

IF v_mem_mb <= 2000 THEN
goto return_2000;
END IF;

IF v_mem_mb > 512000 THEN
v_mem_mb :=v_mem_mb*0.8;
END IF;

MEMORY_TARGET=round(cast(v_mem_mb * 0.12 as int),-3);

TASK_THREADS :=4;
IO_THR_GROUPS :=4;
IF v_cpus < 8 THEN
TASK_THREADS :=4;
IO_THR_GROUPS :=2;
END IF;

IF v_cpus >= 64 THEN
v_cpus := 64;
TASK_THREADS :=16;
IO_THR_GROUPS :=8;
END IF;




BUFFER := round(cast(v_mem_mb * 0.4 as int),-3);
MAX_BUFFER := BUFFER;

RECYCLE :=cast(v_mem_mb * 0.04 as int);


IF v_mem_mb < 70000 THEN

with t as
(
select rownum rn from dual connect by level <= 100
) ,
t1 as
(
select * from t where rn > 1 minus
select
ta.rn * tb.rn
from
t ta,
t tb
where
ta.rn <= tb.rn
and ta.rn > 1
and tb.rn > 1
)
select top 1 rn into BUFFER_POOLS from t1 where rn > v_mem_mb/800 order by 1;

--设置根据内存情况RECYCLE_POOLS参数
with t as
(
select rownum rn from dual connect by level <= 100
) ,
t1 as
(
select * from t where rn > 1 minus
select
ta.rn * tb.rn
from
t ta,
t tb
where
ta.rn <= tb.rn
and ta.rn > 1
and tb.rn > 1
)
select top 1 rn into RECYCLE_POOLS from t1 where rn > v_mem_mb/800/3 order by 1;


ELSE
BUFFER_POOLS := 101;
RECYCLE_POOLS := 41;
END IF;



--修改内存池
IF v_mem_mb >= 16000 THEN
IF v_mem_mb= 16000 THEN
MEMORY_POOL := 1500;
SORT_BUF_GLOBAL_SIZE := 1000;
MEMORY_N_POOLS := 3;
CACHE_POOL_SIZE := 512;
ELSE
MEMORY_POOL := 2000;
SORT_BUF_GLOBAL_SIZE := 2000;
MEMORY_N_POOLS := 11;
CACHE_POOL_SIZE := 1024;
END IF;

FAST_POOL_PAGES :=9999;
-- SORT_FLAG = 0;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE := 10;
RLOG_POOL_SIZE := 1024;

HJ_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
HAGR_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
HJ_BUF_SIZE :=250;
HAGR_BUF_SIZE :=250;
RECYCLE :=round(RECYCLE,-3);

IF v_mem_mb >= 64000 THEN
FAST_POOL_PAGES :=99999;
FAST_ROLL_PAGES :=9999;
BUFFER :=BUFFER-3000;
MAX_BUFFER :=BUFFER;
CACHE_POOL_SIZE := 2048;
RLOG_POOL_SIZE := 2048;
-- SORT_FLAG = 1;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE=50;
SORT_BUF_GLOBAL_SIZE= cast(v_mem_mb * 0.02 as int);

HJ_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.15625 as int);
HAGR_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.04 as int);
HJ_BUF_SIZE :=512;
HAGR_BUF_SIZE :=512;
MEMORY_N_POOLS := 59;
END IF;

DICT_BUF_SIZE := 50;
HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
SORT_BUF_GLOBAL_SIZE :=round(SORT_BUF_GLOBAL_SIZE,-3);
RECYCLE :=round(RECYCLE,-3);
ELSE
MEMORY_POOL :=GREAT(cast(v_mem_mb * 0.0625 as int),100);
MEMORY_POOL :=round(MEMORY_POOL,-2);
MEMORY_N_POOLS := 1;
CACHE_POOL_SIZE := 200;
RLOG_POOL_SIZE := 256;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 500;
DICT_BUF_SIZE := 50;
-- SORT_FLAG = 0;
SORT_BLK_SIZE=1;

HJ_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
HAGR_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
HJ_BUF_SIZE := GREAT(cast(v_mem_mb * 0.00625 as int),50);
HAGR_BUF_SIZE :=GREAT(cast(v_mem_mb * 0.00625 as int),50);
END IF;

IF exec_mode=0 THEN
--修改cpu相关参数
SP_SET_PARA_VALUE(2,'WORKER_THREADS',v_cpus);
SP_SET_PARA_VALUE(2,'TASK_THREADS',TASK_THREADS);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',IO_THR_GROUPS);


--修改内存池相关参数
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', mem_per);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', MEMORY_POOL);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', MEMORY_N_POOLS);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', MEMORY_TARGET);
--修改内存检测参数为1
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK', 1);

--非DSC环境将ENABLE_FREQROOTS设置为1,注意DM7 v$instance视图没有dsc_role字段,DM7这部分可以删掉
if exists(select 1 from v$instance where dsc_role = 'NULL') then
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS', 1);
end if;


--修改缓冲区相关参数
SP_SET_PARA_VALUE(2,'BUFFER', BUFFER);
SP_SET_PARA_VALUE(2,'MAX_BUFFER', MAX_BUFFER);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', BUFFER_POOLS);
SP_SET_PARA_VALUE(2,'RECYCLE', RECYCLE);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', RECYCLE_POOLS);

--修改fast_pool相关参数
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', FAST_POOL_PAGES);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', FAST_ROLL_PAGES);

--修改HASH相关参数
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', HJ_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', HJ_BUF_SIZE );
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',HAGR_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', HAGR_BUF_SIZE );

--修改排序相关参数
-- SP_SET_PARA_VALUE(2,'SORT_FLAG',SORT_FLAG);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',SORT_BLK_SIZE);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', SORT_BUF_SIZE);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', SORT_BUF_GLOBAL_SIZE);

--修改其他内存参数
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', RLOG_POOL_SIZE);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE', CACHE_POOL_SIZE);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE', DICT_BUF_SIZE);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET', 16384);


--修改实例相关参数
SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR', 1);
SP_SET_PARA_VALUE(2,'SVR_LOG', 0);
SP_SET_PARA_VALUE(2,'TEMP_SIZE', 1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT', 102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER', 0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);

--修改优化器相关参数
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);

--开启并行PURGE
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
--开启手动并行
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
--UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作。
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
--开启SQL 注入HINT功能
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);

ELSE
--修改cpu相关参数
PRINT 'SP_SET_PARA_VALUE(2,''WORKER_THREADS'','||v_cpus||');';
PRINT 'SP_SET_PARA_VALUE(2,''TASK_THREADS'','||TASK_THREADS||');';
PRINT 'SP_SET_PARA_VALUE(2,''IO_THR_GROUPS'','||IO_THR_GROUPS||');';


--修改内存池相关参数
PRINT 'SP_SET_PARA_VALUE(2,''MAX_OS_MEMORY'', '||mem_per||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_POOL'', '||MEMORY_POOL||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_N_POOLS'', '||MEMORY_N_POOLS||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_TARGET'', '||MEMORY_TARGET||');';

--修改缓冲区相关参数
PRINT 'SP_SET_PARA_VALUE(2,''BUFFER'', '||BUFFER||');';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_BUFFER'', '||MAX_BUFFER||');';
PRINT 'SP_SET_PARA_VALUE(2,''BUFFER_POOLS'', '||BUFFER_POOLS||');';
PRINT 'SP_SET_PARA_VALUE(2,''RECYCLE'', '||RECYCLE||');';
PRINT 'SP_SET_PARA_VALUE(2,''RECYCLE_POOLS'', '||RECYCLE_POOLS||');';

--修改fast_pool相关参数
PRINT 'SP_SET_PARA_VALUE(2,''FAST_POOL_PAGES'', '||FAST_POOL_PAGES||');';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_ROLL_PAGES'', '||FAST_ROLL_PAGES||');';

--修改内存检测参数为1
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_MAGIC_CHECK'', 1);';
--非DSC环境将ENABLE_FREQROOTS设置为1,注意DM7 v$instance视图没有dsc_role字段,DM7这部分可以删掉
if exists(select 1 from v$instance where dsc_role = 'NULL') then
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_FREQROOTS'', 1);';
end if;

--修改HASH相关参数
PRINT 'SP_SET_PARA_VALUE(1,''HJ_BUF_GLOBAL_SIZE'', '||HJ_BUF_GLOBAL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HJ_BUF_SIZE'', '||HJ_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HAGR_BUF_GLOBAL_SIZE'','||HAGR_BUF_GLOBAL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HAGR_BUF_SIZE'', '||HAGR_BUF_SIZE||');';

--修改排序相关参数
-- PRINT 'SP_SET_PARA_VALUE(2,''SORT_FLAG'','||SORT_FLAG||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BLK_SIZE'','||SORT_BLK_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BUF_SIZE'', '||SORT_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BUF_GLOBAL_SIZE'', '||SORT_BUF_GLOBAL_SIZE||');';

--修改其他内存参数
PRINT 'SP_SET_PARA_VALUE(2,''RLOG_POOL_SIZE'', '||RLOG_POOL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''CACHE_POOL_SIZE'', '||CACHE_POOL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''DICT_BUF_SIZE'', '||DICT_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''VM_POOL_TARGET'', 16384);';
PRINT 'SP_SET_PARA_VALUE(2,''SESS_POOL_TARGET'', 16384);';


--修改实例相关参数
PRINT 'SP_SET_PARA_VALUE(2,''USE_PLN_POOL'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_MONITOR'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''SVR_LOG'', 0);';
PRINT 'SP_SET_PARA_VALUE(2,''TEMP_SIZE'', 1024);';
PRINT 'SP_SET_PARA_VALUE(2,''TEMP_SPACE_LIMIT'', 102400);';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_SESSIONS'', 1500);';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_SESSION_STATEMENT'', 20000);';
PRINT 'SP_SET_PARA_VALUE(2,''PK_WITH_CLUSTER'', 0);';
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_ENCRYPT'',0);';

--修改优化器相关参数
PRINT 'SP_SET_PARA_VALUE(2,''OLAP_FLAG'',2);';
PRINT 'SP_SET_PARA_VALUE(2,''VIEW_PULLUP_FLAG'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''OPTIMIZER_MODE'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''ADAPTIVE_NPLN_FLAG'',0);';

--开启并行PURGE
PRINT 'SP_SET_PARA_VALUE(2,''PARALLEL_PURGE_FLAG'',1);';
--开启手动并行
PRINT 'SP_SET_PARA_VALUE(2,''PARALLEL_POLICY'',2);';
--UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作。
PRINT 'SP_SET_PARA_VALUE(2,''UNDO_EXTENT_NUM'',16);';
--开启SQL 注入HINT功能
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_INJECT_HINT'',1);';


END IF;


select MEMORY_TARGET+BUFFER+RECYCLE+HJ_BUF_GLOBAL_SIZE+HAGR_BUF_GLOBAL_SIZE+CACHE_POOL_SIZE
+DICT_BUF_SIZE+SORT_BUF_GLOBAL_SIZE+RLOG_POOL_SIZE;
exception
when others then
raise_application_error (-20001,substr( ' 执行失败, '||SQLCODE||' '||SQLERRM||' '||dbms_utility.format_error_backtrace , 1, 400));

<<return_2000>> null;
end;
/