-
Basics of hard parsing You surely came across this too many times: hard parsing due to literals.Let’s start from the basics: The direct reason for not sharing these two queries (SQL_IDs) are the literals.In this case ‘2026-07-07 13:21:10′ and ‘2026-07-07 13:21:11‘ make those two statements different (see different SQL_IDs: 8gb3f1q98run2 and 8539hwragf3ag), incurring hard parsing.…
-
Let’s get to presenting the entire solution Here is the tiny and simple Java code (there is a reason why I didn’t use PL/SQL) which upon executing will create storm (10,000) of unique SQL_IDs.The underlying SQL statement contains the offending TIMESTAMP expression which is executed in a loop producing a unique literal.cursor_sharing is set to…
All tags
- 10046 trace (7)
- 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)
- Hard Parsing (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)
- ORA-04031 (2)
- 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 Trace (1)
- SQL Translation Framework (3)
- SQL Tuning (2)
- system statistics (1)
- Temp tablespace (1)
- Tuning in Multitenant Architecture (2)
- Why Offload does not kick in (2)
Latest posts
- SQL Translation Framework – Tackling ORA-04031 (Part 1)
- SQL Translation Framework – Tackling ORA-04031 (Part 2)
- SQL Translation Framework – Tackling ORA-04031 (Part 3)
- 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