1 TEMP 表空间基本概念

TEMP 表空间完全由 DM 数据库自动维护。当 SQL 语句需要磁盘空间来完成某个操作时，DM 数据库会从 TEMP 表空间分配临时段。可以理解为为内存不足的 SQL 操作提供磁盘交换空间

2 TEMP 表空间相关参数

达梦中关于temp相关的参数情况

3 TEMP 监控管理

3.1 TEMP参数配置情况

``

WITH param_order AS (  
SELECT 'TEMP_SIZE' AS para_name, 1 AS ord FROM dual UNION ALL  
SELECT 'TEMP_SPACE_LIMIT', 2 FROM dual UNION ALL  
SELECT 'TMP_DEL_OPT', 3 FROM dual UNION ALL  
SELECT 'TMP_OPT_POLICY', 4 FROM dual UNION ALL  
SELECT 'TMP_MAX_RESERVE_EXTENT', 5 FROM dual UNION ALL  
SELECT 'USE_FTTS', 6 FROM dual UNION ALL  
SELECT 'ENABLE_TMP_TAB_ROLLBACK', 7 FROM dual UNION ALL  
SELECT 'TEMP_PATH', 8 FROM dual  
)  
SELECT v.para_name, v.para_value, v.file_value  
FROM v$dm_ini v  
JOIN param_order p ON v.para_name = p.para_name  
ORDER BY p.ord;

3.2 TEMP表空间使用情况

SELECT
    ALLOC.CURRENT_ALLOCATED_MB,
    FREE.FREE_MB,
    (ALLOC.CURRENT_ALLOCATED_MB - FREE.FREE_MB) AS USED_MB,
    INI.LIMIT_MB AS TEMP_SPACE_LIMIT_MB,
    CASE
        WHEN INI.LIMIT_MB = 0 THEN 'TEMP_SPACE_LIMIT 未设置上限，关注实例磁盘使用情况'
        ELSE ROUND((ALLOC.CURRENT_ALLOCATED_MB - FREE.FREE_MB) * 100 / INI.LIMIT_MB, 2) || '%'
    END AS USED_PERCENT_OF_LIMIT,
    CASE
        WHEN INI.LIMIT_MB = 0 THEN NULL
        ELSE (INI.LIMIT_MB - (ALLOC.CURRENT_ALLOCATED_MB - FREE.FREE_MB))
    END AS REMAIN_MB
FROM
    (SELECT NVL(SUM(BYTES),0)/1024.0/1024.0 AS CURRENT_ALLOCATED_MB
     FROM DBA_DATA_FILES
     WHERE TABLESPACE_NAME = 'TEMP') ALLOC,
    (SELECT NVL(SUM(BYTES),0)/1024.0/1024.0 AS FREE_MB
     FROM DBA_FREE_SPACE
     WHERE TABLESPACE_NAME = 'TEMP') FREE,
    (SELECT TO_NUMBER(PARA_VALUE) AS LIMIT_MB
     FROM V$DM_INI
     WHERE PARA_NAME = 'TEMP_SPACE_LIMIT') INI;

3.2.1 TEMP表空间回收

-----老版本V8.1.2.133之前，仅支持sp_trunc_ts_file方式
--查看临时表空间大小
SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024.0/1024.0 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=='TEMP'; 
--清理临时表会话，此时临时表无数据，但表空间占用的磁盘大小不变
--查看临时表空间大小
SELECT FILE_NAME,TABLESPACE_NAME,BYTES/1024.0/1024.0 FROM DBA_DATA_FILES WHERE TABLESPACE_NAME=='TEMP'; 
--确定TEMP表空间的ID和数据文件的file_id
select GROUP_ID,ID,* from v$datafile WHERE CLIENT_PATH='TEMP.DBF';
--清理临时表空间大小
sp_trunc_ts_file(3,0,128)

---新版本V8.1.2.133之后，废弃sp_trunc_ts_file方式，提供resize方式
ALTER TABLESPACE "TEMP" RESIZE DATAFILE '/dm/dmdata/DAMENG/TEMP.DBF' to 150;

3.2.2 会话TEMP空间使用监控

仅限测试环境验证，不要跑生产，生产直接用sessions查看就可以了，因为sessions中tmp_used_extent_num 字段本身就是动态的，事后观察基本都是回收掉了，以下只是为了测试研究看看sql的开销变化情况。

drop table if exists session_temp_monitor;
CREATE TABLE session_temp_monitor
AS
SELECT
    SYSTIMESTAMP AS sample_time,
    s.*,
    0 AS tmp_used_mb
FROM v$sessions s
WHERE 1 = 0;

CREATE INDEX idx_temp_monitor_sessid ON session_temp_monitor(sess_id,sql_id,sample_time);

---需要留意表空间开销
truncate table session_temp_monitor;
BEGIN
    LOOP
        INSERT INTO session_temp_monitor
        SELECT
            SYSTIMESTAMP,
            s.*,
            s.tmp_used_extent_num * page() / 1024
        FROM v$sessions s
        WHERE s.tmp_used_extent_num > 0;
        --实际要考虑增加额外的条件，尽可能缩小范围
        COMMIT;
        --DBMS_LOCK.SLEEP(2); -- 实时采集确保完整性
    END LOOP;
END;

--关注监控表大小开销
SELECT TABLE_USED_SPACE('SYSDBA','SESSION_TEMP_MONITOR')*(PAGE/1024.0/1024.0) AS TABLE_USE_SPACE_MB;

--看指定会话 TEMP 增长曲线
SELECT 
    sample_time,
    tmp_used_mb
FROM session_temp_monitor
WHERE sess_id = 191684840
ORDER BY sample_time;

--按 SQL 统计峰值
SELECT 
    sample_time,
    sess_id,
    SQL_TEXT,
    tmp_used_mb AS peak_mb
FROM session_temp_monitor
ORDER BY sample_time,peak_mb;

SELECT 
    sess_id,
    MAX(tmp_used_mb) AS peak_mb
FROM session_temp_monitor
GROUP BY sess_id
ORDER BY peak_mb DESC;

--按 SQL 统计峰值
SELECT 
    sql_id,
    MAX(tmp_used_mb) AS peak_mb
FROM session_temp_monitor
GROUP BY sql_id
ORDER BY peak_mb DESC;

4 TEMP 常见报错

4.1 [-502]：临时表空间不足

构造复现

---测试环境配置复现条件，或复现SQL结果集配置更大

SP_SET_PARA_VALUE(2,'TEMP_SIZE',100);
[dmdba@192 bin]$ /dm/dmdbms/bin/DmServiceDMSERVER restart
--TEMP_SIZE如果大于TEMP_SPACE_LIMIT，则会报[-803]:非法INI配置值
SP_SET_PARA_VALUE(1,'TEMP_SPACE_LIMIT',150);
---复现测试
drop table if exists temp_hold;
CREATE GLOBAL TEMPORARY TABLE temp_hold AS
SELECT ROWNUM AS id, RPAD('X',1024,'X') AS filler
FROM DUAL
CONNECT BY LEVEL <= 150000; -- 占用约150MB
---执行失败，失败原因： [-502]：临时表空间不足