Re: Oracle 9i: Information about how frequent materialized views have been used

PMFJI.
I don't know the answer for sure - I assume your first guess is correct. But anyway (to Stefan), how about audit select on the MV as an alternative?

Paul

"Stefan Dipper" <stefan.dipper_at_spamnix.com> wrote in message news:3C4FCF7A.E6844AA7_at_spamnix.com...
> Hi Chris,
>
> thank you for your response. One thing is not clear to me:
>
> Use of materialized views is transparent. So what happens if I query a
table
> and the optimizer decides to use a materialized view instead ? I assume in
> v$sqlarea the original statement text appears, not the statement after
query
> rewrite. But maybe I am wrong.
>
> Regards,
> Stefan
>
> Chris Weiss wrote:
>
> > Query the V$SQLAREA. The execution count will tell you how often SQL
> > against these views is executed.
> >
> > SELECT SUM(EXECUTIONS) FROM V$SQLAREA WHERE SQL_TEXT LIKE '%<OBJECT>%';
> >
> > You could extend this with an anonymous block driven off of all_objects
or
> > user_objects
> >
> > DECLARE
> > CURSOR c_cursor IS
> > SELECT OBEJCT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE=VIEW;
> > l_count PLS_INTEGER;
> > BEGIN
> > FOR c_rec IN c_cursor LOOP
> > EXECUTE IMMEDIATE 'SELECT SUM(EXECUTIONS) FROM V$SQLAREA WHERE
SQL_TEXT
> > LIKE ''%'||c_rec.OBJECT_NAME||'%''' INTO l_count
> > -- Write the results somewhere for review
> > END LOOP;
> > END;
> > /
> >
> > This will be ****SLOW**** so run it off hours.
> >
> > Good Luck!
> > --
> >
> > ~~~~~~~~~~~~~~~~
> > Chris Weiss
> > www.hpdbe.com
> > High Performance Database Engineering
> > ~~~~~~~~~~~~~~~~
> >
> > "Stefan Dipper" <stefan.dipper_at_sap.com> wrote in message
> > news:3C4ED367.960EC055_at_sap.com...
> > > Hi all,
> > >
> > > is there a possibility to figure out in Oracle 9i on a view level how
> > > frequent materialized views have been used ? Information of that kind
is
> > > quite important for a DBA to be able to decide whether the effort to
> > > maintain the materialized view makes sense. I would like to avoid a
> > > situation where there are hundreds of materialized views in a system
and
> > > nobody knows which are the important ones.
> > >
> > > Regards,
> > > Stefan
> > >
> > >
> > >
> > >
>
Received on Thu Jan 24 2002 - 12:54:18 CST