10.4 Managing Extent Usage

An extent in Oracle parlance is the size of the chunks of storage that are allocated to a table or index upon creation or when that table or index needs to be extended to accommodate more data. Extent management was always a problem in older versions of Oracle because it could never be precisely controlled.

With the advent of locally managed tablespaces (LMTs) in Oracle8i, Oracle has greatly simplified space management. LMTs allow DBAs to control the extent sizes allocated for tablespace objects, regardless of their STORAGE specifications. This eliminates the fragmentation that can occur in dictionary-managed tablespaces when objects are created with different extent sizes. Such fragmentation is impossible when LMTs are used. With LMTs, the extent size can be controlled so that all tablespace extents are the same size. In this section, we'll look at the benefits of LMTs and then see how the toolkit script maxext.pl can make this feature even more effective.

10.4.1 Locally Managed Tablespaces (LMTs)

If CREATE TABLE statements request an extent greater than the tablespace's uniform extent size, they receive multiple smaller extents, satisfying the total storage amount requested. This is illustrated in Example 10-16. There, a tablespace is created with locally managed extents of 128K. Even though the requested extent size for EMPTEST is 512K, the space is allocated in 128K chunks. USER_EXTENTS shows four allocated chunks of 128K each.

Example 10-16. Extent allocation in a locally managed tablespace

CREATE TABLESPACE USERS DATAFILE '/u01/oradata/ts01/users.dbf' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
  
CREATE TABLE EMPTEST ( FNAME VARCHAR2(20), LNAME VARCHAR2(20) )
TABLESPACE USERS STORAGE( INITIAL 512K NEXT 512K );
  
BREAK ON TABLESPACE_NAME SKIP 1 ON SEGMENT_TYPE SKIP 1 ON SEGMENT_NAME SKIP 1 ON REPORT
COMPUTE SUM OF BYTES ON REPORT
  
SELECT TABLESPACE_NAME, SEGMENT_TYPE, SEGMENT_NAME,
       DECODE(EXTENT_ID,0,'0',TO_CHAR(EXTENT_ID)) EXTENT_ID, BYTES
  FROM USER_EXTENTS
 WHERE SEGMENT_NAME = 'EMPTEST'
 ORDER BY TABLESPACE_NAME, SEGMENT_TYPE, 
          SEGMENT_NAME, TO_NUMBER(EXTENT_ID);
  
TABLESPACE TYPE   NAME       ID        BYTES
---------- ------ ---------- -- ------------
USERS      TABLE  EMPTEST    0       131,072
                             1       131,072
                             2       131,072
                             3       131,072
********** ****** **********    ------------
sum                                  524,288
4 rows selected.

Even though fragmentation is eliminated, storage still needs monitoring. In Example 10-16 a table is created, with four empty extents. If no more tablespace extents are available, does this require an increase in tablespace size?

Increasing tablespace size may be unnecessary. Even if your table sits within a full tablespace, the table itself contains no data. If you could determine whether all tablespace objects have a sufficient number of unused blocks to satisfy application data needs for six months, for example, there would be no immediate need to increase tablespace size. To figure this out, we will need to check the individual objects to determine if their free space is sufficient. The statistics of interest in determining if an object will soon need more space are the following:

EXTENTS

Total number of extents allocated for an object.

FREE_BLOCKS

Number of blocks on the freelist. Either these blocks are filled below the PCTFREE threshold, or the space used has fallen below the PCTUSED threshold after having been above PCTFREE at some point. Free blocks also include UNUSED_BLOCKS, discussed next.

UNUSED_BLOCKS

Number of blocks allocated to an object which have never contained any data. All unused blocks are also FREE_BLOCKS.

MAX_EXTENTS

Maximum number of extents an object may be allocated.

MAX_BYTES_FREE

Largest chunk of free space in the object's allocation.

NEXT_EXTENT

Size of the next extent for the object.

TOTAL_BLOCKS

Total number of database blocks consumed by an object.

10.4.2 Examining Object Space with maxext.pl

The maxext.pl script in our toolkit determines if an object may be running out of space. It follows the steps illustrated in Figure 10-3 and listed here:

• It checks to see if there are any more segments in DBA_SEGMENTS.

• If so, it checks to see if the current segment is nearing its maximum number of extents or will be unable to extend .

• If either of the previous conditions is true, it checks the number of UNUSED_BLOCKS with DBMS_SPACE.UNUSED_SPACE.

• It sends a warning to the DBA if UNUSED_BLOCKS is below the threshold.

Figure 10-3. Flowchart of maxext.pl operations

In maxext.pl we rely upon UNUSED_BLOCKS, rather than FREE_BLOCKS, because the amount of space available in a used freelist block is unknown. It can be calculated, but we prefer to rely on the ratio of UNUSED_BLOCKS / TOTAL_BLOCKS to determine if a tablespace or tablespace object needs space attention. Before running maxext.pl, make sure that you have a copy of the maxext.conf file stored in PDBA_HOME, as for the other configuration files described earlier in this chapter. There are only a few parameters in this configuration file that you will need to edit, shown in the following list. The entire file maxext.conf is reproduced in Example 10-17.

Example 10-17. maxext.conf

package maxext;
use vars qw{ %config @emailAddresses };
  
%config = ( minExtentsCanExtend => 3, minPctBlocksUnused => 10, );
@emailAddresses = ( '[email protected]', '[email protected]', );

minExtentsCanExtend

Set this to the minimum number of extents by which an object should be able to extend. In the preceding configuration file, this is set to 3, and a table has 98 extents allocated and a MAXEXTENTS value of 100; in this case, a value of 3 will cause a check to be made with DBMS_SPACE. This is because it is only possible for two more extents to be allocated to the table.

minPctBlocksUnused

Percentage of unused blocks that an object should have before a warning is sent to the DBA. If an index has 100 total blocks, and 11 of those are unused blocks, no warning will be issued because 11% of unused blocks is greater than the 10% minimal threshold set.

@emailAddresses

Array of email addresses to which reports should be mailed.

There are only a few command options for maxext.pl, summarized in Table 10-7.

Table 10-7. Command-line options — maxext.pl

Option	Description
-machine	Server where the target database resides.
-database	Target database.
-username	DBA account.
-password	DBA password (optional if password server in use).
-email	Send email to DBAs if a report is generated.
-silent	Only send email, and process without printing output.

The results of running maxext.pl can be seen in Example 10-18. We forced these tables to appear in the report by setting the minPctBlocksUnused parameter to 100 in the maxext.conf file.

Example 10-18. Results from maxext.pl

%oramon > maxext.pl -machine sherlock -database ts01 -username \
  system -email
                                                         NUMBER
                                                         EXTENTS    NEXT
OWNER     NAME         TYPE     AVAILABLE  EXTENT SIZE MAX BYTES FREE
========= ============ ======== ========== =========== ===============
  
JKSTILL   BIG_TABLE    TABLE         UNLIM       65536              0
JKSTILL   FILL_ER_UP   TABLE         UNLIM       65536              0
  
%oramon >