Database DataWarehousing (MOSC)

MOSC Banner

MV refresh takes 4 hours, same statement in CTAS takes 40 seconds

edited May 9, 2017 12:24PM in Database DataWarehousing (MOSC) 8 commentsAnswered

Hi all,

we have a MV with a rather large select statement. We are currently moving this MV from Oracle 11.1 to another server running 12.1. A complete refresh on the 11.1 server takes two minutes tops, on the 12.1 server it takes about four hours. Running the MV's select statement as a CTAS statement on the 12.1 server takes about 40 seconds. Statistics are of course up to date.

When the MV refreshes, I see a lot of time spent on the CPU, doing milliions of logical reads, probably because the optimizer chose a nested loop somewhere. I do not see this doing the CTAS.

Tagged:

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center