Frequent and Fast Refresh Materialized Views has a Big Problem!


While I was checking V$SQL_PLAN view for a whole different reason, I realized that some of the cursors has incredibly high child cursors. I started to dig and finally I find the problem.

My customer have a few frequently fast refreshed materialized views. One of them is refreshed every 10 seconds (you might say that they can use an on commit refresh but since the base tables has too much DML, it decrease the performance and causes some locks while refreshing the materialized view (MV) ). First of all high amount of child cursors are belongs to refresh commands of MV.

one of them to insert new/changed rows and one for deleted or updated rows. you can also see a MERGE command too. both have more than 19.100 child cursors. whenever you wanted to learn why there is a new child cursor, V$SQL_SHARED_CURSOR is the view you should check:

Since I have already realized what is the problem, I just selected the necessary columns above. so reason of too much cursor is something called “Flashback cursor(1)”. Here is the “Flashback Queries” are involved. you might want to check Stew Ashton’s post:

Basically “Flashback Queries are Evil”. Flashback queries can not be shared so every time you run a flashback query ( select * from table1 as of ….) Oracle will create a new child cursor. Stew Ashton suggest a way to share it and it is beautiful (dbms_flashback.enable_at_system_change_number) which also I’ve just learned. if you are able to change your query you can use this but we don’t have a change to change the query that Oracle runs to refresh a materialized view.

All commands to refresh materialized views are flashback queries (instead of “as of timestamp” or “as of scn” they use “as of snapshot”) so they can not use a shared cursor. Now, please do the math, a materialized view which is refreshed every 10 seconds and almost every refresh runs both delete and insert commands against materialized view and non of those sql statements can share a cursor so they are creating new ones. Voila! I have more than 19.100 child cursor of both insert and delete statements.

Since I am so lazy, I just think that if I can leave them as they are but when I checked just the memory used by those cursors, I am shocked.

just the 2 sql statement’s cursors are using more almost 5GB of memory. my whole memory on this server is 48GB so this is huge to this DB.

I just flushed the cursors immediately:

of course all child cursors are gone but they are as expected started to spawn again because MV refresh is continuing it’s job.

So this is reported as a bug in Oracle and as a workaround you set a hidden parameter:

be careful while using MV’s. they can cause a lot of pains but very powerful tools.

have a nice day.

EDIT: I just tested _cursor_obsolete_threshold parameter on my local 19.6 database but I am not sure how it is work exactly! it does not start removing cursor immediately when the count exceed 100 but it does flush some after a while. Probably there is a time interval to check obsolete cursors. In any case I will flush the cursors every hour just in case.

Can not Drop Materialized View on 12c and above?

Hello Everyone,

I couldn’t write for a long time because of a crazy workload. During this heavy hardworking, I faced with a problem on one of my customers. They have a on commit refresh Materialized View (MV) and started to complain about they couldn’t insert into main table of MV. When I checked, I saw some error (which I don’t remember now) about refresh. Since it was an emergency, I run a complete refresh on MV and it was all ok but insert statement continued to fail on main table. So I decided to drop MV and I just realized that something else is also wrong because after 5 minutes, drop statement failed and even my session has lost. I tracked the drop comment and I saw that Oracle was trying to delete some tables:


after a quick search, I learned that those are new tables which came with 12c and above. Oracle stores some statistical information on them by default but they have no indexes and when you tried to drop an MV oracle try to delete those tables for that MV but because lack of indexes (probably) it gets an internal timeout.

So I simply delete (actually truncate) those tables and re-run drop MV command it worked! MV has been dropped in seconds. you can disable (or at least minimize) the logging level for MVs with:

I hope this helps to whoever has the same problem.