- 浏览: 246276 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
nodonkey:
貌似还是不行,再等等吧,amfphp要出2.0了
amfphp1.9与php5.3.X版本不兼容 -
live711:
请问amfphp与php5.3.X搭配能用了吗?
amfphp1.9与php5.3.X版本不兼容 -
zhousheng193:
非常感谢!
安装flash player debug版本遇到的一些问题 -
sp42:
谢谢提示,我遇到的也是不能加密,用MAC地址代替之。
DI-624+A路由器韧体升级解决经常掉线的问题(转) -
心似海:
不错,要挖去了,哈哈
深入sql之merge into
转自:http://hi.baidu.com/kywinder/blog/item/f96e62d4aa69c82306088b6f.html
案例:
同事将一关键表中删了多余的300w条数据后,程序就变的异常缓慢。分析得出,应该是表空间碎片过多,旧的索引效率过低。
执行下面两句话:
alter table ycsbt_qyygxx_jb move;
alter index R_SBXX_YCSBD_FK rebuild online;
效果非常明显。
deltete不会释放表空间,但是可以重用,也就是插入可以填补空洞,当然现实应用中确实是存在经常删除很少插入的情况,这样就存在了释放表空间优化数据库的可行性了,truncate有不能带条件的缺陷,自然就想到用alter table move重移表空间的方法。这里要注意三个要素 1、 alter table move 省略了tablespace XXX, 表示用户移到自己默认的表空间,因此当前表空间至少要是该表两倍大,这很好理解,由于易错所以提出,就不再细说了。 2、 alter table move过程中会导致索引失效,必须要考虑重新索引 3、 alter table move过程中会产生锁,应该避免在业务高峰期操作! 就第二点和第三点做实验说明如下吧 Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as ljb 先获取该SESSION的SID,方便实验观察 SQL> select sid from v$mystat where rownum=1; SID -------------------- 160 SQL> create table ljb_test as select * from dba_objects; Table created SQL> select count(*) from ljb_test; COUNT(*) ------------------- 62659 SQL> create index idx_test on ljb_test(object_id); Index created 查询当前该SESSION并无锁 SQL> select * from v$lock where sid=160; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------------------------------------- 查看索引状态也正常! SQL> select index_name,table_name,status from user_indexes where table_name='LJB_TEST'; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ----------------------------------------------- IDX_TEST LJB_TEST VALID alter table ljb_test move; 重新再开一个窗口 执行如下命令,发现锁已经产生了 select * from v$lock where sid=160; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ------ ---- ------- ---------- ------ -------- ------ ------------------------------------------------------------------ 2043451C 20434530 160 CF 0 0 4 0 0 0 1FA072BC 1FA073D8 160 TX 917534 592 6 0 1 0 204344C0 204344D4 160 HW 76 323783147 6 0 0 0 1F9C4224 1F9C423C 160 TM 84825 0 6 0 0 0 204342F4 20434308 160 TT 76 16 4 0 0 0 1F9C377C 1F9C37C4 160 TS 76 323783147 6 0 0 0 不过由于alter table move命令未结束,索引仍然有效! SQL> select index_name,table_name,status from user_indexes where table_name='LJB_TEST'; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ---------------------------------------------------- IDX_TEST LJB_TEST VALID 等alter table ljb_test move;命令结束后,再查看发现锁消失了 SQL> select * from v$lock where sid=160; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ------------------------------------------ 但是索引却失效了! SQL> select index_name,table_name,status from user_indexes where table_name='LJB_TEST'; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ---------------------------------------------------- IDX_TEST LJB_TEST UNUSABLE 总结:这个实验说明:除了知道alter table move命令可以释放空间(当然这语句最根本的作用还是移动表到不同的表空间去,这里只是借用它可以释放空间的一个特性),还要了解该动作会锁表直到命令结束,而且会导致索引失效,属于危险命令,建议千万不要在业务高峰期操作。
都知道alter table move 或shrink space可以收缩段,用来消除部分行迁移,消除空间碎片,使数据更紧密,但move 跟shrink space还是有区别的。
Move会移动高水位,但不会释放申请的空间,是在高水位以下(below HWM)的操作。
而shrink space 同样会移动高水位,但也会释放申请的空间,是在高水位上下(below and above HWM)都有的操作。
也许很难理解吧,看测试就知道了。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> col TABLE_NAME for a10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
--TEST表初始分配了10M的空间,可以看到有10个EXTENTS,1280个BLOCKS。USER_TABLES视图显示有0个使用的BLOCKS,1280个空闲BLOCKS,即该10M空间内的BLOCK都还没被ORACLE”格式化”。
SQL> begin
2 for i in 1..100000 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
--插入10W条数据后,分配的空间仍不变,因为10个EXTENTS还没使用完。显示使用了186个BLOCKS,空闲1094个BLOCKS。这时候的186BLOCKS即是高水位线
SQL> delete from test where rownum<=50000;
50000 rows deleted.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
77
--这边可以看到,删掉一半数据后,仍然显示使用了186个BLOCKS,高水位没变。但查询真正使用的BLOCK数只有77个。所以DELETE操作是不会改变HWM的
SQL> alter table test move;
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 1199
--MOVE之后,HWM降低了,空闲块也上去了
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
--但是分配的空间并没有改变,仍然是1280个BLOCKS。下面看用SHRINK SPACE的方式
SQL> alter table test enable row movement;
Table altered.
SQL> alter table test shrink space;
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 1 88
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 7
--分配的空间已经降到最小,1个EXTENTS ,88个BLOCKS
所以MOVE并不算真正意义上的压缩空间,只会压缩HWM以下的空间,消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK),也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩,包括初始分配的也压了,所以它是blow and above HWM操作。
至于需要哪种方法,得看你的需求来了,需要分析表的增长情况,要是以后还会达到以前的HWM高度,那显然MOVE是更合适的,因为SHRINK SPACE还需要重新申请之前放掉的空间,无疑增加了操作。
注意:
1.不过用MOVE的方式也可以做到真正的压缩分配空间,只要指定STORAGE参数即可。
SQL> drop table test;
Table dropped.
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
SQL> alter table test move storage (initial 1m);
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 16 128 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 128
2.使用move时,会改变一些记录的ROWID,所以MOVE之后索引会变为无效,需要REBUILD。
3.使用shrink space时,索引会自动维护。如果在业务繁忙时做压缩,可以先shrink space compact,来压缩数据而不移动HWM,等到不繁忙的时候再shrink space来移动HWM。
4.索引也是可以压缩的,压缩表时指定Shrink space cascade会同时压缩索引,也可以alter index xxx shrink space来压缩索引。
5.shrink space需要在表空间是自动段空间管理的,所以system表空间上的表无法shrink space。
发表评论
-
mysqlsla来分析MYSQL的性能及索引
2011-01-17 19:56 1320— Slow log: mysqlsla -lt slow ... -
六款常用mysql slow log分析工具的比较
2011-01-17 19:06 1279转自:http://www.iteye.com/topi ... -
MySQL的大小写敏感性
2011-01-12 14:05 1055转自: http://www.zeali.net/ent ... -
如何查看mysql的版本
2010-05-22 11:52 22036如果我们想要查看mysql数据库的版本有以下四种方法: ... -
MySQL 数据库的备份和恢复
2010-03-19 13:44 925转自 忧里修斯 http://tec ... -
mysql使用show命令以及replace函数批量修改数据
2010-03-19 13:37 2413一.mysql的show命令 a. show tables或 ... -
MySQL中的ROWNUM的实现
2010-01-26 13:43 2205本文转自 http://blog.csdn.net/ACMA ... -
一个MySQL死锁问题的分析及解决
2010-01-20 12:50 1259转自http://java-guru.iteye.com/bl ... -
MySQL 死锁分析
2010-01-20 12:42 33411.MySQL锁和死锁的理解: ... -
sql 按指定顺序排序
2010-01-19 10:53 21831、在ORACLE中使用Decode Decode实 ... -
MYSQL 事务管理
2009-10-26 19:48 1064mysql_query("BEGIN"); ... -
delete 符合条件的记录中的前几条或者重复记录
2009-09-03 20:04 2072今天写代码,遇到了这个问题,只能删除符合条件的记录中的某几条. ... -
sql update delete 中 使用 inner join
2009-08-24 11:38 7198SQL中使用update inner join和delet ... -
What is the difference between VARCHAR, VARCHAR2 ?
2009-06-01 09:43 933Both CHAR and VARCHAR2 types ar ... -
Oracle index
2009-05-15 10:50 0索引是一种可以提高查 ... -
Views and Materialized Views 整理
2009-04-10 14:29 1144Views and Mat ... -
IN and EXISTS, NOT IN AND NOT EXISTS
2009-04-10 14:28 1442Functionally, they are the same ... -
ORACLE 之 TRUNCATE TABLE
2009-03-30 16:49 1985TRUNCATE Caution: Y ... -
深入sql之merge into
2009-01-08 16:38 4782转自 逆水流沙 http://hi.baidu.com/wen ... -
Oracle日期函数操作(收集整理版)
2008-12-04 16:50 2797经常在平时的开发中要用到oracle的日期函数,每次都要上 ...
相关推荐
与`ALTER TABLE T MOVE`相比,`SHRINK SPACE`不需要创建新的表,因此对系统的影响相对较小。但是,它无法解决内部碎片,而且在某些情况下,如果表有大量空洞或者数据分布不均匀,可能无法有效回收空间。 在使用`...
最后,给出了几种降低 HWM 的方法,包括 alter table move、shrink space、数据复制、exp/imp 以及 deallocate unused 等方法。 适合人群:具备 Oracle 数据库基础的数据库管理员和技术人员。 使用场景及目标:帮助...
与ALTER TABLE MOVE相比,SHRINK命令具有明显优势: - 不需要重建索引 - 可以在线操作 - 不需要额外的空闲空间,而ALTER TABLE MOVE通常需要与当前表一样大小的空闲空间。 总之,合理地整理表碎片不仅可以提高...
ALTER TABLE table_name SHRINK SPACE KEEP INDEX; ``` #### 解决高水位带来的空间问题 1. **SHRINK SPACE**:除了用于解决性能问题外,还可以使用`SHRINK SPACE`来回收未使用的空间,减少表的存储需求。 ```...
启用行移动后,当执行`ALTER TABLE SHRINK SPACE`时,Oracle会自动移动行到新的位置,从而重置高水位线。 - **收缩表空间**: ```sql ALTER TABLE table_name SHRINK SPACE; ``` 这条命令用于收缩表空间,即...
3. **使用`ALTER TABLE <table_name> SHRINK SPACE`命令**: - **命令格式**:`ALTER TABLE <table_name> SHRINK SPACE;` - **功能**:这个命令可以通过整理表中的数据,减少表所占用的空间,从而降低高水位。 - ...
SHRINK SPACE`等命令来重新组织数据,并释放不再使用的空间。 通过上述方法和技术的应用,可以有效地提升Oracle数据库的整体性能,并简化其管理复杂度。这对于任何需要处理大量数据的企业来说都是至关重要的。
通过 `ALTER DATABASE DATAFILE SHRINK SPACE` 或 `ALTER TABLE MOVE` 可以减少表空间占用的空间。 5. **设置表空间为只读或只写**: `ALTER TABLESPACE SET READ ONLY` 或 `ALTER TABLESPACE SET READ WRITE` ...
ALTER TABLE 表名 ADD (列说明列表) 例:为test表增加一列Age,用来存放年龄 sql>alter table test add (Age number(3)); 修改基表列定义命令如下: ALTER TABLE 表名 MODIFY (列名 数据类型) 例:将test...
一旦数据被删除,HWM并不会下降,除非使用如DBMS_REDEFINITION或ALTER TABLE ... MOVE等高级操作。这导致即使有大量空闲空间,数据文件也无法收缩,因为HWM不允许新数据插入到HWM以下的块中。 为了分析数据文件的...