DM 达梦数据库 配置参数优化 脚本

安装完达梦数据库后，可以执行以下脚本自动调整性能相关的参数,执行完以上脚本后，重启数据库服务生效。

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=512; 
      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;
/