求解：为什么一个只有四行的表物理读却有近三万次？

Yong Huang

wantfly-pb,

Thanks for the pointer to Tom's Q&A. The words you quoted are about the resource usage of actual moving of data. That is, once you give the permission for row movement, moving the rows (as in case of alter table shrink space) will "consume processing resources...". My question is, Is leaving a table row movement enabled a bad idea? I think his answer at "March 10, 2009 - 2am" is what I need, "Any overhead? No, it is just permission to change a rowid if you do an operation that would cause a rowid to change - like...".

By the way, shrinking a table does not change the index to unusable as moving a table does. In fact, shrinking a table doesn't change the table's last_ddl_time either (but will change the index's), and doesn't invalidate the cursor using the table. It's a strange DDL in this respect.

Yong Huang

Yong Huang

> 有没有什么方法可以自动产生一个报表，来报告这种高水位问题，监控哪一张表有高水位问题，将IO浪费减到最小呢？

If your tablespace is ASSM, you can use dbms_space.space_usage to check space usage. (If it's non-ASSM, use free_space procedure.) I have the simple code at the bottom of http://yong321.freeshell.org/oranotes/Dbms_Space.txt

You could choose to dump the segment header of each segment you want to check (maybe only those big ones). But then you have to write code in your script to convert that hwm hex number to file and block number and do some judgement. It's doable. But it's easier to just check for a great amount of empty space with space_usage procedure.

Yong Huang

宋夕夕

sundog315

wantfly-pb 发表于 2011-12-13 10:53
对这个问题的思考：

这种高水位的现象，假设test表中原来有一千万笔数据，经过多次新增，删除操作后，目 ...

生产情况下，如果有合理的索引，一般大表都采用索引扫描的方式，因此，数据分散分布不会带来太大的影响。

如果是典型的OLAP，一般都是批量加载数据的，也很难产生大量的碎片。

napolone1983

wantfly-pb 发表于 2011-12-13 10:53
对这个问题的思考：

这种高水位的现象，假设test表中原来有一千万笔数据，经过多次新增，删除操作后，目 ...

高水位问题只会发生在全表扫描中，一般情况下，对索引查询影响不大。
有个问题没搞明白，既然段头中保存着最后一个使用块的信息，在全表扫描的情况下至少应该从最后一个数据块往下扫描，而不应该从HWM开始吧？

Yong Huang

> 在全表扫描的情况下至少应该从最后一个数据块往下扫描，而不应该从HWM开始吧？

What do you mean? A full table scan starts from the first block until the last (i.e. HWM), not from the last till somewhere.

Yong Huang