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

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('SERVICE_NAME','MODULE');

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

NONCOMPRESSED

45810 rows updated.
Elapsed: 00:00:01.59

COMPRESSED

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 :

UPDATE TAB1 SET

(COLUMNS ....)

= (SELECT COLUMNS ....

FROM TAB2

WHERE TAB2.ID = TAB1.TAB2_ID)

WHERE TAB2.DATE_FIELD = TO_DATE('31/03/2015' ,'DD/MM/YYYY')

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.