Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › ORACLE大数据业务表空间碎片化问题
发新帖
litb0125

0

积分

1

好友

2

主题

[SQL调优] ORACLE大数据业务表空间碎片化问题

1#
发表于 2013-2-19 16:51:52 | 查看: 6841| 回复: 13
  情景：ORACLE11g中，表空间使用率在60%左右，数据入库不了。报
ORA-01658: unable to create INITIAL extent for segment in tablespace DATA_0000
使用
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(+)

  见图：


表空间DATA_0000 free extent 有5255092个 最大是2572M，平均0.9M。free extent 过碎满足不了要求。
是运营商大数据业务的OLAP分析系统，每天新建1000张表，数据保存1--3个月，数据不会做更新删除操作，直接DROP。

IMP/EXP重建表空间的方案，因数据量太大可操作性比较低，还有其他的解决方案吗？

后续其他地方打算重新规划表空间，分出多个表空间，一个表空间存储一个月份或是更少时间的数据，直接删除表空间的方法避免同样的问题？希望大家多给我提一些解决方案。谢谢！


1.jpg (55.51 KB, 下载次数: 485)

表空间的free extent

表空间的free extent

2#
发表于 2013-2-19 17:00:28


select count(*), ts_name from dba_recyclebins  group by ts_name

查一下

回复 只看该作者 道具 举报

3#
发表于 2013-2-20 09:01:05

select count(*), ts_name from dba_recyclebins  group by ts_name

本帖最后由 litb0125 于 2013-2-20 09:02 编辑

select count(*), ts_name from dba_recyclebins  group by ts_name

2.png (44.14 KB, 下载次数: 474)

2.png

回复 只看该作者 道具 举报

4#
发表于 2013-2-20 09:48:00
  1. ttitle -
  2.    center  'Database Freespace Summary'  skip 2

  4. comp sum of nfrags totsiz avasiz on report
  5. break on report

  7. set pages 999
  8. col tsname  format     a16 justify c heading 'Tablespace'
  9. col nfrags  format 999,990 justify c heading 'Free|Frags'
  10. col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'
  11. col totsiz  format 999,999 justify c heading 'Total|(MB)'
  12. col avasiz  format 999,999 justify c heading 'Available|(MB)'
  13. col pctusd  format     990 justify c heading 'Pct|Used'

  15. select total.TABLESPACE_NAME tsname,
  16.        D nfrags,
  17.        C/1024/1024 mxfrag,
  18.        A/1024/1024 totsiz,
  19.        B/1024/1024 avasiz,
  20.        (1-nvl(B,0)/A)*100 pctusd
  21. from
  22.     (select sum(bytes) A,
  23.             tablespace_name
  24.             from dba_data_files
  25.             group by tablespace_name) TOTAL,
  26.     (select sum(bytes) B,
  27.             max(bytes) C,
  28.             count(bytes) D,
  29.             tablespace_name
  30.             from dba_free_space
  31.             group by tablespace_name) FREE
  32. where
  33.       total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
  34. /


  37. SELECT t.tablespace_name,
  38.        CASE
  39.          WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  40.           u.bytes
  41.          ELSE
  42.           df.user_bytes - NVL(fs.bytes, 0)
  43.        END / 1024 / 1024 used_mb,
  44.        CASE
  45.          WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  46.           df.user_bytes - NVL(u.bytes, 0)
  47.          ELSE
  48.           NVL(fs.bytes, 0)
  49.        END / 1024 / 1024 free_mb,
  50.        fs.min_fragment / 1024 / 1024 min_fragment_mb,
  51.        fs.max_fragment / 1024 / 1024 max_fragment_mb,
  52.        (fs.bytes / 1024 / 1024) / fs.fragments avg_fragment_mb,
  53.        fs.fragments,
  54.        t.status,
  55.        t.contents,
  56.        t.logging,
  57.        t.extent_management,
  58.        t.allocation_type,
  59.        t.force_logging,
  60.        t.segment_space_management,
  61.        t.def_tab_compression,
  62.        t.retention,
  63.        t.bigfile
  64.   FROM dba_tablespaces t,
  65.        (SELECT tablespace_name,
  66.                SUM(bytes) bytes,
  67.                MIN(min_fragment) min_fragment,
  68.                MAX(max_fragment) max_fragment,
  69.                SUM(fragments) fragments
  70.           FROM (SELECT tablespace_name,
  71.                        SUM(bytes) bytes,
  72.                        MIN(bytes) min_fragment,
  73.                        MAX(bytes) max_fragment,
  74.                        COUNT(*) fragments
  75.                   FROM dba_free_space
  76.                  GROUP BY tablespace_name
  77.                 UNION ALL
  78.                 SELECT tablespace_name,
  79.                        SUM(bytes) bytes,
  80.                        MIN(bytes) min_fragment,
  81.                        MAX(bytes) max_fragment,
  82.                        COUNT(*) fragments
  83.                   FROM dba_undo_extents
  84.                  WHERE status = 'EXPIRED'
  85.                  GROUP BY tablespace_name)
  86.          GROUP BY tablespace_name) fs,
  87.        (SELECT tablespace_name, SUM(user_bytes) user_bytes
  88.           FROM dba_data_files
  89.          GROUP BY tablespace_name
  90.         UNION ALL
  91.         SELECT tablespace_name, SUM(user_bytes) user_bytes
  92.           FROM dba_temp_files
  93.          GROUP BY tablespace_name) df,
  94.        (SELECT tablespace_name, SUM(bytes_used) bytes
  95.           FROM gv$temp_extent_pool
  96.          GROUP BY tablespace_name) u
  97. WHERE t.tablespace_name = df.tablespace_name(+)
  98.    AND t.tablespace_name = fs.tablespace_name(+)
  99.    AND t.tablespace_name = u.tablespace_name(+)
  100. /

  102. select count(distinct segment_name) from dba_extents where tablespace_name='DATA_0000';


  105. select blocks,count(*) from dba_extents where tablespace_name='DATA_0000' group by blocks;
复制代码
查一下 结果保存成txt 不要截图

回复 只看该作者 道具 举报

5#
发表于 2013-2-20 10:00:10
表的创建脚本的initial extent设置的过大
怀疑你exp的时候 没有设置compress=n造成的

imp xxx/xxx file=xxxx.dmp indexfile=create.sql table=xxx

看看create.sql 表的创建脚本INITIAL设置为多大

回复 只看该作者 道具 举报

6#
发表于 2013-2-20 13:37:34
SQL> ttitle -
SQL> center  'Database Freespace Summary'  skip 2
  2  
SQL> comp sum of nfrags totsiz avasiz on report
SQL> break on report
SQL> set pages 999
SQL> col tsname  format     a16 justify c heading 'Tablespace'
SQL> col nfrags  format 999,990 justify c heading 'Free|Frags'
SQL> col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'
SQL> col totsiz  format 999,999 justify c heading 'Total|(MB)'
SQL> col avasiz  format 999,999 justify c heading 'Available|(MB)'
SQL> col pctusd  format     990 justify c heading 'Pct|Used'
SQL>
SQL> select total.TABLESPACE_NAME tsname,
  2           D nfrags,
  3          C/1024/1024 mxfrag,
  4          A/1024/1024 totsiz,
  5           B/1024/1024 avasiz,
  6          (1-nvl(B,0)/A)*100 pctusd
  7   from
  8      (select sum(bytes) A,
  9              tablespace_name
10               from dba_data_files
11              group by tablespace_name) TOTAL,
12       (select sum(bytes) B,
13              max(bytes) C,
14               count(bytes) D,
15              tablespace_name
16               from dba_free_space
17               group by tablespace_name) FREE
18    where
19         total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
20   ;

TSNAME               NFRAGS     MXFRAG     TOTSIZ     AVASIZ PCT
---------------- ---------- ---------- ---------- ---------- ---
SYSTEM                    8      16256     130056 97986.9375 24.
                                                             657
                                                             887
                                                             756
                                                             043
                                                               6

USERS                     1          4          5          4  20
IDX_0000             156769      16256    9282560 7393205.93 20.
                                                             353
                                                             803
                                                             934
                                                             475

ZXT_DATA              63392      16256     291840 278364.312 4.6
                                                             174
                                                             916
                                                             049
                                                             890
                                                               4

ZXT_IDX                  18      16256     291840     291837 0.0
                                                             010
                                                             279
                                                             605
                                                             263
                                                             157
                                                               9

TS_DSA                42064      16256   10543104 5801029.87 44.
                                                             977
                                                             969
                                                             723
                                                             147
                                                               9

SYSAUX                  105   195.8125       5660   1494.875 73.
                                                             588
                                                             780
                                                             918
                                                             727
                                                               9

UNDOTBS1                305  7970.4375      22405 21573.1875 3.7
                                                             126
                                                             199
                                                             509
                                                             038
                                                               2

DATA_0000           3203065      16256   14730240 7626063.25 48.
                                                             228
                                                             520
                                                             037
                                                             691
                                                               2


9 rows selected

SQL>

吉林PS查询结果.txt

14.68 KB, 下载次数: 1207

回复 只看该作者 道具 举报

7#
发表于 2013-2-20 13:37:58
SQL> SELECT t.tablespace_name,
  2         CASE
  3           WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
  4            u.bytes
  5           ELSE
  6            df.user_bytes - NVL(fs.bytes, 0)
  7         END / 1024 / 1024 used_mb,
  8         CASE
  9           WHEN t.contents = 'TEMPORARY' AND t.extent_management = 'LOCAL' THEN
10            df.user_bytes - NVL(u.bytes, 0)
11           ELSE
12            NVL(fs.bytes, 0)
13         END / 1024 / 1024 free_mb,
14         fs.min_fragment / 1024 / 1024 min_fragment_mb,
15         fs.max_fragment / 1024 / 1024 max_fragment_mb,
16         (fs.bytes / 1024 / 1024) / fs.fragments avg_fragment_mb,
17         fs.fragments,
18         t.status,
19         t.contents,
20         t.logging,
21         t.extent_management,
22         t.allocation_type,
23         t.force_logging,
24         t.segment_space_management,
25         t.def_tab_compression,
26         t.retention,
27         t.bigfile
28    FROM dba_tablespaces t,
29         (SELECT tablespace_name,
30                 SUM(bytes) bytes,
31                 MIN(min_fragment) min_fragment,
32                 MAX(max_fragment) max_fragment,
33                 SUM(fragments) fragments
34            FROM (SELECT tablespace_name,
35                         SUM(bytes) bytes,
36                         MIN(bytes) min_fragment,
37                         MAX(bytes) max_fragment,
38                         COUNT(*) fragments
39                    FROM dba_free_space
40                   GROUP BY tablespace_name
41                  UNION ALL
42                  SELECT tablespace_name,
43                         SUM(bytes) bytes,
44                         MIN(bytes) min_fragment,
45                         MAX(bytes) max_fragment,
46                         COUNT(*) fragments
47                    FROM dba_undo_extents
48                   WHERE status = 'EXPIRED'
49                   GROUP BY tablespace_name)
50           GROUP BY tablespace_name) fs,
51         (SELECT tablespace_name, SUM(user_bytes) user_bytes
52            FROM dba_data_files
53           GROUP BY tablespace_name
54          UNION ALL
55          SELECT tablespace_name, SUM(user_bytes) user_bytes
56            FROM dba_temp_files
57           GROUP BY tablespace_name) df,
58         (SELECT tablespace_name, SUM(bytes_used) bytes
59            FROM gv$temp_extent_pool
60           GROUP BY tablespace_name) u
61  WHERE t.tablespace_name = df.tablespace_name(+)
62     AND t.tablespace_name = fs.tablespace_name(+)
63     AND t.tablespace_name = u.tablespace_name(+)
64  ;

TABLESPACE_NAME                   USED_MB    FREE_MB MIN_FRAGMENT_MB MAX_FRAGMENT_MB AVG_FRAGMENT_MB  FRAGMENTS STATUS    CONTENTS  LOGGING   EXTENT_MANAGEMENT ALLOCATION_TYPE FORCE_LOGGING SEGMENT_SPACE_MANAGEMENT DEF_TAB_COMPRESSION RETENTION   BIGFILE
------------------------------ ---------- ---------- --------------- --------------- --------------- ---------- --------- --------- --------- ----------------- --------------- ------------- ------------------------ ------------------- ----------- -------
SYSTEM                         32067.0625 97986.9375          0.9375           16256   12248.3671875          8 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            MANUAL                   DISABLED            NOT APPLY   NO
USERS                                   0          4               4               4               4          1 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
IDX_0000                       1889259.06 7393205.93          0.0625           16256 47.159871769929     156769 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
ZXT_DATA                       13472.6875 278364.312          0.0625           16256 4.3911583874936      63392 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
ZXT_IDX                                 0     291837           15999           16256 16213.166666666         18 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
TS_DSA                         4741993.06 5801006.93          0.1875           16256 137.91234428119      42063 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
SYSAUX                           4164.125   1494.875          0.0625        195.8125 14.236904761904        105 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
UNDOTBS1                         331.1875 22072.8125          0.0625       7970.4375 22.755476804123        970 ONLINE    UNDO      LOGGING   LOCAL             SYSTEM          NO            MANUAL                   DISABLED            NOGUARANTEE NO
DATA_0000                      7104444.37 7625644.62          0.0625           16256 2.3806073001200    3203235 ONLINE    PERMANENT LOGGING   LOCAL             SYSTEM          NO            AUTO                     DISABLED            NOT APPLY   NO
TEMP                                    0      20478                                                            ONLINE    TEMPORARY NOLOGGING LOCAL             UNIFORM         NO            MANUAL                   DISABLED            NOT APPLY   NO

10 rows selected

回复 只看该作者 道具 举报

8#
发表于 2013-2-20 13:38:22
SQL> select count(distinct segment_name) from dba_extents where tablespace_name='DATA_0000';

COUNT(DISTINCTSEGMENT_NAME)
---------------------------
                       1234

SQL> select blocks,count(*) from dba_extents where tablespace_name='DATA_0000' group by blocks;

    BLOCKS   COUNT(*)
---------- ----------
         6    1966740
       832         55
       736         50
      1216         33
      1472         35
      1664         44
       544         78
      1120         49
      1248         37
      1728         29
      1536         97
        12     144090
       178         81
        82         82
       156         87
       138         67
        70         98
       184         87
       212         72
       114         92
        86        106
       244         71
       110         67
       200         57
       210         80
       228         78
        52         83
        44        104
       512        456
       576         54
       384         58
      1760         35
       992         31
       896         45
       672         38
       416         50
       448         49
       352         44
       214         68
        54         99
       124         79
       204         78
       218         62
        60         86
       144         90
        48        100
       158         82
       146         77
       206         65
      2048      13466
      1792        107
      1152         33
      1056         52
       768        268
      1984         32
      1088         33
      1888         29
      1920         24
        30      62983
         8     201296
        16     116868
        74         90
       238         77
        66         99
       172         79
       190         95
       216         74
        50         94
        58         98
        76         78
       232         69
       226         76
         2      22346
        64       6439
      1344         44
       320         51
       864         46
      1376         38
       640         36
      1632         33
        56         88
       202         80
       108         92
       148         80
        84         92
       130         91
        94         84
       152         79
       140         79
       102         87
        42         73
       104         74
       186         90
       162         84
        72         81
       136         87
       154         89
       288        147
        96       4092
       704         34
      1824         29
       608         49
        24      63519
        78         74
        46         85
       132         79
       116         86
       236         77
        98         86
        40         89
       208         77
        32    3746884
       256     158090
      1696         36
       224       2045
       128       3256
      1952         25
      2016         33
      1568         40
      1440         28
        18      88794
        22      67822
        20      75922
       198         87
       164         89
        68         71
       220        100
       230         72
       118         96
       240         90
       222         68
        62         90
       248         72
       170         95
       134         81
       254         73
       188         92
        38         90
       480         34
       160       2762
      1024        189
      1312         47
      1504         22
      1408         26
      1600         36
      1184         32
        26      63060
        14     144175
       126        107
        34         94
        88         88
       250         81
       176         78
       122         91
       196         98
       112        100
       142        100
       168         76
       252         91
        36         76
      1280        121
      1856         46
       192       2496
       960         39
       928         47
       800         58
         4      55485
        10     142841
       182         81
        28      64518
       246         72
       242         75
       166         83
       194         78
        80         90
       234         85
       106         91
        90         80
       100         94
       120         72
        92         88
       150         88
       174         83
       180         79

184 rows selected

回复 只看该作者 道具 举报

9#
发表于 2013-2-21 15:32:25
SQL> select blocks,count(*) from dba_extents where tablespace_name='DATA_0000' group by blocks;

    BLOCKS   COUNT(*)
---------- ----------
         6    1966740
        12     144090
        32    3746884

有大量大小不规则的 extent ( 6、12)

继续查：
SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';
select name,value from v$sysstat where name like '%SMON%';

select segment_name,count(*) from dba_extents where blocks=6 group by segment_name;
select segment_name,count(*) from dba_extents where blocks=12 group by segment_name;



show parameter event



回复 只看该作者 道具 举报

10#
发表于 2013-2-25 09:11:47
SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';
select name,value from v$sysstat where name like '%SMON%';

select segment_name,count(*) from dba_extents where blocks=6 group by segment_name;
select segment_name,count(*) from dba_extents where blocks=12 group by segment_name;

6.txt

556.41 KB, 下载次数: 1104

回复 只看该作者 道具 举报

11#
发表于 2013-3-5 08:45:05
快沉了，顶一下

回复 只看该作者 道具 举报

12#
发表于 2013-3-5 19:50:40
litb0125 发表于 2013-3-5 08:45
快沉了，顶一下

本质上还是小的表/索引太多， 考虑分表空间吧

回复 只看该作者 道具 举报

13#
发表于 2013-3-5 20:19:52
Maclean Liu(刘相兵 发表于 2013-3-5 19:50
本质上还是小的表/索引太多， 考虑分表空间吧

现在的库除了IMP/EXP重建表空间的方案，还有其他的解决方案吗？

后续其他地方打算重新规划表空间，分出多个表空间，一个表空间存储一个月份或是更少时间的数据，直接删除表空间的方法避免同样的问题

回复 只看该作者 道具 举报

14#
发表于 2013-3-9 21:07:10
先把小表小index 移走看看。新的再新建表空间

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-27 15:20 , Processed in 0.070299 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569