Oracle Blog
Piotr Sajda Oracle Tuning Blog
This page contains the oracle stuff which mainly relates to performance tuning, emphasizing the specifics of Oracle Exadata.
Interested in tailored performance tuning workshops?
Please get in touch on piotr.sajda@gmail.com or LinkedIn (https://www.linkedin.com/in/piotr-sajda-246304/)
-
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. You have surely noticed that a total cost reported in the trace involves two summands: IO_COST and CPU_COST, where although in real the latter being just a fraction of the entire…
-
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 I encourage you to reach out to the part3 of the mentioned series and navigate to “Quantifying physical reads for low CLUF” and…
-
This post is the continuation of the previous part True price of high Clustering Factor: elapsed time, SGA and CPU 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 :Elapsed times with low and…
-
This post is the continuation of the previous part. Clustering Factor – tests with 10046 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 a query retrieving its data from the table, yet by the means of either one:…
-
This post is the continuation of the previous part. Clustering Factor – tests with block dumps 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– ID_MESS (almost unique, generated as scattered) with the index on it– PAD (filler…
-
Clustering Factor – theory 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): AVG_DATA_BLOCKS_PER_KEY and AVG_LEAF_BLOCKS_PER_KEY—While they represent close relationship to Clustering Factor, the CLUF tends to be more recognized in the…
-
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 (of what you may not be aware straight away), visibly slower – and most of all wasting I/O and CPU…
-
Analysis of joins with 10053 CBO trace This post continues from Part 1, 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 Hash Join.But do not treat its content as limited to just a Cartesian Join.It will also help…
-
Cartesian Join & Hash Join – Bonnie and Clyde “This movie is based on true story” Some time ago, I was approached to provide a solution to a performance issue.To illustrate this case, I will later simulate the scenario highlighting the critical aspects and providing a detailed explanation of the underlying causes.For now, let me…
-
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 numbers to the SQL Plan for the most basic scenario — comparing index range scan access vs. full table scan. As we know,…