RE: Locally Managed Tablespace Survey

Gopal,

Please refer my other posting regarding this issue. Bottom line is that this is not relevant for tablespaces of type - TEMPORARY. Gaja.

• kgopalakrishnan <kgopalakrishnan_at_mantraonline.com> wrote:
  >
  > 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
  > >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: kgopalakrishnan
  > INET: kgopalakrishnan_at_mantraonline.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).