Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Locally Managed Tablespace Survey

RE: Locally Managed Tablespace Survey

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Sun, 18 Jun 2000 20:20:25 -0700 (PDT)
Message-Id: <10532.109668@fatcity.com>


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.

> 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" Received on Sun Jun 18 2000 - 22:20:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US