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


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.

Thursday, April 2, 2015

12c adr support

I noticed that with the upgrade of GI to 12c some new things are in the ADRCI
 the scan listeners are know under adrci control as well, 
In 12c,(12.1.0.2.4)

adrci

ADRCI: Release 12.1.0.2.0 - Production on Tue Mar 31 18:17:21 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/grid"
adrci> show control
DIA-48448: This command does not support multiple ADR homes

adrci> show homes
ADR Homes:
diag/tnslsnr/node2/listener_clone
diag/tnslsnr/node2/listener_scan2
diag/tnslsnr/node2/listener_scan1
diag/tnslsnr/node2/listener
diag/tnslsnr/node2/listener_scan3
diag/tnslsnr/node2/listener_dg
diag/tnslsnr/node2/mgmtlsnr
diag/rdbms/_mgmtdb/-MGMTDB
diag/crs/node2/crs
diag/asm/+asm/+ASM2
diag/asm/user_grid/host_4252752997_82
adrci> set home diag/tnslsnr/node2/listener


on 11g this was not the case


while on 11204 …

ADRCI: Release 11.2.0.4.0 - Production on Tue Mar 31 18:21:18 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/grid"
adrci> show homes
ADR Homes:
diag/asm/+asm/+ASM2
diag/tnslsnr/other_node2/listener
diag/tnslsnr/other_node2/listener_dg
diag/tnslsnr/other_node2/listener_clone