RE: SMON - Does it cause a degrade?

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 25 Jan 2002 19:11:21 -0800
Message-ID: <F001.003FC04D.20020125190520@fatcity.com>

Cary Millsap, in his 'Myths About Extents' presentation advocates turning off the auto-coalescing by SMON, stating that the auto-coalesce feature does more harm than good.
He had presented this at OAUG Database SIG meeting in Hawaii (held in Oct, 2000).

• Kirti

-----Original Message-----
Sent: Friday, January 25, 2002 6:16 PM
To: Multiple recipients of list ORACLE-L

Why the heck would you set pctincrease to anything but 0 at the tablespace level. All you need is one table, created without storage parameters and you are fragmented.

Try "Stop Defragmenting and Start Living"... what you want to do is exactly what your DBA said, with the addition of either local management (in which case, pctincrease is moot) or at least "minimum extent" on the tablespace. Create all tables in the tablespace with NO storage clause, let it default to the tablespace's storage parameters.

This does several things:

1. you don't have to worry about storage parameters when creating tables
2. all extents will be the same size or a multiple of each other -- so NO fragmentation

Then you don't have to worry about the silly batch job either and can go on and do something much more interesting.

• Rajesh.Rao_at_chase.com wrote:
  > Hey Fellas,
  >
  > I have an application DBA who insists that the pctincrease at the
  > data
  > tablespace should be set to 0 so that SMON does not coalesce the
  > tablespace. He says coalesce will be performed by using a scheduled
  > batch
  > job written for that purpose. He states that having SMON to perform
  > an
  > coalesce of the tablespace could cause an performance degrade??? I
  > have
  > never heard of such a thing, but then I dont wanna argue with him.
  > He's got
  > wrinkles on his face, and grey hair ;-)
  >
  > My argument would be, go back to the drawing board, get your tables
  > sized
  > properly, if you anticipate fragmentation. And SMON does not cause a
  > performance degrade? It wakes up every 5 minutes, does hold ST
  > enqueue
  > locks if a tablespace needs coalescing, but it does not cause a
  > performance
  > degrade? Or does it???
  >
  > Now, can I have a definitive word on this? Any sites, white papers,
  > to
  > refer to that says so, or to the contrary. I need to convince the
  > higher
  > ups.
  >
  > Raj
  >
  > --
  > Please see the official ORACLE-L FAQ: http://www.orafaq.com
  > --
  > Author:
  > INET: Rajesh.Rao_at_chase.com
  >
  > 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).
  

  ---

Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.com

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: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.com

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).

Received on Fri Jan 25 2002 - 21:11:21 CST