oracle中临时段的使用回收规则

最新推荐文章于 2021-04-05 17:52:28 发布

P10ZHUO

最新推荐文章于 2021-04-05 17:52:28 发布

阅读量1.5k

点赞数

分类专栏：安装配置文章标签： oracle

本文链接：https://blog.csdn.net/fanzhuozhuo/article/details/112644862

版权

安装配置专栏收录该内容

9 篇文章 1 订阅

订阅专栏

smon回收临时段

perment表空间里的临时段
temp表空间使用原则

之前的由于出现问题：与temp表空间相关的操作数据库都会hang。特此对oracle中临时表空间做一个深入了解，结果发现，内有乾坤。oracle中的临时段有2种：
如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。
mos解释如下：
在这里插入图片描述

perment表空间里的临时段

用于临时操作的临时段不仅仅存在于临时表空间中，在普通的表空间里也可能存在临时段，比如我们通过CTAS创建一张表，在CTAS命令没有结束前，新的表的数据是放在临时段中的，这些临时段在CTAS完成的时候会转换为PERMENT段。如果这些临时操作由于某些原因异常中止了，那么这些临时段就没有创建完成，也没有最终转为PERMENT段，这种情况下，我们就可以观察到在PERMENT表空间中也会存在临时段。这些临时段会被SMON自动清理掉。不过也会因为某些情况或者BUG，SMON长时间没有清理掉这些临时段，这种情况在早期的ORACLE数据库版本中会出现，Oracle 10g以后出现的较少。如果要手工清理，可以使用drop_segments诊断事件。

EVENT: 10500 “turn on traces for SMON”

下面我们测试下SMON清理自动清理的机制
要想跟踪SMON清理过程，必须设置一个事件

[oracle@oracle11g trace]$ oerr ora 10500
10500, 00000, "turn on traces for SMON"
// *Document: NO
// *Cause:
// *Action:
//    Level:  <=5  trace instance recovery
//            > 5  trace posting of SMON


Error:  ORA 10500
Text:   turn on traces for SMON
-------------------------------------------------------------------------------
Cause:
Action:
        Level:  <=5  trace instance recovery
        > 5  trace posting of SMON

To set event 10500:

For the instance:
a.  Shutdown database
b.  Edit the initialisation parameter file and add:
    event="10500 trace name context forever, level <value>"
c.  restart the database

For the SMON session:
Post the SMON process using oradbx (Oracle 7) or oradebug (Oracle 8).
For oradebug from server manager issue:

oradebug setospid <OS PID>
oradebug event 10500 trace name context forever, level <value>

For further information about oradebug Note 29786.1
                              oradbx   Note 28863.1

<value> is 'Level' as per above

测试event 10500：
默认情况下，路径下没有smon的trace文件

[oracle@oracle11g trace]$ pwd
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace
[oracle@oracle11g trace]$ ls -ltr *smon*
ls: cannot access *smon*: No such file or directory

打开trace跟踪

SQL> alter system set events '10500 trace name context forever,level 10';

System altered.
[oracle@oracle11g trace]$ ls -ltr *smon*
ls: cannot access *smon*: No such file or directory
[oracle@oracle11g trace]$

还是没有文件产生。

执行建表语句

SQL> create table smon as select * from dba_objects;

Table created.
[oracle@oracle11g trace]$ ls -ltr *smon*
-rw-r-----. 1 oracle oinstall   87 Jan 14 22:54 zhuo_smon_1471.trm
-rw-r-----. 1 oracle oinstall 1509 Jan 14 22:54 zhuo_smon_1471.trc

trace产生。

部分片段：

[oracle@oracle11g trace]$ tail -200f zhuo_smon_1471.trc 
Trace file /u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_smon_1471.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      oracle11g
Release:        2.6.32-642.el6.x86_64
Version:        #1 SMP Wed Apr 13 00:51:26 EDT 2016
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: zhuo
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 1471, image: oracle@oracle11g (SMON)


*** 2021-01-14 22:54:06.160
*** SESSION ID:(13.1) 2021-01-14 22:54:06.160
*** CLIENT ID:() 2021-01-14 22:54:06.160
*** SERVICE NAME:(SYS$BACKGROUND) 2021-01-14 22:54:06.160
*** MODULE NAME:() 2021-01-14 22:54:06.160
*** ACTION NAME:() 2021-01-14 22:54:06.160
 
SMON: process sort segment requests begin

*** 2021-01-14 22:54:06.160
SMON: process sort segment requests end

*** 2021-01-14 22:54:06.160
SMON: Posted, but not for trans recovery, so skip it.
SMON: obj$ cleanup begin
SMON: obj$ cleanup end. more:0
SMON: cleanup the cursor transient types begin
SMON: cleanup the cursor transient types end
SMON: launch SMCO begin
SMON: launch SMCO end
SMON: poll shared mount broadcast channel begin
SMON: poll shared mount broadcast channel end
SMON: poll flashback broadcast channel begin
SMON: poll flashback broadcast channel end
SMON: poll segment info broadcast channel begin
SMON: poll segment info broadcast channel end

在创建一条索引

SQL> create index idx_smon on smon(object_id);

Index created.

会有如下trace产生：

*** 2021-01-14 22:56:34.632
SMON: system monitor process posted msgflag:0x1000 (-/-/-/-/-/-/-)

*** 2021-01-14 22:56:34.634
SMON: process sort segment requests begin

*** 2021-01-14 22:56:34.634
SMON: process sort segment requests end

*** 2021-01-14 22:56:34.634
SMON: parallel transaction recovery begin

*** 2021-01-14 22:56:34.635
SMON: parallel transaction recovery end
SMON: offline rollback segment begin
SMON: offline rollback segment end
SMON: launch SMCO begin
SMON: launch SMCO end
SMON: poll shared mount broadcast channel begin
SMON: poll shared mount broadcast channel end
SMON: poll flashback broadcast channel begin
SMON: poll flashback broadcast channel end
SMON: poll segment info broadcast channel begin
SMON: poll segment info broadcast channel end

说明smon的跟踪被打开，只有有事务或者段操作，才会产生smon trace。

关闭：

SQL>  alter system set events '10500 trace name context off';

System altered.

SQL> create index idx_na on smon(object_name);

Index created.

即使创建索引，也不再产生trace，trace内容不再更新。

smon是如何清理永久表空间上的temporary segment

设置10500事件以跟踪smon进程

session 1：
SQL> alter system set events '10500 trace name context forever,level 10';

System altered.
执行create table命令，这将产生一定量的Temporary Extents（在建表的过程中会产生temporary，创建完后，永远也查不到这种类型数据，所以建的表一定要大，时间长，我们才能捕捉到）
SQL> conn zhuo/zhuo
SQL> create table myTestTable6 as
  2  select rownum as id,
  3  to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
  4  trunc(dbms_random.value(0, 100)) as random_id,
  5  dbms_random.string('x', 20) random_string
  6  from dual
  7  connect by level <= 50000000;

在另一个会话中执行对DBA_EXTENTS视图的查询，可以发现产生了多少临时区间

session 2：
SQL>  SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';

  COUNT(*)
----------
         0

SQL> /

  COUNT(*)
----------
        18

SQL> /

  COUNT(*)
----------
        26
--Temporary一直在增加
SQL> select name,value from v$sysstat where name like '%SMON%';
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
total number of times SMON posted                                        17
SMON posted for undo segment recovery                                     0
SMON posted for txn recovery for other instances                          0
SMON posted for instance recovery                                         0
SMON posted for undo segment shrink                                       0
SMON posted for dropping temp segment                                     4

6 rows selected.
可以通过v$sysstat视图中的”SMON posted for dropping temp segment”事件统计信息来了解SMON收到清理要求的情况，现在为4

终止以上create table的session 1，等待一段时间后观察smon后台进程的trc可以发现以下信息:
*** 2021-01-28 05:22:13.978
SMON: system monitor process posted msgflag:0x0200 (-/-/-/-/TMPSDROP/-/-)

*** 2021-01-28 05:22:13.982
SMON: process sort segment requests begin

*** 2021-01-28 05:22:13.982
SMON: process sort segment requests end

*** 2021-01-28 05:22:13.982
SMON: Posted, but not for trans recovery, so skip it.
SMON: cleanup temp segment begin

*** 2021-01-28 05:22:13.984
SMON: clean up temp segments in slave
SMON: cleanup temp segment end
SMON: check for unpinning handles begin
KGL handles upinned : 0
SMON: check for unpinning handles begin
SMON: poll shared mount broadcast channel begin
SMON: poll shared mount broadcast channel end
SMON: poll flashback broadcast channel begin
SMON: poll flashback broadcast channel end
SMON: poll segment info broadcast channel begin
SMON: poll segment info broadcast channel end

可以看到smon通过slave进程完成了对temporary segment的清理.

说明：
1、对于永久表空间上的temporary segment，SMON会三分钟清理一次(前提是接到post)，如果SMON过于繁忙那么可能temporary segment长期不被清理。temporary segment长期不被清理可能造成一个典型的问题是:在rebuild index online失败后，后续执行的rebuild index命令要求之前产生的temporary segment已被cleanup，如果cleanup没有完成那么就需要一直等下去。在10gR2中我们可以使用dbms_repair.online_index_clean来手动清理online index rebuild的遗留问题:
2、如果遇到SMON一直没有清理永久表空间里的临时段，会造成虽然永久表空间真正使用的量并不大，但是空闲比例很低。我们可以使用event:drop_segments来手工清理这些临时段。
alter session set events ‘immediate trace name DROP_SEGMENTS level TS#+1’;
SMON的清理操作如果中途失败，那么这些临时段就会遗留在表空间中，不会马上被清除。pmon发现会话异常后，会通知smon在后台清理这些temporary segment,不过有时候smon会出现一些异常，无法及时清理这些对象。有可能这些对象会长期遗留下来。会造成表空间真正使用的量并不大，但是空闲比例很低，里面有大量的temporary 段。遇到这种情况，我们可以使用event:drop_segments来手工清理这些临时段。

 alter session set events 'immediate trace name DROP_SEGMENTS level TS#+1';
select ts# from v$tablespace

如何禁止SMON清理临时段

可以通过设置诊断事件event=’10061 trace name context forever, level 10’禁用SMON清理临时段(disable SMON from cleaning temp segments)。

alter system set events '10061 trace name context forever, level 10';

temp表空间使用原则

temp表空间是可以释放的，有好几种方法，重启或是重建是在特殊情况下才使用的。hash join，with as，lob字段，也都有可能使用临时表空间，lob字段在某些版本确实很难释放临时表空间，需要设置一个event解决这个问题。

临时表空间包含仅在会话期间持续存在的临时数据。临时表空间可以提高无法装入内存的多个排序操作的并发性，还可以提高排序期间空间管理操作的效率。在临时表空间中，针对特定实例的所有排序操作共享一个排序段，并且每个执行需要临时空间的排序操作的实例都存在排序段。排序段是由在启动后使用临时表空间进行排序的第一个语句创建的，只有在关闭时才释放。默认情况下，为每个新的 Oracle 数据库安装创建一个名为 TEMP 的临时表空间。也可以使用 create TABLESPACE 语句创建额外的临时表空间。

临时表空间用于存储以下内容:
中间结果排序；
临时表和临时索引；
临时 Lob；
临时 B tree

Oracle 临时表空间作用
Oracle 临时表空间主要用来做查询和存放一些缓冲区数据。临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间，如果不能重启实例，而一直保持问题 SQL 语句的执行，temp 表空间会一直增长直到耗尽磁盘空间。网络上有人猜测在磁盘空间的分配上，oracle 使用的是贪心算法，如果上次磁盘空间消耗达到 1GB，那么临时表空间就是 1GB。也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小。

临时表空间的主要作用：
索引 create 或 rebuild；
Order by 或 group by；
Distinct 操作；
Union 或 intersect 或 minus；
Sort-merge joins；
analyze.
如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创建一个sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。
临时表空间中创建的临时段一次分配，多次使用。同数据表空间的使用原则不同。
当排序等操作完成之后，空间不会被回收，而是标记相应的Extents为Free，这些Extents可以被重用。
这些区的管理，通过一个新的内存组件Sort Extent Pool（SEP）来完成，该内存在SGA中分配。

创建测试表

session 1：
create table testt1 (id number, name varchar2(1));
begin
for i in 1 .. 1000000 loop
insert into testt1 values(i, dbms_random.string('a',1));
end loop;
commit;
end;
/

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TESTT1';

SUM(BYTES)/1024/1024
--------------------
                  13

默认temp数据文件自动扩展打开，为了测试，设置小一点，关闭自动扩展，观察使用情况
通过查询V$SORT_SEGMENT来看temp segment是free还是being used

session 2：
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    7936           0        7936

SQL> 
SQL> 
SQL> alter database tempfile 1 resize 13m;

Database altered.

SQL> ALTER DATABASE tempfile 1 autoextend off;

Database altered.

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                       0           0           0
--此时临时段还没有被分配，大小为0。

验证什么情况下会使用临时段

session 1：
SQL> select * from testt1;
。。。。。
        ID N
---------- -
    648737 M
    648738 B
    648739 e
    648740 T
    648741 S
    648742 K
    648743 k
    648744 a
    648745 q
    648746 g
 。。。。。。。。
不断的刷屏

session 2：
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                       0           0           0
--没有sort，所以不占用临时段。此时和初始状态一致，没有分配。

开始排序

session 1：
SQL> select * from testt1 order by 1;
select * from testt1 order by 1
              *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
--开始报错临时段不足，说明查询已经开始占用临时段了。
session 2：

SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    1536           0        1536

SQL> select 1536*8/1024 from dual;

1536*8/1024
-----------
         12
--开始第一次sort，临时段就开始分配好了。
才12M，所以不够，因为可能会有段头块。全部都是free，因为报错了，所以还没开始执行。

验证free 和used的使用变化。

session 2：
SQL> alter database tempfile 1 resize 14m;

Database altered.

SQL>  alter database tempfile 1 resize 15m;

Database altered.

session 1：
SQL> select * from testt1 order by 1;
select * from testt1 order by 1
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
session 2：
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    1792           0        1792  --sort后，临时段扩展到了1792个块。

SQL> alter database tempfile 1 resize 16m;

Database altered.

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    1792           0        1792 ---此时没有sort查询，就没有使用临时段的需求，所以临时段的大小还是1792个block。

session 1：
SQL> select * from testt1 order by 1;
select * from testt1 order by 1
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    1920           0        1920  --现在又sort查询，所以临时段需要扩展，才会变到最大1920个。

SQL> alter database tempfile 1 resize 17m;

Database altered.
经验证，一直到17m的时候，sort查询才没问题。不知道此处为什么时17m？
session 1：
SQL>  select * from testt1 order by 1;
。。。。。
        ID N
---------- -
    293426 o
    293427 t
    293428 d
    293429 J
    293430 o
    293431 V
    293432 o
    293433 j
    293434 x
    293435 W
    293436 L
。。。。。。。
无尽的刷屏。
ctrl+c停止。

session 2：
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    2048        2048           0
SQL> select 2048*8/1024 from dual;

2048*8/1024
-----------
         16
--此时需要的临时段时16M，13M的数据，需要16M的临时段，原因待定。
虽然把查询取消了，但是临时段的分配提前已经分配好了，所以临时段时刚开始查询的时候，就已经提前一次性分配了。

sort结束后，used就会变为free的。

session 1：
SQL> exit
session 2：
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    2048           0        2048
--在做sort的时候，segment标识为used，sort结束后，标志为free。实际上这时候相应的extents都被释放回SEP（sort extent pool）里面。

此处参考老熊的博客：一次临时表空间大量占用问题的处理

对于Fetch Cursor，有两点：
1、一个游标，或者说一条SQL语句，并不要求客户端把所有数据取完，只取了一部分数据就关闭游标也是可以的。
2、只要还没有关闭游标，数据库就要维护该游标的状态，如果是排序的SQL，也需要维持该SQL已经排好序的数据。
很显然，从上述第2点可以知道，如果一条SQL使用了临时段来排序，在SQL对应的游标没关闭的情况下，Oracle数据库不会去释放临时段，因为对于Oracle数据库来说，它不会知道客户端是否还要继续取游标的数据。

只有在会话游标被关闭，临时段就会被释放。即由used变为free。
此处会话都exit退出了，根据临时特性，临时数据肯定释放了。
但是在实际当中，很少又exit会话退出的，一般都只有重启实例，空间才会释放，使用率才会下降。
6. 如果比之前的表小做排序，临时段会缩小吗？

session 1：

SQL> create table testt2 as select * from testt1 where rownum<500000; 

Table created.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TESTT2';

SUM(BYTES)/1024/1024
--------------------
                   7
session 2：                   
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    2048           0        2048
--发现表的大小比分配好的临时段大小小时，都不会使用临时段。

打开temp文件自动扩展，验证是否会不断增长？

session 1：
SQL>  insert into testt1 select * from testt1;

1010098 rows created.

SQL> commit;

Commit complete.

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='TESTT1';

SUM(BYTES)/1024/1024
--------------------
                  27
session 2：
SQL> ALTER DATABASE tempfile 1 autoextend on;

Database altered.

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    2048           0        2048
SQL> SELECT D.tablespace_name, 
  2                 SPACE                                      "SUM_SPACE(M)", 
  3                 blocks                                     "SUM_BLOCKS", 
  4                 used_space                                 "USED_SPACE(M)", 
  5                 Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)", 
  6                 SPACE - used_space                         "FREE_SPACE(M)" 
  7          FROM   (SELECT tablespace_name, 
  8                         Round(SUM(bytes) / ( 1024 * 1024 ), 2) SPACE, 
  9                         SUM(blocks)                            BLOCKS 
 10                  FROM   dba_temp_files 
 11                  GROUP  BY tablespace_name) D, 
 12                 (SELECT tablespace, 
 13                         Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE 
 14                  FROM   v$sort_usage 
 15                  GROUP  BY tablespace) F 
 16          WHERE  D.tablespace_name = F.tablespace(+)
 17            AND  D.tablespace_name='TEMP';

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP                                     17       2176                          0
--temp文件大小现在为17m
session 1：
开始sort排序
SQL> select * from testt1 order by 1;
不断刷屏。
ctrl+c停止。

session 2：
SQL> /

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP                                     33       4224            32        96.97             1

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    4096        4096           0

SQL> select 4096*8/1024 from dual;

4096*8/1024
-----------
         32
--temp数据文件一直扩展到了33M。
所以随着表的增大，temp表空间会一直增长变大。

当排序等操作完成之后，空间不会被回收，而是标记相应的Extents为Free，这些Extents可以被重用。

session 2：
SQL> ALTER DATABASE tempfile 1 autoextend off;

Database altered.

SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    4096        4096           0
session 1：
SQL> select * from testt1 order by 1；
不断刷屏
ctrl+c停止

session 2：
SQL> /

TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------------------- ------------ ----------- -----------
TEMP                                    4096        4096           0

SQL> SELECT D.tablespace_name, 
  2                 SPACE                                      "SUM_SPACE(M)", 
  3                 blocks                                     "SUM_BLOCKS", 
  4                 used_space                                 "USED_SPACE(M)", 
  5                 Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)", 
  6                 SPACE - used_space                         "FREE_SPACE(M)" 
  7          FROM   (SELECT tablespace_name, 
  8                         Round(SUM(bytes) / ( 1024 * 1024 ), 2) SPACE, 
  9                         SUM(blocks)                            BLOCKS 
 10                  FROM   dba_temp_files 
 11                  GROUP  BY tablespace_name) D, 
 12                 (SELECT tablespace, 
 13                         Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE 
 14                  FROM   v$sort_usage 
 15                  GROUP  BY tablespace) F 
 16          WHERE  D.tablespace_name = F.tablespace(+)
 17            AND  D.tablespace_name='TEMP';

TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
------------------------------ ------------ ---------- ------------- ------------ -------------
TEMP                                     33       4224            32        96.97             1
--temp文件没有再增长。而是重复使用。

当你监控temporary tablespace 的空闲空间，发现空闲空间并没有随着sort的结束而增加，甚至当前数据库没有任何客户端连接也是。
那么很有可能是你的监控语句写错了。

你很有可能使用如下监控临时表空间的语句：
SELECT TU.TABLESPACE_NAME AS “TABLESPACE_NAME”,
TT.TOTAL - TU.USED AS “FREE(G)”,
TT.TOTAL AS “TOTAL(G)”,
ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS “USED(%)”,
ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS “FREE(%)”
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

SQL> SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
  2         TT.TOTAL - TU.USED                                    AS "FREE(G)",
  3         TT.TOTAL                                              AS "TOTAL(G)",
  4         ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
  5         ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
  6  FROM (SELECT TABLESPACE_NAME, 
  7                SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
  8         FROM GV_$TEMP_SPACE_HEADER
  9         GROUP BY TABLESPACE_NAME) TU ,
 10       (SELECT TABLESPACE_NAME,
 11                SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
 12         FROM DBA_TEMP_FILES
 13         GROUP BY TABLESPACE_NAME) TT
 14  WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

Tablespace Name         FREE(G)   TOTAL(G)    USED(%)    FREE(%)
-------------------- ---------- ---------- ---------- ----------
TEMP                          0 .016601563        100          0    --啥都没干，每业务，使用率100%
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2254824 bytes
Variable Size             352323608 bytes
Database Buffers          163577856 bytes
Redo Buffers                3780608 bytes
Database mounted.
Database opened.
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

Tablespace Name      TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
-------------------- ------------ ----------- -----------
TEMP                         2048           0        2048
SQL> SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
  2         TT.TOTAL - TU.USED                                    AS "FREE(G)",
  3         TT.TOTAL                                              AS "TOTAL(G)",
  4         ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
  5         ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
  6  FROM (SELECT TABLESPACE_NAME, 
  7                SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
  8         FROM GV_$TEMP_SPACE_HEADER
  9         GROUP BY TABLESPACE_NAME) TU ,
 10       (SELECT TABLESPACE_NAME,
 11                SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
 12         FROM DBA_TEMP_FILES
 13         GROUP BY TABLESPACE_NAME) TT
 14  WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

Tablespace Name         FREE(G)   TOTAL(G)    USED(%)    FREE(%)
-------------------- ---------- ---------- ---------- ----------
TEMP                          0 .016601563        100          0   --如上面所说，即时重启，临时表空间也不释放，难道真的不释放吗？
SQL> alter tablespace temp add tempfile  size 20m;
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

Tablespace Name      TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
-------------------- ------------ ----------- -----------
TEMP                         2048           0        2048
SQL> SELECT TU.TABLESPACE_NAME                                    AS "TABLESPACE_NAME",
  2         TT.TOTAL - TU.USED                                    AS "FREE(G)",
  3         TT.TOTAL                                              AS "TOTAL(G)",
  4         ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3)            AS "USED(%)",
  5         ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
  6  FROM (SELECT TABLESPACE_NAME, 
  7                SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
  8         FROM GV_$TEMP_SPACE_HEADER
  9         GROUP BY TABLESPACE_NAME) TU ,
 10       (SELECT TABLESPACE_NAME,
 11                SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
 12         FROM DBA_TEMP_FILES
 13         GROUP BY TABLESPACE_NAME) TT
 14  WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;

Tablespace Name         FREE(G)   TOTAL(G)    USED(%)    FREE(%)
-------------------- ---------- ---------- ---------- ----------
TEMP                 .018554688 .036132813     48.649     51.351   --只有在添加一个数据文件，使用率才会下降。

参考：ORACLE使用GV_$TEMP_SPACE_HEADER统计临时表空使用情况不准确的问题

因为使用v$temp_space_header和gv$temp_space_header统计的数据不准确，官方解释为：
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$temp_space_header显示的是每一个temp文件在某一个时刻使用过的最大大小，从本质上说，它显示的是每一个tempfile的初始化大小，而不是实际分配的块大小

正确得监控临时表空间使用率得语句如下，查询v$sort_usage视图：

SELECT D.tablespace_name, 
               SPACE                                      "SUM_SPACE(M)", 
               blocks                                     "SUM_BLOCKS", 
               used_space                                 "USED_SPACE(M)", 
               Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)", 
               SPACE - used_space                         "FREE_SPACE(M)" 
        FROM   (SELECT tablespace_name, 
                       Round(SUM(bytes) / ( 1024 * 1024 ), 2) SPACE, 
                       SUM(blocks)                            BLOCKS 
                FROM   dba_temp_files 
                GROUP  BY tablespace_name) D, 
               (SELECT tablespace, 
                       Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE 
                FROM   v$sort_usage 
                GROUP  BY tablespace) F 
        WHERE  D.tablespace_name = F.tablespace(+)
          AND  D.tablespace_name='TEMP';

SQL> SELECT D.tablespace_name, 
  2                 SPACE                                      "SUM_SPACE(M)", 
  3                 blocks                                     "SUM_BLOCKS", 
  4                 used_space                                 "USED_SPACE(M)", 
  5                 Round(Nvl(used_space, 0) / SPACE * 100, 2) "USED_RATE(%)", 
  6                 SPACE - used_space                         "FREE_SPACE(M)" 
  7          FROM   (SELECT tablespace_name, 
  8                         Round(SUM(bytes) / ( 1024 * 1024 ), 2) SPACE, 
  9                         SUM(blocks)                            BLOCKS 
 10                  FROM   dba_temp_files 
 11                  GROUP  BY tablespace_name) D, 
 12                 (SELECT tablespace, 
 13                         Round(SUM(blocks * 8192) / ( 1024 * 1024 ), 2) USED_SPACE 
 14                  FROM   v$sort_usage 
 15                  GROUP  BY tablespace) F 
 16          WHERE  D.tablespace_name = F.tablespace(+)
 17            AND  D.tablespace_name='TEMP';

Tablespace Name      SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- ------------ ---------- ------------- ------------ -------------
TEMP                           37       4736                          0                  ---使用率才0，因为排序完成后，临时表空间使用过的块都会被标记为free，可以重复使用。

在这里插入图片描述

参考：
https://www.eygle.com/archives/2006/04/oracle_temporary_segment.html
https://mp.weixin.qq.com/s?__biz=MzA5MzQxNjk1NQ==&mid=2647839907&idx=1&sn=5a1ec4afea76604f411326118f3c381f&chksm=88784837bf0fc121a60d8a26c44dfcd4c382eb3e22dc86e88ca69c6ef7fb256b164a9ecd0641&scene=126&sessionid=1611793922&key=f4c334f5d6d13eef2c31643919be0a0db7d41d4157d5972c95478a589042f7253fe28a74587612989d7a70d566886e6b08eacc161f4f79eaac55027a8684f36fa33751e9ca5c3ef00b3532291e6734462c4946c4a4ca90a595f6074dcd99ced1e278d0293e0ea9414f10ae024c51aa407d3120e9d1e4221c274b2fdb8f8a5031&ascene=1&uin=MTEyNDY5MTcwMQ==&devicetype=Windows

https://mp.weixin.qq.com/s?__biz=MzA5MzQxNjk1NQ==&mid=2647839846&idx=1&sn=8aeaafd15986a5192eb666df2b57fd8c&chksm=887848f2bf0fc1e4bd1adcb1f6811bcd4bd38360b0b660077223991c9cc40d0214d4451d5403&scene=126&sessionid=1611793938&key=00a5d610cd8d1098a9db90d70aacdbb7c3557f4f0e790a53952a8cab81a6a2f4a4d63e084d41bd26a13eee590230d55b528885d0cbd08ff558f8b04481b5f5994d31f5d051724a14f120e0a0bd19e3da35dd3c2e86fa98ef86576e4b5b86ffa491733c6757e2d4fc71b88166177ad78918e1abc0aad190227a506a5ead4433f1&ascene=1&uin=MTEyNDY5MTcwMQ==&devicetype=Windows

https://www.askmaclean.com/archives/smon-cleanup-temporary-segment.html
http://blog.chinaunix.net/uid-22948773-id-3758510.html
Master Note: Database System Monitor Process (SMON) (Doc ID 1495163.1)

P10ZHUO

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle中临时段的使用回收规则

smon回收临时段temp表空间使用原则如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。temp表空间使用原则临时表空间包含仅在会话期间持续存在的临时数据。临时表空间可以提高无法装入内存的多个排序操作的并发性，还可以提高排序期间空间管理操作的效率。在临时表空间中，针对特定实例的所有排序操作共享一个排序段，
复制链接

扫一扫

专栏目录