Week 15
Segment Management

Manage storage in segments efficiently with Oracle Database 10g—by 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 reorganizations—the 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 online—just 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.

Oracle does not release that space for use by other objects for a simple reason: because that space is reserved for new inserts and to accommodate the growth of existing rows. The highest space occupied is known as a High Water Mark (HWM), as shown in Figure 2.

There are two main problems with this approach, however:

• When a user issues a full table scan, Oracle must scan the segment all the way up to the HWM, even though it does not find anything. This task extends full table scan time.
• When rows are inserted with direct path—for example, through Direct Load Insert (insert with the APPEND hint) or through the SQL*Loader direct path—the data blocks are placed directly above the HWM. The space below it remains wasted.

In 10g, this task has become trivial; you can now shrink segments, tables, and indexes to reclaim free blocks and give them to the database for other uses, provided that Automatic Segment Space Management (ASSM) is enabled in your tablespace. Let's see how.

Segment Management the 10g Way

Suppose you have a table BOOKINGS, which holds online bookings from the website. After the booking is confirmed, it's stored in an archival table BOOKINGS_HIST and the row is deleted from BOOKINGS. The time between booking and confirmation varies widely among customers, so a lot of rows are inserted above the HWM of the table because sufficient space is not available from the deleted rows.

Now you need to reclaim wasted space. First, find out exactly how much space is wasted in that segment that can be reclaimed. Because this is in an ASSM-enabled tablespace, you have to use the procedure SPACE_USAGE of the package DBMS_SPACE, as shown below.

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 = 16384
                            

The 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 HWM—but the HWM itself is not disturbed.

After the operation, let's see the change in space utilization. Using the PL/SQL block shown in the first step, you can see how the blocks are organized now:

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
                               
FS3 Blocks = 1 Bytes = 0
FS4 Blocks = 0 Bytes = 0
Full Blocks = 2 Bytes = 16384
                            

Note 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
---------
     4224

The number of blocks occupied by the table—4,224—remains 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;

                            

SQL> select blocks from user_segments where segment_name = 'BOOKINGS';

    BLOCKS
----------
         8

The 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.

This shrink operation occurs completely online and does not affect users.

You can also compact the indexes of the table in one statement:

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 latter—resetting of the HWM—the 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
--------------------------------
T

If 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 index—you 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 = 12582912

Suppose you want to use some parameters that will potentially increase the size of the index—for 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 =  
                              
13631488
                            

Note 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 3—by 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: 2097152

Changing the table's PCT_FREE parameter to 30 from 20, by specifying

pct_free => 30

we get the output:

Used: 1441792
Allocated: 2097152

Note 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 PROJECTED

The 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 facility—not collected directly from the segment.

Note the values in this column prior to March 9—they 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

Back to Series Index