Monitor MV

[Roshan]

Hello Erman,

is there a way we can monitor the materialized view resource consumption running on DB?

Should I run trance file on DB?
SQL> alter session set events '10046 trace name context forever, level 12';

Regards,

Roshan

[ErmanArslansOracleBlog]

Materialized view resource consumption occurs, when both you query it and when you(or database) refreshes it..

'10046 trace name context forever, level 12' is promising. We see it almost on every MOS note that is  related to the MV diagnostics.

Basically, you should monitor the session operating on the MV.. If it is a job that is refreshing the MV, then you should monitor that job session to get insight about the resource consumption..
If someone is executing a query that gets its data from the MV, then you should monitor the session of that someone..

For other type of monitor, like monitoring the refreshes and etc, check the note -> Materialized View Refresh: Locking, Performance, Monitoring (Doc ID 258252.1)

Also check the following note, it also includes that event you sent me ..

SRDC - Data to Supply for Slow Materialized View REFRESH or CREATE, DROP statements (Doc ID 1672764.1)

Additional notes;

How to obtain SQL And Optimizer Traces for a Materialized View (MV) Creation Or Refresh Process? (Doc ID 1616695.1)
How to Troubleshoot Unable to or Cannot Fast Refresh a Materialized View (Doc ID 1387767.1)
Using Execution Plan to Verify Materialized View Query Rewrite (Doc ID 245635.1)

[Roshan]

Thanks for the update