09. 04. 2018.

Resource Manager – internal plan

In this article I’m going to disclose Oracle Resource Manager Internal plan and how it can influence on database performances. Many of Oracle DBA are convinced that Resource Manager is not active, and that RM has to be enabled to be active. To confirm that theory, DBA can execute the following statement: select name, display_value, […]

26. 12. 2017.

Calling PL/SQL functions from the SQL and it’s influence on query execution

In all books that I red about performance tuning in Oracle (and other major vendor) relational databases, you can find that when optimizing SQL, you remove the select part and concentrate on part starting with FROM clause to inspect what tables have been involved along with the WHERE clause. Recently I have a interesting case […]

06. 09. 2017.

AWR migration between databases

This time I’ll explain AWR statistics migration between databases. This is not a new feature (available from 10g) but many DBA/Architects doesn’t know how to use it properly or even that this feature exist. Basically, the migration itself is very simple, and Oracle has provided scripts (awrextr.sql & awrload.sql) to make migartion as simple as […]

04. 08. 2017.

Chained rows and enq TX – Row Lock Contention

A couple of days ago, I’ve been called to solve integration interface issue between SAP and ReIM (ReIM is application within the Oracle Retail Suite). The main challenge when solving such issue is to find out where you should start with your analysis and to distinguish between what is the cause of the issue and […]

19. 07. 2017.

Streams AQ: enqueue blocked on low memory while using Data Pump utility in 11.2.0.4

While helping with migration of one Retail production system, I need to export certain schemas by using Data Pump utility. As the database is pretty small (about 3TB), I’ve just executed expdp command and expected it will finish within 2 to 3 hours. When I checked in the evening, on my surprise, Data Pump job […]

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

08. 05. 2017.

ReIM Consolidated documents cost resolution – performance improvement

After external contractor of the company where I’m working and Oracle Support were not able to find solution that would improve performance of the ReIM consolidated documents functionality, it’s time for me to take that challenge. Very quickly I’ve found the problematic SQL. Here I’ll provide only the important part, as the query is over […]

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

21. 04. 2016.

RPM – unable to open promotion component performance issue

Recently I’ve got a new interesting issue, which clearly shows all the traps and different kind of knowledge required to solve it. The tile of the issue is “Unable to open promotion component”. In description of the issue I’ve found the following: Users are unable to enter promotion component when it has more than a […]