达梦数据库自动根据机器配置调整性能相关参数

机器配置调整性能相关参数"/>

达梦数据库自动根据机器配置调整性能相关参数

--安装完达梦数据库后，可以执行以下脚本自动调整性能相关的参数

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;beginSELECT 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  THENreturn;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 THENwith t as(select rownum rn from dual connect by level <= 100) ,t1 as(select * from t where rn > 1 minusselectta.rn * tb.rnfromt ta,t tbwhereta.rn <= tb.rnand ta.rn  > 1and 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 minusselectta.rn * tb.rnfromt ta,t tbwhereta.rn <= tb.rnand ta.rn  > 1and 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;ELSEBUFFER_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 THENCACHE_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);ELSEMEMORY_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;
/

  --执行完以上脚本后，重启数据库服务即可生效

更多资讯请上达梦技术社区了解：