-
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…
-
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 a Cartesian Join.It will also…
-
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,…
All tags
- 10046 trace (4)
- 10053 trace (5)
- 10200 Trace (1)
- Active Session History (1)
- Analysis of costing of joins in 10053 (1)
- Auto SQL Plan Management (3)
- AVG_DATA_BLOCKS_PER_KEY (1)
- AVG_LEAF_BLOCKS_PER_KEY (1)
- Batched I/O (1)
- Cartesian Join (2)
- CBO costing (3)
- 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)
- large SGA (1)
- Linux (1)
- LRU (1)
- Nested Loop (1)
- NSMTIO (3)
- Offload (4)
- Oracle 23ai (3)
- Oracle performance (1)
- Oracle tuning (1)
- oradebug (2)
- Parallel Query (1)
- Performance Tuning (1)
- Resc (1)
- Resp (1)
- smart scan (3)
- Sort Merge Join (1)
- SQL baseliine (1)
- SQL baseline (2)
- SQL Plan (1)
- SQL Tuning (1)
- sql_id (1)
- system statistics (1)
- Temp tablespace (1)
Latest posts
- 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
- Clustering Factor (CLUF) – the anatomy and its impact on performance – part1
- Resp in the CBO 10053 Trace File