dm
### [dbeaver 连接dm](https://zhuanlan.zhihu.com/p/615526878)
### [更换授权](https://blog.csdn.net/Penrosee/article/details/127000640)
## 单机安装
```bash
#!/usr/bin/env bash
groupadd dinstall
useradd -g dinstall -m -d /home/dmdba -s /bin/bash dmdba
echo "EasyViews-1234" | passwd --stdin dmdba
mkdir -p /mnt/dm/dm8
mkdir -p /mnt/dm/dmdata
mkdir -p /mnt/dm/dmbak
mkdir -p /mnt/dm/dmarch
chown -R dmdba:dinstall /mnt/dm/
# 关闭内存透明⼤⻚
echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled ' >> /etc/rc.d/rc.local
chmod u+x /etc/rc.d/rc.local
cat >> /etc/security/limits.conf <<EOF
dmdba soft core unlimited
dmdba hard core unlimited
dmdba soft nofile 65536
dmdba hard nofile 65536
dmdba soft nproc 65536
dmdba hard nproc 65536
dmdba soft stack 65536
dmdba hard stack 65536
EOF
sysctl -p
#---------------------切换用户----------------------------------
su - dmdba
cat >> /home/dmdba/.bash_profile <<EOF
export DM_HOME=/mnt/dm/dm8
export PATH=\$PATH:DM_HOME/bin
export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:DM_HOME/bin
EOF
mkdir /mnt/dm/dminstaller
mount -o loop dm8_20220830_x86_kylin10_64.iso /mnt/dm/dminstaller
#
su - dmdba
cd /mnt/dm/dminstaller
./DMInstall.bin -i
"
请选择安装语言(C/c:中文 E/e:英文) [C/c]:c
解压安装程序.........
欢迎使用达梦数据库安装程序
是否输入Key文件路径? (Y/y:是 N/n:否) [Y/y]:n #输入上传的dm.key的绝对路+
是否设置时区? (Y/y:是 N/n:否) [Y/y]:y
设置时区:
[ 1]: GTM-12=日界线西
...
[21]: GTM+08=中国标准时间
...
[27]: GTM+14=基里巴斯
请选择设置时区 [21]:21
安装类型:
1 典型安装
2 服务器
3 客户端
4 自定义
请选择安装类型的数字序号 [1 典型安装]:1
所需空间: 1835M
请选择安装目录 [/home/dmdba/dmdbms]:/mnt/dm/dm8
可用空间: 110G
是否确认安装路径(/mnt/dm/dm8)? (Y/y:是 N/n:否) [Y/y]:y
安装前小结
安装位置: /mnt/dm/dm8
所需空间: 1835M
可用空间: 110G
版本信息:
有效日期:
安装类型: 典型安装
是否确认安装? (Y/y:是 N/n:否):y
"
#安装后操作
su - root
/mnt/dm/dm8/script/root/root_installer.sh
#新建实例
cd /mnt/dm/dm8/bin
./dminit PATH=/mnt/dm/dmdata PAGE_SIZE=32 CASE_SENSITIVE=n CHARSET=1 DB_NAME=EASYVIEWS INSTANCE_NAME=EASYVIEWS PORT_NUM=5236 SYSDBA_PWD=ssqj@easyviews.pw
#注册数据库实例为系统服务
cd /mnt/dm/dm8/script/root
./dm_service_installer.sh -t dmserver -p EASYVIEWS -dm_ini /mnt/dm/dmdata/EASYVIEWS/dm.ini
# 现场使⽤版本，system服务方式动有bug,使⽤nohup方式
cd /mnt/dm/dm8/bin/
nohup ./dmserver /mnt/dm/dmdata/EASYVIEWS/dm.ini > /mnt/dm/dmdata/EASYVIEWS/dm.log 2>&1 &
# 若现场版本没有问题，使⽤ systemctl方式启动
systemctl start DmServiceEASYVIEWS
[root@node23 dm]# cat start_dm.sh
#!/bin/bash
if [[ `whoami` == "dmdba" ]];then
echo "开始启动达梦数据库......"
nohup /mnt/dm/dm8/bin/dmserver /mnt/dm/dmdata/EASYVIEWS/dm.ini > /mnt/dm/dmdata/EASYVIEWS/dm.log 2>&1 &
else
echo "请切换到 dmdba 用户执行！"
fi
[root@node23 dm]# cat stop.sh
#!/bin/bash
ps -ef|grep dm.ini|grep -v grep |awk '{print $2}'|xargs kill -9
```
## 命令行登录
```bash
cd /mnt/dm/dm8/bin
./disql sysdba/'"ssqj@easyviews.pw"':5236
#执行脚本不加分号
start /mnt/workarea/sqldir/dm02.sql
select table_name from dba_tables
select * from dba_tables
SET IDENTITY_INSERT deploy ON;
update sys_menu set sort=0 where id=37;
#'字符串'
commit;
```
## 逻辑备份
```bash
# 导出
./dexp userid=sysdba/'"ssqj@easyviews.pw"':5236 directory=/mnt/dm/dm8/backup file=full_`date +%F`.dmp log=full_`date +%F`.log full=y
# 全库逻辑导入
./dimp userid=sysdba/'"ssqj@easyviews.pw"':5236 directory=/mnt/dm/dm8/backup file=full_`date +%F`.dmp log=impfull.log full=y
```
## 归档和备份配置
```sql
-- 开启归档模式
ALTER DATABASE MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE ADD ARCHIVELOG 'DEST=/mnt/dm/dmarch, TYPE=LOCAL, FILE_SIZE=1024, SPACE_LIMIT=10240';
ALTER DATABASE OPEN;
-- 查询归档
select arch_mode from v$database;
select arch_lsn,clsn,path from v$arch_file;
SQL> select arch_mode from v$database;
行号 arch_mode
---------- ---------
1 Y
已用时间: 0.891(毫秒). 执行号:60818.
SQL> select arch_lsn,clsn,path from v$arch_file;
行号 arch_lsn clsn PATH
---------- -------------------- -------------------- --------------------------------------------------------------------
1 114940 116287 /mnt/dm/dmarch/ARCHIVE_LOCAL1_0x3320192E_EP0_2024-03-13_14-04-11.log
已用时间: 1.865(毫秒). 执行号:60819.
--备份作业
SP_INIT_JOB_SYS(1);
--开启代理作业
SF_BAKSET_BACKUP_DIR_ADD（'DISK','/data/dmdata/dmbak'）;
--增加备份路径
--全量备份：定时每周六晚22点整进行
call SP_CREATE_JOB('JOB_FULL_BAK_TIMELY',1,0,'',0,0,'',0,'定时全量备份');
call SP_JOB_CONFIG_START('JOB_FULL_BAK_TIMELY');
call SP_ADD_JOB_STEP('JOB_FULL_BAK_TIMELY', 'STEP_FULL_BAK', 6, '01000000/data/dmdata/dmbak', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_FULL_BAK_TIMELY', 'SCHEDULE_FULL_BAK', 1, 2, 1, 64, 0, '22:00:00', NULL, '2019-07-01 22:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('JOB_FULL_BAK_TIMELY');
--(2)bak2的方式：基于备份集的方式备份，可以根据实际情况设置备份并行数和备份片大小
call SP_CREATE_JOB('JOB_INCREMENT_BAK_TIMELY',1,0,'',0,0,'',0,'定时增量备份');
call SP_JOB_CONFIG_START('JOB_INCREMENT_BAK_TIMELY');
call SP_ADD_JOB_STEP('JOB_INCREMENT_BAK_TIMELY', 'STEP_INCREMENT_BAK', 6, '11000000/data/dmdata/dmbak|/data/dmdata/dmbak', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_INCREMENT_BAK_TIMELY', 'SCHEDULE_INCREMENT_BAK', 1, 2, 1, 63, 0, '22:00:00', NULL, '2019-07-01 22:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('JOB_INCREMENT_BAK_TIMELY');
--(3)删除备份：删除基于备份集备份的方式生成的备份集
call SP_CREATE_JOB('JOB_DEL_BAK_TIMELY',1,0,'',0,0,'',0,'定时删除备份');
call SP_JOB_CONFIG_START('JOB_DEL_BAK_TIMELY');
call SP_ADD_JOB_STEP('JOB_DEL_BAK_TIMELY', 'STEP_DEL_BAK', 0, ' SF_BAKSET_BACKUP_DIR_ADD(“DISK”,”/data/dmdata/dmbak”);SP_DB_BAKSET_REMOVE_BATCH(NULL,SYSDATE-15);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_DEL_BAK_TIMELY', 'SCHEDULE_DEL_BAK', 1, 2, 1, 1, 0, '21:00:00', NULL, '2020-12-16 21:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('JOB_DEL_BAK_TIMELY');
--注：SP_DB_BAKSET_REMOVE_BATCH：批量删除指定时间之前的数据库备份集
--(4)删除归档
call SP_CREATE_JOB('JOB_DEL_ARCH_TIMELY',1,0,'',0,0,'',0,'定时删除归档');
call SP_JOB_CONFIG_START('JOB_DEL_ARCH_TIMELY');
call SP_ADD_JOB_STEP('JOB_DEL_ARCH_TIMELY', 'STEP_DEL_ARCH', 0, 'SP_ARCHIVELOG_DELETE_BEFORE_TIME(SYSDATE-7);', 1, 2, 0, 0, NULL, 0);
call SP_ADD_JOB_SCHEDULE('JOB_DEL_ARCH_TIMELY', 'SCHEDULE_DEL_ARCH', 1, 2, 1, 1, 0, '21:00:00',NULL, '2020-12-16 21:00:00', NULL, '');
call SP_JOB_CONFIG_COMMIT('JOB_DEL_ARCH_TIMELY');
```
## 数据库优化
```sql
declare
exec_mode int:= 0; --0表示直接执行脚本修改参数，1表示不直接修改参数，打印设置参数的语句，设置为1后，必须调整v_mem_mb和v_cpus
is_dsc int := 0; --是否是dsc集群,如果是dsc集群请设置为1，将自动调整dsc相关参数
mem_per int:= 70; --默认所有的内存归达梦数据库使用，如实际不能100%可用，可以调整此参数
v_mem_mb int:= 32000; --exec_mode为1时请自行根据机器实际内存调整此参数
v_cpus int:= 8; --exec_mode为1时请自行根据机器实际CPU核数调整此参数
sort_mode int:=0; --并发量较高的OLTP类型系统此参数设置为0，并发量不高的一般业务系统和OLAP类的系统此参数设置为1
pk_cluster_mode int:=0; --是否使用聚集主键：性能要求高且大字段较少的业务场景建议设置为1，大字段多的场景设置为0
tname varchar(100);
MEMORY_POOL int;
MEMORY_N_POOLS int;
MEMORY_TARGET 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;
HJ_BUF_SIZE INT;
HAGR_BUF_SIZE INT;
HJ_BUF_GLOBAL_SIZE INT;
HAGR_BUF_GLOBAL_SIZE INT;
SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;
TASK_THREADS INT;
IO_THR_GROUPS INT;
FAST_POOL_PAGES INT :=3000;
FAST_ROLL_PAGES INT :=1000;
CNT INT;
begin
CNT :=0;
if exec_mode=0 then
SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;
end if;
v_mem_mb := v_mem_mb * (mem_per/100.0);
v_mem_mb=round(v_mem_mb,-3);
IF v_mem_mb <= 2000 THEN
goto return_2000;
END IF;
IF v_mem_mb > 512000 THEN
v_mem_mb :=v_mem_mb*0.8;
END IF;
MEMORY_TARGET=round(cast(v_mem_mb * 0.12 as int),-3);
TASK_THREADS :=4;
IO_THR_GROUPS :=4;
IF v_cpus < 8 THEN
TASK_THREADS :=4;
IO_THR_GROUPS :=2;
END IF;
IF v_cpus >= 64 THEN
v_cpus := 64;
TASK_THREADS :=16;
IO_THR_GROUPS :=8;
END IF;
BUFFER := round(cast(v_mem_mb * 0.4 as int),-3);
RECYCLE :=cast(v_mem_mb * 0.04 as int);
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;
ELSE
BUFFER_POOLS := 101;
END IF;
--修改内存池
IF v_mem_mb >= 16000 THEN
IF v_mem_mb= 16000 THEN
MEMORY_POOL := 1500;
SORT_BUF_GLOBAL_SIZE := 1000;
MEMORY_N_POOLS := 3;
CACHE_POOL_SIZE := 512;
ELSE
MEMORY_POOL := 2000;
SORT_BUF_GLOBAL_SIZE := 2000;
MEMORY_N_POOLS := 11;
CACHE_POOL_SIZE := 1024;
END IF;
FAST_POOL_PAGES :=9999;
SORT_FLAG = 0;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE := 10;
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 :=250;
HAGR_BUF_SIZE :=250;
IF v_mem_mb >= 64000 THEN
FAST_POOL_PAGES :=99999;
FAST_ROLL_PAGES :=9999;
BUFFER :=BUFFER-3000;
CACHE_POOL_SIZE := 2048;
RLOG_POOL_SIZE := 2048;
SORT_FLAG = 1;
SORT_BLK_SIZE=1;
SORT_BUF_SIZE=50;
SORT_BUF_GLOBAL_SIZE= cast(v_mem_mb * 0.02 as int);
HJ_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.15625 as int);
HAGR_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.04 as int);
HJ_BUF_SIZE :=512;
HAGR_BUF_SIZE :=512;
MEMORY_N_POOLS := 59;
END IF;
DICT_BUF_SIZE := 50;
HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
SORT_BUF_GLOBAL_SIZE :=round(SORT_BUF_GLOBAL_SIZE,-3);
RECYCLE :=round(RECYCLE,-3);
ELSE
MEMORY_POOL :=GREAT(cast(v_mem_mb * 0.0625 as int),100);
MEMORY_POOL :=round(MEMORY_POOL,-2);
MEMORY_N_POOLS := 2;
CACHE_POOL_SIZE := 200;
RLOG_POOL_SIZE := 256;
SORT_BUF_SIZE := 10;
SORT_BUF_GLOBAL_SIZE := 500;
DICT_BUF_SIZE := 50;
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;
--设置根据RECYCLE情况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 <= RECYCLE*1024/3000/(page()/1024) order by 1 desc;
tname :='BAK_DMINI_' || to_char(sysdate,'yymmdd');
execute IMMEDIATE 'select count(*) from USER_ALL_TABLES where table_name= ?' into CNT using tname;
if exists(select 1 from V$INSTANCE where MODE$ in ('NORMAL','PRIMARY')) then
IF CNT=0 THEN
execute IMMEDIATE 'CREATE TABLE BAK_DMINI_' || to_char(sysdate,'yymmdd') || ' as select *,sysdate uptime from v$dm_ini';
ELSE
execute IMMEDIATE 'INSERT INTO BAK_DMINI_' || to_char(sysdate,'yymmdd') || ' select *,sysdate uptime from v$dm_ini';
END IF;
end if;
--如果sort_mode设置为0，采用旧的排序模式
if sort_mode=0 then
SORT_FLAG = 0;
SORT_BUF_SIZE := 2;
end if;
MAX_BUFFER := BUFFER;
IF exec_mode=0 THEN
--修改cpu相关参数
SP_SET_PARA_VALUE(2,'WORKER_THREADS',v_cpus);
SP_SET_PARA_VALUE(2,'TASK_THREADS',TASK_THREADS);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',IO_THR_GROUPS);
--修改内存池相关参数
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', mem_per);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', MEMORY_POOL);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', MEMORY_N_POOLS);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', MEMORY_TARGET);
--修改内存检测参数为1
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK', 1);
--修改缓冲区相关参数
SP_SET_PARA_VALUE(2,'BUFFER', BUFFER);
--新版本已去掉MAX_BUFFER参数，如果存在就修改
IF EXISTS (SELECT * FROM V$DM_INI WHERE PARA_NAME='MAX_BUFFER') THEN
SP_SET_PARA_VALUE(2,'MAX_BUFFER', MAX_BUFFER);
END IF;
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);
--修改fast_pool相关参数，如果是dsc环境，适当放小，以免影响启动速度
IF is_dsc= 1 THEN
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', 10000);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', 3000);
ELSE
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', FAST_POOL_PAGES);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', FAST_ROLL_PAGES);
--如果不是dsc环境，开启热页动态加载，关闭预读
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
SP_SET_PARA_VALUE(2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE(2,'PRELOAD_SCAN_NUM',0);
SP_SET_PARA_VALUE(2,'PRELOAD_EXTENT_NUM',0);
END IF;
--修改HASH相关参数
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', HJ_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', HJ_BUF_SIZE );
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',HAGR_BUF_GLOBAL_SIZE);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', HAGR_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,'RLOG_POOL_SIZE', RLOG_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,'VM_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET', 16384);
--修改实例相关参数
SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 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', 102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
--性能要求高且大字段较少的业务场景建议设置为1，大字段多的场景设置为0
if pk_cluster_mode = 1 then
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER',1);
else
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER',0);
end if;
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
--修改优化器相关参数
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
--开启并行PURGE
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
--开启手动并行
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
--UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作。
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
--开启SQL 注入HINT功能
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
SP_SET_PARA_VALUE(2,'FAST_LOGIN',1);
SP_SET_PARA_VALUE(2,'BTR_SPLIT_MODE',1);
--关闭参数监控
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR_BP',0);
IF is_dsc= 1 THEN
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',0);
--2022年5月月度版以后版本放开数据页预加载参数，DSC环境下开启
IF exists(select 1 from v$version where banner like '%-%' and REGEXP_SUBSTR(banner,'[^-]+',1,2)>='2022-05-25' and REGEXP_SUBSTR(banner,'[^-]+',1,3)>=161267) THEN
SP_SET_PARA_VALUE(2,'MULTI_PAGE_GET_NUM',16);
SP_SET_PARA_VALUE(2,'PRELOAD_SCAN_NUM',4);
SP_SET_PARA_VALUE(2,'PRELOAD_EXTENT_NUM',5);
ELSE
SP_SET_PARA_VALUE(2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE(2,'PRELOAD_SCAN_NUM',0);
SP_SET_PARA_VALUE(2,'PRELOAD_EXTENT_NUM',0);
END IF;
SP_SET_PARA_VALUE(2,'DSC_N_POOLS',MEMORY_N_POOLS);
IF EXISTS (SELECT * FROM V$DM_INI WHERE PARA_NAME='DSC_GBS_REVOKE_OPT') THEN
SP_SET_PARA_VALUE(2,'DSC_GBS_REVOKE_OPT',0);
END IF;
SP_SET_PARA_VALUE(2,'DSC_HALT_SYNC',0);
SP_SET_PARA_VALUE(2,'DSC_N_CTLS',50000);
SP_SET_PARA_VALUE(2,'DSC_ENABLE_MONITOR',0);
SP_SET_PARA_VALUE(2,'TRX_DICT_LOCK_NUM',5);
SP_SET_PARA_VALUE(2,'DIRECT_IO',1);
END IF;
ELSE
--修改cpu相关参数
PRINT 'SP_SET_PARA_VALUE(2,''WORKER_THREADS'','||v_cpus||');';
PRINT 'SP_SET_PARA_VALUE(2,''TASK_THREADS'','||TASK_THREADS||');';
PRINT 'SP_SET_PARA_VALUE(2,''IO_THR_GROUPS'','||IO_THR_GROUPS||');';
--修改内存池相关参数
PRINT 'SP_SET_PARA_VALUE(2,''MAX_OS_MEMORY'', '||mem_per||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_POOL'', '||MEMORY_POOL||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_N_POOLS'', '||MEMORY_N_POOLS||');';
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_TARGET'', '||MEMORY_TARGET||');';
--修改缓冲区相关参数
PRINT 'SP_SET_PARA_VALUE(2,''BUFFER'', '||BUFFER||');';
--新版本已去掉MAX_BUFFER参数，如果存在就修改
IF EXISTS (SELECT * FROM V$DM_INI WHERE PARA_NAME='MAX_BUFFER') THEN
PRINT 'SP_SET_PARA_VALUE(2,''MAX_BUFFER'', '||MAX_BUFFER||');';
END IF;
PRINT 'SP_SET_PARA_VALUE(2,''BUFFER_POOLS'', '||BUFFER_POOLS||');';
PRINT 'SP_SET_PARA_VALUE(2,''RECYCLE'', '||RECYCLE||');';
PRINT 'SP_SET_PARA_VALUE(2,''RECYCLE_POOLS'', '||RECYCLE_POOLS||');';
--修改fast_pool相关参数，如果是dsc环境，适当放小，以免影响启动速度
IF is_dsc= 1 THEN
PRINT 'SP_SET_PARA_VALUE(2,''FAST_POOL_PAGES'', 10000);';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_ROLL_PAGES'', 3000);';
ELSE
PRINT 'SP_SET_PARA_VALUE(2,''FAST_POOL_PAGES'', '||FAST_POOL_PAGES||');';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_ROLL_PAGES'', '||FAST_ROLL_PAGES||');';
--如果不是dsc环境，开启热页动态加载，关闭预读
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_FREQROOTS'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''MULTI_PAGE_GET_NUM'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''PRELOAD_SCAN_NUM'',0);';
PRINT 'SP_SET_PARA_VALUE(2,''PRELOAD_EXTENT_NUM'',0);';
END IF;
--修改内存检测参数为1
PRINT 'SP_SET_PARA_VALUE(2,''MEMORY_MAGIC_CHECK'', 1);';
--修改HASH相关参数
PRINT 'SP_SET_PARA_VALUE(1,''HJ_BUF_GLOBAL_SIZE'', '||HJ_BUF_GLOBAL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HJ_BUF_SIZE'', '||HJ_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HAGR_BUF_GLOBAL_SIZE'','||HAGR_BUF_GLOBAL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(1,''HAGR_BUF_SIZE'', '||HAGR_BUF_SIZE||');';
--修改排序相关参数
PRINT 'SP_SET_PARA_VALUE(2,''SORT_FLAG'','||SORT_FLAG||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BLK_SIZE'','||SORT_BLK_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BUF_SIZE'', '||SORT_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''SORT_BUF_GLOBAL_SIZE'', '||SORT_BUF_GLOBAL_SIZE||');';
--修改其他内存参数
PRINT 'SP_SET_PARA_VALUE(2,''RLOG_POOL_SIZE'', '||RLOG_POOL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''CACHE_POOL_SIZE'', '||CACHE_POOL_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''DICT_BUF_SIZE'', '||DICT_BUF_SIZE||');';
PRINT 'SP_SET_PARA_VALUE(2,''VM_POOL_TARGET'', 16384);';
PRINT 'SP_SET_PARA_VALUE(2,''SESS_POOL_TARGET'', 16384);';
--修改实例相关参数
PRINT 'SP_SET_PARA_VALUE(2,''USE_PLN_POOL'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_MONITOR'', 1);';
PRINT 'SP_SET_PARA_VALUE(2,''SVR_LOG'', 0);';
PRINT 'SP_SET_PARA_VALUE(2,''TEMP_SIZE'', 1024);';
PRINT 'SP_SET_PARA_VALUE(2,''TEMP_SPACE_LIMIT'', 102400);';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_SESSIONS'', 1500);';
PRINT 'SP_SET_PARA_VALUE(2,''MAX_SESSION_STATEMENT'', 20000);';
--性能要求高且大字段较少的业务场景建议设置为1，大字段多的场景设置为0
if pk_cluster_mode = 1 then
PRINT 'SP_SET_PARA_VALUE(2,''PK_WITH_CLUSTER'', 1);';
else
PRINT 'SP_SET_PARA_VALUE(2,''PK_WITH_CLUSTER'', 0);';
end if;
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_ENCRYPT'',0);';
--修改优化器相关参数
PRINT 'SP_SET_PARA_VALUE(2,''OLAP_FLAG'',2);';
PRINT 'SP_SET_PARA_VALUE(2,''VIEW_PULLUP_FLAG'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''OPTIMIZER_MODE'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''ADAPTIVE_NPLN_FLAG'',0);';
--开启并行PURGE
PRINT 'SP_SET_PARA_VALUE(2,''PARALLEL_PURGE_FLAG'',1);';
--开启手动并行
PRINT 'SP_SET_PARA_VALUE(2,''PARALLEL_POLICY'',2);';
--UNDO_RETENTION如果放大，可以适当调大UNDO_EXTENT_NUM。负载高的时候，减少文件系统的申请/释放操作。
PRINT 'SP_SET_PARA_VALUE(2,''UNDO_EXTENT_NUM'',16);';
--开启INJECT HINT功能
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_INJECT_HINT'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''BTR_SPLIT_MODE'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''FAST_LOGIN'',1);';
--关闭参数监控
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_MONITOR_BP'',0);';
IF is_dsc= 1 THEN
PRINT 'SP_SET_PARA_VALUE(2,''ENABLE_FREQROOTS'',0);';
--2022年5月月度版以后版本放开数据页预加载参数
IF exists(select 1 from v$version where banner like '%-%' and REGEXP_SUBSTR(banner,'[^-]+',1,2)>='2022-05-25' and REGEXP_SUBSTR(banner,'[^-]+',1,3)>=161267) THEN
PRINT 'SP_SET_PARA_VALUE(2,''MULTI_PAGE_GET_NUM'',16);';
PRINT 'SP_SET_PARA_VALUE(2,''PRELOAD_SCAN_NUM'',4);';
PRINT 'SP_SET_PARA_VALUE(2,''PRELOAD_EXTENT_NUM'',5);';
ELSE
PRINT 'SP_SET_PARA_VALUE(2,''MULTI_PAGE_GET_NUM'',1);';
PRINT 'SP_SET_PARA_VALUE(2,''PRELOAD_SCAN_NUM'',0);';
PRINT 'SP_SET_PARA_VALUE(2,''PRELOAD_EXTENT_NUM'',0);';
END IF;
PRINT 'SP_SET_PARA_VALUE(2,''DSC_N_POOLS'',' ||MEMORY_N_POOLS ||');';
IF EXISTS (SELECT * FROM V$DM_INI WHERE PARA_NAME='DSC_GBS_REVOKE_OPT') THEN
PRINT 'SP_SET_PARA_VALUE(2,''DSC_GBS_REVOKE_OPT'',0);';
END IF;
PRINT 'SP_SET_PARA_VALUE(2,''DSC_HALT_SYNC'',0);';
PRINT 'SP_SET_PARA_VALUE(2,''DSC_N_CTLS'',50000);';
PRINT 'SP_SET_PARA_VALUE(2,''DSC_ENABLE_MONITOR'',0);';
PRINT 'SP_SET_PARA_VALUE(2,''TRX_DICT_LOCK_NUM'',5);';
PRINT 'SP_SET_PARA_VALUE(2,''DIRECT_IO'',1);';
END IF;
END IF;
select MEMORY_TARGET+BUFFER+RECYCLE+HJ_BUF_GLOBAL_SIZE+HAGR_BUF_GLOBAL_SIZE+CACHE_POOL_SIZE
+DICT_BUF_SIZE+SORT_BUF_GLOBAL_SIZE+RLOG_POOL_SIZE;
exception
when others then
raise_application_error (-20001,substr( ' 执行失败, '||SQLCODE||' '||SQLERRM||' '||dbms_utility.format_error_backtrace , 1, 400));
<<return_2000>> null;
end;
/
```