Re: SMON - Does it cause a degrade?

I think I mentioned this earlier. :)

SMON will do a coalesce if necessary to provide blocks for an extent, if necessary.

The following link tells all. This may be in the 8i manual, but it works the same way in the Oracle 7.

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c02block.htm#2846

As for the 8i space management features not applying in Oracle 7, well that paper is not just about Oracle 8i. The same principles apply in Oracle 7. Just make your extents a uniform size.

Personally though, I decided years ago that I wasn't going to waste my time 'defragmenting' tablespaces unless there was a significant space savings and space was tight.

The improvement in performance that comes from rebuilding a table is due to reducing block fragmentation, not tablespace fragmentation.

Whether you want to spend time re-orging is up to you. I just always have something better to do. Like evangelizing about space, Perl and wasted time. :)

Jared

On Friday 25 January 2002 18:35, Rajesh.Rao_at_chase.com wrote:
> Okie. To be more specific. This is a siebel application running against a
> 7.3.4 database. So the 8i features for space management are out of the
> question. Second, there are large tables with varied values for initial and
> next extent in the tablespace. There are also going to be temporary tables
> created and dropped during a data load. I know for sure this tablespace is
> going to be badly fragmented. Hence, I was suggesting that pctincrease be
> set to 1 at the tablespace level so that SMON could coalesce the adjacent
> free extents. But the other side was of the opinion that SMON could cause a
> performance degrade. I needed to confirm this.
>
> The answer that I was looking for was: Is SMON resource intensive? Melissa
> directed me to a note on Metalink which said they could be. Could hog the
> CPU, and hold ST enqueue locks on the data dictionary space transaction
> tables. The bosses always want to see it on paper, saying Oracle says so.
>
> And to update you guys further, I had my say. Got two tablespaces. One were
> all extents are going to be of 128M and another where they will be 256M.
> Chosen to be multiples of block_size*db_multiblock_read_count. And a
> pctincrease of 0 at the tablespace level, which obviusly I dont mind now.
>
> Thanks everybody. Now gotto go and do something interesting ;-)
>
> Raj
>
> Rachel Carmichael <wisernet100_at_yahoo.com>@fatcity.com on 01/25/2002
> 07:16:06 PM
>
>
> Please respond to ORACLE-L_at_fatcity.com
>
>
> Sent by: root_at_fatcity.com
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
>
>
>
>
>
> 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:
>
> a) you don't have to worry about storage parameters when creating
> tables
> b) 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: Jared Still
  INET: jkstill_at_cybcon.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).