2

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 ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.