Nested materialized views find out actions on commit?

Question

I'd like to find out what actions (i.e. selects, inserts, updates) a database does in response to say:

insert into T (col_x, col_y, col_z) values (val_x, val_y, val_z);

T in this case is a table with a materialized view log but has a tree of nested materialized views which are based on it which refresh on commit. (e.g. T1 is based on T, T2 is based on T1 and U1 etc).

I want to see what the database does in response to the commit, so I can see if their are things I could do to speed up the process (i.e. add indexes or modify the materialized views).

I'd prefer a solution that doesn't require database system user/direct machine access, but its ok if that is the only solution possible.

Answer 1

What you can do is start a database trace by

 exec dbms_monitor.database_trace_enable();

This will trace everything that's happening in the database to the udump dir. then just

 grep "MV_NAME" *

This will output all trace files which had anything to do with your MVs. After that you'll have to to summarize the traces so you could better understand them by using tkprof.

Don't forget to disable the database trace.

Daniel