Sunday, April 19, 2015

OLTP compression and Updates

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.


Martin Preiss said...

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 (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

Philippe said...

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