This content has been marked as final. Show 5 replies
There is very little, if any, effect on performance. In most cases, performance actually improves since fewer blocks are read physically.
Check out the white papers here - http://www.oracle.com/technetwork/database/options/compression/index.html
AFAIK, Compression in 10gR2 is only for Direct Path operations, not for OLTP.
Hemant K Chitale
Yes, I have read a lot of documentation related to the improvement related to the physical reads, but the facts do not show that. I have a 200m rows table, with a lot of FTS, and between compression and not compression there are not performance differences.
My experience with compression is that it often improves OLTP operations but I say that with a caveat. First the standard declaration that "it depends." You've given no indication what version of 10gR2 nor the percentage of compression, nor table width, or other relevant factors. Second you've not provided sufficient information to indicate whether the time being spent is on table scans or index reads, etc. But equally important ... 10gR2 is in desupport mode .... move to 11gR2. Things work better with newer software.
OLTP compression is Advanced Compression option but from 11g db. Also this kind compression has a penalty of updates - so it depends on the type of load - a lot of selects, inserts? it is fine. but for updates you will suffer.