I did some small tests (actually more troubleshooting ) with OLTP compression, a couple of weeks back.

Some execution times nearly doubled, I wanted to get a clear understanding of what happened so I asked to trace the procedure.

Which was easy since dbms_application.set_module could be used in the procedure to set the module and the action.

This made it much less cumbersome to enable the trace, the  dbms_monitor package has a procedure which allows you to start tracing whenever the combination of service_name and module is used very handy

so in the end I used this to trace


That made it easy to trace as soon as the procedure was launched and avoid to play around with logon triggers and trace more, then necessary.

I did the tests both with a warmed up DB cache as with a flushed db cache and will do them again next week the results were in the same league

But back to the problem, speed of the query was much slower.

I checked both


45810 rows updated.
Elapsed: 00:00:01.59


45810 rows updated.
Elapsed: 00:00:04.38

Quite a difference …

The query looks a bit like this I had to obfuscate because of privacy reasons :


(COLUMNS ....)





This is how the trace file looks like after being processed with Method R profiler a tool i really can recommend, it save me lots of time, time you can use to solve you performance issue, instead of crawling through raw trace files …

Here You see the uncompressed trace

here is the compress trace
Much more blocks in Current mode
In both cases the last step of the execution plan takes quite a bit
the last step of the compressed update took ; ~ 3,25 sec (4,37 -0,34 ….)
the last step of the non compressed update ;  ~ 0,77 sec
So unbelievable that I will redo some tests again next week and add them to the post.

2 thoughts on “OLTP compression and Updates

  1. Philippe,
    just a wild guess: maybe you get some row migration problems with the updates in the OLTP compressed table. Though OLTP compression should be (according to the documentation) able to compress during updates – it seems not to be. At least that's what Jonathan Lewis says in http://allthingsoracle.com/compression-in-oracle-part-3-oltp-compression/ (and there are some articles in Randolf Geist's blog showing the same problem). Only if there is a mix of updates and inserts the compression works: so an updates unpacks the formerly compressed row and this may lead to a row migration.


    Martin Preiss

  2. Philippe says:

    thank you martin to come by indeed that might be the issue the idea was to investigate further but had no time yet.

    thx for the links


Leave a Reply