-
Let us finally focus on my problem.I will come back to my query : As a reminder – the predicates which are of the greatest importance are the following : The state before tuning If not timed out after 2 minutes (such constraint was applied at the application layer) with the SQL Plan depicted below…
-
A time ago I came across a problem which inspired me to write this article.It also made me thinking how important it is to understand the client’s business model.Not that you need to get in depth of it – most of the cases we remain within the specifics of SQL.The optimistic side of it, with…
-
After this rather long discussion of what led the optimizer to choose one index over another — and, more importantly, of the significant consequences this choice brought — let us return to my test query.Bad news though – we are still not done with the optimizer’s internal decision-making, so this means diving yet again into…
-
A time ago I stumbled upon a multi-dimensional performance problem where the OEM sessions got completely stuck spawning a growing pile of SQL executions that felt practically never-ending.The culprit was a standard OEM query that returns genuinely useful information (you’ll recognize it immediately from the SQL text): tablespace space utilization, temp tablespace details, and related…
-
This post continues from :”When OEM meets the CBO: cardinality chaos across containers – Part 1“ Getting down to a single & simple query and using “FROM CONTAINERS” to my own advantage Let’s further “divide and conquer” –to confirm that each component of the “UNION ALL” being executed at the CDB level (which looks like that…
-
For years, SQL Plan Management was a powerful but somewhat manual tool in the DBA’s belt — capture baselines, evolve them, accept or reject new plans… rinse and repeat. It worked, but it required attention.With 23ai, Oracle takes a bold step toward self-driving databases: Automatic SQL Plan Management. The database now watches for regressions, evaluates…
-
Short reminder, in the two previous articles I made up two test scenarios of delving into the Auto SQL Plan Management. In the first part I constructed some fairly obvious conditions for Automatic SQL Plan Management to operate — it worked exactly as expected.The choice was based on obvious metrics: lower CPU usage, fewer logical…
-
In the previous article I went through setting up the Auto-SPM functionality, so turned the SPM to true “auto”. I then constructed some fairly obvious conditions for Automatic SQL Plan Management to operate.And yes — it worked exactly as expected. The engine kicked in on its own, activated a clearly better SQL plan, and did…
-
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…
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?