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 …
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…
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 …
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): …
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…
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…
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 …
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…
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …