DB file sequential read and SQL*Net more data to client After Upgrading Mview Site To 19C from 11.2.0.4 (Doc ID 2762292.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

You are having issues while refreshing Materialized views after upgrading database  from 11.2.0.4 to 19.8. There is one mview refresh session which is waiting for "db file sequential read" while executing an insert on the mview itself, on the remote site, 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 we 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 mview object , so basically the session is waiting on a "db file sequential read" during an insert operation on the Mview base table itself.

Mview site upgraded from 11204 to 19C
Master site is still 11.2.0.4

Changes

 Upgraded the Mview site from 11.2.0.4 to 19C

Cause

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