RMAN sql_id “0az7czjdw8z7j” 执行计划异常

# RMAN sql\_id "0az7czjdw8z7j" 执行计划异常

1. 问题描述
	1. SOLUTION MANAGER报警IPS系统数据库TEMP表空间使用率达到百分之百x3
2. 问题解决
	1. 确定temp的使用率
		1. Select f.tablespace\_name,
		2. d.file\_name "Tempfile name",
		3. round((f.bytes\_free + f.bytes\_used) / 1024 /1024, 2) "total MB",
		4. round(((f.bytes\_free + f.bytes\_used) -nvl(p.bytes\_used, 0)) / 1024 / 1024, 2) "Free MB" ,
		5. round(nvl(p.bytes\_used, 0)/ 1024 / 1024, 2)"Used MB",
		6. round((round(nvl(p.bytes\_used, 0)/ 1024 /1024, 2)/round((f.bytes\_free + f.bytes\_used) / 1024 / 1024, 2))*100,2) as"Used\_Rate(%)"
		7. from SYS.V\_$TEMP\_SPACE\_HEADER f,DBA\_TEMP\_FILES d, SYS.V\_$TEMP\_EXTENT\_POOL p
		8. where f.tablespace\_name(+) = d.tablespace\_name
		9. and f.file\_id(+) = d.file\_id
		10. and p.file\_id(+) =d.file\_id;

![](/media/202404//1714352043.2558644.png)

* 
	1. 查找temp表空间使用情况
		1. select se.username,
		2.  se.sid,
		3.  su.extents,
		4.  su.blocks * to\_number(rtrim(p.value)) as Space,
		5.  tablespace,
		6.  segtype,
		7.  sql\_text,se.status
		8.  from v$sort\_usage su, v$parameter p, v$session se, v$sql s
		9.  where p.name = 'db\_block\_size'
		10.  and su.session\_addr = se.saddr
		11.  and s.hash\_value = su.sqlhash
		12.  and s.address = su.sqladdr
		13.  and su.TABLESPACE='TEMP'
		14.  order by se.username, se.sid;

![](/media/202404//1714352043.271153.png)

* 
	1. 杀死inactive会话
		1. ALTER SYSTEM KILL SESSION '576,43211';
		2. ALTER SYSTEM KILL SESSION '2649,13890';
		3. ALTER SYSTEM KILL SESSION '3784,43964';
		4. ALTER SYSTEM KILL SESSION '4541,14512';
		5. ALTER SYSTEM KILL SESSION '4727,44877';
		6. ALTER SYSTEM KILL SESSION '5105,57465';
		7. ALTER SYSTEM KILL SESSION '5110,16657';
		8. ALTER SYSTEM KILL SESSION '5676,32621';
	2. 收缩temp
		1. alter tablespace temp shrink space keep 40G;
	3. 查询temp的使用率：数据库temp表空间使用率恢复正常
		1. Select f.tablespace\_name,
		2. d.file\_name "Tempfile name",
		3. round((f.bytes\_free + f.bytes\_used) / 1024 /1024, 2) "total MB",
		4. round(((f.bytes\_free + f.bytes\_used) -nvl(p.bytes\_used, 0)) / 1024 / 1024, 2) "Free MB" ,
		5. round(nvl(p.bytes\_used, 0)/ 1024 / 1024, 2)"Used MB",
		6. round((round(nvl(p.bytes\_used, 0)/ 1024 /1024, 2)/round((f.bytes\_free + f.bytes\_used) / 1024 / 1024, 2))*100,2) as"Used\_Rate(%)"
		7. from SYS.V\_$TEMP\_SPACE\_HEADER f,DBA\_TEMP\_FILES d, SYS.V\_$TEMP\_EXTENT\_POOL p
		8. where f.tablespace\_name(+) = d.tablespace\_name
		9. and f.file\_id(+) = d.file\_id
		10. and p.file\_id(+) =d.file\_id;

1. temp表空间使用率报警x2
	1. 没过多久，TEMP表空间使用率在达到100%；
	2. 找出到底是哪个语句占用了temp表空间
		1. SELECT sql\_id,sum(TEMP\_SPACE\_ALLOCATED)/1024/1024/1024 temp\_space\_GB 
		2. FROM DBA\_HIST\_ACTIVE\_SESS\_HISTORY 
		3. where snap\_id between 18662 and 18672 
		4. GROUP BY SQL\_ID 
		5. HAVING sum(TEMP\_SPACE\_ALLOCATED)/1024/1024/1024>10
		6. order by temp\_space\_GB DESC;

![](/media/202404//1714352043.2804513.png)

* 
	1. 找到SQL\_ID=0az7czjdw8z7j，原来是rman的任务（注意：SQL\_ID是固定的）
		1. select * from v$sqlarea where sql\_id ='0az7czjdw8z7j';

![](/media/202404//1714352043.287297.png)

* 
	1. 深入分析，这个语句大量的temp表空间的读写，占用了大量的磁盘IO，这个可能是ips系统月结期间运行缓慢的根因。而工作期间只有阿里云的DBS在运行，所以怀疑是阿里云的dbs服务导致的：经过向阿里云工程师的咨询，他们表示无法在dbs层面给出解决办法。提供了一个参考建议：[rman 触发temp空间不足问题-brjl-ChinaUnix博客](http://m.blog.chinaunix.net/uid-20687159-id-5868803.html?spm=5176.2020520104.0.0.3369709a5Irp65)
	2. 复现了temp使用率100%的现象
		1. 在RMAN中执行report obsolete命令

![](/media/202404//1714352043.2954018.png)

* 
	+ 
		1. 查询temp表空间使用率发现达到100%

![](/media/202404//1714352043.3022914.png)

* 
	+ 
		1. 服务器io层面：18:21开始磁盘写达到限制，一直到18:31，temp表空间被写满。磁盘写降低为0

![](/media/202404//1714352043.3098426.png)

* 
	+ 
		1. 服务器IO层面：18：31分，temp被写满后，temp表空间的读持续增加，达到限制，持续到18:41，磁盘读降低

![](/media/202404//1714352043.3165355.png)

* 
	+ 
		1. rman命令report obsolete命令执行成功

![](/media/202404//1714352043.3238447.png)

* 
	1. 清理过期备份数据：delete obsolete;执行失败，阿里云DBS备份的路径无法访问

![](/media/202404//1714352043.3322966.png)

* 
	1. 启动trace事件跟踪rman report obsolete命令：RMAN>不支持SET 类型语句的执行。

![](/media/202404//1714352043.3386269.png)

* 
	1. 启用10046事件跟踪rman report obsolete命令执行过程：成功，RMAN>sql " "支持alter类型的语句

![](/media/202404//1714352043.3450618.png)

* 
	+ 
		1. 执行过程temp表空间使用率再次达到100%
		2. 相应的IO读写都达到瓶颈值
		3. 找到该会话sid=948，通过sid找到这个进程的跟踪日志文件路径：/tqls\_system/app/oracle/products/diag/rdbms/ips/ips/trace/ips\_ora\_2325.trc

![](/media/202404//1714352043.3528297.png)

* 
	+ 
		1. 过程共执行了30分钟。生成的ips\_ora\_2325.trc文件2.34G大小。
		2. 使用tkprof工具，利用跟踪日志文件ips\_ora\_2325.trc生成分析报告ips\_ora\_2325.txt
			1. tkprof /tqls\_system/app/oracle/products/diag/rdbms/ips/ips/trace/ips\_ora\_2325.trc ips\_ora\_2325.txt
			2. more ips\_ora\_2325.txt

![](/media/202404//1714352043.3595304.png)

* 
	+ 
		- 
			1. 发现有语句排序的行达到6E条（673025325条），造成TEMP瞬间爆满，并伴随着磁盘读写资源被全部占用。（这会造成其他会话无资源可用而形成卡顿）

![](/media/202404//1714352043.3673198.png)

* 
	+ 
		- 
			1. 但是相关的这几张表的条目数挺大的

![](/media/202404//1714352043.3782022.png)

* 
	+ 
		- 
			1. 清理过失效数据，报错，因为访问不了阿里云的dbs,问题又回去了-找阿里云
			2. 查询SELECT * FROM X$KCCBP：发现2022年4月21日的dbs备份信息都还在

![](/media/202404//1714352043.384614.png)

* 
	+ 
		1. 目前两个方向：1，收集fixed tble的统计信息（其他数据库的统计信息更多（比如ccs），但是执行不至于这么慢，所以他的执行计划有问题）；2，清理数据库备份信息历史记录
		2. 清理数据库备份信息历史记录
			1. 如果allocate channel c1 disk;delete obsolete只能清理磁盘类型的备份信息；如果allocate channel c1 SBT\_TAPE;报错，未能加载介质管理库

![](/media/202404//1714352043.3964856.png)

* 
	+ 
		- 
			1. 针对上述报错：allocate channel c1 SBT\_TAPE;oracle自动分配的磁带通道报错的问题。
			2. 阿里云的建议是：分配一个假channel，指向他本地，来对本地控制文件里备份集元信息进行一些删除。
				1. ALLOCATE CHANNEL t1
				2. DEVICE TYPE sbt PARMS='sbt\_library=oracle.disksbt,
				3. ENV=(backup\_dir=/tmp)';
			3. 完整脚本如下RMAN>
				1. RUN {
				2.  ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT\_TAPE' PARMS='SBT\_LIBRARY=ORACLE.DISKSBT, ENV=(BACKUP\_DIR=/tmp)';
				3.  crosscheck backup;
				4.  delete expired backup;
				5.  RELEASE CHANNEL c1;
				6. };
			4. 阿里云建议要在测试环境测试下，但是我们的测试环境都没有使用DBS服务，无法测试......
		1. 收集fixed tble的统计信息
			1. 在检修窗口期，收集所有fixed objects统计信息命令：exec dbms\_stats.gather\_fixed\_objects\_stats;
				1. 收集完成后，RMAN>report obsolete;
				2. 依然很慢，磁盘IO暴涨，立即停了请求，不行。
			2. 在检修窗口期，重新执行了以下命令：这次可以了，执行RMAN>report obsolete相当的快！
				1. exec dbms\_stats.gather\_table\_stats('SYS','X$KCCOR')
				2. exec dbms\_stats.gather\_table\_stats('SYS','X$KRBPPBCTX')
				3. exec dbms\_stats.gather\_table\_stats('SYS','X$KCCFE')
				4. exec dbms\_stats.gather\_table\_stats('SYS','X$KCCDC')
				5. exec dbms\_stats.gather\_table\_stats('SYS','X$KCCBF')
				6. exec dbms\_stats.gather\_table\_stats('SYS','X$KCCBS')
				7. exec dbms\_stats.gather\_table\_stats('SYS','X$KCCPD')
				8. exec dbms\_stats.gather\_fixed\_objects\_stats;
				9. begin
				10. dbms\_stats.gather\_table\_stats(
				11.  ownname =>'SYS',
				12.  tabname => 'X$KCCBP',
				13.  no\_invalidate => false,
				14.  degree=>2,
				15.  method\_opt=>'for all columns size auto',
				16.  cascade => true
				17. );
				18. end;
				19. /
			3. 以上，最紧急的问题解决了，IPS月结卡顿问题能否明显改善需要确认。
		2. 再回到第一个方向：我们的备份信息会日积月累，慢慢变多，阿里云DBS备份的信息无法删除始终会成为麻烦，
			1. 目前主要的几张表

![](/media/202404//1714352043.4068115.png)

* 
	+ 
		- 
			1. 考虑在本次检修窗口试试阿里云给的建议

![](/media/202404//1714352043.4138212.png)

* 
	+ 
		- 
			1. 执行以下命令：成功了...
				1. RUN {
				2.  ALLOCATE CHANNEL c1 DEVICE TYPE 'SBT\_TAPE' PARMS='SBT\_LIBRARY=ORACLE.DISKSBT, ENV=(BACKUP\_DIR=/tmp)';
				3.  crosscheck backup;
				4.  delete expired backup;
				5.  RELEASE CHANNEL c1;
				6. };

![](/media/202404//1714352043.4218144.png)

* 
	+ 
		- 
			1. 清理掉阿里云dbs备份信息31820条记录。要知道ccs数据库有将近9w条备份记录。
			2. 阿里云提醒：清理了阿里云的备份信息，DBS增备可能异常，需要重新做一个全备。

![](/media/202404//1714352043.4301496.png)

* 
	+ 
		- 
			1. 查看那几张表的数据:数据还是那么多。嗯...需要一个10046事件跟踪下RAMN>delete obsolete到底删了哪几张表的数据。

![](/media/202404//1714352043.4401164.png)