达梦数据库常见参数调整

下面是在命令行客户端工具直接执行的脚本（执行后，会输出待调整参数）：

备注：第一次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 8 3 when N_CPU<32 then N_CPU 2 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.0 1.0/2048 100 AS INT))) v_bufs，,(

select VAL refs from b where rn=( 3 + CAST( v_mem_mb/1024.0 1.0/2048 100 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||' 1024 0.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

/

只有DBA权限用户或者属于自己的模式才可以删除。达梦数据库删除模式只有DBA权限用户或者属于自己的模式才可以删除。梦数据库管理系统是达梦公司推出的具有完全自主知识产权的高性能数据库管理系统，简称DM。达梦数据库管理系统的最新版本是7.0版本，简称DM7。DM7采用全新的体系架构，在保证大型通用的基础上，针对可靠性、高性能、海量数据处理和安全性做了大量的研发和改进工作，极大提升了达梦数据库产品的性能、可靠性、可扩展性，能同时兼顾OLTP和OLAP请求，从根本上提升了DM7产品的品质。

使用的编译命令为：

/home/dmdba/dmdbms/bin/dpc_new FILE=./update.pc CHECK=TRUE MODE=ORACLE

cc -DOS_LINUX -c -o ./update.o -L//home/dmdba/dmdbms/bin/ -lm -I/home/dmdba/dmdbms/include update.c -L/home/dmdba/dmdbms/include -ldmdpi -ldmdpc

cc -o ./update -L//home/dmdba/dmdbms/bin/ ./update.o -L/home/dmdba/dmdbms/include -ldmdpi -ldmdpc

---