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
准备:
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|
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|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26764973/viewspace-1378654/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26764973/viewspace-1378654/