My Oracle Support Banner

Slow Materialized View Refresh After Upgrading Materialized View database To 19C (Doc ID 2766301.1)

Last updated on APRIL 05, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 19.8.0.0.0 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.

You are having issues while refreshing some materialized views on the database after its upgrade from 11.2.0.4 to 19C. There is one materialized view refresh session which is waiting for "db file sequential read" while executing an insert on the materialized view itself, on the remote site(materialized view log) you see the corresponding session with ACTIVE status waiting for "SQL*Net more data to client".

The refresh process is taking too much time and enabling trace 10046 level12 on the session refreshing the mview you see lots of messages like these:

WAIT #140055101335816: nam='db file sequential read' ela= 690 file#=1072 block#=2604108 blocks=1 obj#=151287 tim=320107026011
WAIT #140055101335816: nam='db file sequential read' ela= 215 file#=1071 block#=2603916 blocks=1 obj#=151287 tim=320107026343
WAIT #140055101335816: nam='db file sequential read' ela= 173 file#=1071 block#=2603980 blocks=1 obj#=151287 tim=320107026570
WAIT #140055101335816: nam='db file sequential read' ela= 154 file#=1070 block#=2603532 blocks=1 obj#=151287 tim=320107026772
WAIT #140055101335816: nam='db file sequential read' ela= 150 file#=1070 block#=2603596 blocks=1 obj#=151287 tim=320107026968

OBJ#151287 is the materialized view object, so basically, the session is waiting on a "db file sequential read" during an insert operation on the table itself.






Changes

The materialized view site upgraded from 11204 to 19C

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.