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

From: Chris Weiss <chris_at_www.hpdbe.com>
Date: Wed, 23 Jan 2002 13:40:22 -0500
Message-ID: <a2mv6r$r37$1@msunews.cl.msu.edu>

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 Wed Jan 23 2002 - 12:40:22 CST