Materialized View fast refresh on commit run out of memory?
We created a materialized view and made it refresh fast on commit. It works well in UAT. But when we do the same thing in SIT, which has smaller memory, when they do a lot of delete and commit on the master table, it jumps out the ORA-04031 error.
ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","unknown object","sga heap(3,0)","modification ")
Seems like it run out of memory of shared pool, our current solution is changing "on commit" to once a hour. But is there any better way to do it? And since mview fast refresh only refresh the incremental part, it shouldn't take that much memory... Could that because it refresh the indexes every time as well? I have 2 indexes on this mview.