Determining Oracle Materialized View usage

We have some materialized views but are not sure they’re being used after index and schema changes were made to improve performance.

1. Enable auditing: AUDIT SELECT ON MVIEW_NAME;
2. run targeted queries
3. Find no data in SYS.AUD$
   1. Table is huge so straight SELECT * FROM SYS.AUD$; takes too long
   2. Try to look for selects: SELECT * FROM SYS.AUD$ WHERE ACTION#=3 /* SELECT from SYS.AUDIT_ACTIONS */;
   3. Non-relevant data found
4. Enable auditing on table that is commonly used: AUDIT SELECT ON TABLE_NAME;
5. Run some selects
6. Still find no data in SYS.AUD$

Since auditing seems to not be working as I expected (possibly because google-whacking it wrong), try a different tack. See if the common queries being run would be rewritten with dbms_mview.explain_rewrite on each query.

1. The REWRITE_TABLE needs to be created if not already done
2. run begin dbms_mview.explain_rewrite('<the query>', 'MVIEW_NAME', to_char(sysdate, 'yyyy-mm-dd-hh24:mi:ss.ssss')); end;
3. Look at the results in the REWRITE_TABLE: select * from rewrite_table order by 1 desc;