17. 05. 2019.

How to change the execution plan without changing the code in Oracle 10g

Recently I had an interesting case when I’ve been asked how to change the execution plan of one complex query without changing the code. I’ve already had a lecture with similar title at 2012 in Oracle User Group Conference – more details you can find at the following link: How to change the execution plan […]

01. 03. 2019.

Starting with Oracle Unit Testing Framework – Part 3 Automation

In this article I’ll describe how you can automate previously created Unit Test. I believe that many of the Oracle SQL Developer users use only core functionality of the tool, and have never become familiar with its more advanced features. I’ve already describes in several articles some of its advanced features (you can use search […]

28. 02. 2019.

Starting with the Oracle Unit Testing Framework – Part 2

This is a second part of the Oracle Unit Testing Framework post. In the first part I’ve created Unit Test repository and prepare everything for testing. Details you can find on the following link: https://www.performatune.com/en/starting-with-oracle-unit-testing-framework-part-1/   In this part I’ll perform actual test and show some of the numerous options that you have on disposal. […]

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 […]

06. 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 […]

09. 10. 2017.

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, […]

09. 10. 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 […]

09. 10. 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 […]