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
Received on Mon Jun 19 2000 - 11:04:23 CDT
