PIOTR SAJDA

│

ORACLE EXPERT ARCHITECT (ACE)

HOME

ARTICLES

WORKSHOPS

SPEAKING

ABOUT

CONTAKT

Articles & Insights

Oracle Performance Tuning & Database Engineering

All technical articles →

01.10.2024

Calculations of I/O cost in 10053 trace vs. SQL Plan

One day, while browsing publicly available resources (blogs, white papers), I realized there’s a lack of easily accessible information on the following topic:
How to map the cost-based optimizer (CBO) formulas and …

Read article →

19.04.2026

Be cautious when the optimizer estimates a cardinality of 1 - PART 1

A time ago I came across a problem which inspired me to write this article.
It also made me thinking how important it is to understand the client’s business model…

Read article (part 1) →

01.03.2025

Why this extra physical read while performing index scan?

While preparing the series of posts about Clustering Factor (aka CLUF in 10053 traces) I came across an interesting caveat which I decided to explore.
I will make an attempt to explain its matter, although …

Read article →

02.11.2026

SQL Translation Framework – Tackling ORA-04031 - PART 1

Basics of hard parsing

You surely came across this too many times: hard parsing due to literals.
Let’s start from the basics: …

Read article (part 1) →

27.02.2025

Clustering Factor (CLUF) – the anatomy and its impact on performance - PART 1

Some time ago I wrote a post diving into, what I like to call the “younger siblings” of Clustering Factor (aka CLUF – this is how it’s called in the 10053 trace): …

Read article (part 1) →

02.11.2026

SQL Translation Framework – Tackling ORA-04031 - PART 2

Here is the tiny and simple Java code (there is a reason explained later why I didn’t use PL/SQL) which upon executing will create storm (10,000) of unique SQL_IDs…

Read article (part 2) →

02.11.2026

SQL Translation Framework – Tackling ORA-04031 - PART 3

I recommend reading Part 1 first for a basic understanding of hard parsing internals, followed by Part 2 to familiarize yourself with the scripts and tools used in this article…

Read article (part 3) →

19.04.2026

Be cautious when the optimizer estimates a cardinality of 1 - PART 2

Let us finally focus on my problem.
I will come back to my query …

Read article (part 2) →

16.04.2026

Be cautious when the optimizer estimates a cardinality of 1 - PART 3

After this rather long discussion of what led the optimizer to choose one index over another — and, more importantly, of the significant consequences this choice brought …

Read article (part 3) →

09.02.2026

When OEM meets the CBO: cardinality chaos across containers - PART 1

A time ago I stumbled upon a multi-dimensional performance problem where the OEM sessions got completely stuck spawning a growing pile of SQL executions that felt practically never-ending…

Read article (part 1) →

09.02.2026

When OEM meets the CBO: cardinality chaos across containers - PART 2

Let’s further “divide and conquer” –
to confirm that each component of the “UNION ALL” being executed at the CDB level (which looks like that excerpt directly below), spawns as many queries …

Read article (part 2) →

20.10.2025

Auto SQL Plan Management (look under the hood) - PART 1

For years, SQL Plan Management was a powerful but somewhat manual tool in the DBA’s belt — capture baselines, evolve them, accept or reject new plans… rinse and repeat. It worked, but it required attention …

Read article (part 1) →

20.10.2025

Auto SQL Plan Management (look under the hood) - PART 2

In the previous article I went through setting up the Auto-SPM functionality, so turned the SPM to true “auto”. I then constructed some fairly obvious conditions for Automatic SQL Plan Management to operate …

Read article (part 2) →

20.10.2025

Auto SQL Plan Management (look under the hood) - PART 3

Short reminder, in the two previous articles I made up two test scenarios of delving into the Auto SQL Plan Management.
In the first part I constructed …

Read article (part 3) →

20.10.2025

Converting of CPU Costing in the Cost Based Optimizer 10053 Trace File

Let’s take a look at the point within the 10053 trace which, albeit usually not critical, sometimes may be a tip turning the scales …

Read article →

01.03.2025

Clustering Factor (CLUF) – the anatomy and its impact on performance - PART 4

This time let’s try to quantify the usage of the resources applying much broader index range scan.
I’ll force retrieving 10% (1 million) of rows and will pay close attention to …

Read article (part 4) →

28.02.2025

Clustering Factor (CLUF) – the anatomy and its impact on performance - PART 3

Let’s go through the cases represented by SELECT statements and examine the true effort measured with the number of physical reads which are hidden behind …

Read article (part 3) →

28.02.2025

Clustering Factor (CLUF) – the anatomy and its impact on performance - PART 2

My test environment comprises a table with 10 million rows and three columns with two indexes:
– ID_SORTED (unique, generated as sorted) with the index on it …

Read article (part 2) →

03.02.2025

RESP in the CBO 10053 Trace File

Resp: you might have spotted it “here and there” in the 10053 trace file.
It may turn out important if, for example, all of sudden your SQL similar to the one below starts running in parallel …

Read article →

03.02.2025

Cartesian Join – the usual suspect - PART 2

This post continues from the previous one diving deeper into the challenges posed by a suboptimal SQL query that led the optimizer to select a Cartesian Join combined with an overly expensive …

Read article (part 2) →

05.01.2025

Cartesian Join – the usual suspect - PART 1

Some time ago, I was approached to provide a solution to a performance issue.
To illustrate this case, I will later simulate the scenario …

Read article (part 1) →

10.09.2024

ORADEBUG: example of building of the command syntax

Over time oradebug became an extremely powerful tool, yet there is no official documentation available apart of contextual help …

Read article →

10.09.2024

Lack of offload with large SGA - PART 3

Over time oradebug became an extremely powerful tool, yet there is no official documentation available apart of contextual help …

Read article (part 3) →

09.09.2024

Lack of offload with large SGA - PART 2

To do that, I will force reading the table into the SGA.
To remain fair, I won’t cache it entirely.
Why am I considering it a “fair” approach? …

Read article (part 12 →

20.08.2024

Lack of offload with large SGA - PART 1

Recently I witnessed an interesting case which added a headache to the application owners and the business.
Namely, one of the important reports …

Read article (part 1) →

20.08.2024

AVG_DATA_BLOCKS_PER_KEY and AVG_LEAF_BLOCKS_PER_KEY demystified

They are to be spotted “here and there”. I bet though you may tend to skip them as not important or.. not really well understood, so not important …

Read article →

07.03.2024

Measure the utilization of I/O channel reported by HBA in Linux

So, is your environment more or less following –
Your linux boxes are attached to some High End Storage infrastructure to which you have no access?
The above implies that the organization you’re in  …

Read article →

05.03.2024

Temp tablespace on Flash – boost your SQL query without even running it

I had a SQL query to optimize that was heavily relying on the TEMP tablespace due to multiple HASH JOINs and aggregations. The session quickly exhausted its allotted PGA …

Read article →

Let's talk Oracle performance.

CONSULTING · SPEAKING · WORKSHOPS 

© 2026 Piotr Sajda
Legal Notice
Privacy Policy