A detailed introduction to the middle-stage management of Oracle databases

Use Oracle Database 10 Gb to efficiently store and manage segments by recycling wasted space, reorganizing online tables, and evaluating growth trends. Recently, someone asked me to evaluate an RDBMS competing with the Oracle database. During the demonstration by the supplier, the audience thought that the "best" feature was the support for the reorganization of the couplet-this product can redeploy the data block online to make the equivalence of the segment more concise, and does not affect the current user.

At that time, Oracle did not provide this feature in Oracle 9i Database. But now, with the Oracle Database 10 Gb, you can easily recycle wasted space and compress objects online-just suitable for beginners.

However, before testing this feature, let's take a look at the "traditional" approach to this task.

Current practice

Let's take a look at a segment, such as a table, which is filled with blocks, as shown in 1. Some rows are deleted during normal operations, as shown in figure 2. There is a lot of waste of space: (I) there is a lot of space between the last end of the table and the existing blocks, and (ii) inside the block, there are some rows that have not been deleted.

 
Figure 1: blocks allocated to the table. Use a gray square to represent rows

Oracle does not release space for other objects. There is a simple reason: because space is reserved for newly inserted rows, it must adapt to the growth of existing rows. The maximum space occupied is the maximum usage mark (HWM), as shown in Figure 2:

 
Figure 2: The block behind the row has been deleted; HWM remains unchanged

However, this method has two main problems:

When a user initiates a full table scan, Oracle must always scan from the segment to HWM, even if nothing is found. This task extended the full table scan time.

When a row is inserted using a direct path-for example, the APPEND command is used to directly load and insert the row) or the SQL * Loader command is used to directly store the data block on the HWM. The space below it is wasted.

In Oracle9i and earlier versions, you can delete the table, recreate the table, and reload the data to recycle space; you can also use the alter table move command to MOVE the TABLE to a different tablespace to recycle space. Both methods must be performed offline. In addition, you can use the online table reorganization feature, but this requires at least twice the existing tablespace.

In 10 Gb, the task has become insignificant. If your tablespace supports automatic segment space management (ASSM), you can now narrow down the segments, tables, and indexes, to recycle idle blocks and provide them to the database for other purposes, let's take a look at the reason.

10g segment Management

Suppose there is a BOOKINGS table that stores online registration via the Web site. After a registration is confirmed, it is stored in an archive table BOOKINGS_HIST and deleted from the BOOKINGS table. The time interval between registration and confirmation varies greatly depending on the customer. Many Rows are inserted to the table's HWM because they cannot get enough space from the deleted rows.

Now you need to recycle the wasted space. First, accurately find out how much space is wasted in recyclable segments. Because it is in a tablespace that supports ASSM, you will have to use the SPACE_USAGE process of the DBMS_SPACE package, as shown below:

declarel_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;begindbms_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_fs1_bytes);dbms_output.put_line(' FS3 Blocks = '    ||l_fs3_blocks||' Bytes = '||l_fs1_bytes);dbms_output.put_line(' FS4 Blocks = '    ||l_fs4_blocks||' Bytes = '||l_fs1_bytes);dbms_output.put_line('Full Blocks = '    ||l_full_blocks||' Bytes = ||l_full_bytes);end;/

The output result is as follows:

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

The output shows that there are 4,148 blocks with 75-100% free space (FS4); no other idle blocks are available. Here, there are only two fully used blocks. 4,148 blocks can be recycled.

Next, make sure that the table supports row movement. If not, you can use the following command to support it:

Alter table bookings enable row movement;

Or use the Enterprise Manager 10 GB on the Administration page. You must also disable all row id-based triggers on the table because the row will move and the row id may change.

Finally, you can use the following command to reorganize the existing rows in the table:

Alter table bookings shrink space compact;

This command will re-allocate rows in the block, as shown in 3. This will generate more idle blocks under HWM, but HWM itself will not be allocated.

 
Figure 3: blocks in the table after reorganizing rows

After performing this operation, let's take a look at the changes in space utilization. The PL/SQL block displayed in step 1 shows how the block is organized:

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

Note the important change here: the number of FS4 blocks with 75-100% free space) is now reduced from 4,148 to 0. We also see that the number of FS3 blocks with 50-75% free space increases from 0 to 1. However, because HWM is not reset, the total space utilization is still the same. Run the following command to check the space used:

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

The number of blocks occupied by the table (4,224) is still the same, because HWM is not removed from its original position. You can move HWM to a lower position and use the following command to recycle the space:

Alter table bookings shrink space;

Note that the COMPACT Clause does not appear. This operation will return unused blocks to the database and reset the HWM. You can check the space allocated to the table to test it:

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

The number of blocks is reduced from 4,224 to 8. All unused spaces in the table are returned to the tablespace for use by other segments, as shown in figure 4.

 
Figure 4: Return idle blocks to the database after contraction

This contraction operation occurs in the online status and does not affect the user.

You can also use a statement to compress the table index:

Alter table bookings shrink space cascade;

The online shrink command is a powerful feature used to recycle wasted space and reset HWM. I reset the latter HWM) as the most useful result of this command, because it improves the performance of full table scan.

Find the appropriate choice for shrinking

Before online contraction, you may want to determine the segment that can be fully compressed to find the maximum return. Simply use the built-in function verify_shrink_candidate in the dbms_space package. If the segment can be reduced to 1,300,000 bytes, you can use the following PL/SQL code for testing:

beginif (dbms_space.verify_shrink_candidate('ARUP','BOOKINGS','TABLE',1300000)then:x := 'T';else:x := 'F';end if;end;/

The PL/SQL process is successfully completed.

SQL> print xX ------------------------------ T if the target is scaled down, use a smaller number, such as 3,000: beginif (values ('arup', 'bookings ', 'table', 30000) then: x: = 'T'; else: x: = 'F'; end if; end;

The value of variable x is set to 'F', which means the table cannot be reduced to 3,000 bytes. Now we assume that you are starting to create an index on a table or a group of tables. Except for common structure elements, such as columns and single-value features, the most important thing you will have to consider is the expected size of the index-make sure that the tablespace has enough space to store new indexes.

In Oracle Database 9i and earlier versions, many DBAs use a large number of tools from workbooks to independent programs) to estimate the size of future indexes. In 10 Gb, this task becomes extremely trivial by using the DBMS_SPACE package.

1. Oracle 11g real-time application test case
2. Update Data in the connection view of Oracle
3. Build. NET applications based on Oracle