- 論壇徽章:
- 0
|
創(chuàng)建表
create table my_object as select * from dba_objects;
分析my_object表咱用塊數(shù)
analyze table my_object compute statistics;
select table_name,tablespace_name,blocks from user_tables where table_name='MY_OBJECT';
TABLE_NAME TABLESPACE_NAME BLOCKS
------------------------------ ------------------------------ ------------------------------ ------------------------------
MY_OBJECT SYSTEM 685
占用數(shù)據(jù)庫685
set autotrace traceonly;
SQL> select count(*) from my_object;
執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 3447259112
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 154 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MY_OBJECT | 49874 | 154 (2)| 00:00:02 |
------------------------------------------------------------------------
統(tǒng)計信息
----------------------------------------------------------
124 recursive calls
0 db block gets
700 consistent gets
690 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到有690 physical reads
刪除一部分數(shù)據(jù)
delete my_object where rownum<15000；
從新分析該表
analyze table my_object compute statistics;
select table_name,tablespace_name,blocks from user_tables where table_name='MY_OBJECT';
TABLE_NAME TABLESPACE_NAME BLOCKS
------------------------------ ------------------------------ ----------
MY_OBJECT SYSTEM 685
會發(fā)現(xiàn)該表還是685塊
再次運行
SQL> select count(*) from my_object;
執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 3447259112
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 154 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MY_OBJECT | 49874 | 154 (2)| 00:00:02 |
------------------------------------------------------------------------
統(tǒng)計信息
----------------------------------------------------------
1 recursive calls
0 db block gets
690 consistent gets
0 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從以上的試驗看到，雖然我們刪除了15000行數(shù)據(jù)，可是在查詢該表的時候還是會查詢690個塊。
我們蘇要手工回收刪除的空間。
手工回收方法很多，我現(xiàn)在用ORACLE 9I以上版本手工回收空間
alter table my_object enable row movement;
alter table my_object shrink space;
然后執(zhí)行
SQL> select count(*) from my_object;
執(zhí)行計劃
----------------------------------------------------------
Plan hash value: 3447259112
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 105 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| MY_OBJECT | 31950 | 105 (1)| 00:00:02 |
------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統(tǒng)計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
480 consistent gets
469 physical reads
0 redo size
410 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到 469 physical reads
analyze table my_object compute statistics;
select table_name,tablespace_name,blocks from user_tables where table_name='MY_OBJECT';
TABLE_NAME TABLESPACE_NAME BLOCKS
------------------------------ ------------------------------ ----------
MY_OBJECT HWM 468 |
|
免費注冊
發(fā)表于 2008-08-17 00:04
