签到成功

知道了

CNDBA社区CNDBA社区

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

2020-11-18 11:43 1310 0 转载 DM 达梦
作者: Dave

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

https://www.cndba.cn/dave/article/4303
https://www.cndba.cn/dave/article/4303
https://www.cndba.cn/dave/article/4303
https://www.cndba.cn/dave/article/4303
https://www.cndba.cn/dave/article/4303
https://www.cndba.cn/dave/article/4303https://www.cndba.cn/dave/article/4303https://www.cndba.cn/dave/article/4303https://www.cndba.cn/dave/article/4303
https://www.cndba.cn/dave/article/4303

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;
/
用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1904
    原创
  • 2
    翻译
  • 460
    转载
  • 134
    评论
  • 访问:3753168次
  • 积分:3147
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群