达梦数据库常见参数调整

下面是在命令行客户端工具直接执行的脚本（执行后，会输出待调整参数）： 备注：第一次dminit后就要调整，获取的是当前系统可用内存。

set serveroutput on declare v_maxsess bigint;

v_cpus bigint; v_mem_mb bigint; v_bufs bigint; v_refs bigint; v_maxdisk_mb bigint; begin

-- manual set

v_maxsess=5000;

-- reload messages v_cpus=64; v_mem_mb=500000; v_bufs=101; v_refs=67; v_maxdisk_mb=100000;

SELECT cast(67434610688*0.8/1024/1024 as int) into v_maxdisk_mb FROM V$SYSTEMINFO ORDER BY DRIVER_TOTAL_SIZE DESC ;

-- SELECT * FROM V$SYSTEMINFO ORDER BY DRIVER_TOTAL_SIZE DESC LIMIT 1 OFFSET 1;

SELECT case when N_CPU<8 then 83 when N_CPU<32 then N_CPU2 else 64 end n_CPU,FREE_PHY_SIZE/1024/1024 MEM into v_cpus,v_mem_mb FROM V$SYSTEMINFO LIMIT 1;

with a(val) as(select 5 union all select 7 union all select 11 union all select 13 union all select 17 union all select 19 union all select 23 union all select 29 union all select 31 union all select 37 union all select 41 union all select 43 union all select 47 union all select 53 union all select 59 union all select 61 union all select 67 union all select 71 union all select 73 union all select 79 union all select 83 union all select 89 union all select 97 union all select 101),b as( select rownum rn,val val from a) select ( select VAL bufs from b where rn=( 3 + CAST( v_mem_mb/1024.01.0/2048100 AS INT))) v_bufs，,( select VAL refs from b where rn=( 3 + CAST( v_mem_mb/1024.01.0/2048100 AS INT)) )v_refs into v_bufs,v_refs from dual ; print ' begin SP_SET_PARA_VALUE(2,''MAX_OS_MEMORY'',100); SP_SET_PARA_VALUE(2,''MEMORY_POOL'',cast( '||v_mem_mb||'0.1 as int) ); SP_SET_PARA_VALUE(2,''MEMORY_TARGET'',cast ( '||v_mem_mb||'0.2 as int) ); SP_SET_PARA_VALUE(2,''MEMORY_MAGIC_CHECK'',1);

SP_SET_PARA_VALUE(2,''VM_POOL_TARGET'',cast( (0.37)'||v_mem_mb||'10240.6/'||v_maxsess||' as int)); SP_SET_PARA_VALUE(2,''SESS_POOL_TARGET'',cast( (0.37)'||v_mem_mb||'1024*0.3/'||v_maxsess||' as int ) ); SP_SET_PARA_VALUE(2,''CACHE_POOL_SIZE'',cast( (0.37)'||v_mem_mb||'('||v_maxsess||'/2000.0)0.55 as int) );

SP_SET_PARA_VALUE(2,''BUFFER'',cast('||v_mem_mb||' * 0.5 as int)); SP_SET_PARA_VALUE(2,''MAX_BUFFER'',cast('||v_mem_mb||' * 0.5 as int)); SP_SET_PARA_VALUE(2,''RECYCLE'',10000*'||v_cpus||'/100); SP_SET_PARA_VALUE(2,''BUFFER_POOLS'','||v_bufs||'); SP_SET_PARA_VALUE(2,''RECYCLE_POOLS'','||v_refs||'); SP_SET_PARA_VALUE(2,''WORKER_THREADS'','||v_cpus||'); SP_SET_PARA_VALUE(2,''TASK_THREADS'','||v_cpus||');

SP_SET_PARA_VALUE(2,''HJ_BUF_GLOBAL_SIZE'', cast('||v_mem_mb||' * 0.18 as int)); SP_SET_PARA_VALUE(2,''HJ_BUF_SIZE'', cast('||v_mem_mb||' * 0.0018 as int)); SP_SET_PARA_VALUE(2,''HAGR_BUF_GLOBAL_SIZE'',cast('||v_mem_mb||' * 0.12 as int)); SP_SET_PARA_VALUE(2,''HAGR_BUF_SIZE'', cast('||v_mem_mb||' * 0.0024 as int));

SP_SET_PARA_VALUE(2,''DICT_BUF_SIZE'','||v_refs||'5); SP_SET_PARA_VALUE(2,''TEMP_SIZE'',5000'||v_mem_mb||'/1024.0/256);

SP_SET_PARA_VALUE(2,''VM_POOL_SIZE'','||v_refs||'5); SP_SET_PARA_VALUE(2,''SESS_POOL_SIZE'','||v_refs||'5);

SP_SET_PARA_VALUE(2,''MAX_SESSIONS'','||v_maxsess||'); SP_SET_PARA_VALUE(2,''MAX_SESSION_STATEMENT'','||v_maxsess||'*8);

SP_SET_PARA_VALUE(2,''ENABLE_ENCRYPT'',0); SP_SET_PARA_VALUE(2,''USE_PLN_POOL'',1); SP_SET_PARA_VALUE(2,''OLAP_FLAG'',2); SP_SET_PARA_VALUE(2,''OPTIMIZER_MODE'',1); SP_SET_PARA_VALUE(2,''VIEW_PULLUP_FLAG'',1); SP_SET_PARA_VALUE(2,''COMPATIBLE_MODE'',2); SP_SET_PARA_VALUE(2,''MONITOR_TIME'',0); SP_SET_PARA_VALUE(2,''ENABLE_MONITOR'',1); SP_SET_PARA_VALUE(2,''SVR_LOG'',0);

end; ';

print ' create tablespace "USER" datafile ''USER01.dbf'' size 200;'; --print ' --alter user SYSDBA default tablespace "USER";'; print ' sp_set_para_value(1,''PWD_POLICY'',0);'; print ' create user DMDBA identified by DMDBA default tablespace "USER";'; print ' GRANT DBA TO dmdba;'; print ' sp_set_para_value(1,''PWD_POLICY'',2);';

print ' ALTER DATABASE MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE ADD ARCHIVELOG ''DEST=/home/dmdba/dmdbms/arch,TYPE=LOCAL,FILE_SIZE=1024,SPACE_LIMIT='||cast(v_maxdisk_mb*0.1 as int)||'''; ALTER DATABASE OPEN;';

print ' sp_set_para_value(1,''BAK_USE_AP'',2); BACKUP DATABASE FULL TO DMBAK_FULL_00 BACKUPSET ''DMBAK_FULL_00'' COMPRESSED; ';

exception when others then raise_application_error (-20001,substr( ' 执行失败, '||SQLCODE||' '||SQLERRM||' '||dbms_utility.format_error_backtrace , 1, 400)); end;

/

本文由博客一文多发平台 OpenWrite 发布！