We have some materialized views but are not sure they’re being used after index and schema changes were made to improve performance.
- Enable auditing: AUDIT SELECT ON MVIEW_NAME;
- run targeted queries
- Find no data in SYS.AUD$
- Table is huge so straight SELECT * FROM SYS.AUD$; takes too long
- Try to look for selects: SELECT * FROM SYS.AUD$ WHERE ACTION#=3 /* SELECT from SYS.AUDIT_ACTIONS */;
- Non-relevant data found
- Enable auditing on table that is commonly used: AUDIT SELECT ON TABLE_NAME;
- Run some selects
- 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.
- The REWRITE_TABLE needs to be created if not already done
- run begin dbms_mview.explain_rewrite('<the query>', 'MVIEW_NAME', to_char(sysdate, 'yyyy-mm-dd-hh24:mi:ss.ssss')); end;
- Look at the results in the REWRITE_TABLE: select * from rewrite_table order by 1 desc;