Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
  Phone
  800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

    Privacy Policy

  

 

Rampant Oracle In-Focus Series

 

  Oracle Performance Troubleshooting
with Dictionary Internals SQL & Tuning Scripts

Robin Schumacher
ISBN 0-9727513-4-3 
Publication Date - May 2003
Retail Price $27.95
/  £17.95 

Order now and get 30% off the retail price!

You also receive immediate online access to the code depot!

Only $19.95


Sample Text:         

Once you identify tablespace fragmentation , what do you do about it?  Honeycomb fragmentation is easy to fix.  All that needs to be done is to combine adjacent free segments into one by issuing a coalesce  statement similar to this:

alter tablespace USERS coalesce;

Bubble fragmentation  is more difficult to handle.  Of course, the best course of action is to prevent it in the first place.  The best weapon for this is to use locally-managed tablespaces.  It may sound too simple, but in reality, implementing these storage structures in your database can just about remove the need to perform full tablespace reorganizations. 

However, what do you do if you are in a situation where you have many databases that were set up with dictionary-managed  tablespaces?  You have two options, at least if you are running Oracle 8i (the version that gave birth to locally-managed tablespaces).  First, you can create new locally-managed tablespaces in your database and then perform one last, full tablespace reorganization of all the database objects into the new tablespaces. 

Needless to say, this can be a difficult task if you have large databases and no third-party reorganization tool.  However, it will be worth the effort as you will likely never have to reorganize your tablespaces again, and your objects should never again encounter a maximum extent limit. 

If you are using Oracle 8.1.6 or higher, you can convert any current dictionary-managed  tablespaces to locally-managed tablespaces.  Buried in the Oracle documentation is a procedure for converting a tablespace's extent management from dictionary to local or vice-versa. 

The additions to the sys.dbms_space_admin  package make it quite simple to convert a tablespace to locally-managed (or to convert a locally-managed tablespace to dictionary- managed if desired).  For example, if you want to convert a dictionary-managed tablespace, called USERS to locally-managed in Oracle; all you would have to do is issue this single command:      

sys.dbms_space_admin .tablespace_migrate_to_local('USERS')

If you are afraid of how long this procedure might take on large tablespaces, do not be.  It actually runs very fast.  If, for some reason, you would like to take a tablespace that is locally-managed back to dictionary management, you can issue this command:

sys.dbms_space_admin .tablespace_migrate_from_local('USERS')

There are a few restrictions on these conversion procedures (for example, 9i UNDO tablespaces currently cannot be converted, etc.), so you should check the Oracle documentation for the specifics of using these new procedures.  Also, note that converting a dictionary-managed  tablespace that has existing objects to local will not magically rebuild all the existing object extents to conform to the sizing guidelines used by locally-managed tablespaces. 

If your situation precludes the use of locally-managed tablespaces, what choices are you left with to control tablespace fragmentation ?  One thing you can do is manually mimicking the mechanisms of locally-managed tablespaces.  This is done by:

  • Creating a tablespace that has same-sized extents for every object's INITIAL and NEXT extent values

  • Setting the tablespace's PCTINCREASE property to zero

  • Creating new objects in the tablespace without storage properties so they will inherit the tablespace's default storage properties

  • Setting each object's maximum extent limit to unlimited

 

If you're a DBA who's looking for real world Oracle tuning techniques, Oracle scripts, and advice on how to get to the heart of critical Oracle performance problems, then you've come to the right place. Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning Scripts was written by one the world's most widely-read DBAs and Oracle internals experts. Robin Schumacher focuses his incredible knowledge of the Oracle data dictionary into a superb book that shows how to quickly troubleshoot and correct Oracle performance problems. 

Plus! The online code depot is available immediately!

http://www.dba-oracle.com/bp/bp_book5_perf.htm

 


 

 

   

 Copyright © 1996 -2011 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks