-
This post is the continuation of the previous part The impact of High Clustering Factor on 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…
-
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 – introduction 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): I mean: 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…
All tags
- 10046 trace (5)
- 10053 trace (7)
- 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 (4)
- 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 (5)
- 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 (4)
- NSMTIO (4)
- OEM (2)
- Offload (5)
- Oracle 23ai (3)
- Oracle performance (1)
- Oracle tuning (1)
- oradebug (2)
- Parallel Query (2)
- Performance Tuning (1)
- Resc (1)
- Resp (1)
- smart scan (4)
- SQL baseline (3)
- SQL Monitor (4)
- SQL Plan (1)
- SQL Tuning (2)
- system statistics (1)
- Temp tablespace (1)
- Tuning in Multitenant Architecture (2)
- Why Offload does not kick in (2)
Latest posts
- Be cautious when the optimizer estimates a cardinality of 1 – Part2
- Be cautious when the optimizer estimates a cardinality of 1 – Part1
- Be cautious when the optimizer estimates a cardinality of 1 – Part3
- 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?