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

 
 

  
  问题：
 
 
 
 

  
  发现有一条由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