oracle 11g 本地表空间管理碎片问题

cnaning

这周发现数据库性能下降一倍 ，特别是io等待，对比awr 各项指标都正常，只有表空间的io等待加大一倍，所以考虑是表空间碎片导致，
查询表空间碎片脚本如下：
SQL> select  tablespace_name,
  2      sqrt(max(blocks)/sum(blocks))*(100/SQRT(SQRT(COUNT(BLOCKS)))) fsfi
  3        from dba_free_space
group by tablespace_name  order by 2;  4  
TABLESPACE_NAME            FSFI
-------------------- ----------
IDX_TLOTINFO         15.9037582
UNDOTBS1             28.9169984
IDX_TLOTGXINFO       38.4743552
T_GX_BATCHINF        39.0358087
T_LOT_BATCHINF        40.468023
T_GX_BETINF          40.7802583
T_GX_TICKINF         41.1676583
T_LOT_TICKINF        41.4099921
T_GX_SALE            43.4335474
USERS                45.0817052
SYSAUX               50.3438367


ORACLE的自由空间碎片索引（FSFI）
一、        计算自由空间碎片索引的公式：
   
FSFI = 100 X SQRT（最大盘区/盘区总空间）/ （SQRT（SQRT（盘区数）））

请刘大指点

Maclean Liu(刘相兵

运行以下脚本 并贴出输出


select * from v$version;

select count(*)  from dba_recyclebin;

set pages 999
col tsname  format     a16 justify c heading 'Tablespace'
col nfrags  format 999,990 justify c heading 'Free|Frags'
col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'
col totsiz  format 999,999 justify c heading 'Total|(MB)'
col avasiz  format 999,999 justify c heading 'Available|(MB)'
col pctusd  format     990 justify c heading 'Pct|Used'

select total.TABLESPACE_NAME tsname,
       D nfrags,
       C/1024/1024 mxfrag,
       A/1024/1024 totsiz,
       B/1024/1024 avasiz,
       (1-nvl(B,0)/A)*100 pctusd
from
    (select sum(bytes) A,
            tablespace_name
            from dba_data_files
            group by tablespace_name) TOTAL,
    (select sum(bytes) B,
            max(bytes) C,
            count(bytes) D,
            tablespace_name
            from dba_free_space
            group by tablespace_name) FREE
where
      total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
/


REM tablespace report

set linesize 200

select a.tablespace_name,
       round(a.bytes_alloc / 1024 / 1024) megs_alloc,
       round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
       round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
       round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
       100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
       round(maxbytes / 1048576) Max
  from (select f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
          from dba_data_files f
         group by tablespace_name) a,
       (select f.tablespace_name, sum(f.bytes) bytes_free
          from dba_free_space f
         group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
       round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
       round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             1048576) megs_free,
       round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
       100 -
       round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
             sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
       round(sum(f.maxbytes) / 1048576) max
  from sys.v_$TEMP_SPACE_HEADER h,
       sys.v_$Temp_extent_pool  p,
       dba_temp_files           f
where p.file_id(+) = h.file_id
   and p.tablespace_name(+) = h.tablespace_name
   and f.file_id = h.file_id
   and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/

cnaning

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select count(*)  from dba_recyclebin;

  COUNT(*)
----------
         0
SQL> set pages 999
SQL>  col tsname  format     a16 justify c heading 'Tablespace'
SQL>  col nfrags  format 999,990 justify c heading 'Free|Frags'
col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'
col totsiz  format 999,999 justify c heading 'Total|(MB)'
col avasiz  format 999,999 justify c heading 'Available|(MB)'
SQL> SQL> SQL> SQL>  col pctusd  format     990 justify c heading 'Pct|Used'
SQL> select total.TABLESPACE_NAME tsname,
  2          D nfrags,
  3          C/1024/1024 mxfrag,
        A/1024/1024 totsiz,
        B/1024/1024 avasiz,
        (1-nvl(B,0)/A)*100 pctusd
from
     (select sum(bytes) A,
             tablespace_name
             from dba_data_files
             group by tablespace_name) TOTAL,
     (select sum(bytes) B,
             max(bytes) C,
             count(bytes) D,
             tablespace_name
             from dba_free_space
  4    5    6    7    8    9   10   11   12   13   14   15   16   17               group by tablespace_name) FREE
18   where
       total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
/
  19   20  
                   Free    Largest   Total   Available Pct
   Tablespace     Frags   Frag (MB)   (MB)     (MB)    Used
---------------- -------- --------- -------- --------- ----
IDX_TLOTLOGN            6        57      100        63   37
SYSAUX                 12        57    1,050        62   94
UNDOTBS1               28     1,798    4,300     3,897    9
T_GX                    4        74      200       139   31
IDX_LOTSELECT           3        98      100        99    1
IDX_LOTMESS             1       499      500       499    0
T_LOT_REQINF_MAX        1        99      100        99    1
VAL
T_GX_BATCHINF_MA        1        99      100        99    1
XVAL
IDX_GXSELECT            6        71      700        72   90
T_LOT_BATCHINF          5       127      500       347   31
T_LOT_LOTERROR          2       197      200       199    1
USERS                   9       638    1,200     1,047   13
IDX_LOTUP               3        83      900        84   91
T_GX_SALE              12       864    2,124     1,322   38
IDX_GX_SALE             6       125      900       127   86
SYSTEM                  3        10    1,130        11   99
T_LOTMESS               1       999    1,000       999    0
IDX_TLOTLOG             7        59      100        63   37
T_LOT_BATCHINF_M        1        99      100        99    1
AXVAL
T_GX_BETINF_MAXV        1        99      100        99    1
AL
T_GX_TICKINF_MAX        1        99      100        99    1
VAL
T_GX_TICKINF            5       186      700       491   30
IDX_GX                  2        59      100        59   41
T_LOTSELECT             1        99      100        99    1
IDX_TLOTINFO           60        12    1,200        61   95
T_LOT_TICKINF_MA        1        99      100        99    1
XVAL
IDX_TLOTGXINFO         29       112    1,000       141   86
T_LOT_LOTERROR_M        1        99      100        99    1
AXVAL
T_LOT_TICKINF           5       202      800       527   34
T_GX_BETINF             5       303    1,200       807   33
T_GX_BATCHINF           5        95      400       279   30
T_LOTUP                 4        43      500        47   91
T_GXSELECT              2        84      600        84   86
T_LOT_NOTIFYINF_        1        99      100        99    1
MAXVAL

34 rows selected.
SQL> set linesize 200
SQL>  
SQL> select a.tablespace_name,
        round(a.bytes_alloc / 1024 / 1024) megs_alloc,
        round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
        round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
        round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
        100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
        round(maxbytes / 1048576) Max
   from (select f.tablespace_name,
  2    3    4    5    6    7    8    9                  sum(f.bytes) bytes_alloc,
10                  sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
           from dba_data_files f
          group by tablespace_name) a,
        (select f.tablespace_name, sum(f.bytes) bytes_free
           from dba_free_space f
          group by tablespace_name) b
11   12   13   14   15   16   where a.tablespace_name = b.tablespace_name(+)
17   union all
select h.tablespace_name,
        round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
        round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
              1048576) megs_free,
18   19   20   21   22          round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
              sum(h.bytes_used + h.bytes_free)) * 100) Pct_Free,
        100 -
        round((sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) /
              sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
23   24   25   26   27   28          round(sum(f.maxbytes) / 1048576) max
   from sys.v_$TEMP_SPACE_HEADER h,
29   30          sys.v_$Temp_extent_pool  p,
        dba_temp_files           f
where p.file_id(+) = h.file_id
    and p.tablespace_name(+) = h.tablespace_name
    and f.file_id = h.file_id
    and f.tablespace_name = h.tablespace_name
group by h.tablespace_name
ORDER BY 1
/ 31   32   33   34   35   36   37   38  
TABLESPACE_NAME                MEGS_ALLOC  MEGS_FREE  MEGS_USED   PCT_FREE   PCT_USED        MAX
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
IDX_GX                                100         59         41         59         41      32768
IDX_GXSELECT                          700         72        628         10         90      32768
IDX_GX_SALE                           900        127        773         14         86      32768
IDX_LOTMESS                           500        499          1        100          0      32768
IDX_LOTSELECT                         100         99          1         99          1      32768
IDX_LOTUP                             900         84        816          9         91      32768
IDX_TLOTGXINFO                       1000        141        860         14         86      32768
IDX_TLOTINFO                         1200         61       1139          5         95      32768
IDX_TLOTLOG                           100         63         37         63         37      32768
IDX_TLOTLOGN                          100         63         37         63         37      32768
SYSAUX                               1050         62        988          6         94      32768
SYSTEM                               1130         11       1119          1         99      32768
TEMP                                  600        600          0        100          0      16384
T_GX                                  200        139         61         69         31      32768
T_GXSELECT                            600         84        516         14         86      32768
T_GX_BATCHINF                         400        279        121         70         30      32768
T_GX_BATCHINF_MAXVAL                  100         99          1         99          1      32768
T_GX_BETINF                          1200        807        393         67         33      32768
T_GX_BETINF_MAXVAL                    100         99          1         99          1      32768
T_GX_SALE                            2124       1322        802         62         38      32768
T_GX_TICKINF                          700        491        209         70         30      32768
T_GX_TICKINF_MAXVAL                   100         99          1         99          1      32768
T_LOTMESS                            1000        999          1        100          0      32768
T_LOTSELECT                           100         99          1         99          1      32768
T_LOTUP                               500         47        453          9         91      32768
T_LOT_BATCHINF                        500        347        153         69         31      32768
T_LOT_BATCHINF_MAXVAL                 100         99          1         99          1      32768
T_LOT_LOTERROR                        200        199          1         99          1      32768
T_LOT_LOTERROR_MAXVAL                 100         99          1         99          1      32768
T_LOT_NOTIFYINF_MAXVAL                100         99          1         99          1      32768
T_LOT_REQINF_MAXVAL                   100         99          1         99          1      32768
T_LOT_TICKINF                         800        527        273         66         34      32768
T_LOT_TICKINF_MAXVAL                  100         99          1         99          1      32768
UNDOTBS1                             4300       3897        403         91          9      16384
USERS                                1200       1047        153         87         13      32768
35 rows selected.

cnaning

请见附件

Maclean Liu(刘相兵

碎片最多的 IDX_TLOTINFO 表空间也仅有 60个Free Frags，最大的FREE Frags 12 MB ， 而其使用率为 95 % ， 属于正常现象。

cnaning

谢谢刘大

icnm

不过，IDX_TLOTINFO 表空间从名称上看都是索引吧