CSDN博客

img snowhite2000

[Tip] 如何管理table 使用的空间

发表于2004/7/13 22:52:00  1574人阅读

作者:snowhite2000    时间:02-03-13 20:21

说明 如何管理table 使用的空间

1. 如何发现你的table 有fragmentation:

(a) analyzed table tablename compute statistics;
---- you may analyze all the tables under that user schema)
(b) select table_name, round(avg_row_len*num_rows/1024/1024, 0) data_size from user_tables;
---- 你可以知道表中大约的data size (单位: M)
(c) select segment_name, round(blocks*8/1024, 0) table_size from user_segments where segment_type='TABLE';
---- 你可以知道你的表的实际size (单位: M)

如果你比较同一个表的(c) table_size 远远大于(b) data_size,你的表可能有fragmentation。

也许有没有用的extents 在high water mark 上面,如果是这种情况,可以直接deallocate unused space:

alter table tablename deallocate keep 0;

2. 如果你确定是fragmentation存于表中,有下面的方法解决。
(a) create a temp table, copy all the data from fragmented table to temp table, truncate the fragmented table, copy data back, then drop temp table:
---- this way, you really don't need to recrate any indexes, constrains etc. but if there are columns in the table are other tables' reference foreign key, and the table has some procedures/tiggers, you have to disable them all before truncate the table. And, you also need enable those foreign keys, procedures and triggers.
(b) alter table tablename move tablespace B; (tablespace B is different than original tablespace, you may move back from tablespace B to original one, run table move commend again)
----you need rebuild all indexes on the table
(c) export/import
---- 这个方法不用多说了吧。

请有好方法的朋友跟贴。谢谢。


作者:笨笨熊    时间:02-03-13 22:16

如何发现你的table space有fragmentation:

ttitle 'Tablespace Fragmentation Information'

SELECT SUBSTR(ts.name,1,10) TSPACE,
tf.blocks BLOCKS,
SUM(f.length) FREE,
COUNT(*) PIECES,
MAX(f.length) BIGGEST,
MIN(f.length) SMALLEST,
ROUND(AVG(f.length)) AVERAGE
FROM sys.fet$ F,
sys.file$ TF,
sys.ts$ TS
WHERE ts.ts# = f.ts#
AND ts.ts# = tf.ts#
GROUP BY ts.name, tf.blocks;

解决的方法我同意你的。


作者:ArthurXu    时间:02-03-13 23:36

try this one

column table_name format a40
column degree heading " DEGREE"
column density heading " DATA|DENSITY"
column new_free format 99 heading "SUGGEST|PCTFREE"
column new_used format 99 heading "SUGGEST|PCTUSED"
column reads_wasted format 999999 heading "MBREADS|TO SAVE"

select /*+ ordered */
u.name ||'.'|| o.name table_name,
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree, 1)), 7) degree,
substr(
to_char(
100 * t.rowcnt / (
floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))
* t.blkcnt
),
'999.00'
),
2
) ||
'%' density,
1 new_free,
99 - ceil(
( 100 * ( p.value - 66 - t.initrans * 24 -
greatest(
floor(
(p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11)
) - 1,
1
) * greatest(t.avgrln + 2, 11)
)
/
(p.value - 66 - t.initrans * 24)
)
) new_used,
ceil(
( t.blkcnt - t.rowcnt /
floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))
) / m.value
) reads_wasted
from
sys.tab$ t,
( select
value
from
sys.v_$parameter
where
name = 'db_file_multiblock_read_count'
) m,
sys.obj$ o,
sys.user$ u,
(select value from sys.v_$parameter where name = 'db_block_size') p
where
t.tab# is null and
t.blkcnt > m.value and
t.chncnt = 0 and
t.avgspc > t.avgrln and
ceil(
( t.blkcnt - t.rowcnt /
floor((p.value - 66 - t.initrans * 24) / greatest(t.avgrln + 2, 11))
) / m.value
) > 0 and
o.obj# = t.obj# and
o.owner# != 0 and
u.user# = o.owner#
order by
5 desc, 2
/


作者:Yong Huang    时间:02-03-14 15:08

Re: 如何管理table 使用的空间

Comments on the following suggestion.

1. It's only available in Oracle8i.
2. Tablespace B can be the same as the original.
3. In fact, the tablespace clause can be omitted entirely.
4. Do remember to rebuild indexes as snowwhite2000 suggested. Indexes become unusable after alter table move, even if the indexes are on columns with no data.

Here's my screen:

SQL> alter table t move;

Table altered.

SQL> select tablespace_name from user_tables where table_name = 'T';

TABLESPACE_NAME
------------------------------
SYSTEM

SQL> alter table t move tablespace system;

Table altered.

Note: don't create a table in SYSTEM like I do here.

Yong Huang

quote:
最初由 snowhite2000 发布

(b) alter table tablename move tablespace B; (tablespace B is different than original tablespace, you may move back from tablespace B to original one, run table move commend again)
----you need rebuild all indexes on the table


作者:Yong Huang    时间:02-03-14 15:22

This query may fail to find anything if the tablespace is locally managed.

Yong Huang

quote:
最初由 笨笨熊 发布

SELECT SUBSTR(ts.name,1,10) TSPACE,
tf.blocks BLOCKS,
SUM(f.length) FREE,
COUNT(*) PIECES,
MAX(f.length) BIGGEST,
MIN(f.length) SMALLEST,
ROUND(AVG(f.length)) AVERAGE
FROM sys.fet$ F,
sys.file$ TF,
sys.ts$ TS
WHERE ts.ts# = f.ts#
AND ts.ts# = tf.ts#
GROUP BY ts.name, tf.blocks;


作者:flyingknife    时间:02-03-14 22:02

这种情况有没有办法?

如果ORACLE是8I以下的版本,而数据库需要运行在7*24的模式下,有什么办法可以清理碎片呢?


作者:chao_ping    时间:02-03-15 03:59

quest的产品live-reorg可以满足你的需求。
不过应该会非常的昂贵的


作者:oracledba    时间:02-03-16 06:19

如果发现有碎片。。。

alter tablespace temp coalesce;

这样做可以嘛?


作者:chao_ping    时间:02-03-16 06:40

这个语句帮助不大。只能够融合本来就是相邻的碎片。
原来yong huang讲过这个问题,就是表空间的碎片分成两种,一个是碎片都是相邻的,一个是被某个extent分离的。
第二种情况才是真正的碎片。
而你的语句子能够处理第一仲情况。而第一仲情况实际上是你把pctincrease=1也可以做到的。或者在oracle allocate new extent的时候也能自动合并的。

表空间的碎片重组还是比较麻烦的,如果大家有兴趣,可以专题讨论一下。


作者:snowhite2000    时间:02-03-17 01:43

如何发现现有的table storage parameter 不合适

不合适的storage parameter 会导致 fragmentation。如何发现不合适的parameter?

这是一种方法:
运行这个 script:

代码:

------------------------------------------------
ttitle -
  
center  'Segment Storage Summary'  skip 2

col ownr format a8      heading
'Owner'        justify c
col name format a28     heading
'Segment Name' justify c
col type format a8      heading
'Type'         justify c trunc
col hfil format   9
,990 heading 'Header|File'  justify c
col hblk format  99
,990 heading 'Header|Block' justify c
col exts format   9
,990 heading 'Extents'      justify c
col blks format 999
,990 heading 'Blocks'       justify c

break on ownr skip 1

select
  owner         ownr
,
  
segment_name  name,
  
segment_type  type,
  
header_file   hfil,
  
header_block  hblk,
  
extents       exts,
  
blocks        blks
from
  dba_segments
where
  owner like upper
('&ownr')
    and
  
segment_name like upper('&segt')
/

undef ownr
undef segt
set verify on
----------------------------------------------------


我在一个有问题的database 下面的运行结果:
代码:

Segment Storage Summary

                                               Header Header
Owner           Segment Name           Type    File   Block  Extents  Blocks
-------- ---------------------------- -------- ------ ------- ------- --------
CLINICAL ACCOUNT_INFO                 TABLE        18       2     236    4,720
         ADDRESS                      TABLE        19       2   1
,359   27,465
         ALLERGY_INFO                 TABLE        27       2       1       20
         AUDIT_EVENT                  TABLE        20       2       1       80
         CONTACT_INFO                 TABLE        21       2     164    3
,280
         CONTACT_TYPE_DESC            TABLE        27      22       1       20
         DOCTOR_HOSPITAL_ASSOCIAT     TABLE        27      42       1       50
         DOCTOR_INFO                  TABLE        27      92       1       35
         DOCTOR_PATIENT_RELN          TABLE        22       2     158    3
,160
         DOC_PAT_RELN_CODE            TABLE        27     127       1       20
         DRUG_INFO                    TABLE        27     147       1       80
         ERROR_PROCEDURES             TABLE        22      22     624   12
,620
         ERROR_TXN                    TABLE         8       2   1
,691  108,480
         ERROR_TXN_DESC               TABLE        27     227       1       20
         GUARANTOR                    TABLE        22     182     154    3
,080
         HOSPITAL_INFO                TABLE        27     247       1       20
         INSURANCE                    TABLE        23       2     523   10
,460
         LAB_RAD_ORDER                TABLE        24       2   2
,087   42,145
         LAB_RAD_ORDER_RESULT         TABLE        25       2     560   11
,200
         LAB_RAD_ORDER_STATUS         TABLE        27     267       1       20
         LAB_RAD_RESULT_DESC          TABLE        26       2   9
,013  169,442
         MEDICAL_RECORD               TABLE        24      22      49    



用下面 statement 检查:

代码:

SQL
> select segment_name, next_extent/1024 from user_segments;

SEGMENT_NAME                        NEXT_EXTENT/1024
----------------------------------- ----------------
ACCOUNT_INFO                                     128
ADDRESS                                          128
ALLERGY_INFO                                     128
AUDIT_EVENT                                      128
CONTACT_INFO                                     128
CONTACT_TYPE_DESC                                128
DOCTOR_HOSPITAL_ASSOCIAT                         128
DOCTOR_INFO                                      128
DOCTOR_PATIENT_RELN                              128
DOC_PAT_RELN_CODE                                128
DRUG_INFO                                        128
ERROR_PROCEDURES                                 128
ERROR_TXN                                        512
ERROR_TXN_DESC                                   128
GUARANTOR                                        128
HOSPITAL_INFO                                    128
INSURANCE                                        128
LAB_RAD_ORDER                                    512
LAB_RAD_ORDER_RESULT                             128
LAB_RAD_ORDER_STATUS                             128
LAB_RAD_RESULT_DESC                              128
MEDICAL_RECORD                                 



注意到了:
LAB_RAD_RESULT_DESC TABLE 26 2 9,013 169,442
这行的结果,169442个blocks 来自9013个extents,产生了overextended segments 的问题。因此解决办法就是将 next_extent 的size 加大,我将原来的128K 加大至 512K。等待下一次 database downtime 时, reorginize 这些 tables。再查看结果。

同样的问题,可以使用OEM diagnostics Pack 中的 tablespace map 工具,analyze tablespace 之后 report 有显示有问题的 tables/indexes.


请感兴趣的朋友贴出您的建议,谢谢。


作者:iHero    时间:02-03-18 00:13

使用OEM.会使你分析更加的方便:
以下是我分析结果和例子:
使用OEM 管理数据库是很方便的:
由TABLEASPCE MAP ->选则表空间的分析

2002-1-22 表空间的分析报告结果
TABLESPACE ANALYSIS REPORT

类型 段名 预警状态

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

表 iHero.iHero_RESUME_BAS ALERT
ALERT: 过分扩展的段。 已分配的区数: 2350。
WARNING: 过多的链式行。 7.01% 的行被链接/移植。平均行长度 (字节): 311.

表 iHero.iHero_RESUME_EDU ALERT
ALERT: 过分扩展的段。 已分配的区数: 1091

EXCESSIVE ROW CHAINING

在两种情形下,表或表分区中某一行的数据可能太多而无法装入单个数据块。因此产生了行碎片。

在第一种情形中,第一次插入该行时,由于行过大而无法装入一个数据块。Oracle Server 使用为该段保留的一连串数据块存储行数据。链式行常常在使用较大的行时出现,如包含一列长数据类型的行。在这种情形下,如果不使用由 DB_BLOCK_SIZE 初始化参数定义的较大的数据库块大小,行延续是不可避免的。

但在第二种情形下,起初能够装入一个数据块的某行被更新,使得整行长度增加,并且数据块的空闲空间已经完全被填满。在这种情形下,Oracle Server 将整行数据移植到一个新的数据块 (假设这一整行可以装入新块)。Oracle Server 会保留移植行的原始行,使其引用包含移植行的新块。

在续行或移植行时,与该行相关联的输入/输出性能降低,因为 Oracle Server 必须扫描一个以上的数据块以检索该行的信息。

有两种解决行移植问题的方法。重建表或表分区可以避免在重建过程中,随着行被紧密地装入各数据块而产生行碎片。但是,如果未对表或表分区进行其它更改 (只是修复移植行),将集中修复有问题的行,而不是完全重建。

注:如果行在更新中可能增大,请考虑增加段的 PCTFREE 值.
我的分析了.自己的数据库db_block=8K 足够大. 而且行的size不是很大, 造成行移植的主要原因是数据更新很多, 原始欲留的不是多.
所以重新建立表更该表的参数加大PCTFREE值.导入数据.
1. creat table temp as select * from tablename
2. DROP TABLE NAME .
3. 重新建表(更改表的参数)
4. insert into tablename select * from temp . 导入数据.
可以用INSERT /*APPEND*/ INTO TALBENAME SELECT * FORM TEMP. 避免LOGGING. Direct load insert .

已分配的区数太多.
分析原因可能是表空间的原始参数太小. NEXT 45k .
1.调整TABLESPACE NEXT 大小. ALTER TABLESPACE TABLESPACENAME MINIMUM EXTENT 54k
2.exp 导出数据.
3.执行imp命令时使用indexfile参数:
 imp userid=scott/tiger file=emp.dmp indexfile=emp.sql Oracle把表和索引的创建信息写到指定的文件,而不是把数据写回。
 对它进行编辑,去除"REM"等信息,找到Initial参数,根据需要改变它。
4.在SQL*plus中执行emp.sql。
5.IMP 装入数据:Imp userid=scott/tiger ignore=y file=emp.dmp

同样也可以不用SQL 文件建立表. 直接IMP. 在修改表的存储参数. ALTER TABLE TABLENAME STORAGE ( NEXT 540k )


作者:ligengocp    时间:02-03-20 23:44

1 什么是Migrated and Chained Rows
If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.
Dynamic space management, especially migration and chaining, is detrimental to performance:
UPDATE statements that cause migration and chaining perform poorly.

Queries that select migrated or chained rows must perform more I/O.

2 如何查看Migrated and Chained Rows
2。1 Use the ANALYZE statement to collect information about migrated and chained rows. For example:
ANALYZE TABLE order_hist LIST CHAINED ROWS;

Query the output table:

SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96


The output lists all rows that are either migrated or chained.

If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows with the following steps:

Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:

CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');


Delete the migrated and chained rows from the existing table:

DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');


Insert the rows of the intermediate table into the existing table:

INSERT INTO order_hist

SELECT *
FROM int_order_hist;


Drop the intermediate table:

DROP TABLE int_order_history;


Delete the information collected in step 1 from the output table:

DELETE FROM CHAINED_ROWS

WHERE TABLE_NAME = 'ORDER_HIST';

2.2 在V$sysstat中查看TABlE FETCH CONTINUED ROW记录

3. 如何查避免Migrated and Chained Rows
3.1
加大表参数PCTFREE,让块有更多的空间去容纳记录的增长
3.2
加大DB_BLOCK_SIZE,可以容纳较大的字录
**Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns. **


作者:ligengocp    时间:02-03-21 00:04

1 什么是Migrated and Chained Rows
If an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block, then Oracle tries to find another block with enough free space to hold the entire row. If such a block is available, then Oracle moves the entire row to the new block. This is called migrating a row. If the row is too large to fit into any available block, then Oracle splits the row into multiple pieces and stores each piece in a separate block. This is called chaining a row. Rows can also be chained when they are inserted.
Dynamic space management, especially migration and chaining, is detrimental to performance:
UPDATE statements that cause migration and chaining perform poorly.

Queries that select migrated or chained rows must perform more I/O.

2 如何查看Migrated and Chained Rows
2。1 使用Analyze with LIST CHAINED ROWS,分析有哪些Migrated and Chained Rows,其结果可以输入到表CHAINED_ROWS中,脚本/ORACLE_HOME/RDBMS/ADMIN/UTLCHAIN.SQL如下:
create table CHAINED_ROWS (
owner_name varchar2(30),
table_name varchar2(30),
cluster_name varchar2(30),
partition_name varchar2(30),
subpartition_name varchar2(30),
head_rowid rowid,
analyze_timestamp date
);

Use the ANALYZE statement to collect information about migrated and chained rows. For example:
ANALYZE TABLE order_hist LIST CHAINED ROWS;

Query the output table:

SELECT *
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST';

OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP
---------- ---------- -----... ------------------ ---------
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96
SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96


The output lists all rows that are either migrated or chained.

If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows with the following steps:

Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:

CREATE TABLE int_order_hist
AS SELECT *
FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');


Delete the migrated and chained rows from the existing table:

DELETE FROM order_hist
WHERE ROWID IN
(SELECT HEAD_ROWID
FROM CHAINED_ROWS
WHERE TABLE_NAME = 'ORDER_HIST');


Insert the rows of the intermediate table into the existing table:

INSERT INTO order_hist

SELECT *
FROM int_order_hist;


Drop the intermediate table:

DROP TABLE int_order_history;


Delete the information collected in step 1 from the output table:

DELETE FROM CHAINED_ROWS

WHERE TABLE_NAME = 'ORDER_HIST';

2.2 在V$sysstat中查看TABlE FETCH CONTINUED ROW记录

3. 如何查避免Migrated and Chained Rows
3.1
加大表参数PCTFREE,让块有更多的空间去容纳记录的增长
3.2
加大DB_BLOCK_SIZE,可以容纳较大的字录
**Chaining is often unavoidable with tables that have a LONG column or long CHAR or VARCHAR2 columns. **

0 0

相关博文

我的热门文章

img
取 消
img