干货 | 临时表空间监控误告警的定位与解决方法

东方龙马 2020-07-10

1173

东方龙马（OLM）

张红记 | 东方龙马 · 北京

背景

东方龙马在金融行业的某客户经常会收到核心生产数据库监控TEMP表空间使用率超过90%告警，DBA分析告警发出时间范围内并没有需要使用temp表空间的大SQL。DBA实时跟踪temp空间使用率，发现告警时间范围内temp使用率大部分为0。我们怀疑问题可能出现在TEMP表空间监控的应用程序上。

DBA和监控运维人员要到temp监控SQL语句，应用程序使用v$sort_segment 的TOTAL_BLOCKS字段计算临时表空间使用率。

#4临时表空间空闲信息

select s.tb_sp_name,

nvl(s.tb_sp_size - u.tb_sp_used, s.tb_sp_size) tb_sp_freesize,

null tb_sp_maxblksize,

null tb_sp_fragmentation

from (select seg.TABLESPACE_NAME tb_sp_name,

seg.TOTAL_BLOCKS * ts.block_size tb_sp_usedfrom v$sort_segment seg, Dba_Tablespaces ts

where ts.tablespace_name = seg.TABLESPACE_NAME

and seg.TABLESPACE_NAME not like '%UNDO%') u,

(select tablespace_name tb_sp_name, sum(bytes) tb_sp_size

from DBA_TEMP_FILES

where tablespace_name not like '%UNDO%'

group by tablespace_name) s

where s.tb_sp_name = u.tb_sp_name(+)

）

根据ORACLE官方文档信息，USED_BLOCKS更适合于临时表空间的使用率的计算。

客户要求对USED_BLOCKS和TOTAL_BLOCKS字段对维护人员进行测试学习。

测试目标

视图gv$sort_segment中字段

1. used_blocks为SQL执行过程中占用的block数

2. total_blocks为当前的block总数

3. total_blocks在RAC两个节点之间可以相互使用，但总和不大于表空间总块数

测试语句

使用order by 占用临时表空间

一节点执行

create table comframe.H_VM_WF_471_201805_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201805 order by workflow_id

二节点执行

create table comframe.H_VM_WF_471_201804_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201804 order by workflow_id;

监控语句

select inst_id,TABLESPACE_NAME,used_blocks,total_blocks,CURRENT_USERS from gv$sort_segment where tablespace_name='TEMP_TEST_TBS';

测试数据量

临时表大小不足以支持大表的排序操作

临时表空间（容量1GB）

create TEMPORARY TABLESPACE temp_test_tbs TEMPFILE '+DATA1' size 1g;

表容量

OWNER SEGMENT_NAME SIZE_M SEGMENT_TYPE TABLESPACE_NAME

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

COMFRAME H_VM_WF_471_201804 7308 TABLE TS_PUBDB_01

COMFRAME H_VM_WF_471_201805 28805 TABLE TS_PUBDB_01

测试过程

场景一

新的临时表空间未使用时gv$sort_segment为空

执行监控语句：

select inst_id,TABLESPACE_NAME,used_blocks,total_blocks,CURRENT_USERS from gv$sort_segment where tablespace_name='TEMP_TEST_TBS';

无结果

场景二

一节点开始执行SQL过程中临时表空间使用率开始增加，由于临时表空间不足，SQL执行失败后USED_BLOCKS释放为0，TOTAL_BLOCKS为最终结果没有变化

一节点执行SQL

14:53:44 SQL>

create table comframe.H_VM_WF_471_201805_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201805 order by workflow_id

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TEST_TBS

Elapsed: 00:00:34.52

执行监控语句：

14:53:55 SQL>

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 130944 0

场景三

二节点开始执行SQL时一节点TOTAL_BLOCKS不断被二节点获取，直到一节点TOTAL_BLOCKS=0

二节点执行SQL

14:55:01 SQL> create table comframe.H_VM_WF_471_201804_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201804 order by workflow_id;

create table comframe.H_VM_WF_471_201804_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201804 order by workflow_id

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TEST_TBS

14:55:58 SQL>

监控

14:55:29 SQL> --二节点还没有开始使用临时表空间

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 130944 0

1 rows selected.

14:55:35 SQL> --二节点开始使用临时表空间

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 118144 0

2 TEMP_TEST_TBS 128 128 1

2 rows selected.

14:55:37 SQL> --二节点继续使用临时表空间，一节点TOTAL_BLOCKS不断减少

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 109312 0

2 TEMP_TEST_TBS 12800 12800 1

2 rows selected.

14:55:50 SQL> --一节点TOTAL_BLOCKS=0

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 0 0

2 TEMP_TEST_TBS 129792 129792 1

2 rows selected.

14:55:54 SQL> --二节点SQL执行失败，USED_BLOCKS释放

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 0 0

2 TEMP_TEST_TBS 0 130944 0

2 rows selected.

监控

寂静在喧嚣里低头不语，沉默在黑夜里与目光结交，于是，我们看错了世界，却说世界欺骗了我们。by 泰戈尔

场景三

一二节点同时执行SQL,两个节点同时获取TOTAL_BLOCKS

同时执行：

节点一

15:05:24 SQL>

create table comframe.H_VM_WF_471_201805_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201805 order by workflow_id

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TEST_TBS

Elapsed: 00:00:20.93

15:06:08 SQL>

节点二

15:05:27 SQL> create table comframe.H_VM_WF_471_201804_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201804 order by workflow_id;

create table comframe.H_VM_WF_471_201804_t tablespace TS_PUBDB_01 as select * from comframe.H_VM_WF_471_201804 order by workflow_id

ERROR at line 1:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_TEST_TBS

15:06:08 SQL>

监控

15:05:59 SQL> --没开始使用temp

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 28544 0

2 TEMP_TEST_TBS 0 93440 0

2 rows selected.

15:06:00 SQL> --二节点开始使用temp

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

2 TEMP_TEST_TBS 128 93440 1

1 TEMP_TEST_TBS 0 28544 0

2 rows selected.

15:06:01 SQL> --一二节点同时使用temp

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 1920 28544 1

2 TEMP_TEST_TBS 13056 93440 1

2 rows selected.

15:06:05 SQL> --一节点TOTAL_BLOCKS超过原来的28544时开始获取二节点TOTAL_BLOCKS

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 43904 43904 1

2 TEMP_TEST_TBS 72320 80640 1

2 rows selected.

15:06:07 SQL> --SQL执行失败，USED_BLOCKS返回0

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 51456 0

2 TEMP_TEST_TBS 0 79488 0

2 rows selected.

场景五

一小时后为执行SQL,TOTAL_BLOCKS保持原来数据

监控

16:05:13 SQL> select inst_id,TABLESPACE_NAME,used_blocks,total_blocks,CURRENT_USERS from gv$sort_segment where tablespace_name='TEMP_TEST_TBS';

INST_ID TABLESPACE_NAME USED_BLOCKS TOTAL_BLOCKS CURRENT_USERS

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

1 TEMP_TEST_TBS 0 51456 0

2 TEMP_TEST_TBS 0 79488 0

2 rows selected.

测试结果

视图gv$sort_segment中字段

1. used_blocks为SQL执行过程中占用的block数成立

2. total_blocks为当前的block数成立

3. total_blocks在两个节点之间可以相互使用，但总和不大于表空间总块数成立

改进建议

我们建议应用人员对已经发出的警告进行统一处理，将TOTAL_BLOCKS字段由USED_BLOCKS代替，对正在发出的相关警告进行屏蔽，修改临时表空间监控模板并统一下发至各核心数据库。

查看当前正在使用临时表空间的SQL

SELECT a.username,

a.sid,

a.serial#,

a.osuser,

b.tablespace,

b.blocks,

c.sql_text

FROM v$session a, v$tempseg_usage b, v$sqlarea c

WHERE a.saddr = b.session_addr

AND c.address = a.sql_address

AND c.hash_value = a.sql_hash_value

ORDER BY b.tablespace, b.blocks;

| 北京 | 上海 | 广州 | 成都 |

4008-906-960

数据库

文章转载自东方龙马，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

干货 | 临时表空间监控误告警的定位与解决方法

评论