Using the Correct Extent Size and Eliminating Fragmentation

It is recommended that you regularly monitor your database to find segments that are growing to extreme numbers of extents (over a thousand) and then manage those segments appropriately.

select segment_name, segment_type, extents, bytes
from dba_segments a, dba_tablespaces b
where a.extents > 1000;

Query DBA_SEGMENTS on a regular basis to ensure that objects are not building up too many extents (when not using ASM). Catching problems early is the key to avoiding performance issues later. Correctly locating objects in tablespaces with uniform. extent sizes that are appropriate based on the growth of the object is the key.

When data is read from a table, it is either accessed by a ROWID operation via an index or by a full table scan (except for index-organized tables). In most cases, access by ROWID is the preferred method. This is because the ROWID method allows the database to determine the exact block that a record resides in and therefore bypasses any extent allocation information in the segment. The short answer is that ROWID operations do not care how many extents are in the segment. Database block sizes generally range from 4K to 32K. So, regardless of the number of extents in a segment, a full table scan will always perform. the same number of reads as long as the extent size is a multiple of the database block size. So do we still need to worry about extent counts if we are using extents that are multiples of the block size? Yes we do, but we aren’t as driven by it as we used to be. Think of it this way, the more extents you have, the more you have to manage, even if it is managed via faster methods. Therefore, my rule of thumb is, if you have a segment growing over 4096 extents (assuming you are using locally managed tablespaces), consider moving it to a tablespace where the extent size is more appropriate for the size of the segment. If you have a 15GB table, using a 200M extent size is probably more efficient than using a 1M extent size. For the purposes of loading data alone, you will save back-end processing time because the database does not have to allocate as many extents during the load process.

So If a table is getting very large, you can reduce the number of extents by creating a new tablespace and moving the data to the new location. In our example, the customer table is fragmented into 100 extents of 1M each, which can be found by querying the DBA_EXTENTS view:

select segment_name,bytes
from dba_extents
where segment_name='CUSTOMER';

SEGMENT_NAME BYTES
CUSTOMER 1048576
CUSTOMER 1048576
CUSTOMER 1048576
CUSTOMER 1048576
..etc.

First, create a tablespace and then create a new customer table called customer1 into the new_10M_dat tablespace (this tablespace has 10M extents that will better accommodate its growth):
create table customer1
tablespace new_10M_dat
as select * from customer;

After ensuring that the customer1 table was created, then drop the original table and all of its
indexes:

drop table customer;

You can now rename your new table and build its corresponding indexes (rebuilding indexes
can be time consuming depending on the size of the table):
rename customer1 to customer;

alter index ... rebuild online;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12361284/viewspace-594414/，如需轉載，請註明出處，否則將追究法律責任。