-
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 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 Hash Join.But do not treat its content as limited to just characteristics of Cartesian Join.It will…
-
Cartesian Join and Hash Join – Bonnie & 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,…
-
Note – this post is still not finished, so be prepared for a little mess .. Over time oradebug became an extremely powerful tool, yet there is no official documentation available apart of contextual help (sourced from SQL> oradebug doc).Good news, there are several good blogs mitigating that deficiency (see the list at the end of…
-
Note: this is the continuation of the previous post. The natural question arises: how can I set a trap for any specific SQL_ID which causes issues. Update: near the end of this part3 I have also included some immediate solutions to mitigate the problem supported by two other posts. The goal is to intercept an…
-
Note: this is continuation of the previous post. Now, let’s finally simulate my problem. 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?It’s hard to expect, after having executed a query that the entire large table, say 200GB…
-
This post contains 3 parts. Recently I witnessed an interesting case which added a headache to the application owners and the business.Namely, one of the important reports which used to run smoothly all of sudden stopped behaving.Talking numbers, something what people used to get within some seconds (up to 8) unexpectedly took close to 2…
-
You surely came across these two columns while going through DBA_% or Oracle documentation. AVG_LEAF_BLOCKS_PER_KEYAVG_DATA_BLOCKS_PER_KEY 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.I will make an attempt to demystify them and show how valuable they…
All tags
- 10046 trace (4)
- 10053 trace (5)
- 10200 Trace (1)
- Active Session History (1)
- Auto SQL Plan Management (3)
- AVG_DATA_BLOCKS_PER_KEY (1)
- AVG_LEAF_BLOCKS_PER_KEY (1)
- Batched I/O (1)
- cardinality (1)
- Cartesian Join (2)
- CBO costing (3)
- CDB / PDB (2)
- CLUF (4)
- Clustering Factor (4)
- Converting of CPU costing in 10053 (1)
- CPU_COST in 10053 trace (1)
- Exadata (4)
- FC HBA utilization (1)
- FC host (1)
- Flash (1)
- Flash disk (1)
- Hash Join (2)
- HBA (1)
- I/O throughput (1)
- index leaf block dump (2)
- index tree dump (1)
- Join Permutations (1)
- large SGA (1)
- Linux (1)
- LRU (1)
- Nested Loop (2)
- NSMTIO (3)
- OEM (2)
- Offload (4)
- Oracle 23ai (3)
- Oracle performance (1)
- Oracle tuning (1)
- oradebug (2)
- Parallel Query (2)
- Performance Tuning (1)
- Resc (1)
- Resp (1)
- smart scan (3)
- SQL baseline (3)
- SQL Monitor (2)
- SQL Plan (1)
- SQL Tuning (1)
- sql_id (1)
- system statistics (1)
- Temp tablespace (1)
- Tuning in Multitenant Architecture (2)
- Why Offload does not kick in (1)
Latest posts
- When OEM meets the CBO: cardinality chaos across containers – Part 1
- When OEM meets the CBO: cardinality chaos across containers – Part 2
- Auto SQL Plan Management (look under the hood) – part 1
- Auto SQL Plan Management (look under the hood) – part 3
- Auto SQL Plan Management (look under the hood) – part 2
- Converting of CPU Costing in the Cost Based Optimizer 10053 Trace File
- Why this extra physical read while performing index scan?
- Clustering Factor (CLUF) – the anatomy and its impact on performance – part4
- Clustering Factor (CLUF) – the anatomy and its impact on performance – part3
- Clustering Factor (CLUF) – the anatomy and its impact on performance – part2