Re: MONITORING

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


Brian Tkatch wrote in message <3c5353f8.1619018204_at_news.alt.net>...


>On Fri, 25 Jan 2002 21:48:49 +0000 (UTC), "John Darrah"

><jdarrah_at_veripost.net> wrote:

>

>>Brian,

>>Here is the query DBMS_STATS uses to find stale objects.  To get this I

>>typed the following command at a sqlplus prompt:

>>SQL> alter session set events '10046 trace name context forever, level

>>12';

>>SQL> < execute the dbms_stats.gather_stale procedure>

>>Then if you go to your udump directory, you'll find a file named

>>something along the lines of <ORACLE_SID>_ora_<process_num>.trc.  If you

>>just go after the most recent file, most times you'll have the right

>>one.  Inside the file your find a bunch of sql statements, bind

>>variables for those statements and wait events posted while the

>>statements were being run.  Using tkprof, you can get the statement into

>>a more readable statement but you'll lose the bind and wait stuff (until

>>9i).  Anyway, here is the statement, it look pretty much like it uses a

>>10% of total rows changed to determine whether or not to reanalyze.

>

>

>I'm on 8i. I did the trace, after SETting the event was pointed out.

>The resulting trace information was too confusing for me, however, and

>figuring it out seem beyond me.

>>

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

>>                UNION ALL

>>                SELECT MOWNER,

>>                       TEMP_LOG

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

>>UNION ALL

>>SELECT /*+  ordered use_hash(tp) use_nl(o) use_nl(u)  */

>>       U.NAME OWN,

>>       O.NAME TAB,

>>       O.SUBNAME PART,

>>       NULL  SPART

>>  FROM SYS.MON_MODS$ M,

>>       SYS.TABPART$ TP,

>>       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# = TP.OBJ#

>>   AND BITAND(TP.FLAGS,2) = 2

>>   AND ((BITAND(M.FLAGS,1) = 1 ) OR ((M.INSERTS + M.UPDATES  + M.DELETES

>>) > (.1 * TP.ROWCNT ) ))

>>   AND TP.OBJ# = O.OBJ#  AND O.OWNER# = U.USER#

>>UNION ALL SELECT /*+  ordered use_hash(tcp) use_nl(o) use_nl(u)  */

>>      U.NAME OWN,

>>      O.NAME TAB,

>>      O.SUBNAME PART,

>>      NULL  SPART

>> FROM SYS.MON_MODS$ M,

>>      SYS.TABCOMPART$ TCP,

>>      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# = TCP.OBJ#  AND BITAND(TCP.FLAGS,2) = 2

>>   AND ((BITAND(M.FLAGS,1) = 1 ) OR ((M.INSERTS + M.UPDATES  + M.DELETES

>>) > (.1 * TCP.ROWCNT ) ))

>>   AND TCP.OBJ# = O.OBJ#

>>   AND O.OWNER# = U.USER#

>>UNION ALL

>>SELECT /*+  ordered use_hash(tsp) use_nl(o) use_nl(u) use_nl(op)  */

>>       U.NAME OWN,

>>       O.NAME TAB,

>>       OP.SUBNAME PART,

>>       O.SUBNAME SPART

>>  FROM SYS.MON_MODS$ M,

>>       SYS.TABSUBPART$ TSP,

>>       SYS.OBJ$ O,

>>       SYS.USER$ U,

>>       SYS.OBJ$ OP

>> WHERE ((:b1 IS NULL ) OR (U.NAME = :b1 ))

>>   AND ((:b3 IS NOT NULL ) OR (U.NAME != 'SYS' ))

>>   AND M.OBJ# = TSP.OBJ#

>>   AND BITAND(TSP.FLAGS,2) = 2

>>   AND ((BITAND(M.FLAGS,1) = 1 ) OR ((M.INSERTS + M.UPDATES  + M.DELETES

>>) > (.1 * TSP.ROWCNT ) ))

>>   AND TSP.OBJ# = O.OBJ#

>>   AND O.OWNER# = U.USER#

>>   AND TSP.POBJ# = OP.OBJ#

>>ORDER BY 1,2,3,4

>

>

>I ought to tear this apart later. Any pointers to help me understand

>the main tables references would be greatly appreciated.

>

>Brian