Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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?

share|improve this question

migrated from stackoverflow.com Sep 3 '12 at 2:46

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
Thanks. What about reusing space of deleted blocks? I hope Oracle reuses deleted blocks by inserting new data there :) – Centurion Sep 3 '12 at 6:32
Also, have noticed all major DBMS have some sort of shrinking feature (MySQL - Optimize table, PostgreSQL - vacuum). A colleague told me one case. He had to administrate ~1GB PostgreSQL DB. The database was heavily used with updates by modifying almost all data. The problem was the database was constantly growing about ten times every 2.5 month. They have used PostgreSQL 8.3 + CentOS. So, he tried to use AUTOVACUUM but the process was very slow, so instead he was doing database dump export, destroying the old one, recreating database and importing. Then he was able to reduce its size ~10 times – Centurion Sep 3 '12 at 6:35
1  
Yes, Oracle reuses space of deleted data. You say major DBMS and then you mention only mysql and postgres...sigh... anyways, not sure what is your point with your last comment. As I mentioned earlier, oracle does not on its own shrink size of a table but a developer/dba can do that. But real question you have to ask yourself is what is the advantage of shrinking the table if it is going to grow again? Is the shrink worth the efforts? And once again, do not assume anything about oracle based on any (so-called) major DBMS behaviour. Read the concepts and understand how oracle works. – Narendra Sep 3 '12 at 8:41
I have developed Oracle stored procedures/functions in the past, but as a developer, I have never faced table shrink problems, maybe because that was a responsibility of DBAs. Now, a talk with my current colleague about 1 GB PostreSQL DB becoming 10GB made me to think, does the same problem exist on all databases? Also, his decision to drop and recreate whole DB instead of long vacuum process seemed very odd to me at first, but now I got an insight about HWM and shrinking cost and I understand why he did that. Oracle DB is not an exception and it also faces the same problems as other RDBMS. – Centurion Sep 4 '12 at 11:55
Actually, it's a little bit strange why PostreSQL DB was expanding from 1GB to 10GB (with more or less the same actual data size). If after table drop+recreate DB size was reducing from 10G to 1GB then it seems there might was a bug or something because theoretically DBMS should reuse deleted blocks for the insertions of new data. At least, that's how Oracle is/should be working. – Centurion Sep 4 '12 at 12:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.