1、透過user_table_statistics 檢視行遷移數量
準備：
analyze table MPI_PSYCOMPARETEMP compute statistics;
收集統計資訊
select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tab_statistics where TABLE_NAME='MPI_PSYCOMPARETEMP'
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
----------   ---------- ------------    ---------- ----------   -----------
24199     370 14    1693    7491   76
CHAIN_CNT 為：7491
AVG_SPACE 為：1693
select * from MPI_PSYCOMPARETEMP
Plan
SELECT STATEMENT ALL_ROWSCost: 103 Bytes: 1,669,731 Cardinality: 24,199
1 TABLE ACCESS FULL TABLE HZWSJ.MPI_PSYCOMPARETEMP Cost: 103 Bytes: 1,669,731 Cardinality: 24,199

2、create table MPI_PSYCOMPARETEMPBAK AS SELECT  * FROM MPI_PSYCOMPARETEMP
analyze table MPI_PSYCOMPARETEMPBAK compute statistics;
select NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN from user_tab_statistics where TABLE_NAME='MPI_PSYCOMPARETEMPBAK'

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
----------   ----------    ------------ ---------- ----------    -----------
24199     270    114   854    0    74
可以看出新建立的表分配的塊為270個比原先的少100個，並且空餘塊 114 比原先多了100 平均空間少了1693 變為854 行遷移為零，平均行長度為74 少2.
接下來我們看
select * from MPI_PSYCOMPARETEMPBAK
Plan
SELECT STATEMENT ALL_ROWSCost: 76 Bytes: 1,669,731 Cardinality: 24,199
1 TABLE ACCESS FULL TABLE HZWSJ.MPI_PSYCOMPARETEMPBAK Cost: 76 Bytes: 1,669,731 Cardinality: 24,199

沒什麼變化。只不過cost 減少了而已。讀取的資料沒有減少。--- 這裡的讀取的塊應該是變少了但是bytes 為何一樣？

未完。

以上是透過toad的執行計劃得出的。下面是透過set autotrace traceonly 來看的。
alter system flush buffer_cache;
SELECT * FROM MPI_PSYCOMPARETEMP;
1938 consistent gets
342 physical reads

select * from MPI_PSYCOMPARETEMPbak ;

1861 consistent gets
257 physical reads
看出來真正還是發生變化的，有誤解
| Bytes |
| 1630K|
| 1630K|

行遷移測試

相關文章