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

26. 04. 2016.

How to stabilize complex execution plan which cannot be stabilized by SQL Plan Baseline or SQL Profile

Recently I’ve been called to help with production issue that stopped all POS (Point of Sales) for 2 hours, producing lost for the affected company. I’ve found relatively quickly problematic query, which looks at first very simple. This is the problematic SQL code: SELECT * FROM (SELECT DOCUMENT_DATE, DOCUMENT_TYPE, AMT_TOTAL, DOCUMENT_NUM, FISCALISED, REF_DOCUMENT_NUM, ORDER_TSF_NO, STATUS_LOOKUP, […]

15. 03. 2016.

How to index only rows of interest

This is a classical example of what you can achieve by combining several existing features and implemented in a new way, not described within Oracle docs. Here I’m using the fact that Oracle won’t add entry into the index structure if indexed column value is null, in combination with the function-based indexes functionality. I’ve created […]

12. 01. 2016.

Case versus good old Join

SQL language gain a lot with introduction of the “Case” statement. Many SQL programmers are using it, as it is more logical way to accomplish some task without even thinking that SQL language is much older then Case statement, and there is always another (more efficient) way to get the same result. In this post […]