一、达梦临时表概述
TEMP 表空间完全由 DM 数据库自动维护。当 SQL 语句需要磁盘空间来完成某个操作时,DM 数据库会从 TEMP 表空间分配临时段。如创建索引、无法在内存中完成的排序操作、SQL 语句中间结果集以及用户创建的临时表等都会使用到 TEMP 表空间。
Temp 表空间可自动扩充,为了不影响磁盘空间的使用,通常会通过 ini 参数 TEMP_SIZE
配置大小,TEMP_SPACE_LIMIT
设置上限,通过存储过程 SP_TRUNC_TS_FILE
来收缩 Temp 表空间文件。
生产环境中要针对 Temp 表空间的大小做专项检查,经常过大可能说明内存过小或者存在大量排序或者中间结果集存放,需要视情况开展优化工作。
二、TEMP表空间不足的解决方法。
2.1 修改TEMP数据文件大小
修改临时表空间500M 或 10G,静态参数,需要重启生效,
不管TEMP自动扩展到多大,或是新增了TEMP文件,重启数据库后,TEMP会回到初始参数大小。
sp_set_para_value(2,'TEMP_SIZE',500);
sp_set_para_value(2,'TEMP_SIZE',10240);
2.2 TEMP数据文件自动扩展
设置临时表空间上限为100G,动态参数:
sp_set_para_value(1,
'TEMP_SPACE_LIMIT'
,102400);
2.3 TEMP表空间增加数据文件
alter
tablespace
TEMP
add
datafile
'/app/dmdbms/data/DMDB/TEMP01.DBF'
size
100;
2.4 TEMP 数据文件收缩
SP_TRUNC_TS_FILE(3,1,50);
select file_name,file_id,tablespace_name,bytes/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='TEMP'; LINEID FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUTOEXTENSIBLE ---------- -------------------------------- ----------- --------------- -------------------- -------------- 1 /app/dmdbms/data/DMDB/TEMP.DBF 0 TEMP 11 YES 2 /app/dmdbms/data/DMDB/TEMP01.DBF 1 TEMP 100 YES select * from v$tablespace where NAME='TEMP'; ID NAME CACHE TYPE$ STATUS$ MAX_SIZE TOTAL_SIZE FILE_NUM ENCRYPT_NAME ENCRYPTED_KEY ----------- ---- ----- ----------- ----------- -------------------- -------------------- ----------- ------------ ------------- 3 TEMP 2 0 13107200 7808 2 NULL NULL SP_TRUNC_TS_FILE(3,1,50); 第一个参数是临时表空间ID 第二个参数是临时表空间数据文件ID 第三个参数是截断至多大 收缩后: SQL> select file_name,file_id,tablespace_name,bytes/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='TEMP'; LINEID FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024 AUTOEXTENSIBLE ---------- -------------------------------- ----------- --------------- -------------------- -------------- 1 /app/dmdbms/data/DMDB/TEMP.DBF 0 TEMP 11 YES 2 /app/dmdbms/data/DMDB/TEMP |
三、TEMP表空间如何预防和维护
3.1 技术层面
加强表空间使用率监控,通过手工或者部署脚本进行相关人工干预。
3.2 业务层面
- 业务层面避免复杂SQL 或者较大的多表联合hash join、MERGE SORT查询产生大量中间结果集,避免海量场景下避免并行查询并行DML,导致内存池不足使用临时表空间,最终导致临时表空间持续暴涨。
- 对巨型表、超大表、大表进行数据和业务层面拆分,减少中间结果集缓存。
- 添加适当的索引,采用主键自增减少回表和默认内部排序。
- 使用union all 替换union,避免二次排序等。
总之,SQL越简单越好,最简单的即执行效率最快,SQL越简单越符合数据库的设计初衷,简单即高效。
3.3 硬件设施层面
创建多个临时表空间文件,使用不同的临时表空间数据文件,可以把TEMP表空间内的多个数据文件放到不同的存储硬盘上,这样来可以起到分散 I/O 的目的,从而提高系统整体的运行效率。