Doing some tests, I noticed that having
REFRESH FAST ON COMMIT materialized view and flashbacks enabled cause Oracle to create many child cursors in some cases. For example,
CREATE TABLE table1 (table1_id INT NOT NULL PRIMARY KEY, val1 VARCHAR2(20)); CREATE TABLE table2 (table2_id INT NOT NULL PRIMARY KEY, table1_id INT NOT NULL , val2 VARCHAR2(20), CONSTRAINT FK_table2_table1_id FOREIGN KEY (table1_id) REFERENCES table1(table1_id) ); --index on FK column omitted for simplicity CREATE MATERIALIZED VIEW LOG ON table1 WITH ROWID; CREATE MATERIALIZED VIEW LOG ON table2 WITH ROWID; CREATE MATERIALIZED VIEW mv_test REFRESH FAST ON COMMIT AS SELECT A.table1_id, b.table2_id, A.val1, b.val2, a.rowid as rid1, b.rowid as rid2 FROM table1 A,table2 b WHERE (b.table1_id = a.table1_id); INSERT INTO table1 (table1_id,val1) values (1,'test'); INSERT INTO table2 (table2_id,table1_id,val2) VALUES (1,1,'test2'); COMMIT; UPDATE table2 SET val2 = 'test3' WHERE table2_id = 1; COMMIT; SELECT * FROM v$sqlarea WHERE sql_text like upper('%mv_test%'); -- find sql_id used to populate data into mview, -- something starting with /* MV_REFRESH (INS) */, -- version count = 1 UPDATE table2 SET val2 = 'test4' WHERE table2_id = 1; COMMIT; -- check version count in v$sqlarea, it got increased by 2, now it's 3 SELECT * FROM v$sql_shared_cursor WHERE sql_id = :sql_id_from_prev_steps -- it shows 'Y' in FLASHBACK_CURSOR
From now each update(followed by commit) that hits underlying tables (or at least columns that are part of materialized view definition) ends up creating a new child cursor. As far as I understand many child cursors means among other things high usage of sharable memory.
Can it be an issue in real-world scenario even in case of relatively small number of dml against underlying tables? If yes, what is the right way to deal with such cases ?