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.
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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> ... ... ... ... ... |
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.
|
1 2 3 4 5 6 7 |
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 |
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:
|
1 2 3 |
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:
|
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.