My Oracle Support Banner

MATERIALIZED VIEW FAST REFRESH IS VERY SLOW, "AS OF SNAPSHOT" CURSORS NOT BEING SHARED (Doc ID 1051346.1)

Last updated on FEBRUARY 01, 2022

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.5 to 11.2.0.2.0 [Release 10.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.


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.


********************************************************************************

update sys.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.

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.