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

原创 2004年07月13日 22:52:00
作者: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:


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


用下面 statement 检查:



注意到了:
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. **

详解Oracle本地管理表空间

详解Oracle本地管理表空间 分类: [oracle]--[存储结构] 2011-11-13 22:17 926人阅读 评论(0) 收藏 举报 oracletablesqluser数据库 ...
  • haiross
  • haiross
  • 2013年10月16日 16:10
  • 2453

CSS实例:非常不错的鼠标悬停TIP效果

我们可以为我们的链接加上这样的效果,以更加体现网页的亲和力与易用性。我们也可以在提供下载的链接下提供这样的tip提示。我们看这样的鼠标悬停TIP效果是如何实现的。看下面的XHTML代码: ...
  • ufojoan
  • ufojoan
  • 2013年07月30日 17:53
  • 1627

本地管理表空间LMT&自动段空间管理ASSM

本地管理表空间LMT:改进了表空间中对象的性能,并且减轻了表空间的管理,不再存在表空间的存储碎片。 LMT通过位图跟踪表空间的空闲空间 执行任何LMT空间相关操作时可以减少或消除回滚信息。因为表空间中...
  • wbj19890107
  • wbj19890107
  • 2015年03月06日 16:54
  • 598

HTML笔记——小TIP和格式问题

根据w3school整理 HTML 水平线  标签在 HTML 页面中创建水平线。 hr 元素可用于分隔内容。 提示:使用水平线 ( 标签) 来分隔文章中的小节是一个办法(但并不是唯一的办法)...
  • u014694759
  • u014694759
  • 2014年04月13日 21:02
  • 717

Oracle本地管理对比数据字典管理表空间

Locally vs. Dictionary Managed Tablespaces 整理自:http://www.orafaq.com/node/3. When Oracleallocates ...
  • IndexMan
  • IndexMan
  • 2014年09月02日 13:11
  • 3342

文件逻辑结构、外存分配和空闲空间管理

一 文件的逻辑结构(组织方式): (1)顺序文件:所有记录具有相同长度,并且由相同数目、长度固定的域按照特定的顺序组成(记录按关键字排序)。 *优点:通常用于批处理文件,即每次读或写一大批记录,顺...
  • first_wolf
  • first_wolf
  • 2013年05月30日 09:49
  • 2125

Oracle DB 管理空间

• 介绍Oracle DB Server 如何自动管理空间 • 使用压缩节省空间 • 主动监视和管理表空间的空间使用量 • 介绍Oracle DB 中的段的创建 • 控制延迟创建段 • 使用“段指导”...
  • rlhua
  • rlhua
  • 2013年10月29日 00:10
  • 6576

流行的jquery 提示插件 tip

信息提示虽然是小东西,但是在网站设计中却起到了巨大的作用。如果你网站的信息提示做的好,会给访客留下非常深刻的印象。下面有30个非常流行的jQuery信息提示插件,希望对各位有所帮助。记住,所有的这些都...
  • yibing548
  • yibing548
  • 2015年05月12日 11:19
  • 1210

回收站引发ORACLE查询表空间使用缓慢

一个哥们问我 ,他们查询 表空间使用率 跑了一个多小时,这个太坑爹了,让我 帮忙优化一下。 SQL语句如下 select * from ( select ts.tablespa...
  • robinson1988
  • robinson1988
  • 2013年07月12日 16:44
  • 8118

本地管理表空间(LMT)与自动段空间管理(ASSM)概念(未看)

本地管理表空间(LMT)与自动段空间管理(ASSM)概念 创建表空间时,extent management local 定义本地管理表空间(LMT),segment space manageme...
  • haiross
  • haiross
  • 2013年10月08日 16:42
  • 4398
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:[Tip] 如何管理table 使用的空间
举报原因:
原因补充:

(最多只允许输入30个字)