Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: MONITORING

Re: MONITORING

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Mon, 28 Jan 2002 20:10:06 GMT
Message-ID: <3c55ab51.1772451704@news.alt.net>


On Sun, 27 Jan 2002 09:15:25 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>As a first pass, it looks as if it is joining the
>'monitored modifications' table to various
>object tables to turn ids into names.
>
>The different unions are:
>Tables, table partitions, table composite
>subpartitions and table subpartitions.
>The NOT IN exclusion is to avoid handling
>snapshot logs.
>
>
>--
>Jonathan Lewis
>http://www.jlcomp.demon.co.uk
>
>Now running 3-day intensive seminars
>http://www.jlcomp.demon.co.uk/seminar.html
>
>Host to The Co-Operative Oracle Users' FAQ
>http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>Author of:
>Practical Oracle 8i: Building Efficient Databases

So, the main point (before the first UNION ALL) is then

   AND ((BITAND(M.FLAGS,1) = 1 ) OR
        ((M.INSERTS + M.UPDATES + M.DELETES) > (.1 * T.ROWCNT ) )) The first part checks a flag, but I am not sure how the flag gets set. I am assuming that is TRUNCATEd. User_Tab_Modifications seems to point to that as well.

The second part checks if all INSERTs UPDATEs and DELETEs total to more than 10% or the total rowcount.

This seems odd, because I have a table

User_Tables.Num_Rows: 10244
User_Tab_Modifications.INSERTs: 28
User_Tab_Modifications.DELETEs: 0
User_Tab_Modifications.UPDATEs: 4817
User_Tables.Last_Analyzed: 1/21/02 12:04:12 PM
User_Tab_Modifications.TimeStamp: 1/28/02 11:57:37 AM

This puts the total change at ~47.3% ((28 + 0 + 4817) / 10244). Yet it did not get reanalyzed.

I just checked User_Tables, and Monitoring is set to YES.

In fact, if I run

SELECT /*+ ordered full(t) use_hash(t) use_nl(o) use_nl(u) */

	U.Name		Own,
	O.Name		Tab,
	NULL		Part,
	NULL		SPart
FROM
	SYS.MON_MODS$	M,
	SYS.TAB$	T,
	SYS.OBJ$	O,
	SYS.USER$	U
WHERE
	((:b1 IS NULL ) OR (U.NAME = :b1 ))
  AND	((:b3 IS NOT NULL ) OR (U.NAME != 'SYS' ))
  AND	M.OBJ# = T.OBJ#
  AND	( (U.NAME,O.NAME) NOT IN (SELECT MOwner, Log FROM sys.MLog$
				UNION ALL
				SELECT Mowner, Temp_Log FROM sys.MLog$
				WHERE Temp_Log IS NOT NULL))

   AND BITAND(T.FLAGS,16) = 16
   AND ((BITAND(M.FLAGS,1) = 1 ) OR ((M.INSERTS + M.UPDATES + M.DELETES) > (.1 * T.ROWCNT ) ))
   AND T.OBJ# = O.OBJ#
   AND O.OWNER# = U.USER# and change :b1 (and :b3) to the schema owner, it pulls up this very table.

What could be stopping this table from automatically being reANALYZEd?

Brian Received on Mon Jan 28 2002 - 14:10:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US