Re: MONITORING

From: John Darrah <jdarrah_at_veripost.net>
Date: Fri, 25 Jan 2002 21:48:49 +0000 (UTC)
Message-ID: <069b4706da05cc5c235b4dd2f5e87889.36240@mygate.mailgate.org>

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.

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 "Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news:3c51a6f5.1509191282_at_news.alt.net...

> On Thu, 24 Jan 2002 21:53:27 GMT,
> SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch)
> wrote:
>
> >On Thu, 24 Jan 2002 20:34:40 +0000, Connor McDonald
> ><connor_mcdonald_at_yahoo.com> wrote:
> >
> >>Brian Tkatch wrote:
> >>>
> >>> When is an item considered stale, so that GATHER STALE collects
> >>> recollects statistics?
> >>>
> >>> Also, I hacked this together:
> >>>
> >>> SELECT UT.Table_Name, UT.Num_Rows,
> >>> UTM.Inserts, UTM.Updates, UTM.Deletes,
> >>> ROUND((UTM.Inserts + UTM.Deletes) / DECODE(UT.Num_Rows, 0, 100,
> >>> UT.Num_Rows) * 100, 2) || '%' "Percent Added/Deleted",
> >>> ROUND((UTM.Inserts + UTM.Updates + UTM.Deletes) /
> >>> DECODE(UT.Num_Rows, 0, 100, UT.Num_Rows) * 100, 2) || '%' "Percent
> >>> Changed",
> >>> UT.Last_Analyzed, UTM.Timestamp "Modification time"
> >>> FROM User_Tables UT, User_Tab_Modifications UTM
> >>> WHERE UT.Table_Name = UTM.Table_Name
> >>> ORDER BY (UTM.Inserts + UTM.Updates + UTM.Deletes) /
> >>> DECODE(UT.Num_Rows, 0, 100, UT.Num_Rows) DESC
> >>>
> >>> Does it make sense?
> >>>
> >>> Brian
> >>
> >>I remember reading somehwere that gather_stale is based on 10% deltas,
> >>but please don't take that as gospel.
> >
> >But 10% of INSERTs and DELETEs, or do UPDATEs count as well.
> >
> >I'm watching GATHER STALE. I just want to know what to watch.
> >
> >Brian
>
>
> Just to answer my own question. It appears that the 10% rule is
> followed, using INSERTs and DELETEs, but ignoring UPDATEs. I have a
> table with, using the query above, has a Percent Added/Deleted of
> 24%, but Percent Changed of 32.35%. It was not reanalyzed by the
> GATHER STALE. Anything above 10% Added/Deleted was however.
>
> Brian

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Fri Jan 25 2002 - 15:48:49 CST