REPOST: Re: MONITORING

Brian Tkatch wrote:
>
> 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

This would make sense since the main emphasis for re-analyze is for when row counts have changed. Of course, spurious updates could dramatically change the data distribution but I would say that this would not typically be the norm.

Thanks for posting your findings...something people do not do enough of

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."

========= WAS CANCELLED BY =======:
From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Control: cancel <3C51CD58.64B6_at_yahoo.com>
Subject: cmsg cancel <3C51CD58.64B6_at_yahoo.com>
Date: Mon, 28 Jan 2002 02:20:40 GMT
Message-ID: <cancel.3C51CD58.64B6_at_yahoo.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1         
Path: news.uni-stuttgart.de!cert.uni-stuttgart.de!news.belwue.de!news.uni-ulm.de!rz.uni-karlsruhe.de!schlund.de!newsfeed01.sul.t-online.de!t-online.de!fr.clara.net!heighliner.fr.clara.net!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40721959

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.