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 <[EMAIL PROTECTED]>@fatcity.com on 01/25/2002 07:16:06 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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. --- [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
