临时表空间的实时监控，网络上99%的搜索结果都是错误的

“ 你是否曾经担忧更新语句因为临时表空间不足而报错？你是否曾经碰到过临时表空间不足而导致的错误？你是否曾经在网络上进行搜索 “临时表空间使用率”。但结果却不是你想要的？如果你经历过这些，那么这篇文章非常适合你”

 

         关于临时表空间的监控，在网络上进行搜索，你会发现几乎都是错误的答案。网络上的答案永远都是查询临时表空间曾经最大使用情况，而不是实时查询临时表空间的使用情况。看完此篇文章后你将知道如何实时的监控临时表空间。

本文分为两部分：第一部分主要是对网络上错误结果进行分析。第二部分才是对临时表空间的实时监控的试验。如果你只想要相关语句则可以直接跳到第二部分进行阅读。

本文为原创内容，首发公众号：唯一的小彬哥

 

01

—

网络上的结果真的不靠谱

    首先我们来看一看网络上搜索到的查询语句吧，这个语句在网络上被称为临时表空间使用率查询

SELECT C.TABLESPACE_NAME,

C.BYTES/1024/1024 TOTAL_MB,

 (C.BYTES-D.BYTES_USED)/1024/1024 FREE_MB,

D.BYTES_USED/1024/1024 USE_MB,

ROUND(D.BYTES_USED*100/C.BYTES,2)|| '%'USE

FROM  (SELECT TABLESPACE_NAME,SUM(BYTES) BYTES

FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) C,

(SELECT TABLESPACE_NAME,SUM(BYTES_CACHED) BYTES_USED

FROM V$TEMP_EXTENT_POOL GROUP BY TABLESPACE_NAME) D

WHERE C.TABLESPACE_NAME = D.TABLESPACE_NAME;

       查询结果显示如下：

         我保证我的数据库现在是空闲的，但通过这个语句却显示使用百分比已经是99%以上了。这个语句实际上应该被称为临时表空间曾经最大使用情况。如果你通过这个语句来评估是不是该加临时表空间了，那是可以的。但是你要用这个监控临时表空间使用情况？逗我玩呢？

    再来看另外一个语句 这个语句在网络上被称为 查看哪些用户正在使用临时表空间

SELECT A.USERNAME,A.SQL_ID,A.SEGTYPE,

B.BYTES_USED/1024/1024 ||'MB',

B.BYTES_FREE/1024/1024 

FROM V$TEMPSEG_USAGE A JOIN 

V$TEMP_SPACE_HEADER B ON A.TABLESPACE = B.TABLESPACE_NAME;

这个SQL根据网络上的解释是这样的

SQL_ID：正在执行的SQL。

BYTES_USED：SQL语句使用的临时表空间的大小。也就是924MB。BYTES_FREE：空闲的临时表空间大小，也就是0。

所以上面的图翻译一下就是现在我的临时表空间已经用了924MB了，没有剩余临时表空间了，你再执行什么排序啊，分组啊就该报错了。可是我的数据库没有在执行任何SQL。

再来看看占用的SQL到底是什么？这个可以通过查询V$SQL表找到

我保证这个语句真的不是我执行的。

所以我得出的结论是：网络上针对临时表空间的监控的查询结果真是太扯淡了。并且并不是只有一两个页面是这样的查询结果，几乎是所有的内容都是这种。我从未搜索到我想要的答案。

 

02

—

临时表空间的实时监控

    临时表空间如何实时监控？其实可以通过使用V$TEMP_EXTENT_POOL视图来监控。

SELECT 

A.TABLESPACE_NAME, 

case when  A.MAXBYTES/power(1024,3)>1 THEN  ROUND(A.MAXBYTES/power(1024,3),4)||'GB'

ELSE ROUND(A.MAXBYTES/power(1024,2),4)||'MB' END "允许最大值",

case when  A.BYTES/power(1024,3)>1 THEN  ROUND(A.BYTES/power(1024,3),4)||'GB'

ELSE ROUND(A.BYTES/power(1024,2),4)||'MB' END "历史峰值", 

case when  B.BYTES_USED/power(1024,3)>1 THEN 

ROUND(B.BYTES_USED/power(1024,3),4)||'GB'

ELSE ROUND(B.BYTES_USED/power(1024,2),4)||'MB' END "已使用",   

round((B.BYTES_USED/A.BYTES)*100,2)||'%' "已使用/历史峰值",   

round((B.BYTES_USED/A.MAXBYTES)*100,2)||'%' "已使用/允许最大值" 

 FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES,SUM(F.MAXBYTES) MAXBYTES  FROM DBA_TEMP_FILES F   GROUP BY TABLESPACE_NAME) A,

 (SELECT P.TABLESPACE_NAME, SUM(P.BYTES_CACHED) BYTES_CACHED,SUM(P.BYTES_USED) BYTES_USED  FROM V$TEMP_EXTENT_POOL P  GROUP BY TABLESPACE_NAME  ) B

 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME

结果如下图所示：

以TEMP为例，在这个查询中可以看到

临时表空间允许的最大值是32G

历史曾经达到的峰值是924MB（网络上的语句都把这个峰值作为了实时值）

当前已使用空间为0

为了验证该语句的效果，可以运行排序查询（WYDXBG2021 共2千万数据）：

SELECT * FROM WYDXBG2021 ORDER BY F1 DESC;

查询未完成时的结果：

使用率为0.88%

查询完成后的结果：

使用率为1.41%

连接释放后的结果：

使用率为0%

从该试验可以看出该SQL确实对临时表空间进行了实时监控。监控关键就在于视图V$TEMP_EXTENT_POOL。根据Oracle官方解释这个视图的主要作用就是监控临时表空间被实例缓存和使用的情况。

BYTES_USED指正在被使用的

BYTES_CACHED指被缓存的

通过文档理解很容易误解为BYTES_USED+BYTES_CACHED才是实时的使用情况。但是根据我的试验我认为BYTES_USED其实才能反映出临时表空间的实时使用情况。

03

—

结束语

        文章中使用的数据库是Oracle 11g，有兴趣的小伙伴可以自己去验证一下。也可以验证一下网络上搜索出来的语句，你会发现不管你的排序语句是执行中，执行后，网络上搜索出的语句查询出的结果数值都是完全不变的。自己动手记忆永远是最深刻的。

如果看完本文，您有所收获，欢迎关注我的微信公众号：唯一的小彬哥