Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: MONITORING
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))
What could be stopping this table from automatically being reANALYZEd?
Brian Received on Mon Jan 28 2002 - 14:10:06 CST