当前位置：首页 > news >正文

72-Oralce Temporay tablespace(单实例和多租户下的管理)

news 2025/6/29 2:13:56

小伙伴们，生产库是不是已经慢慢都升级到12c+以上的19c这样的多租户场景了，那么临时表空间做了规划了么，有针对性能的提升么

官方推荐为CDB和每个PDB分别创建独立的临时表空间

为了多租户下，资源隔离与独立性，每个PDB应有专属临时表空间：防止某个PDB的临时空间操作（如大型排序、哈希连接）影响其他PDB或CDB根容器。

CDB根容器也需要独立临时表空间，用于系统级操作（如元数据管理）和连接到根容器的会话。

默认行为支持分离，创建PDB时，若未显式指定临时表空间，PDB会自动继承CDB的临时表空间，但这个官方不推荐。

官方手册建议在PDB创建后立即显式指定其专属临时表空间，避免跨容器资源争用

临时表空间组（Temporary Tablespace Group）的增强应用

为提升并发性能和负载均衡，Oracle推荐使用临时表空间组（由多个临时表空间组成），

尤其适用于高并发场景：

并发性提升：不同会话可并行使用组内不同临时表空间，减少I/O竞争。

故障隔离：单个临时文件损坏不影响整体可用性。

动态扩展：可在线添加/移除临时表空间，无需停机

另外在有些极端的开发场景需要大量插入数据后又删除，

优先选择临时表空间+全局临时表（GTT），避免普通表空间的手动管理开销和性能损耗。

一、临时表空间核心功能与技术原理

1. 功能定义

核心作用：存储会话级临时数据（排序、哈希连接、全局临时表、并行查询中间结果），事务结束或会话终止后自动释放。
关键特性：
- 使用临时文件（Tempfile），不生成Redo日志，空间按需分配（稀疏文件），无需预初始化。
- 数据以临时段（Temporary Segments） 管理，同一实例的所有SQL操作共享排序段，实例关闭时释放。

2. 技术原理

SEP管理机制（Sort Extent Pool）：
- 自Oracle 7.3引入，通过共享池中的位图管理临时段扩展分配，减少频繁空间分配开销。
- 排序操作优先使用PGA内存，溢出时从SEP分配空闲扩展区（Extent），完成后标记为空闲而非释放。
与永久表空间对比：

特性	临时表空间	永久表空间
存储内容	中间结果（排序/连接/临时表）	永久对象（表/索引）
持久性	会话结束自动清理	数据永久保存
空间分配	按需动态分配（稀疏文件）	预分配且初始化
日志机制	无Redo，UNDO变化产生Redo	完整Redo日志

特性	临时表空间（临时表）	普通表空间
存储位置	临时存储（TEMP表空间）	永久存储（如USERS表空间）
生命周期	自动清理（会话结束或事务提交）	需手动删除表，否则持久存在
I/O性能	无Redo日志，仅少量Undo，写入速度快30%~50%	产生Redo/Undo日志，写入开销大
锁与碎片	无表级锁竞争，空间自动回收	频繁删表导致元数据锁争用、碎片
适用场景	临时测试数据的首选	需跨会话共享的持久数据

二、版本演进与关键特性

1. Oracle 7.3

SEP算法引入：统一管理临时段扩展，减少碎片和闩锁竞争。

2. Oracle 8i

专用临时表空间：禁止永久对象存储，隔离临时数据，解决SYSTEM表空间碎片问题。

3. Oracle 9i

默认临时表空间：全局配置DEFAULT TEMPORARY TABLESPACE，避免用户未指定时占用SYSTEM空间。
本地化管理：默认采用位图管理空间分配，提升效率。

4. Oracle 10g

临时表空间组（Tablespace Groups）：
- 允许单操作跨多个临时表空间，提升并行排序能力。
- 创建语法：

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/opt/oracle/oradata/FREE/FREEPDB1/temp02.dbf' SIZE 100M TABLESPACE GROUP group1;
ALTER USER HR TEMPORARY TABLESPACE group1;

5. Oracle 11g-12c

PGA自动管理：PGA_AGGREGATE_TARGET优化，减少磁盘排序。
临时Undo（12c）：全局临时表的Undo写入临时表空间，减少Redo生成。

6. Oracle 19c/23ai（多租户增强）

PDB级隔离：每个PDB拥有独立默认临时表空间，支持资源管理器限制PDB临时空间配额：
CDB级共享：支持创建全局临时表空间供所有PDB共享。

三、单实例和多租户下的管理实践

1. 创建与分配

单实例：

CREATE TEMPORARY TABLESPACE temp_tbs TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 30G;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_tbs;

多租户：

ALTER SESSION SET CONTAINER=PDB1;
CREATE TEMPORARY TABLESPACE pdb_temp TEMPFILE '+DATA' SIZE 2G;
ALTER PLUGGABLE DATABASE DEFAULT TEMPORARY TABLESPACE pdb_temp;

碎片清理：

ALTER TABLESPACE temp_tbs SHRINK SPACE KEEP 1G;  -- 在线收缩

3. 性能调优

减少磁盘排序：

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=4G;  -- 增大PGA内存

SQL优化：避免无索引的大表GROUP BY，使用分区过滤减少排序数据量。

四、监控与验证脚本

1. 基础信息查询

-- 查看所有临时文件
SELECT file_name, bytes/1024/1024 AS size_mb, autoextensible 
FROM dba_temp_files;
--
FILE_NAME                                                   SIZE_MB AUTOEXTENSIBLE
________________________________________________________ __________ _________________
/opt/oracle/oradata/FREE/FREEPDB1/temp01.dbf                     84 YES
/opt/oracle/product/23ai/dbhomeFree/dbs/hr_temp01.dbf            50 NO
/opt/oracle/product/23ai/dbhomeFree/dbs/OE_temp01.dbf            50 NO
/opt/oracle/product/23ai/dbhomeFree/dbs/SH_temp01.dbf            50 NO
/opt/oracle/product/23ai/dbhomeFree/dbs/PM_temp01.dbf            50 NO
/opt/oracle/product/23ai/dbhomeFree/dbs/CO_temp01.dbf            50 NO-- 默认临时表空间
SELECT property_value FROM database_properties 
WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
--
PROPERTY_VALUE
_________________
TEMP

2. 空间使用分析包含PDB和CDB整体视角

-- 单实例使用率
SELECT tablespace_name, (tablespace_size - free_space)/tablespace_size * 100 AS "使用率%"
FROM dba_temp_free_space;
--
TABLESPACE_NAME                                           使用率%
__________________ ___________________________________________
TEMP                  2.38095238095238095238095238095238095238
-- 多租户环境（CDB级别）
SELECT c.con_id AS container_id,c.name AS pdb_name,t.tablespace_name,t.status,f.tablespace_size * ts.block_size / 1024 / 1024 AS total_mb,f.free_space * ts.block_size / 1024 / 1024 AS free_mb,ROUND((f.tablespace_size - f.free_space) * 100 / f.tablespace_size, 2) AS used_pct
FROM cdb_tablespaces t
JOIN cdb_temp_free_space f ON t.con_id = f.con_id AND t.tablespace_name = f.tablespace_name
JOIN (SELECT con_id, tablespace_name, block_size FROM cdb_tablespaces WHERE contents = 'TEMPORARY') ts ON t.con_id = ts.con_id AND t.tablespace_name = ts.tablespace_name
JOIN v$containers c ON t.con_id = c.con_id
WHERE t.contents = 'TEMPORARY'
ORDER BY c.con_id, t.tablespace_name;
--CONTAINER_ID PDB_NAME    TABLESPACE_NAME    STATUS       TOTAL_MB    FREE_MB    USED_PCT
_______________ ___________ __________________ _________ ___________ __________ ___________1 CDB$ROOT    TEMP               ONLINE         688128     671744        2.383 FREEPDB1    CO_TEMP            ONLINE         409600     401408           23 FREEPDB1    HR_TEMP            ONLINE         409600     401408           23 FREEPDB1    OE_TEMP            ONLINE         409600     401408           23 FREEPDB1    PM_TEMP            ONLINE         409600     401408           23 FREEPDB1    SH_TEMP            ONLINE         409600     401408           23 FREEPDB1    TEMP               ONLINE         688128     655360        4.763 FREEPDB1    TEMP1              ONLINE         819200     811008           18 rows selected.

3. 高消耗会话追踪

-- 定位排序操作TOP会话
SELECT s.username, s.sid, su.blocks * p.value/1024/1024 AS used_mb, sql.sql_text
FROM v$session s, v$sort_usage su, v$sql sql, v$parameter p
WHERE s.saddr = su.session_addr
AND sql.sql_id = s.sql_id
AND p.name = 'db_block_size'
ORDER BY used_mb DESC;

4. 临时表空间组状态

SELECT group_name, tablespace_name FROM dba_tablespace_groups;

五、常见问题解决方案

问题	原因	解决方案
ORA-01652（空间不足）	临时文件未自动扩展或空间耗尽	ALTER TABLESPACE temp ADD TEMPFILE '+DATA' SIZE 5G AUTOEXTEND ON;
临时文件碎片化严重	频繁分配/释放临时段	重建表空间：新建临时表空间 → 设为默认 → 删除旧空间
PDB临时空间占用过高	未限制资源或SQL低效	通过Resource Manager设置 TEMP_SPACE_LIMIT