19. 06. 2022.

How to speedup pooling queries from the Oracle database

This post is a kind of extension of my previous post named “How to index only rows of interest” which can be found on the following link: https://www.josip-pojatina.com/en/how-to-index-only-rows-of-interest/   Having issues with pooling queries are very common in old, SOA based architectural style, where ESB (Enterprise Service Bus) are throwing the same query again and […]

29. 12. 2021.

Apache Ignite – distributed In-memory SQL database

Apache Ignite is one of the very few In-memory SQL compliant distributed databases/data grid among open-source projects. It’s often called “Redis done right” or “Redis on steroid”, because Redis looks primitive and limited when compared with Apache Ignite. Ignite offers great flexibility and lot of features that can easily fit to many use cases. Instead […]

18. 08. 2021.

Functional monitoring of Microservices architecture by using Apache Superset

Many of you who have started to develop modern apps by using Microservices approach, have already learned that development tools, debuggers, performance monitoring and tracing lag behind the desired architecture. Situation is even worse when it comes to functional monitoring, where your goal is to find out what is going on with your system from […]

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

27. 09. 2018.

Non prefixed local index and slow SQL

Recently I’ve been asked to help explain why one query is running so slow based on only SQL text and table definition that is provided. As query was pretty big (several hundreds lines of code), at the first I thought that is mission impossible, as I have no explain plan available, no running stats, no […]

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. 2018.

Hints and Outlines required action when changing object name

When developing code, usually temporary created objects like tables and indexes have been created just to test some scenario and check behaviour. Very often, in case test passed reside much longer than it was planned. For that reason, at some point in time, it’s needed to change object names to deploy code to upper environments, […]

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

07. 07. 2016.

Performance of various partition types and how to choose optimal partitioning strategy

One of the largest table in Oracle Retail is the TRAN_DATA_HISTORY table which holds stock ledger financial transaction data history. Due to business reporting reason, customer decided to disable salprg Pro*C batch which deletes TRAN_DATA_HISTORY records that are older than the user defined number of retention days defined in SYSTEM_OPTIONS table in the column tran_data_retained_days_no. […]

10. 06. 2016.

Curse of PARALLEL hint

Last week I’ve been faced with issue that is slowing down production system for more than 30 minutes. During that time, users were unable to work, Cluster services were failing and system was unresponsive. After completing some initial investigation, I’ve found the problematic SQL: SELECT /*+ PARALLEL*/ TRAN_NO, REF_NO1, REF_NO25 FROM SA_TRAN_HEAD WHERE STATUS <> […]