Determining Oracle Materialized View usage

less than 1 minute read

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;