do NOT set pctincrease to 1 even in this case.
when you allow SMON to coalesce whenever it wants, it does just that,
WHENEVER it wants -- even if you have heavy user activity. So at that
time SMON is ignoring what it has to do for the users.
Also, SMON coalesce does a very small coalesce, (I can't remember if
it's 8 blocks or 8 extents at a time but it's definitely 8 something)
then goes to sleep. Not particularly efficient.
You can "fake out" the 8i features by creating your tablespace with the
proper storage parameters (or several tablespaces, following the "small
objects", "mid-sized objects", and "large objects" practice -- 3
tablespaces, place tables accordingly) and the creating tables in them
WITH NO STORAGE PARAMETERS on the table create statement. This forces
the tables to use the tablespace parameters and forces you into extents
that are the same size or multiples of one extent size. Which means you
can ignore fragmentation.
Your "gray haired" DBA knows what he/she is talking about. You might
want to listen. Older doesn't mean senility in most cases, it usually
means "I've done this and been bit by it, I learned the hard way, why
not take advantage of my experience"
Rachel
- 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:
> 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).
Received on Sat Jan 26 2002 - 07:04:49 CST
