RE: Locally Managed Tablespace Survey

Hi Gaja !

I think this 125 minute cleanup is the SMON time out operation and i guess it is NOT a new feature of Oracle 8i. It is there from 7.3

THe normal time out for SMON is 5 minutes.Here it scans the FET$ and coalesces the extents (PCTFREE>0 right?) and eevery 25th time out (or wakeup) it cleans the TEMP segments(SEG$).

25*5 125 minutes.

So Here is the SMON's Schedule

Every

5 Minues..      Coalesce the extents
125 minutes     Cleanup the TEMP Segments
12 Hours        Shrink the Rollback Segments

Anybody wants to add anything to SMON's Schedule??? WElcome

K Gopalakrishnan
Bangalore, INDIA
http://www.geocities.com/kgkrish

At 09:16 AM 6/19/00 -0800, Gaja Krishna Vaidyanatha wrote:
>Chris,
>
>Yes, I agree that it is worthwhile to locally manage temp
>spaces, no question about it. I am just not sure about the 125
>minute clean up interval (whether it even occurs). In my
>experience, last tested in 8.0.5.X on Solaris, HP-UX, SGI Irix,
>and NT, I have not seen the clean up work at the interval you
>mention. I am curious to find out what your experience is. Is
>this 125-minute cleanup a new feature in 8i?
>
>Thanks,
>
>Gaja.
>
>--- "Gait, Christopher" <cgait_at_condor.nrl.navy.mil> wrote:
>> Yes, you are right, unless viewed in geological time, 125
>> minute periods
>> (the period of 2 hours 5 minutes between cleanups of the temp
>> space) for
>> deallocation are not 'constant'. As far as I know it is not
>> only instance
>> shutdown, but this 125 minute cycle at which the temp space is
>> cleared of
>> old sorts. Still, there is enough allocation going on to make
>> it worthwhile
>> to locally manage the temp spaces.
>>
>> Regards,
>> Chris Gait
>>
>>
>> > -----Original Message-----
>> > From: Gaja Krishna Vaidyanatha [mailto:gajav_at_yahoo.com]
>> > Sent: Monday, June 19, 2000 12:24 AM
>> > To: Multiple recipients of list ORACLE-L
>> > Subject: RE: Locally Managed Tablespace Survey
>> >
>> >
>> > Chris,
>> >
>> > May I provide a small correction to your response. I am not
>> > sure what you meant by "constant allocation and
>> deallocation" of
>> > space, hence I thought I should clarify. True Temporary"
>> > tablespaces which have been available since 7.3, do not
>> > constantly allocate and deallocate space during the life of
>> an
>> > instance. The temp segment just keeps on growing (subject
>> to
>> > the physical size of the temporary tablespace). Which means
>> > there is only allocation (if needed). The temp segment gets
>> > deallocated by SMON on shutdown.
>> >
>> > What this means is that when the high water mark of the temp
>> > segment is increased due to a "disk sort", it is not reset
>> at
>> > the end of a disk sort. It gets reset only when the
>> instance is
>> > shut down. In fact, if you have a temporary tablespace with
>> > very small/default default storage parameters, you will
>> notice
>> > SMON deallocating all the extents of the temp segment and
>> > dropping the segment on shutdown.
>> >
>> > I personally have experienced a very long "shutdown
>> immediate"
>> > at a customer site, where there were not many transactions
>> to
>> > roll back, but the temp segment residing in the true
>> temporary
>> > tablespace was just out of whack, and it took forever for
>> Oracle
>> > to come down. On monitoring the system while this was going
>> on,
>> > I could see the SMON process gaining CPU-time and an
>> inordinate
>> > amount of I-O activity occuring in the disk volumes where
>> the
>> > TEMP and SYSTEM tablespaces resided.
>> >
>> > A new temp segment is again allocated after start up, on the
>> > first sort that exceeds sort_area_size. Further it also
>> useful
>> > to know that there is only 1 temp segment for the entire
>> true
>> > temporary tablespace. The question of multiple temp
>> segments
>> > arises only when multiple true temporary tablespaces are
>> > created, for the sake of isolating and grouping the disk
>> sorts a
>> > set of users from others (for whatever reasons).
>> >
>> > I am sure the local management of space using bitmaps in the
>> > segment header for LMT, has definite performance benefits
>> over
>> > DMT especially for temporary tablespaces. This is because
>> the
>> > various space management data dictionary tables for the temp
>> > segments, will experience lower fragmentation. The constant
>> > inserts and deletes to these tables, arising due to "extent
>> > allocation" during the life of an instance, and "extent
>> > deallocation" on shutting down an instance, will no longer
>> > occur.
>> >
>> > Best Regards,
>> >
>> > Gaja.
>> >
>> > --- "Gait, Christopher" <cgait_at_condor.nrl.navy.mil> wrote:
>> > <stuff deleted>
>> >
>> > > Temporary tablespaces, on the other hand, are constantly
>> > > allocating and
>> > > deallocating space, and thus gain performance from LMTs,
>> and
>> > > can easily be
>> > > blown away and recreated in case of trouble.
>> > >
>> > > Chris Gait
>> > > Oracle DBA
>> > > Arlington, VA
>> > >
>> > > -----Original Message-----
>> > > Sent: Thursday, June 15, 2000 3:49 PM
>> > > To: Multiple recipients of list ORACLE-L
>> > >
>> > >
>> > > I'm writing a magazine article on locally managed
>> > > tablespaces, and I'd like to get some opinions and
>> feedback
>> > > from anyone who might be using these in a production
>> > > environment. If you are using locally managed tablespaces,
>> > > and you could find the time to answer the following
>> > > questions, I'd be grateful. I'm sure others on this list
>> > > would find the answers interesting as well.
>> > >
>> > >
>> > > Are you currently using locally managed tablespaces in a
>> > > production environment?
>> > >
>> > > Do you see locally managed tablespaces as the preferred
>> > > tablespace type going forward?
>> > >
>> > > Have you kept any tablespaces as dictionary managed, and
>> > > why?
>> > >
>> > > Have you used the AUTOALLOCATE feature to allow Oracle to
>> > > size your extents automatically?
>> > >
>> > > Have you encountered any specific problems with locally
>> > > managed tablespaces that other DBAs should be aware of?
>> > >
>> > > Do you over use DBMS_SPACE_ADMIN to verify or fix the
>> > > bitmaps in your locally managed tablespaces? Do you do
>> this
>> > > on a regular basis, or only when a problem occurs?
>> > >
>> > > Have you ever had a problem where you _needed_ to run
>> > > DBMS_SPACE_ADMIN in order to fix it?
>> > >
>> > > Have you noticed any measurable performance improvement as
>> a
>> > > result of using locally managed tablespaces?
>> > >
>> > >
>> > >
>> > > That's it. Not too many questions, I hope. Of course, if
>> you
>> > > can think of anything useful to add that I havn't asked
>> > > about, that would be great too.
>> > >
>> > > Thanks,
>> > >
>> > > Jonathan
>> > >
>> > > _____________________________________________________
>> > > jonathan_at_gennick.com
>> > > http://gennick.com
>> > > Brighten the Corner Where You Are
>> > > --
>> > > Author: Jonathan Gennick
>> > > INET: jonathan_at_gennick.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).
>> > > --
>> > > Author: Gait, Christopher
>> > > INET: cgait_at_condor.nrl.navy.mil
>> > >
>> > > 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).
>> >
>> >
>> > =====
>> > Gaja Krishna Vaidyanatha | gajav_at_yahoo.com
>> > Brio Technology | (972)-304-1170
>> >
>> > "Opinions and views expressed are my own and not of Brio
>> Technology"
>> >
>> > __________________________________________________
>> > Do You Yahoo!?
>> > Send instant messages with Yahoo! Messenger.
>> > http://im.yahoo.com/
>> > --
>> > Author: Gaja Krishna Vaidyanatha
>> > INET: gajav_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).
>> >
>> --
>> Author: Gait, Christopher
>> INET: cgait_at_condor.nrl.navy.mil
>>
>> 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).
>
>
>=====
>Gaja Krishna Vaidyanatha | gajav_at_yahoo.com
>Brio Technology | (972)-304-1170
>
>"Opinions and views expressed are my own and not of Brio Technology"
>
>__________________________________________________
>Do You Yahoo!?
>Send instant messages with Yahoo! Messenger.
>http://im.yahoo.com/
>--
>Author: Gaja Krishna Vaidyanatha
> INET: gajav_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
Received on Thu Jun 22 2000 - 14:54:32 CDT