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

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
> >
> >
> >
> >

• WAS CANCELLED BY =======: From: Stefan Dipper <stefan.dipper_at_spamnix.com> Control: cancel <3C4FCF7A.E6844AA7_at_spamnix.com> Subject: cmsg cancel <3C4FCF7A.E6844AA7_at_spamnix.com> Date: Mon, 28 Jan 2002 01:58:25 GMT Message-ID: <cancel.3C4FCF7A.E6844AA7_at_spamnix.com> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.misc NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!dns.phoenix-ag.de!news.csl-gmbh.net!news-x2.support.nl!216.17.128.8.MISMATCH!newsfeed.frii.com!out.nntp.be!propagator-SanJose!in.nntp.be!hub1.meganetnews.com!hub1.nntpserver.com!peer1-sjc1.usenetserver.com!usenetserver.com!gestalt.direcpc.com!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp03!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40716585

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Thu Jan 24 2002 - 03:10:18 CST