Robin Schumacher
OTJ, Spring 1996
An expert Oracle DBA gives his "tips from the trenches" on how to manage space in Oracle7 -- and still have a life.
It's late and I'm tired. Because I have a 60-mile round-trip commute every day, I try to leave a little early and beat the rush, but sometimes, like today, it doesn't happen. This time, the problem holding me up is that my users are loading data into one of the Oracle databases that I manage. Normally, this wouldn't be a problem. But today it is because they're putting more data into the tables than they led me to believe during our initial planning stage, and a number of space problems have reared their ugly heads.
So, I suppose the first thing to mention when talking about managing space effectively in Oracle is to expect the unexpected. I've been a DBA for a number of years, and looking back, regardless of the database engine that I was using, space management within the database stung me more times than I care to remember. Now I try (less haphazardly than I did in the past) to plan out the space that a database will use. Still, there are times, like today, when even the best-laid plans don't work out and I end up scrambling to fix things. Therefore, don't feel bad when it happens to you.
Having said that, let's talk about some of the more important aspects of managing space in Oracle7.
Space for user data is allocated in Oracle via the tablespace. Tablespaces are logical objects that point to physical data files located on a database server. Tablespaces can consist of one or many files. Within the tablespace exist the various objects that an Oracle database uses -- mainly tables and indexes.
When an object is created inside a tablespace, it's given a default "extent" size for its initial and next block of space, unless the extent sizes are specified by the object creator. Ideally, a DBA wants each object to occupy no more than one extent, because when Oracle needs data from the object, it's much quicker and efficient for the database to access one extent rather than several extents, some of which may not be adjacent in the tablespace. (Tablespaces reside on the server machine's disk drives.)
If any object hasn't been sized properly and begins to grow into more extents, access time can increase ‹ which is something no one wants. Also, if an object begins to grow dramatically, it may approach or exceed its "maximum extents limit." Once an object hits its maximum extents, it isn't going anyplace else soon, and you will receive a nasty error message. (The most typical is the ORA-1547 "failed to allocate extent" message, which usually means that the tablespace either doesn't have enough total space to hold the object, or it has enough total space but it is just not a big enough contiguous piece of free space to hold the object's initial extent.)
On top of all this, if a tablespace has constant data definition language (DDL) within it, and objects are constantly being added or dropped, it may become fragmented and have numerous pockets of free space that are surrounded by database objects. Obviously, these free space pockets are undesirable in a database. Plus, if object growth gets way out of hand, objects may fill up an entire tablespace, causing all sorts of headaches for a DBA, especially if disk space on the database server is tight.
Now that I've explained some of the terminology and discussed a few things to avoid, let's talk about how you can detect and correct these types of problems.
Initially, DBAs think that in order to avoid space errors, all they have to watch is the amount of free space in their tablespaces Š wrong, wrong, wrong. What really matters is the amount of contiguous space, not just the total amount of free space. What's the difference?
Once upon a time, I wanted to reorganize a table that had grown into many extents on a production server. Unless you have a nice DBA tool that handles reorganizations for you, the best option is to perform an export of the object, specifying compress=y as one of the arguments (so that when an import is performed, all the data will be compressed into one extent). After the export, you drop the object and perform an import to recreate it, and hope for the best. Being the good novice DBA that I was, I first checked how much free space I had in the target tablespace, and found it sufficient. The export went fine; the drop went fine (of course). However, the critical part -- the import -- blew up and gave me that pesky ORA-1547 error. In other words, I was out of luck.
And that concluded my lesson on contiguous free space vs. total free space. Upon further investigation, I found several free space fragments within my target tablespace, none of which was big enough to hold the one large extent for which I was asking. Learn from my experience: Contiguous free space is what matters.
Fine you say, so how can I tell how much contiguous free space I have in my tablespaces? Again, there are a number of good tools on the market that can help you, but the following SQL script can also do the trick. The script will display the name of each tablespace, the total amount of free space available (in megabytes), the number of fragments that make up the tablespace, and, finally, the maximum amount of contiguous free space available (in megabytes):
SELECT SUBSTR(C.NAME,1,20) TABLESPACE, ROUND(SUM((A.LENGTH) * 8192)/1000000,1) FREE_SPACE, COUNT(*) FRAGMENTS, ROUND(MAX((A.LENGTH) * 8192)/1000000,1) LARGEST FROM SYS.FET$ A, SYS.TS$ C WHERE C.TS# = A.TS# GROUP BY SUBSTR(C.NAME,1,20) ORDER BY SUBSTR(C.NAME,1,20);
(To make this script work for your installation, you will need to substitute your database block size for the "8192" shown in lines two and five.)
Ideally, you want this script to produce a listing showing each tablespace that has one piece of free space. The only exceptions should be your temporary tablespace, where (hopefully) all of your sort activity occurs. (For an in-depth discussion of optimizing sort routines, see Guy Harrison's article.) If you determine that more than one free space fragment exists, chances are that objects are being dropped and recreated (thus creating the fragments).
Another fundamental rule about fragmentation: You should ensure that the system tablespace is not used by anyone or anything, because data dictionary analysis can be slowed down quite a bit by fragmentation. Aside from Oracle's data dictionary, no objects should exist within this tablespace.
Typically, two types of fragmentation may exist: bubbles and honeycombs. Bubbles occur when pieces of free space are trapped between two objects. Honeycombs occur when two pieces of free space are next to one another but are not contiguous. This latter type of fragmentation is supposed to be resolved by Oracle's background process, SMON, on a periodic basis. This process coalesces the two or more adjacent pieces of free space into one chunk. (Also, if you are upgrading to Oracle7.3, you should research the new capability to coalesce free extents. Because I am not yet familiar with the technique, you may want to check out the documentation.)
If you find that you have several fragments of free space within some tablespaces, you may want to consider reorganization. Generally, this equates to exporting everything out of the tablespace (specifying compress=y, so objects will be compressed into one extent on import), dropping the tablespace, recreating it, and importing all the objects back in. Needless to say, you should perform several good backups before attempting this reorganization process.
Sizing objects properly should occur during database design. That way, when the DDL is built to generate your objects, the initial extent is large enough to accommodate the incoming data. Not taking the time to size objects properly during design will result in objects that extend into numerous extents, which ultimately degrades performance. This is the place where good planning on a DBA's part pays off later -- size objects correctly from the start to avoid sticky situations later.
Still, no matter how clever you are, you're likely to encounter objects that have grown beyond their initial extent. Your main goals should be detection and correction. But first, you need to find them. The following SQL script should do the trick. The script makes use of a variable that you can use in Oracle's SQL*Plus to input your own extent limit. The output of this script will show you the objects that currently exceed this limit:
SELECT SEGMENT_NAME,SEGMENT_TYPE,SUM(EXTENTS) FROM DBA_SEGMENTS GROUP BY SEGMENT_NAME, SEGMENT_TYPE HAVING SUM(EXTENTS) > &limit;
Once you've identified the objects that have outgrown their initial extent, there are several issues to consider. The obvious issue is performance. Oracle will have a much easier time accessing its data if the information is contained within one extent (vs. numerous non-adjacent extents). The solution, therefore, is to reorganize the object. If your problem object is an index, drop the index and recreate it with a larger initial extent (and possibly the next extent as well). If the object is a table, unless you have a productivity tool that performs reorganizations, export the object (specifying compress=y), drop the table, and reimport it. You may want to create the table ahead of time, specifying larger initial and next extent allocations, and then use the ignore=y parameter of the import utility to have the exported data placed inside the new table structure.
You may encounter another problem when an object cannot extend because of a lack of contiguous free space inside of its tablespace. When an object extends, it takes the next extent's allocation and multiplies it by a parameter called pct_increase, which determines how much larger a new extent will grow, in order to arrive at the new extent size. If Oracle cannot find enough contiguous space to allocate to the new extent, Oracle issues that good old ORA-1547 error message.
To check whether any of your tablespaces has the potential for this situation, use the PL/SQL script shown in Listing 1. It takes in as arguments a tablespace name and the database's block size, and checks for tables that will not be able to allocate their next extent. You should note that this script assumes that a table exists called bad_extents that has two columns: object_name, which has a varchar(30) data type; and next_extent, which has a number data type. After the script completes, you can query the bad_extents table to see if any objects exist that will encounter errors when trying to allocate their next extent. You may also alter the script in Listing 1 to perform indexes as well.
Listing 1 -- -- This script checks for objects inside a tablespace that cannot -- allocate their next extent -- -- -- Variables -- DECLARE V_MAX_SPACE NUMBER(15); V_NEXT_EXTENT NUMBER(15); V_TABLE VARCHAR2(30); -- -- Main script cursor -- CURSOR CANT_ALLOC IS SELECT TABLE_NAME, NEXT_EXTENT * (1 + (PCT_INCREASE/100)) FROM DBA_TABLES WHERE TABLESPACE_NAME = &&TS; ORDER BY TABLE_NAME; BEGIN -- -- Get maximum contiguous free space for tablespace -- SELECT MAX((A.LENGTH) * &db_block_size) INTO V_MAX_SPACE FROM SYS.FET$ A, SYS.TS$ B WHERE B.TS# = A.TS# AND B.NAME = &&TS; -- -- Delete from bad extents table -- DELETE FROM BAD_EXTENTS; -- -- Start checking for extents that will exceed tablespaces ¹ -- maximum contiguous free space -- OPEN CANT_ALLOC; LOOP FETCH CANT_ALLOC INTO V_TABLE, V_NEXT_EXTENT; -- -- If object's next extent is greater than max free space, -- insert into table -- IF V_NEXT_EXTENT > V_MAX_SPACE THEN INSERT INTO BAD_EXTENTS VALUES (V_TABLE, V_NEXT_EXTENT); END IF; EXIT WHEN CANT_ALLOC%NOTFOUND; END LOOP; CLOSE CANT_ALLOC; COMMIT; END; /
A caveat: The data that Listing 1 reports is based on the current size of free extents in the database, and therefore can be misleading. It does not account for the fact that Oracle can coalesce adjacent free extents to make a larger free extent when it cannot find a free extent of the requested size. More important, the report -- and calculation -- may vary depending on your version of Oracle. In version 6, the calculation of the current value of next*pctincrease equals the size of the object's next extent (rounded up to the nearest block size). However, in version 7, Oracle automatically changes the value of next when allocating extents for an object. Therefore, unlike with version 6, the value of next that you see in the data dictionary for an object will actually be the size of the object's next extent. Keep these points in mind when you run this report.
One last note on object extents: You should try your best to keep your objects in one extent, but don't think you have to compress objects that contain only a few extents back into one continually. Instead, use your own judgment as to when objects should be compressed. For example, I have a self-imposed limit of five extents for an object. If anything goes over that, I reorganize it.
There are various things DBAs can check regularly for good space management. The first is a simple check of the total space allocated to each tablespace, followed by the amount free. The following simple script will give you this information:
SELECT TABLESPACE_NAME, SUM(BYTES), 'Total Space' FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME UNION SELECT TABLESPACE_NAME, SUM(BYTES), 'Free Space' FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1, 3 DESC;
If you have fragmentation in your tablespaces or if various objects have exploded into multiple extents, you may want to see a representation of the various extent allocations within the tablespace. By using the following script, you can see the various pockets of free space within a tablespace. Importantly, the script will enable you to see the various objects in your tablespace, including those that make up more than one extent, and their sizes in bytes. Also, the script shows you whether the various object extents are adjacent to one another or are spread throughout the tablespace:
SELECT 'FREE SPACE' OBJECT_OWNER, ' ' OBJECT_NAME, BLOCK_ID, BYTES FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = &&TS;UNION SELECT SUBSTR(OWNER,1,30), SUBSTR(SEGMENT_NAME,1,30), BLOCK_ID, BYTES FROM DBA_EXTENTS WHERE TABLESPACE_NAME = &&TS;ORDER BY 3,1,2;
Good old rollback segments -- what would you do without them? If sized correctly, rollback segments won't cause you many problems. Because I'm only dealing with space issues in this article, I won't discuss how to plan how many rollback segments you'll need; rather, I'll discuss how to detect potential space problems with rollback segments.
In previous versions of Oracle, a single transaction could cause a rollback segment to extend in its tablespace until it literally wiped out all the free space. If there were other rollback segments in the same tablespace, they would then be unable to grow if necessary. With Oracle7, the OPTIMAL clause lets a rollback segment not only grow, but once the additional space is no longer needed, the rollback segment will shrink back to the optimal size.
With the OPTIMAL approach, you solve the problem of a single rollback segment hogging all the space inside the rollback segment tablespace, but you also add the slight overhead of the segment shrinking back down to the OPTIMAL size. The key here is detecting when rollback segments extend and shrink. The following SQL script will help you determine if any of your rollback segments are extending into multiple extents or are extending and then shrinking back to their OPTIMAL size (as an alternative, you can use the Rollback Segment monitor of Server Manager where available to view the same information):
SELECT NAME, OPTSIZE, SHRINKS, EXTENDS FROM V$ROLLNAME A, V$ROLLSTAT B WHERE A.USN = B.USN ORDER BY NAME;
If this script shows rollback segments with numerous shrinks and extends, you may want to consider replacing them with rollback segments that are sized more in tune with the current database activity, or you can adjust the optimal setting of the current rollback segments to get them back in the shape you want. The one question to keep in mind, however, is whether the rollback segments in question were the target of large batch or load jobs. If so, you should create a new, much larger rollback segment to accommodate those types of jobs. To make use of the new, larger rollback segment, the jobs should use the set transaction use rollback segment command.
In order for you to size your rollback segments correctly, it is imperative that you know your data. As a general rule of thumb, if your system handles long queries or long running transactions, the optimal size for the rollback segments should be larger than that of systems with short transactions. An added side benefit to smaller rollback segments is that they can be cached in memory for better performance.
One afternoon, I received a call from a user who was having a terrible time with an application that was using Oracle as the back-end database. The user complained that she couldn't do anything with her application -- add, delete, and so on. Fortunately, the developers had coded error logic into the program that was displayed to the user so she could read back the problem: "ORA-00257 archiver error. Connect internal only, until freed." In other words, the Oracle archive process was "stuck."
With Oracle, you have the recovery option of making copies of the online redo log files before they are written over by new transactions. As each online redo log fills, a log switch occurs and Oracle begins writing to a new log file. When Oracle wraps back around to the first redo log, if archiving is not turned on, Oracle simply writes over the old log information. With archiving turned on, Oracle's archiving process, arch, copies the redo log file to a destination area specified in Oracle's configuration file before any new transactions are logged into the online redo log file. You can then use these archived redo log files in disaster recovery situations to recover to a specific point in time.
Keep in mind that these files must be copied somewhere on the database server, which typically translates to an area on one of the machine's hard disks. You also have the option of writing them to tape. However, when I consider this option, I'm reminded of a Far Side cartoon in which a guy is bending over and looking into a missile silo -- the caption reads, "Never, never do this." The same is true for allowing Oracle's arch process to write archive log files to tape. It's much slower and can cause a backlog of archive log files if activity is heavy enough.
If you're using archiving, you must ensure that the archive file destination is large enough to hold the quantity of archive files that you want to hold on the machine. In the example I previously mentioned, the file system I created to hold the archive files on my Unix box had simply filled up. As soon as I moved a number of the archive logs to a new location, Oracle picked up where it had left off and wrote a new archive file.
If you're running your Oracle database in archive mode, you must manage the space that you've designated to hold the archive log files. It's a good idea to separate out an area on the database server to hold just the archive logs. If you're running Unix, you can do this by creating a separate file system, or, if you're running Windows NT (or other Intel environments), you can create either a directory or a partitioned hard disk area to hold the files. Having a dedicated area on the machine makes backups easier to administer.
Your strategy for how long you should maintain older archive log files depends on the space you have open on the database server itself. Some people will simply back up the archive log area to tape each night, and then delete the files after the backup has run. But this approach makes me nervous for two reasons: First, the backup may fail to run for some reason, but the purge of the archive logs may complete successfully; second, you can always get a bad tape. If either situation occurs, and you must recover using the archive logs, you're dead in the water.
Because I have some room to spare on my database server, I've chosen an alternate solution: Each night a job runs that backs up the entire archive log file system to tape. The backup job is followed by a command that deletes any archive log file that is more than 14 days old. This way, I have some overlap on the archive backups in case a bad tape is encountered. Also, if I have to recover the database, the recovery will proceed faster if all I have to use are the logs that are on the server; reading them back from tape will naturally take longer.
Your purge of the archive log area should also coincide with your full database backup scenario. If you get a good, full database backup, the need for any previous archive log files is almost nil. Of course, with any backup and recovery plan, test like mad to ensure it works and that you can execute it in your sleep -- you may need to!
If time and space permitted, I could continue discussing space management for another few hundred pages. But I hope the guidelines I've provided in this article will help you prevent, diagnose, and correct space problems within your Oracle database.
As for me, I need to get back and rework some of the objects and tablespaces that I initially thought would be sufficient enough to hold my users' data. Unfortunately, they have once again proven me wrong.
Looks like I'm not going to beat the traffic out of town today either...
Robin Schumacher is currently a DBA with Alliant Health Systems in Louisville, Kentucky. His responsibilities include administration of both Oracle7 and Microsoft SQL Server databases, as well as development with PowerBuilder on Windows NT workstations and NT servers. You can reach Robin via email at 102615.621@ compuserve.com.