请教下物化视图刷新慢引起的系统性能变慢，优化的思路？

suredandan

数据库版本11.2.0.3  RAC，操作系统为redhat5.8 64位。
最近系统一到高峰期就很慢，请别人做了一个3个小时间隔的awr报告，因为时间间隔太长，所以无法精确定位高峰期的信息，只能根据等待事件来判断一下：

发现主要的等待事件为db file scattered read，然后去查了一下sql order那一截，发现主要的sql均为和物化视图刷新的sql。这个系统物化视图用的比较多，都是以快速刷新方式创建的。

另外，根据db file scattered read等待事件，我也请别人查了一下：大量物理读sql、全表扫描的sql、全索引扫描的sql，发现依然是和物化视图刷新有关的。

再查了下io读取的性能，多块读基本都在10微秒左右，IO上面应该是没问题的。

另外，因为是RAC的，所以还看了一下awr的RAC一个性能指标

从这个上面看到disk读很高，我想这个也是慢的原因。

现在想请教一下，我接下来应该怎么对这个系统或者说对这些物化视图去做优化呢？因为我觉得物化视图都已经是快速刷新了，而且差不多是每半个月都要手工对这些物化视图的物化视图日志进行move来降低高水位，甚至对一些物化视图进行重建。请教一下思路，谢谢。

Maclean Liu(刘相兵

给出该语句：

/* MV_REFRESH (DEL) */ DELETE FROM "ISC"."GETUSERSBYORGROLE_MVIEW" SNA$ WHERE "AROWID" IN (SELECT /*+ NO_MERGE HASH_SJ */ * FROM (SELECT CHARTOROWID("MAS$"."M_ROW$$") RID$ FROM "ISC"."MLOG$_ISC_USER" "MAS$" WHERE "MAS$".SNAPTIME$$ > :B_ST1 ) AS OF SNAPSHOT(:B_SCN) MAS$)

6ph89trc2y0um

的 @?/rdbms/admin/awrsqrpt 信息

suredandan

Maclean Liu(刘相兵 发表于 2014-11-18 14:07
给出该语句：

/* MV_REFRESH (DEL) */ DELETE FROM "ISC"."GETUSERSBYORGROLE_MVIEW" SNA$ WHERE "AROWID" ...

这个只有明天才能给出来，因为甲方的人都下班了。。
刘大，你是想通过看执行计划来确定是不是“_mv_refresh_use_stats”的问题吗？

suredandan

6ph89trc2y0um的awrsqrpt 信息来了

suredandan

Maclean Liu(刘相兵 发表于 2014-11-18 14:07
给出该语句：

/* MV_REFRESH (DEL) */ DELETE FROM "ISC"."GETUSERSBYORGROLE_MVIEW" SNA$ WHERE "AROWID" ...

刘大，已经贴了sql的awr了，请帮忙看看，3Q。

Maclean Liu(刘相兵

Id        Operation        Name        Rows        Bytes        Cost (%CPU)        Time
0        DELETE STATEMENT                                   377 (100)         
1           DELETE        GETUSERSBYORGROLE_MVIEW                                    
2             HASH JOIN RIGHT SEMI                 203        36743        377 (1)        00:00:05
3               TABLE ACCESS FULL        MLOG$_ISC_USER        12        1656        371 (0)        00:00:05
4               MAT_VIEW ACCESS FULL        GETUSERSBYORGROLE_MVIEW        203        8729        5 (0)        00:00:01



select count(*) from MLOG$_ISC_USER;

select count(*) from GETUSERSBYORGROLE_MVIEW;

suredandan

select count(*) from MLOG$_ISC_USER;
46

select count(*) from GETUSERSBYORGROLE_MVIEW;
3416531

感觉走HASH_SJ 不如走 NESTED LOOPS

suredandan

Maclean Liu(刘相兵 发表于 2014-11-20 11:46
Id        Operation        Name        Rows        Bytes        Cost (%CPU)        Time
0        DELETE STATEMENT                                   377 (100)         
1           DELETE        GETUS ...

select count(*) from MLOG$_ISC_USER;
46

select count(*) from GETUSERSBYORGROLE_MVIEW;
3416531

感觉走HASH_SJ 不如走 NESTED LOOPS

Liu Maclean(刘相兵

select count(*) from GETUSERSBYORGROLE_MVIEW;
3416531


上述执行计划 中对该表全表扫描的成本评估显然不足，可以考虑通过 统计信息来 “优化”该SQL

suredandan

Liu Maclean(刘相兵 发表于 2014-12-1 12:03
select count(*) from GETUSERSBYORGROLE_MVIEW;
3416531

谢谢~ 我先去检查一下统计信息的收集情况，以及这个mv的dml的频率。

WMLM

比较典型的一个问题，好贴备注一下