Enter value for input_table_name: t
Enter value for input_owner: scott
NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
100 387 125 7921 0 262 0 03-NOV-11 NO
SQL> set autotrace traceonly
SQL> select count(*) from t; -->SQL的执行计划中预估的值准确，为100行
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 86 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 100 | 86 (0)| 00:00:02 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
375 consistent gets -->consistent gets的值仍然为375，并没有下降
0 physical reads
SQL> set autotrace off;
SQL> alter table t enable row movement; -->启用row movement
SQL> alter table t shrink space cascade; --> 实施shrink space
SQL> alter table t disable row movement;
SQL> exec dbms_stats.gather_table_stats('SCOTT','T');
SQL> analyze table t compute statistics;
SQL> @Tab_Stat -->此时对象上的统计信息已发生变化，已使用的块为4块，空闲块为4块
Enter value for input_table_name: t
Enter value for input_owner: scott
NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA
---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---
100 4 4 7921 0 259 25 03-NOV-11 NO
SQL> set autotrace traceonly
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 100 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets -->表段收缩之后，consistent gets由375下降为6
0 physical reads
SQL> truncate table t; -->使用表截断技术(turncate table)
Table truncated.
SQL> exec dbms_stats.gather_table_stats('SCOTT','T'); -->收集统计信息
PL/SQL procedure successfully completed.
SQL> select count(*) from t; -->此时执行计划中的rows变为1
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 1 | 2 (0)| 00: