RE: Time for a reorg?

Hi John

It's my party and I'll cry if I want to! ;P

I accept everything you say below.. And have no argument against it.. ;)

What I am trying to do is come up with a script that reports on a number of factors that would lead a DBA in to feeling that it is time for a reorg.

For example, you say "3) LMT and well sized tablespaces reduces/removes the need to reorg " fair enough, I agree completely - but, good practice with LMT's is to also split objects of various sizes in to different "sized" TSs that have uniform extent sizes.. Some DBAs may *still* consider doing a reorg of a table or index once it reaches a certain number of extents, to move it to a larger extent sized TS. Also things such as chained/migrated rows etc. would you evaluate this as a percentage of the total rows within the table - or would you just say right I have 1,000,000 chained rows - time for a reorg.. ?

We do have tools that do this (reorg), but what I am looking for is a script/scripts that will collect the info that most DBAs would deem important when reviewing whether an object needs a reorg. I can build the logic in to the monitoring tool, with "user defined" thresholds against the data returned so that this can be customised to the DBAs own personal feel for "their database". This way all I have to do is give them the means to collect the info, and define their own threshold parameters to drive an alert.

For an example (no this is not a plug - but I need to give you an idea of what I'm on about):

http://www.cool-tools.co.uk/Support/UDC/Oracle/tablespace_io.html

Thanks for your help with this,

Mark

 -----Original Message-----
Sent: 13 March 2002 14:18
To: Multiple recipients of list ORACLE-L

Having a sulk Mark? :-)
There may be a number of factors involved here 1) Database well designed and managed and reorganisation does not become a problem
2) Regular use of alter table move and rebuild index reduces/removes the need to reorg
3) LMT and well sized tablespaces reduces/removes the need to reorg 4) As systems get bigger it is harder to find the outage therefore tend not to bother
5) Overtime is reduced at a lot of sites therefore less inclination to work out of hours for free
6) Disk is faster, more cpu etc so negative impact on performance is not as noticeable
I think all of the above have some truth in them I think it hard to define hard and fast guidelines because of the individuality of each site and the requirements of each database, combined with the availability and cost of resource to implement reorgs. Don't you sell any of these reorg tools?. If so how do they calculate whether they are needed or not?
HTH
John

-----Original Message-----
Sent: 13 March 2002 11:39
To: Multiple recipients of list ORACLE-L

Wow - does nobody reorg databases anymore? ;P Thanks for all your help guys.. I'll take that as a cue to put some time aside..;0P
-----Original Message-----
Sent: 12 March 2002 11:43
To: Multiple recipients of list ORACLE-L

Does anyone have any "set limits" to know when it would be time for object reorgs?
Firstly, I *know* that the reorg issue is viciously guarded on two sides - those that say reorgs should be done, and those that argue that they are unnecessary in todays environments. I agree with both sides ;P And don't intend this to break out the debate for the nth time.. What I am really after is guidelines from people to their own views of when a reorg is deemed necessary. Things such as percentage of rows that are chained/migrated, or number of extents, or number of extents as a percentage of maxextents or whatever..
Anybody come up with a really cool script that evaluates this - instead of me re-inventing the wheel? I could do this - I just don't really have the time at the moment - so your input is greatly appreciated! Thanks in advance
Mark

 Mark Leith             | T: +44 (0)1905 330 281
 Sales & Marketing      | F: +44 (0)870 127 5283
 Cool Tools UK Ltd      | E: mark_at_cool-tools.co.uk
===================================================
           http://www.cool-tools.co.uk
       Maximising throughput & performance

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).


=========================================================
This electronic message contains information from the mmO2 plc Group
which may be privileged or confidential. The information is intended to be
for the use of the individual(s) or entity named above. If you are not the
intended recipient be aware that any disclosure, copying, distribution or
use of the contents of this information is prohibited. If you have received
this electronic message in error, please notify us by telephone or email


(to the numbers or address above) immediately.


=========================================================

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may


also send the HELP command for other information (like subscribing).