Frequent and Fast Refresh Materialized Views has a Big Problem! Mustafa, 2020-07-252020-07-25 Hi, 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. Oracle PL/SQL select sql_text from v$sql Where sql_id='1tup2y6qaad0c' and rownum = 1; /* MV_REFRESH (INS) */ INSERT /*+ NOAPPEND */ INTO ... select sql_text from v$sql Where sql_id='d2546kmkkapbu' and rownum = 1 /* MV_REFRESH (DEL) */ DELETE FROM ... 12345 select sql_text from v$sql Where sql_id='1tup2y6qaad0c' and rownum = 1;/* MV_REFRESH (INS) */ INSERT /*+ NOAPPEND */ INTO ... select sql_text from v$sql Where sql_id='d2546kmkkapbu' and rownum = 1/* MV_REFRESH (DEL) */ DELETE FROM ... 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: Oracle PL/SQL select SQL_ID, CHILD_NUMBER, FLASHBACK_CURSOR, REASON from v$sql_Shared_cursor where sql_id = '1tup2y6qaad0c'; SQL_ID CHILD_NUMBER FLASHBACK_CURSOR REASON 1tup2y6qaad0c 1 Y <ChildNode><ChildNumber>1</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 3 Y <ChildNode><ChildNumber>3</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 5 Y <ChildNode><ChildNumber>5</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 7 Y <ChildNode><ChildNumber>7</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 9 Y <ChildNode><ChildNumber>9</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> ... ... ... ... ... 123456789101112 select SQL_ID, CHILD_NUMBER, FLASHBACK_CURSOR, REASON from v$sql_Shared_cursor where sql_id = '1tup2y6qaad0c';SQL_ID CHILD_NUMBER FLASHBACK_CURSOR REASON1tup2y6qaad0c 1 Y <ChildNode><ChildNumber>1</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 3 Y <ChildNode><ChildNumber>3</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 5 Y <ChildNode><ChildNumber>5</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 7 Y <ChildNode><ChildNumber>7</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> 1tup2y6qaad0c 9 Y <ChildNode><ChildNumber>9</ChildNumber><ID>21</ID><reason>Flashback cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode> ............... 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: https://stewashton.wordpress.com/2018/07/23/optimistic-locking-8-double-checking-with-flashback/ 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. Oracle PL/SQL select round(sum(persistent_mem)/1024/1024,-2) size_in_mb from v$sql where sql_id = '1tup2y6qaad0c'; SIZE_IN_MB 3200 select round(sum(persistent_mem)/1024/1024,-2) size_in_mb from v$sql where sql_id = 'd2546kmkkapbu'; SIZE_IN_MB 1600 1234567 select round(sum(persistent_mem)/1024/1024,-2) size_in_mb from v$sql where sql_id = '1tup2y6qaad0c';SIZE_IN_MB3200 select round(sum(persistent_mem)/1024/1024,-2) size_in_mb from v$sql where sql_id = 'd2546kmkkapbu';SIZE_IN_MB1600 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: Oracle PL/SQL select 'EXEC SYS.DBMS_SHARED_POOL.PURGE (''' ||ADDRESS||', '|| HASH_VALUE ||''', ''C'');' from gv$sqlAREA where SQL_ID in ('d2546kmkkapbu'); EXEC SYS.DBMS_SHARED_POOL.PURGE ('000000A824555200, 3844429178', 'C'); -- to flush cursor 123 select 'EXEC SYS.DBMS_SHARED_POOL.PURGE (''' ||ADDRESS||', '|| HASH_VALUE ||''', ''C'');' from gv$sqlAREA where SQL_ID in ('d2546kmkkapbu'); EXEC SYS.DBMS_SHARED_POOL.PURGE ('000000A824555200, 3844429178', 'C'); -- to flush cursor 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: Oracle PL/SQL alter system set "_cursor_obsolete_threshold"=100 scope=spfile; 1 alter system set "_cursor_obsolete_threshold"=100 scope=spfile; 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. 12c 18c 19c Administration Development Materialized View Performance /* MV_REFRESH (DEL) *//* MV_REFRESH (INS) *//* MV_REFRESH (MRG) */fast refresh materialized viewfast refresh MVflashback cursorflashback cursor(1)materialized viewtoo many child cursor
Hi Mustafa, you mentioned in article it’s been reported as a bug to Oracle, what’s the bug no.? The fix with _cursor_obsolete_threshold doesn’t really fix the issue, it just keeps a limit on the amount of cursors? So it’s still generating cursors constantly with mv refresh? I have an application that is performing row by row commit which we can’t do much about and it’s generating 000’s cursors due to a mv refresh fast on commit Reply
Hi Ojock, with your question I realized that I wrote that phrase wrong. this is not a bug. this is a known issue and how it is worked. check the Doc ID 2192862.1 for more info. thanks to you, I also corrected my post. I am aware of that “_cursor_obsolete_threshold” is not a solution but a workaround so you can limit your cursors but at least help you with the problem. row by row commit and fast refresh materialized view should be painful 🙂 maybe you should change the architecture here. you can create your own table to store whatever MV stores and add it to your dml procedures so you can generate data without any flashback operation and save yourself from thousands version of sql. I wish oracle would used dbms_flashback.enable_at_system_change_number instead of “as of ” queries. that way we wouldn’t deal with this problem. by the way, on a client system, I literally wrote a job and purge those sql id’s to get rid of them. Reply