mysql表碎片空间计算_请问表空间碎片率是怎么算出来的?

给你参考一下：

---------------------------------------------------------------------------------------------------------------------------------------------------

Space management is very important to maximizing usage of your database. Over

time your database can become fragmented. This will affect the performance,

and resources of your database. Here are some symptoms that may indicate

fragmentation in your database:

* If you are receiving any ORA errors regarding allocation of

extents or space(i.e. ora-1547, ora-1562, etc).

* If you look in dba_free_space and you see that there is a lot of free

space left in that tablespace but you are receiving space errors.

What is fragmentation?

When a tablespace is fragmented, there may be a lot of free space in the

tablespace, but they are in such small pieces that Oracle cannot use them.

When an object needs a next extent, Oracle has to allocate one contiguous

extent. If you do not have one chunk of free space that is large enough for

the extent, Oracle will return an error. Your tablespace may have smaller

pieces of free space that may add up to the size of the next extent, but

Oracle cannot divide the next extent of the object into smaller pieces to fit

into the free space.

There are two types of fragmentation. The tablespace may have two pieces of

free space but in between the two, there is a permanent object. There is no

way to coalesce those two free extents. Another type of fragmentation occurs

when the tablespace has two pieces of free space that are adjacent to each

other, but they are separate. With this type of fragmentation, Oracle's SMON

will coalesce the two extents into one large extent of free space. This

automatic coalescing is new in Oracle7 and on.

SCRIPT TO DETECT FRAGMENTATION

Solution Description:

=====================

The following is a script that will determine how many extents of contiguous

free space you have in Oracle as well as the total amount of free space you

have in each tablespace. From these results you can detect how fragmented your

tablespace is. Run this as connect internal.

create table SPACE_TEMP (

TABLESPACE_NAME CHAR(30),

CONTIGUOUS_BYTES NUMBER)

/

declare

cursor query is select *

from dba_free_space

order by tablespace_name, block_id;

this_row query%rowtype;

previous_row query%rowtype;

total number;

begin

open query;

fetch query into this_row;

previous_row := this_row;

total := previous_row.bytes;

loop

fetch query into this_row;

exit when query%notfound;

if this_row.block_id = previous_row.block_id + previous_row.blocks then

total := total + this_row.bytes;

insert into SPACE_TEMP (tablespace_name)

values (previous_row.tablespace_name);

else

insert into SPACE_TEMP values (previous_row.tablespace_name,

total);

total := this_row.bytes;

end if;

previous_row := this_row;

end loop;

insert into SPACE_TEMP values (previous_row.tablespace_name,

total);

end;

.

/

set pagesize 60

set newpage 0

set echo off

ttitle center 'Contiguous Extents Report' -

skip 1 center new_today skip 3

break on "TABLESPACE NAME" skip page duplicate

spool contig_free_space.lis

rem

column "CONTIGUOUS BYTES" format 999,999,999

column "COUNT" format 999

column "TOTAL BYTES" format 999,999,999

column "TODAY" noprint new_value new_today format a1

rem

select TABLESPACE_NAME "TABLESPACE NAME",

CONTIGUOUS_BYTES "CONTIGUOUS BYTES"

from SPACE_TEMP

where CONTIGUOUS_BYTES is not null

order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;

select tablespace_name, count(*) "# OF EXTENTS",

sum(contiguous_bytes) "TOTAL BYTES"

from space_temp

group by tablespace_name;

spool off

drop table SPACE_TEMP

/

exit

/

Solution Explanation:

=====================

Hints:

The ideal situation is to have one large free extent in your tablespace. The

more extents of free space there are in the tablespace, the more likely you

will run into fragmentation problems. The size of the free extents is also

very important. If you have a lot of small extents (too small for any next

extent size) but the total bytes of free space is large, then you may want to

consider defragmentation options.

COMMON CAUSES OF FRAGMENTATION

Solution Description:

=====================

Transference of extents from an object to the free list will cause a

tablespace to become fragmented. The more often this movement of space occurs,

the more fragmented your tablespace will become. Here are some ways Oracle can

become fragmented:

SYSTEM TABLESPACE

Your system tablespace should only contain data dictionary information

and your system rollback segment. Anything else in your system tablespace can

potentially cause fragmentation. One of the most common ways this tablespace

can become fragmented is if your users default and temporary tablespace point

to this tablespace. You can check this by looking in dba_users. Users should

have a separate data and temp tablespace from system because creating and

dropping objects, and temporary segments will fragment the tablespace.

DATA and INDEX TABLESPACE

For these tablespaces, the concern for fragmentation is not at the

tablespace level, but rather, the object level. If you drop and recreate many

tables or indexes, this will cause fragmentation at the tablespace level.

Objects that stay in this tablespace generally do not cause tablespace

fragmentation. But tables and indexes can get fragmented. One of the most

common ways to fragment a table or index is if you are doing large deletes and

then inserts frequently. When you delete a lot of rows and then immediately

insert rows, this will cause the table to grow. The reason you see this effect

is because of delayed block cleanout. This will cause fragmentation in the

table.

When you do any dml on a table, Oracle issues a fast commit, which

means that those blocks have been marked for change, but the change will not

actually be implemented until the block is touched again(i.e. doing a select

statement after the delete will force Oracle to go and touch the block and

actually delete the rows and then free up the space). This is delayed block

cleanout.

Indexes will also become fragmented with inserts and deletes because

of the way indexes are implemented. When a row is deleted, Oracle will not

reuse the index space. Pctused for indexes is always 0, which means the index

blocks will not be put on the free list for reuse. Therefore, indexes are

always growing and can become very fragmented. This is the tradeoff for more

efficient index performance.

TEMP TABLESPACE

This tablespace by nature gets fragmented. Temporary segments are constantly

being created and dropped for sorting. There should not be any permanent

objects in this tablespace, as that would prevent Oracle from coalescing all

the free extents together. Another way this tablespace can become more

fragmented is if initial and next extent are different and the pctincrease is

set. Setting the initial and next extents to the same size and pctincrease to

0 will ensure that Oracle will grab the same size extent every time. This will

reduce uneven sized free extents.

HOW DO YOU RESOLVE FRAGMENTATION?

The only way to get rid of fragmentation is drop and recreate the object. In

most cases doing an export and import will solve the problem. If you need to

defragment your system tablespace, you must rebuild the whole database as

Oracle will not let you drop the system tablespace.

Please refer to Bulletin:Recreating database objects for more details