Analyze table 做了什么工作？
如何降低表HWM (High Water Mark)?
| 1 | Anonlyze 只影响 user_tables的统计数据, 不影响 user_segments占用空间等参数。 Analyze Table 会分析 BLOCKS,NUM_ROWS,AVG_ROW_LEN,LAST_ANALYZED….到 user_tables analyze table t compute statistics; analyze table用来收集统计信息，作为cbo优化器的依据。数据发生大的变化后不重新收集会导致cbo判断错误，会出现性能问题。 |
| 2 | --降低HWM只有10g,11g 可以缩减表，9i不支持。 alter table T enable row movement; alter table T shrink space; |
| 3 | User_tables.blocks+empty_block = user_setments.blocks |
| 4 | Delete table 不能降低 HWM,即使表数据被全部 delete，也不会降低全表扫描的时间，表也不会释放占用空间 。 Truncate table 则把HWM降到最初始值,占用1个extents,8个blocks. Analyze table blocks=0 |
实验开始：
Create table t nologging as select * from dba_objects;
Table created;
fkz@ORCL>select count(*) from t;
COUNT(*)
----------
73188
create table t$user_tables nologging as select * from user_tables where table_name ='T';
Table created;
select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space
from t$user_tables;
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 1068 | 73188 | 97 | 2010/11/14 12:46:16 | 0 | 0 |
fkz@ORCL>delete from t where rownum<=3188;
已删除3188行。
fkz@ORCL>commit;
提交完成。
fkz@ORCL>select count(*) from t;
COUNT(*)
----------
70000
-- 第2次anayze
fkz@ORCL>analyze table t compute statistics;
表已分析。
-- 存储第二次 Analyze 的结果。
fkz@ORCL>insert into t$user_tables select * from user_tables where table_name ='T';
已创建 1 行。
fkz@ORCL>commit;
提交完成。
--查询第 二 次分析的结果
Select * from (select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space
from t$user_tables
order by last_analyzed desc)
Where rownum=1;
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 1068 | 70000 | 101 | 2010/11/15 20:34:48 | 84 | 1137 |
-- Analyze 结果分析： delete 并没有减少表所占的BLOCKS,EMPTY_BLOCK 增加, NUM_ROWS，AVG_ROW_LEN变化，LAST_ANALYZED 为最新时间。
fkz@ORCL>delete from t where rownum<=10000;
已删除10000行。
fkz@ORCL>commit;
提交完成。
fkz@ORCL>select count(*) from t;
COUNT(*)
----------
60000
-- 第3次anayze
fkz@ORCL>analyze table t compute statistics;
表已分析。
-- 存储第三次 Analyze 的结果。
fkz@ORCL>insert into t$user_tables select * from user_tables where table_name ='T';
已创建 1 行。
fkz@ORCL>commit;
提交完成。
--查询第 三 次分析的结果
Select * from (select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space
from t$user_tables
order by last_analyzed desc)
Where rownum=1;
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 1068 | 60000 | 102 | 2010/11/15 20:43:26 | 84 | 2020 |
-- 第三次Analyze 结果分析： delete仍旧没有减少表所占的BLOCKS,EMPTY_BLOCK没变, NUM_ROWS，AVG_ROW_LEN变化，LAST_ANALYZED 为最新时间。
通过分析记录，得出结论，Analyze Table 会分析 BLOCKS,NUM_ROWS,AVG_ROW_LEN,LAST_ANALYZED….
由于上面的删除并没有缩减表占用的BLOCKS,如需缩减采用如下方法：
fkz@ORCL>col segment_name for a20
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- -------------------------------
T ASSM 9437184 1152 24
fkz@ORCL>alter table T enable row movement;
表已更改。
fkz@ORCL>alter table T shrink space;
表已更改。
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- -----------------------------
T ASSM 7340032 896 22
上面的 SHRINK SPACE 只改变了user_segments的数据， 并没有改变 user_tables数据， 如改变，还需要 对表 Analyze.
fkz@ORCL>analyze table t compute statistics;
表已分析。
-- 存储第四次 Analyze 的结果。
fkz@ORCL>insert into t$user_tables select * from user_tables where table_name ='T';
已创建 1 行。
fkz@ORCL>commit;
提交完成。
--查询第 四 次分析的结果：
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 871 | 60000 | 102 | 2010/11/15 21:01:45 | 25 | 868 |
-- 第四次Analyze 结果分析： 在数据行数没有改变的情况下，表Shrink Space 后，HWM降低，占用的BLOCKS减少,EMPTY_BLOCK也减少,
NUM_ROWS，AVG_ROW_LEN没有变化(没有增删改），LAST_ANALYZED 为最新时间。
fkz@ORCL>col table_name for a10
fkz@ORCL>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
会话已更改。
fkz@ORCL>select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space
2 from t$user_tables
3 order by last_analyzed desc;
4次analyze 加一次shrink Space 后的全部结果：
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 871 | 60000 | 102 | 2010/11/15 21:01:45 | 25 | 868 |
| T | 1068 | 60000 | 102 | 2010/11/15 20:43:26 | 84 | 2020 |
| T | 1068 | 70000 | 101 | 2010/11/15 20:34:48 | 84 | 1137 |
| T | 1068 | 73188 | 97 | 2010/11/14 12:46:16 | 0 | 0 |
Anonlyze 影响 user_tables的统计数据, 不影响 user_segments占用空间等参数。
--只有10g,11g 可以缩减表，9i不支持。
alter table T enable row movement;
alter table T shrink space;
HWM是oracle中block有没有使用的分界线，它会随着数据的insert而上升，但它并不会随数据的delete而下降，因此全表扫描的时间并不因数据的delete而减少，相反可能由于块清除反而全表扫描时间增加，可以用下面方法的任一一种来降低HWM：
shrink
适用于10g以后的版本，前提是这个表，索引，物化视图或物化视图log所在表空间segment的管理是auto的，并且表已enable row movement
由以下删除10000行数据后 anolyze table 后查询 user_segments 前后比较， BYTES,BLOCKS,EXTENTS 跟存储空间有关系的参数都没有任何改变。
Anolyze 只是分析的出结果，并不改变Block , extents 等参数。
fkz@ORCL>delete from t where rownum<=10000;
已删除10000行。
fkz@ORCL>commit;
提交完成。
fkz@ORCL>select count(1) from t;
COUNT(1)
----------
50000
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ------------------------------
T ASSM 7340032 896 22
--第五次Analyze table
fkz@ORCL>analyze table t compute statistics;
表已分析。
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ------------------------------
T ASSM 7340032 896 22
insert into t$user_tables select * from user_tables where table_name ='T';
Commit;
select table_name,blocks,num_rows,avg_row_len,last_analyzed,empty_blocks,avg_space
from t$user_tables
order by last_analyzed desc;
--查询第 五 次分析的结果：
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 871 | 50000 | 102 | 2010/11/15 22:13:00 | 25 | 2026 |
| T | 871 | 60000 | 102 | 2010/11/15 21:01:45 | 25 | 868 |
| T | 1068 | 60000 | 102 | 2010/11/15 20:43:26 | 84 | 2020 |
| T | 1068 | 70000 | 101 | 2010/11/15 20:34:48 | 84 | 1137 |
| T | 1068 | 73188 | 97 | 2010/11/14 12:46:16 | 0 | 0 |
fkz@ORCL>delete from t;
已删除50000行。
fkz@ORCL>commit;
提交完成。
fkz@ORCL>select count(*) from t;
COUNT(*)
----------
0
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ----------
T ASSM 7340032 896 22
--第六次Analyze table
fkz@ORCL>analyze table t compute statistics;
表已分析。
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ----------
T ASSM 7340032 896 22
-- 保存第六次分析的结果
fkz@ORCL>insert into t$user_tables select * from user_tables where table_name ='T';
已创建 1 行。
fkz@ORCL>commit;
提交完成。
fkz@ORCL>truncate table t;
表被截断。
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='T';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ----------
T ASSM 65536 8 1
-- Truncate table 后 user_segments 也已经降到最低的 extents=1 blocks=8 bytes=8*8192=65536
--第七次Analyze table
fkz@ORCL>analyze table t compute statistics;
表已分析。
-- 保存第七次分析的结果
fkz@ORCL>insert into t$user_tables select * from user_tables where table_name ='T';
已创建 1 行。
fkz@ORCL>commit;
提交完成。
--查询第 七 次分析的结果：
| TABLE_NAME | BLOCKS | NUM_ROWS | AVG_ROW_LEN | LAST_ANALYZED | EMPTY_BLOCKS | AVG_SPACE |
| T | 0 | 0 | 0 | 2010/11/15 22:31:05 | 8 | 0 |
| T | 871 | 0 | 0 | 2010/11/15 22:28:29 | 25 | 7908 |
| T | 871 | 50000 | 102 | 2010/11/15 22:13:00 | 25 | 2026 |
| T | 871 | 60000 | 102 | 2010/11/15 21:01:45 | 25 | 868 |
| T | 1068 | 60000 | 102 | 2010/11/15 20:43:26 | 84 | 2020 |
| T | 1068 | 70000 | 101 | 2010/11/15 20:34:48 | 84 | 1137 |
| T | 1068 | 73188 | 97 | 2010/11/14 12:46:16 | 0 | 0 |
--Truncate table 后 analyze block=0,num_rows=0,empty_block=8
fkz@ORCL>select count(*) from tobj;
COUNT(*)
----------
40
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='TOBJ';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ----------
TOBJ ASSM 65536 8 1
fkz@ORCL>truncate table tobj;
表被截断。
fkz@ORCL>select segment_name,segment_subtype,bytes,blocks,extents
2 from user_segments
3 where segment_name='TOBJ';
SEGMENT_NAME SEGMENT_SU BYTES BLOCKS EXTENTS
-------------------- ---------- ---------- ---------- ----------
TOBJ ASSM 65536 8 1
实验环境Win7 Ultimate Oracle 11g r2 11.2.0.1.0：
fkz@ORCL>select * from v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
