物化视图BUG 导致CPU消耗超高
单击此项可添加到收藏夹
MATERIALIZED VIEW FAST REFRESH IS VERY SLOW, "AS OF SNAPSHOT" CURSORS NOT BEING SHARED (文档ID 1051346.1) 转到底部转到底部
In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Database - Enterprise Edition - Version 10.1.0.5 to 11.2.0.2.0 [Release 10.1 to 11.2] Information in this document applies to any platform.
Symptoms
On a production database version 11.1.0.7, the fast refresh of a nested materialized view takes a lot of time comparing to the select statement used for the creation of the materialized view.
The refresh of the mview takes approximately 16 min. The select statement itself finishes in about 8 seconds.
It can be seen that most of the time is spent with the parse of update sys.sumpartlog$ statement.
******************************************************************************* *
updatesys.sumpartlog$ s set s.timestamp = :1, s.scn = :2
where
rowid in (select rowid from sumpartlog$ AS OF SNAPSHOT(:3) s1 where
s1.bo# = :4 and s1.timestamp >= to_date('4000-01-01:00:00:00',
'YYYY-MM-DD:HH24:MI:SS'))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 966.75 955.72 0 0 7648 0
Execute 1 0.01 0.00 0 1 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 966.76 955.72 0 1 7648 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE SUMPARTLOG$ (cr=6 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=6 pr=0 pw=0 time=0 us cost=1 size=68 card=1)
0 TABLE ACCESS BY INDEX ROWID SUMPARTLOG$ (cr=6 pr=0 pw=0 time=0 us cost=0 size=34 card=1)
0 INDEX RANGE SCAN I_SUMPARTLOG$ (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 612)
0 TABLE ACCESS BY USER ROWID SUMPARTLOG$ (cr=0 pr=0 pw=0 time=0 us cost=1 size=34 card=1)
******************************************************************************* *
The same issue can be encountered on a 10204 environment.
Cause
Bug 8865718: RECURSIVE CURSORS CONTAINING "AS OF SNAPSHOT" CLAUSE ARE NOT SHARED Affects: RDBMS (B1-C1)
NB: FIXED
Abstract: Recursive cursors for MV refresh not shared
Fixed-Releases: C100
Tags: MVIEW OERI OPERF POOL
Details:
Certain recursive statements generated during a MATERIALIZE VIEW refresh
are not shared (due to FLASHBACK_CURSOR) causing progressive performance
degradation or even ORA-600 [kksfbc-new-child-thresh-exceeded] errors.
The recursive SQL contains "AS OF SNAPSHOT" clauses and has high version
counts.
Note: It is normal for cursors using "AS OF SNAPSHOT" clauses
not to be shared - this issue is specific to cursors used
for Materialized View refresh which are a special case
and can be shared under specific circumstances.
Solution
Apply patch 8865718 if is available for your platform otherwise request for a backport.
To download the patch please go to My Oracle Support
1) Click on `Patches`& 'Updates'tab
2) Click on 'Oracle, Siebel and Hyperion products' link
3) Click on `Simple Search` link.
4) Enter patch number in the `Patch Number` field.---------> 8865718
5) Select your platform.
6) Click on `Download` to begin the download process. Note: Please review the Readme file for instructions on how to install the patch.
OR
Apply an appropriate upgrade, patch set, or patch set update from the following list:
11.2.0.2 (Server Patch Set)
11.2.0.1.2 (Patch Set Update)
11.1.0.7.4 (Patch Set Update)
11.2.0.1 Patch 3 on Windows Platforms
11.1.0.7 Patch 24 on Windows Platforms
References
BUG:8865718 - RECURSIVE CURSORS CONTAINING "AS OF SNAPSHOT" CLAUSE ARE NOT SHARED