临时表空间的动态性能试图和数据字典

最新推荐文章于 2021-10-11 15:45:31 发布

cuanchang7550

最新推荐文章于 2021-10-11 15:45:31 发布

阅读量242

点赞数 1

文章标签：数据库

temp表空间的作用

临时表空间只保存 session 内的短暂数据。临时表空间可以存储：排序结果、临时表和临时索引、临时 lob 、临时 B 树。一个实例中的所有排序可以共享一个排序段。默认的临时表空间为 TEMP ，当然也可以建立其他临时表空间并指定默认临时表空间。在临时表空间中是不能显示的建立对象的。

更改默认临时表空间

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

查看默认临时表空间

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE

PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE

-------------------------- ------------------------------

DEFAULT_TEMP_TABLESPACE TEMP

ORA-1652问题处理

问题原因

ORA-1652是oracle数据库常见的错误之一，该错误主要是由于临时表空间不足导致的。在默认情况下，临时表空间临时性的存储了排序结果和临时表等信息，在大部分情况下由于SQL需要更多的临时表空间去排序，而没有更多的临时段分配的时候就会引发此错误。

解决办法

1.优化引发临时段占用过高的sql

SQL> Select user,session_addr,sql_id,tablespace from v$sort_usage;

USER SESSION_ADDR SQL_ID TABLESPACE

------------------------------ ---------------- ------------- -------------------------------

SYS 0000001D80F26D20 grdqgvq18ca7q TEMP

但这是查找当前使用排序段的sql，在错误发生的时候，事务其实已经失败了。最好是查看告警日志中的信息，找到告警日志中的trace文件并定位sql。

2.把临时表的默认表空间指向其他表空间

创建临时表空间

create temporary tablespace temp1 tempfile ‘dir1’ size 2000m

在创建临时表时指定到刚才创建的表空间中

CREATE GLOBAL TEMPORARY TABLE temp_tab_name

([column datatype [,column datatype])

[ON COMMIT {DELETE | PRESERVE} ROWS]

TALBESPACE temp1;

临时表就不会占用系统默认临时表空间，未临时表空间做排序省下了空间开支。

3.增大临时表空间

SQL> select FILE_NAME,BYTES,AUTOEXTENSIBLE from dba_temp_files;

FILE_NAME BYTES AUTOEXTENSIBLE

--------------------------------------------- ---------- --------------

+DATA/test/tempfile/temp.263.920211339 2632345190 YES

+DATA/ test/oradata/temp01 3221225472 NO

+DATA/ test/oradata/temp02 2116026368 NO

有2个文件没有开启autoextend，但是temp已经设置了最大值，temp02就需要设置到最大或者开启autoextend。

SQL> alter database tempfile '+DATA/test/oradata/temp02' resize 3221225472;

Database altered

或者

SQL> alter database tempfile '+DATA/test/oradata/temp02' autoextend on maxsize unlimited;

Database altered

然后看空间情况添加几个临时表空间数据文件

SQL> alter tablespace temp add tempfile '+DATA/test/oradata/temp3' size 3221225472;

Tablespace altered

…

temp表空间相关的动态性能视图、数据字典

DBA_TEMP_FREE_SPACE

可以查看临时表空间的总量和使用量和剩余量

dba_temp_files

临时表空间的数据文件

v$temp_extent_pool

临时区的使用情况

可以看到跟从v$temp_space_header试图中计算出来的使用值是很接近的

v$temp_space_header，DBA_TEMP_FREE_SPACE

临时表空间的分配量，我们来重点看下v$temp_space_header，DBA_TEMP_FREE_SPACE两个试图

从上面的查询看出，临时表空间的总量、使用量从各个视图查询出来的指都可以对应，但是，v$temp_space_header，DBA_TEMP_FREE_SPACE中的临时表空间剩余量却对应不上。

查找v$temp_space_header试图的定义

v$temp_space_header基于gv$temp_space_header试图，然后用同样的方式再查找gv$temp_space_header试图的定义

来自TS$,X$KTFTHC

查找DBA_TEMP_FREE_SPACE视图的定义

可以看出DBA_TEMP_FREE_SPACE是从gv$temp_space_header,gv$sort_segment两个试图中查找的，而且重点关注剩余空间的计算，除了计算gv$temp_space_header中的bytes_free，还计算了gv$sort_segment的free_blocks*blocksize。

刚好就是v$temp_space_header，DBA_TEMP_FREE_SPACE中剩余量的差值。

那么我们在看看gv$sort_segment的定义

完全就是从x$ktstssd中获取的未加工数据

那么到底哪个数据可以代表真实的临时表空间使用量呢？MOS文档给出了如下解释(文档 ID 2095211.1)

The views v$sort_usage or v$tempseg_usage ( and v$sort_segment) give the correct information regarding the allocation of sort segments.
We should always query these views to find out the actual temp usage.

The view v$temp_space_header shows that these many blocks were touched in each temp file at some point when temp usage was at its highest,
in essence, it shows the number of initialized blocks for each tempfile, not the actual allocated blocks.
The views v$sort_usage/v$tempseg_usage show the actual sort extents allocated for each transaction from these initialized blocks.

Also, v$temp_space_header is persistent across restarts. v$sort_segment and v$sort_usage are not.

大意是，v$sort_usage，v$tempseg_usage试图给出的是实际的使用量，而v$temp_space_header给出的是临时表空间数据文件的最大分配值，也可以叫做最大使用过的量。

V$TEMPSEG_USAGE,V$SORT_USAGE

都可以查到使用者