03. 02. 2019.

Starting with Oracle Unit Testing Framework – Part 1

For developers that are using Scrum methodology, it is not important to stress importance of unit tests when developing a new piece of code. Unit tests are important for many reasons like: can find problems in just developed/changed code at the earliest stage (before testing team) are the smallest testable part of application cover just […]

04. 01. 2019.

How to efficiently debug PL/SQL code

Almost every day I remember proverb/adage: if you haven’t discovered anything new for a while, it doesn’t mean you know everything and there is nothing left that you can learn. It only means you stop learning. I started with SQL Developer when it was internal and later alpha project inside the Oracle with a code […]

19. 11. 2018.

LOB data type side effect on DML trigger based auditing

I’ve been listening scary stories about all kind of issues related to LOB data types (there are many data types based on the LOB concept like Spatial, CLOB, BLOB, XML…). In this article I’ll put some light on one of the lesser known consequences related to mechanism how LOB works under the hood. To solve […]

24. 08. 2018.

Performance tuning by using optimistic instead of pessimistic locking

Even though Oracle Db started with optimistic locking many years back, old fashion programming style can still be observed too often. In this article I’ll explain why you should avoid using pessimistic locking as mach as possible, and will show one of the alternative way to do the same task by using optimistic locking. I’ll […]

29. 05. 2017.

ORA 7445 error and plsql_optimize_level

This is the typical example when compiler during the compilation of the source code, can introduce bugs, as Oracle has to re-arrange the code and make many modifications (optimizations like inline expansion etc.) of the code to achieve better performance. With higher level of optimization, compiler modifications will make will be more intrusive. The same […]

02. 05. 2017.

Example of how 10x technically faster code is 1000x slower in production

Recently I’ve solved this interesting issue on Oracle Retail production (code is from RPM – Retail Price Management). After upgrading Oracle Retail I found problematic part of code that is running fine in the old version of the RPM. In new version, developer has correctly changed the following part of code with much faster version […]

12. 12. 2016.

How to trace execution of PL/SQL code for target application that uses connection pool

Here is a problem: I need to track all PL/SQL code that application of interest (Oracle Retail Price Management in this case) is executing, where I need to find out not only all anonymous code along with stored procedures/packages/functions that have been executed, but also the sequence of execution (time of execution). In this case, […]

10. 10. 2016.

Compound triggers performance and use cases

Let’s assume you have to track changes in stage table (in this case tstage table) and insert all inserted rows in trigger_test table. Once of your logical choices is to create a trigger (there are a couple of other options related to AQ/streams). Before 11g version, you can create before/after row trigger (where later is […]

25. 02. 2016.

PL/SQL vs Java for Data intensive tasks

This is the second part of my previous article, where I’ve compared PL/SQL with Java on CPU intensive tasks. I’ll use simple task of inserting values into the table with LOB columns, calculating the length of LOB, trim the LOB and re-calculate the length again. Although not realistic, test case is good enough to show […]

09. 02. 2016.

PL/SQL vs Java vs C vs Python for CPU intensive tasks – architectural decision

Main goal of this article is to show performance comparison mainly between PL/SQL and Java (running inside and outside of the Oracle Db), with included C and Python code just to be able to compare results. This may hopefully help you to decide where you are going to put Java CPU intensive code when you’ll […]