Manage storage in segments efficiently with Oracle Database 10gby reclaiming wasted space, reorganizing tables online, and estimating growth trends
Recently, I was asked to evaluate an RDBMS that competes with Oracle Database. During the vendor's presentation, the feature that registered the biggest "wow" factor in the audience was its support for online reorganizationsthe product can relocate data blocks to make the equivalent of segments more compact online, without affecting current users.At that time, Oracle did not offer such a capability in Oracle9i Database. But now, with Oracle Database 10g, you can easily reclaim wasted space and compact objects onlinejust for starters. Before examining the feature, however, let's take a look at the "traditional" approach to this task. Current Practices Consider a segment, such as a table, where the blocks are filled up as shown in Figure 1. During normal operation, some rows are deleted, as shown in Figure 2. Now we have a lot of wasted space: (i) between the previous end of the table and the existing block and (ii) inside the blocks where some of the rows have not been deleted.
declare l_fs1_bytes number; l_fs2_bytes number; l_fs3_bytes number; l_fs4_bytes number; l_fs1_blocks number; l_fs2_blocks number; l_fs3_blocks number; l_fs4_blocks number; l_full_bytes number; l_full_blocks number; l_unformatted_bytes number; l_unformatted_blocks number; begin dbms_space.space_usage( segment_owner => user, segment_name => 'BOOKINGS', segment_type => 'TABLE', fs1_bytes => l_fs1_bytes, fs1_blocks => l_fs1_blocks, fs2_bytes => l_fs2_bytes, fs2_blocks => l_fs2_blocks, fs3_bytes => l_fs3_bytes, fs3_blocks => l_fs3_blocks, fs4_bytes => l_fs4_bytes, fs4_blocks => l_fs4_blocks, full_bytes => l_full_bytes, full_blocks => l_full_blocks, unformatted_blocks => l_unformatted_blocks, unformatted_bytes => l_unformatted_bytes ); dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes); dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes); dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes); dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes); dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes); end; / The output is: FS1 Blocks = 0 Bytes = 0 FS2 Blocks = 0 Bytes = 0 FS3 Blocks = 0 Bytes = 0 FS4 Blocks = 4148 Bytes = 0 Full Blocks = 2 Bytes = 16384The output shows that there are 4,148 blocks with 75-100% free space (FS4); no other free blocks are available. There are only 2 full blocks. The 4,148 blocks can be recovered. Next, you must ensure that the table is row-movement enabled. If it's not, you can enable it with:
alter table bookings enable row movement;or via Enterprise Manager 10g, on the Administration page. You should also ensure that all rowid-based triggers are disabled on this table because the rows are moved and the rowids could change. Finally, you can reorganize the existing rows of the table with:
alter table bookings shrink space compact;This command re-distributes the rows inside the blocks as shown in Figure 3, resulting in more free blocks under the HWMbut the HWM itself is not disturbed.
FS1 Blocks = 0 Bytes = 0 FS2 Blocks = 0 Bytes = 0 FS3 Blocks = 1 Bytes = 0 FS4 Blocks = 0 Bytes = 0 Full Blocks = 2 Bytes = 16384Note the important change here: the number of FS4 blocks (with 75-100% free space) is now 0, down from 4,148. We also see an increase in FS3 blocks (50-75% free space) from 0 to 1. However, because the HWM has not been reset, the total space utilization remains the same. We can check the space used with:
SQL> select blocks from user_segments where segment_name = 'BOOKINGS'; BLOCKS --------- 4224The number of blocks occupied by the table4,224remains the same because the HWM has not moved from its original position. You can move the HWM to a lower position and reclaim the space with
alter table bookings shrink space;
Note that the clause COMPACT is not present. This operation will return the unused blocks to the database and reset the HWM. You can test it by checking the space allocated to the table:
SQL> select blocks from user_segments where segment_name = 'BOOKINGS'; BLOCKS ---------- 8The number of blocks is down from 4,224 to 8; all the unused space inside the table was returned to the tablespace for use in other segments, as shown in Figure 4.
alter table bookings shrink space cascade;The online shrink command is a powerful feature for reclaiming wasted space and resetting the HWM. I consider the latterresetting of the HWMthe most useful result of this command because it improves the performance of full table scans. Finding Candidates for Shrinking Before performing an online shrink, you may want to find out the biggest bang-for-the-buck by identifying the segments that can be most fully compressed. Simply use the built-in function verify_shrink_candidate in the package dbms_space. Execute this PL/SQL code to test if the segment can be shrunk to 1,300,000 bytes:
begin if (dbms_space.verify_shrink_candidate ('ARUP','BOOKINGS','TABLE',1300000) ) then :x := 'T'; else :x := 'F'; end if; end; / PL/SQL procedure successfully completed. SQL> print x X -------------------------------- TIf you use a low number for the target shrinkage, say 3,000:
begin if (dbms_space.verify_shrink_candidate ('ARUP','BOOKINGS','TABLE',30000) ) then :x := 'T'; else :x := 'F'; end if; end;the value of the variable x is set to 'F', meaning the table cannot be shrunk to 3,000 bytes. Taking the Guesswork Out of Index Space Requirements Now let's say you are about to embark on the task of creating an index on a table, or perhaps a set of tables. Besides the usual structural elements such as columns and uniqueness, the most important thing you have to consider is the expected size of the indexyou must ensure that the tablespace has enough space to hold the new index. With Oracle9i Database and below, many DBAs use tools ranging from spreadsheets to standalone programs to estimate the size of the future index. In 10g, this task has become extremely trivial through the use of the DBMS_SPACE package. Let's see it in action. We are asked to create an index on the columns booking_id and cust_name of the table BOOKINGS. How much space does the proposed index need? All you do is execute the following PL/SQL script.
declare l_used_bytes number; l_alloc_bytes number; begin dbms_space.create_index_cost ( ddl => 'create index in_bookings_hist_01 on bookings_hist '|| '(booking_id, cust_name) tablespace users', used_bytes => l_used_bytes, alloc_bytes => l_alloc_bytes ); dbms_output.put_line ('Used Bytes = '||l_used_bytes); dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes); end; /The output is:
Used Bytes = 7501128 Allocated Bytes = 12582912Suppose you want to use some parameters that will potentially increase the size of the indexfor example, specifying an INITRANS parameter of 10.
declare l_used_bytes number; l_alloc_bytes number; begin dbms_space.create_index_cost ( ddl => 'create index in_bookings_hist_01 on bookings_hist '|| '(booking_id, cust_name) tablespace users initrans 10', used_bytes => l_used_bytes, alloc_bytes => l_alloc_bytes ); dbms_output.put_line ('Used Bytes = '||l_used_bytes); dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes); end; /The output is:
Used Bytes = 7501128 Allocated Bytes = 13631488Note the increase in the allocated bytes from specifying a higher INITRANS. Using this approach you can easily determine the impact of the index on storage space. You should be aware of two important caveats, however. First, this process applies only to tablespaces with SEGMENT SPACE MANAGEMENT AUTO turned on. Second, the package calculates the estimated size of the index from the statistics on the table. Hence it's very important to have relatively fresh statistics on the tables. But beware: the absence of statistics on the table will not result in an error in the use of the package, but will yield a wrong result. Estimating Table Size Suppose there is a table named BOOKINGS_HIST, which has the average row length of 30,000 rows and the PCTFREE parameter of 20. What if you wanted to increase the parameter PCT_FREE to 3by what amount will the table increase in size? Because 30 is a 10% increase over 20, will the size go up by 10%? Instead of asking your psychic, ask the procedure CREATE_TABLE_COST inside the package DBMS_SPACE. Here is how you can estimate the size:
declare l_used_bytes number; l_alloc_bytes number; begin dbms_space.create_table_cost ( tablespace_name => 'USERS', avg_row_size => 30, row_count => 30000, pct_free => 20, used_bytes => l_used_bytes, alloc_bytes => l_alloc_bytes ); dbms_output.put_line('Used: '||l_used_bytes); dbms_output.put_line('Allocated: '||l_alloc_bytes); end; /The output is:
Used: 1261568 Allocated: 2097152Changing the table's PCT_FREE parameter to 30 from 20, by specifying
pct_free => 30we get the output:
Used: 1441792 Allocated: 2097152Note how the used space has increased from 1,261,568 to 1,441,792 because the PCT_FREE parameter conserves less room in the data block for user data. The increase is about 14%, not 10%, as expected. Using this package you can easily calculate the impact of parameters such as PCT_FREE on the size of the table, or of moving the table to a different tablespace. Predicting the Growth of a Segment It's holiday weekend and Acme Hotels is expecting a surge in demand. As a DBA, you are trying to understand the demand so that you can ensure there is enough space available. How do you predict the space utilization of the table? Just ask 10g; you will be surprised how accurately and intelligently it can make that prediction for you. You simply issue this query:
select * from table(dbms_space.OBJECT_GROWTH_TREND ('ARUP','BOOKINGS','TABLE'));The function dbms_space.object_growth_trend() returns record in PIPELINEd format, which can be displayed by the TABLE() casting. Here is the output:
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY ------------------------------ ----------- ----------- ------------ 05-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED 06-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED 07-MAR-04 08.51.24.421081 PM 8586959 39124992 INTERPOLATED 08-MAR-04 08.51.24.421081 PM 126190859 1033483971 INTERPOLATED 09-MAR-04 08.51.24.421081 PM 4517094 4587520 GOOD 10-MAR-04 08.51.24.421081 PM 127469413 1044292813 PROJECTED 11-MAR-04 08.51.24.421081 PM 128108689 1049697234 PROJECTED 12-MAR-04 08.51.24.421081 PM 128747966 1055101654 PROJECTED 13-MAR-04 08.51.24.421081 PM 129387243 1060506075 PROJECTED 14-MAR-04 08.51.24.421081 PM 130026520 1065910496 PROJECTEDThe output clearly shows the size of the table BOOKINGS at various times as shown in the column TIMEPOINT, in the TIMESTAMP datatype. The SPACE_ALLOC column shows the bytes allocated to the table and the SPACE_USAGE column shows how many of those bytes have been used. This information is collected by the Automatic Workload Repository, or AWR (see Week 6 of this series), every day. In the above output, the data was collected well on March 9, 2004, as indicated by the value of the column QUALITY - "GOOD." The space allocated and usage figures are accurate for that day. However, for all subsequent days, the value of this column is PROJECTED, indicating that the space calculations are projected from the data collected by the AWR facilitynot collected directly from the segment. Note the values in this column prior to March 9they are all INTERPOLATED. In other words, the value was not really collected or projected, but simply interpolated from the usage pattern for whatever data is available. Most likely the data was not available at that time and hence the values had to be interpolated. Conclusion With the availability of segment level manipulations you now have fine-grained control over how space is used inside a segment, which can be exploited to reclaim free space inside a table, reorganize the table rows to make it more compact online, and much more. These facilities help DBAs free themselves from the routine and mundane tasks like table reorganization. The online segment shrink feature is especially helpful in eliminating internal fragmentation and lowering the high water mark of the segment, which can significantly reduce the cost of a full table scan. For more information about the SHRINK operation, see this section of the Oracle Database SQL Reference . Learn more about the DBMS_SPACE package in Chapter 88 of the PL/SQL Packages and Types Reference . For a comprehensive review of all new space management features in Oracle Database 10g, read the Technical Whitepaper The Self-Managing Database: Proactive Space & Schema Object Management . Finally, a demo of Oracle Database 10g space management is available online.
Next Week: Transportable Tablespaces