安装完达梦数据库后，可以执行以下脚本自动调整性能相关的参数：
disql SYSDBA/SYSDBA@10.226.79.4:5237
declare
v_mem_mb int;
v_cpus int;
mem_per int:=100;
MEMORY_POOL 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;
SESS_POOL_SIZE INT;
HJ_BUF_SIZE INT;
HAGR_BUF_SIZE INT;
HJ_BUF_GLOBAL_SIZE INT;
HAGR_BUF_GLOBAL_SIZE INT;
TASK_THREADS INT;
SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;
begin
SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;
print v_cpus;
print v_mem_mb;
v_mem_mb=round(v_mem_mb,-3);
print v_mem_mb;
IF v_mem_mb <= 2000 THEN
return;
END IF;
IF v_mem_mb > 512000 THEN
v_mem_mb :=v_mem_mb*0.8;
END IF;
TASK_THREADS :=8;
IF v_cpus < 8 THEN
TASK_THREADS :=4;
END IF;
IF v_cpus >= 64 THEN
v_cpus := 64;
TASK_THREADS :=16;
END IF;
SP_SET_PARA_VALUE(2,'WORKER_THREADS',v_cpus);
SP_SET_PARA_VALUE(2,'TASK_THREADS',TASK_THREADS);
BUFFER := round(cast(v_mem_mb * 0.5 as int),-3);
MAX_BUFFER := BUFFER;
RECYCLE :=LEAST(cast(v_mem_mb * 0.125 as int),50000);
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;
PRINT 'BUFFER_POOLS ' || BUFFER_POOLS;
--设置根据内存情况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;
PRINT 'RECYCLE_POOLS ' || RECYCLE_POOLS;
ELSE
BUFFER_POOLS := 101;
RECYCLE_POOLS := 41;
END IF;
--修改内存池
IF v_mem_mb >= 16000 THEN
MEMORY_POOL := 2048;
CACHE_POOL_SIZE := 1024;
SORT_FLAG = 0;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 2000;
SESS_POOL_SIZE := 16;
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 :=500;
HAGR_BUF_SIZE :=500;
IF v_mem_mb >= 64000 THEN
CACHE_POOL_SIZE := 2048;
RLOG_POOL_SIZE := 2048;
SORT_FLAG = 1;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE=10;
SORT_BUF_GLOBAL_SIZE=5120 ;
SESS_POOL_SIZE := 32;
HJ_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),15000);
HAGR_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),15000);
HJ_BUF_SIZE :=1000;
HAGR_BUF_SIZE :=1000;
END IF;
DICT_BUF_SIZE := 500;
HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
RECYCLE :=round(RECYCLE,-3);
ELSE
MEMORY_POOL :=GREAT(cast(v_mem_mb * 0.0625 as int),100);
CACHE_POOL_SIZE := 200;
RLOG_POOL_SIZE := 256;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 500;
DICT_BUF_SIZE := 50;
SESS_POOL_SIZE =16;
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;
v_mem_mb := v_mem_mb * (mem_per/100.0);
--修改内存
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', mem_per);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', MEMORY_POOL);
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);
SP_SET_PARA_VALUE(2,'HJ_BUF_GLOBAL_SIZE', HJ_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(2,'HJ_BUF_SIZE', HJ_BUF_SIZE );
SP_SET_PARA_VALUE(2,'HAGR_BUF_GLOBAL_SIZE',HAGR_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(2,'HAGR_BUF_SIZE', HAGR_BUF_SIZE );
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', RLOG_POOL_SIZE);
SP_SET_PARA_VALUE(2,'SESS_POOL_SIZE', SESS_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,'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,'USE_PLN_POOL', 1);
SP_SET_PARA_VALUE(2,'OLAP_FLAG', 2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG', 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', 20480);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
SP_SET_PARA_VALUE(1,'PK_WITH_CLUSTER', 0);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
end;
/
根据空间情况扩一下redo日志，默认的较小
alter database resize logfile 'DMDB01.log' to 1024;
alter database resize logfile 'DMDB02.log' to 1024;
执行完以上脚本后，重启数据库服务即可生效。
systemctl restart DmServiceDMSERVER.service
浙公网安备 33010602011771号