Re: MONITORING

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 Received on Sat Jan 26 2002 - 19:14:48 CST