Oracle优化神技之临时表
2024-06-03 20:55 AlfredZhao 阅读(502) 评论(0) 编辑 收藏 举报Oracle临时表在处理临时数据、会话数据隔离和复杂查询优化方面非常有用。
其底层逻辑是通过Oracle特殊的临时表来减少I/O操作和日志开销，提高了数据库性能和查询效率。开发者可以根据具体需求和场景，合理使用临时表来简化数据处理逻辑和提高系统性能。
早期开发人员在使用Oracle数据库时，经常因为不熟悉或不了解全局临时表（Global Temporary Table，下文简称GTT）的特性，因而自行定义了所谓的“临时表”，不但增加了开发复杂度，比如需要自行做数据清理和会话隔离等问题，还因高频操作这类表产生了大量重做日志（redo logs），进而增加了I/O负载和系统开销，主要代价这么多，最终的应用性能还不够好。
所幸这类问题随着用户量的提升，大家口口相传这个最佳实践，后续开发已经很少会犯这类低级问题。
那是不是用了Oracle的临时表就可以高枕无忧了呢？
最近笔者在某客户遇到一个临时表的问题，在分析这个客户问题的过程，也和大家一起来回顾下有关Oracle临时表的知识。
- 1.创建临时表
- 2.临时表统计信息
- 3.临时表索引
- 4.临时表是否cache
- 5.临时表相关问题
1.创建临时表
本次遇到问题的临时表，是使用的Oracle的GTT，且定义表中数据是基于session-specific的类型，脱敏后的创建语句为：
CREATE GLOBAL TEMPORARY TABLE "JINGYU"."G_T_T1"
("ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(30) NOT NULL ENABLE)
ON COMMIT PRESERVE ROWS;
下面是官方文档截图，比较了GTT和PTT的差异：
除了上面提到的命名规则等差异之外，还要补充一点：
GTT是8i后就已经支持的技术，而PTT要在18c及以后版本才支持。
关于GTT的两种类型，文档说明如下：
根据你的应用需求选择，简单说就是如果想在事务结束就清空表，选择DELETE ROWS类型；如果想在会话结束才清空表，就选择PRESERVE ROWS类型。
2.临时表统计信息
临时表也是有统计信息的，而且临时表统计信息的机制在不同版本也有差异。
先看下在19c版本下表现：
我先在session1中插入两条测试数据，收集统计信息：
INSERT INTO G_T_T1 VALUES (1, 'Alfred');
INSERT INTO G_T_T1 VALUES (2, 'Mcdull');
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
cascade => TRUE
);
END;
/
SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';
紧接着在session2中插入一条数据，收集统计信息：
INSERT INTO G_T_T1 VALUES (3, 'Test');
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
cascade => TRUE
);
END;
/
SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';
两个查询结果是不一样的，两行结果，分别显示为2条和1条数据的统计信息。
--result1:
08:52:42 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';08:53:39 2 08:53:39 3
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- ------------ ----------
JINGYU G_T_T1
JINGYU G_T_T1 2 1 0 0
Elapsed: 00:00:00.02
--result2:
08:53:35 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
FROM dba_tab_statistics
WHERE table_name = 'G_T_T1';08:53:44 2 08:53:44 3
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- ------------ ----------
JINGYU G_T_T1
JINGYU G_T_T1 1 1 0 0
Elapsed: 00:00:00.01
这说明全局临时表在19c版本的默认统计信息是session级别。
--查看全局临时表的统计信息首选项设置
SELECT DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS', 'JINGYU', 'G_T_T1') AS global_temp_table_stats
FROM dual;
09:04:59 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> --查看全局临时表的统计信息首选项设置
SELECT DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS', 'JINGYU', 'G_T_T1') AS global_temp_table_stats
FROM dual;
09:05:00 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> 09:05:00 2
GLOBAL_TEMP_TABLE_STATS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SESSION
Elapsed: 00:00:00.01
具体查了下文档，说是在Oracle 12c及之后的版本中，全局临时表（GTT）的统计信息确实有可能是会话级别的。这意味着每个会话可以有自己的统计信息，这与全局共享的统计信息不同。
Default in 12c is now SESSION global temporary table statistics. Consider whether your database application depends on SHARED global temporary table statistics.
而如果你的数据库还是11g版本，那么这个统计信息的机制就是不同的，也就是SHARED，这里模拟将19c的这个也修改为SHARED模式，看看表现：
BEGIN
DBMS_STATS.SET_TABLE_PREFS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
pname => 'GLOBAL_TEMP_TABLE_STATS',
pvalue => 'SHARED'
);
END;
/
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'JINGYU',
tabname => 'G_T_T1',
cascade => TRUE
);
END;
/
成功修改为shared后，当表中有3条数据时收集统计信息后再次查询，会发现这个统计信息在其他会话也可以访问到：
09:10:46 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> r
1 SELECT owner, table_name, num_rows, blocks, empty_blocks, avg_space
2 FROM dba_tab_statistics
3* WHERE table_name = 'G_T_T1'
OWNER TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE
------------------------------ ------------------------------ ---------- ---------- ------------ ----------
JINGYU G_T_T1 3 1 0 0
Elapsed: 00:00:00.02
09:10:48 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1>
3.临时表索引
GTT是可以创建索引的，当然这个索引也是临时的属性，另外还可以在GTT上创建视图和触发器。
You can create indexes for global (not private) temporary tables with the CREATE INDEX
statement. These indexes are also temporary. The data in the index has the same
session or transaction scope as the data in the temporary table. You can also create a
view or trigger on a global temporary table.
4.临时表是否cache
使用ALTER TABLE ... CACHE可以提高GTT查询性能。
ALTER TABLE G_T_T1 CACHE;
在Oracle中，通过使用ALTER TABLE ... CACHE语句可以在创建GTT时指定缓存属性。GTT是一种特殊类型的数据库表，用于存储临时数据，数据在会话结束或事务完成时被清除。CACHE关键字在这里的作用是指定GTT的缓存属性。
具体来说，CACHE关键字指示Oracle数据库将GTT的数据块缓存在内存中，而不是直接写入磁盘。这样做的好处是可以提高查询临时表数据的性能，因为访问内存通常比访问磁盘要快得多。
需要注意的是，使用CACHE会占用更多的内存空间，因为临时表的数据在会话结束或事务完成后会被清除，所以对于大型数据量或长时间运行的会话，可能需要权衡内存利用和性能。
临时表是否设置了CACHE属性是可以通过dbms_metadata.get_ddl中看到cache标识的：
select dbms_metadata.get_ddl('TABLE','G_T_T1','JINGYU') from dual;
CREATE GLOBAL TEMPORARY TABLE "JINGYU"."G_T_T1"
( "ID" NUMBER(10,0) NOT NULL ENABLE,
"NAME" VARCHAR2(30) NOT NULL ENABLE
) ON COMMIT PRESERVE ROWS
CACHE
特别需要注意的是，这个简单的cache命令，在线直接执行很可能会失败：
alter table G_T_T1 cache;
会报错ORA-14450：
14:46:14 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> alter table G_T_T1 cache;
alter table G_T_T1 cache
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use
Elapsed: 00:00:00.02
这是因为有会话在使用GTT，确保没有使用的会话重新执行才能成功：
14:51:14 PRIMARY @DB0913_9DF_IAD -> JINGYU @DEMO1> alter table G_T_T1 cache;
Table altered.
Elapsed: 00:00:00.10
5.临时表相关问题
顺便查了一些相关问题，作为扩展供大家学习参考：
FYI:
- Performance Issue After Creating Global Temporary Table After Upgrading to 12.2 (Doc ID 2509782.1)
- How to Create Statistics on Global Temporary Tables (Doc ID 351190.1)
- https://blogs.oracle.com/optimizer/post/global-temporary-tables-and-upgrading-to-oracle-database-12c-dont-get-caught-out
