2017-06-28 DBA日记，查询v$rman_status缓慢诊断与优化

最新推荐文章于 2024-07-20 08:30:00 发布

原创最新推荐文章于 2024-07-20 08:30:00 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

ORACLE DBA 专栏收录该内容

59 篇文章

订阅专栏

本文针对一条由SYS用户在后台调用的SQL语句“selectp,NULL,NULLfrom(selectcount(*)pfromv$rman_statuswhereoperation='BLOCKMEDIARECOVERY')”运行缓慢的问题进行了解析。通过收集数据、生成执行计划并参照Oracle官方文档，最终找到了解决方案：删除特定统计信息，有效提升了SQL执行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题：
发现有一条由SYS用户在后台调用的SQL语句“select p, NULL, NULL from (select count(*) p from v$rman_status  where operation = 'BLOCK MEDIA RECOVERY')”运行缓慢约5分钟,为什么呢？
第一次分析:
1）收集数据：
1.1 查询v$active_session_history，获得该语句运行时信息。
1.2 生成该SQL语句的执行计划

 1.3 查找相关案例 support.oracle.com ,QUERIES ON V$RMAN_STATUS are very slow even after GATHER_FIXED_OBJECTS_STATS is run (文档 ID 1525917.1)
2) 数据分析
从1.1中得知该SQL语句在运行时没有发生等待，全程使用CPU，与1.3的文章描述比较接近
3）结论：
 按照oracle提供的方案SQL> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR') 在测试环境上试了一下，发现只有在用sys用户继续执行问题SQL语句，性能才有所提升，其它用户执行，依然很慢。 
为确保生产环境的稳定，最后在闲时执行，并观察数据库的反应，如有不良反应，则执行
exec SYS.DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
附录一:
QUERIES ON V$RMAN_STATUS are very slow even after GATHER_FIXED_OBJECTS_STATS is run (文档 ID 1525917.1)



This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.
***Checked for relevance on 17-Jul-2014***
SYMPTOMS

QUERIES ON V$RMAN_STATUS are very slow even after running GATHER_FIXED_OBJECTS_STATS

Getting SQL 10046 trace and running the trace file through TKPROF shows the problem query:

 

select * 
from 
v$rman_status 

call     count       cpu    elapsed       disk      query    current         rows 
------- ------  -------- ---------- ---------- ---------- ----------   ---------- 
Parse        1      0.04       0.20          7        128          0            0 
Execute      1      0.00       0.00          0          0          0            0 
Fetch       17   2488.40    2525.34          0          0          0          233 
------- ------  -------- ---------- ---------- ---------- ----------   ---------- 
total       19   2488.44    2525.55          7        128          0     
 
Almost all the elapsed time is spent on CPU.


CAUSE

Bug 14078947 - SELECT FROM V$RMAN_STATUS VERY SLOW WHEN DATABASE IS OPEN

The problem only occurs when database is open.
 

 

SOLUTION

Delete the following statistics in the target:

SQL> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR')
 

REFERENCES

BUG:14078947 - SELECT FROM V$RMAN_STATUS VERY SLOW WHEN DATABASE IS OPEN