Query V$SEGSTAT or V$SEGMENT_STATISTICS views slowly (慢)

昨天有个朋友在做性能监控时遇到了一个性能问题咨询我是否遇到过? 在所有的数据库实例中只有一个节点查询V$SEGMENT_STATISTICS时几个小时都未出结果, 其实这个问题影响了11.2.0.3 ,11.2.0.4多个版本及平台,而且也是我第二次遇到所以分享出来,希望可以给遇到相同问题的人可以节约时间. 这个问题在MOS上有一个相似的NOTE, 记录查询的是v$segstat view.

其实我们可以确认的是 V$SEGMENT_STATISTICS 和V$SEGSTAT 在11G版本中都共同使用了x$ksolsfts

select view_name, view_definition text from v$fixed_View_definition where upper(view_name) like ''

SQL> @v v$segstat
Show SQL text of views matching "%v$segstat%"...
no rows selected

VIEW_NAME                      TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
GV$SEGSTAT                     select inst_id,                                              fts_tsn,
                               fts_objn,                                             fts_objd,
                               fts_statnam,                                          fts_statid,
                               fts_staval,                                           con_id
                               from x$ksolsfts                                       where fts_inte = 0

V$SEGSTAT                      select ts#,                                                    obj#,
                               dataobj#,                                               statistic_name,
                               statistic#,                                             value,
                               con_id                                           from  gv$segstat where inst_id =
                               userenv('instance')


SQL> @v V$SEGMENT_STATISTICS 
Show SQL text of views matching "%V$SEGMENT_STATISTICS%"...

-- 输出省略, 11g中也是使用了x$ksolsfts 与其它基表的关连, 在12.2中改写了该VIEW的定义,不过同样也是包含x$ksolsfts, ts$换成了x$kccts.

这个问题主要表现查询的view中使用了x$ksolsfts ,而x$ksolsfts 表存在大量的记录及重复记录, 而导致的查询缓慢, 而且在RAC环境中有可能只影响一个节点.
确认方法:
— 正常情况下不存在重复记录

SQL> select   statistic# ,count (*) from  V$SEGSTAT
  2  group by  ts#,obj#,dataobj#,  statistic#
  3  having count (*) > 1;

no rows selected

— 异常情况时记录数多, 且存在重复记录

SQL> select count(*) from V$SEGSTAT;
select count(*) from V$SEGSTAT
                     *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

--因等待时间太久我取消了操作.

SQL> select   statistic# ,count (*) from  V$SEGSTAT where  rownum<1e5
  2  group by  ts#,obj#,dataobj#,  statistic#
  3  having count (*) > 1
  4  ;
STATISTIC#   COUNT(*)
---------- ----------
         0          2
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2
         8          2
         9          2
        11          2
        12          2
        13          2
        14          2
        15          2
        17          2
        18          2
        20          2

18 rows selected.

SQL> select   statistic# ,count (*) from  V$SEGSTAT where rownum<1e6
 2  group by  ts#,obj#,dataobj#,  statistic#
  3  having count (*) > 1;
STATISTIC#   COUNT(*)
---------- ----------
         0        188
         1        188
         2        188
         3        188
         4        188
         5        188
...
...
...

解决方法:
1, 应用对应版本中BUG 21050285的补丁
or
2, 重启问题节点的实例可以缓解该问题, 会在启动后重新构造数据.