Home » RDBMS Server » Security » Audit access on a MV
Audit access on a MV [message #27416] Wed, 01 October 2003 06:30 Go to next message
Messages: 417
Registered: September 1998
Senior Member

I created a new MV and wanted to know how often it is accessed.
Because the user will not directly select against the MV but only access it via Query Rewrite I can't just trace the SQL.

So my question is: what do you recommand to do to audit the access via Query Rewrite on a MV?
Re: Audit access on a MV [message #27441 is a reply to message #27416] Thu, 02 October 2003 14:37 Go to previous message
Messages: 1089
Registered: May 2002
Senior Member
normal auditing on the Materialized_view doesnt help here becos its only via query rewrite(unless you are selecting directly, which I hope not ,in your case)..but you can trace the execution plan . Maybe there are other ways,but I tried this ..

SQL> drop table t;

Table dropped.

SQL> create materialized view t_mview enable query rewrite as
2 select deptno,sum(sal) from t group by deptno;

Materialized view created.

SQL> analyze table t_mview compute statistics;

Table analyzed.

SQL> analyze table t compute statistics;

Table analyzed.

-- just making sure the Materialized_View is being used by the Query rewrite

SQL> select deptno,sum(sal) from t group by deptno;

---------- ----------
10 156729
20 58070
30 24796

Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=20)
1 0 TABLE ACCESS (FULL) OF 'T_MVIEW' (Cost=2 Card=4 Bytes=20)

SQL> alter system flush shared_pool;

System altered.

SQL> select * from v$sql_plan where object_name like 'T_MVIEW';

no rows selected

SQL> select deptno,sum(sal) from t group by deptno;

---------- ----------
10 156729
20 58070
30 24796

SQL> select operation,options,object_owner,object_name from v$sql_plan where object_name like 'T_MVIEW';

------------------------------ ------------------------------ ------------------------------

Now you know that your mview is being used. I know this method wont tell you the count or other audit details. But using the ADDRESS value, you can find more info about using

SQL> select sql_text,version_count,fetches,executions,module from v$sqlarea where
2 address=(select address from v$sql_plan where object_name='T_MVIEW');

------------- ---------- ---------- ------------------------------------------------------
select deptno,sum(sal) from t group by deptno
1 4 2 SQL*Plus

and then join to v$session etc to find out who called this..but again,that may only tell you who executed the parent cursor(ie your query) and not the child cursor(ie materialised view)..but once you are sure of the execution plan, you know its your Mview being used and not the base table.
Since library_cache works on LRU algorithm, the execution plan will be flushed out if its not used and so as long as you find it in v$sqlplan,you know the mview is being used and can trace it back to who called it via Query_rewrite.

I know there is some other elegant way..but hope this helps.

Previous Topic: User security questions
Next Topic: Auditing changes/updates to a table....
Goto Forum:

Current Time: Mon Nov 23 15:55:55 CST 2020