oracle 学习笔记使用Shrink 降低高水位

原创

jsj_007 2012-06-07 23:04:00 博主文章分类：ORACLE ©著作权

文章标签 Shrink High Water Mark 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者jsj_007的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';

TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 201653 1000

SQL> set autotrace traceonly ;
SQL> select * from t;

201653 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 201K| 5513K| 227 (4)| 00:00:03 |
| 1 | TABLE ACCESS FULL| T | 201K| 5513K| 227 (4)| 00:00:03 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
156 recursive calls
0 db block gets
14384 consistent gets ---逻辑读
0 physical reads
0 redo size
8596453 bytes sent via SQL*Net to client
148258 bytes received via SQL*Net from client
13445 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
201653 rows processed

SQL> select * from t;

201653 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14368 consistent gets
0 physical reads
0 redo size
8596453 bytes sent via SQL*Net to client
148258 bytes received via SQL*Net from client
13445 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
201653 rows processed

SQL> set autotrace off;
SQL> delete from t where rownum<=30000;

30000 rows deleted.

SQL> commit;

Commit complete.

SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';

TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 201653 1000

SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T' ,estimate_percent => 100 ,cascade => true);

PL/SQL procedure successfully completed.

SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';

TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 171653 1000
----数据已经删除，HWM并未下降。
SQL> set autotrace traceonly;
SQL> select * from t;

171653 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 171K| 4693K| 226 (3)| 00:00:03 |
| 1 | TABLE ACCESS FULL| T | 171K| 4693K| 226 (3)| 00:00:03 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12375 consistent gets -----逻辑读较之前有下降。
0 physical reads
0 redo size
7323608 bytes sent via SQL*Net to client
126258 bytes received via SQL*Net from client
11445 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
171653 rows processed
SQL> set autotrace off;
SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table t enable row movement;

Table altered.

SQL> alter table t shrink space;

Table altered.

SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T' ,estimate_percent => 100

PL/SQL procedure successfully completed.

SQL> SELECT table_name,num_rows,blocks FROM dba_tables where table_name='T' and owner='SYS';

TABLE NUM_ROWS BLOCKS
----- ---------- ----------
T 171653 791

SQL> set autotrace traceonly;
SQL> select * from t;

171653 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 171K| 4693K| 181 (4)| 00:00:03 |
| 1 | TABLE ACCESS FULL| T | 171K| 4693K| 181 (4)| 00:00:03 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
156 recursive calls
0 db block gets
12208 consistent gets
0 physical reads
0 redo size
7323608 bytes sent via SQL*Net to client
126258 bytes received via SQL*Net from client
11445 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
171653 rows processed

Shrink 可以降低高水位，减少逻辑读。