I have several questions:
1) If Oracle manages both High-water Mark and Low High-water Mark in ASSM tablespace then do we have to think about that when thinking about performance? I mean, does Oracle manages deleted blocks itself and does not scan those blocks during table full scan, or do we still have to shrink those tables (alter table mytable shrink space;) in order to clean those deleted blocks and increase performance?
2) If ASSM gives automatisation for this and other related stuff then no doubtfully there's a performance trade-offs when choosing ASSM instead of MSSM. What should be major criteria on choosing ASSM or MSSM?
UPDATE. I did a test on my laptop with mytable (ASSM). First, inserted 1.000.000 records. Query "select count(*) from mytable" was executing at average 0.05 s. Then did full DELETE. The same query was executing at average 0.03 s, so maybe Oracle was still reading deleted blocks. Only after TRUNCATE the query executed at average 0.001 s. Maybe ASSM takes time to shrink table space, or still I have manually to shrink it?
Best Answer
ASSM is not about reducing high water mark of the table. The main benefit of ASSM is it automates the task of freelist management in order to handle the concurrent DML on the table. Freelists are used to allocate new blocks when data is added to the table. You will be able to see the performance benefits of ASSM when multiple sessions try to populate the data concurrently into a table. As for the issue of deleted blocks and its performance impact, the answer is it depends. ASSM or any other oracle feature does not automatically remove blocks from table when data is deleted. If you delete large amount of data from a big table and do not explicitly shrink the table and if your queries mostly do full table can, then you will not see any performance benefit by deleting the table data. Even if you decide to shrink the table to get immediate performance benefit, you will need to think about how quickly new data will be added to the table and the table will grow back, basically a cost-benefit analysis. Again, a better explanation of this can be found in oracle documentation and in books like Expert Oracle Database Architecture authored by Tom Kyte.