ORA-01652: Unable to Extend Temp Segment. Don't Just Add Space.
Lab 10x the TEMP bought 10x the runtime and the same error. Restoring one join predicate made the report need none.
Oracle Performance Tuning
AWR/ASH, wait events, SQL & optimizer tuning
Oracle performance work goes wrong when it starts from ratios and hunches instead of where time actually goes. The method that holds up is time-based: read DB time, rank the wait events by their share of it, and follow the top one to the SQL or the segment behind it. These guides teach that method end to end — how to read an AWR report without drowning, the half-dozen wait events that carry most real problems, and how to tell a SQL problem from a storage problem — with a free lab that reproduces each wait event on your own machine so you see the signature, not just read about it.
Guides in this pillar
-
-
Same SQL, Fast for One Value, Slow for Another
Lab The first bind value decided the plan for everyone. The common value inherited a nested-loops plan at 17 times the work, five runs in a row, and adaptive cursor sharing never stepped in.
-
You Deleted 99% of the Rows. The Full Scan Didn't Get Any Faster.
Lab Deleting 99% of the rows didn't change the full scan's cost at all. SHRINK SPACE cut it 95-fold.
-
Bind Variables: The Hard-Parse Storm That Melts Your Shared Pool
Lab The same query run 1,000 times: literals made 1,000 cursors and ~38 MB of shared pool; one bind variable made 1 cursor and ~39 KB.
-
Oracle Indexes: When They Help, When They Hurt, and How to Tell
Lab The same index makes a query for a rare value more than 1,000× cheaper, and makes a query for a common value worse.
-
Oracle Partition Pruning: Read One Partition, Not the Whole Table
Lab Both queries return the same 50,000 rows. Wrapping the partition key in TO_CHAR made Oracle read every partition instead of one.
-
Oracle SQL Plan Management: Stop a Good Plan From Going Bad
Lab A routine stats change flipped a good index plan to a full scan. An accepted SQL plan baseline held the good plan.
-
Oracle Optimizer Statistics, Demystified
Lab Without a column group the optimizer expected 200 rows, got 10,000, and chose a nested loop. With one on (make, model) it estimated 10,000 and switched to a hash join.
-
Oracle Execution Plans, Decoded: The One Number That Matters
Lab The optimizer expected 500K rows and got 500. Fixing the estimate turned a full scan into an index plan.
-
ORA-00060 Deadlock: Find It, Fix It, Prevent It
Lab Two sessions locking the same two rows in opposite order raised ORA-00060 in one of them. The same work in a consistent lock order committed cleanly.
-
Oracle Wait Events, Decoded: The Half-Dozen
Lab Each drill moved its own wait event: 730 single-block reads, 358 multi-block reads, 342 direct path reads, and 10,017 log file syncs for 10,000 commits sent one at a time.
-
How to Read an AWR Report Without Drowning
Lab DB CPU topped both reports. Even in the I/O drill, at 42,985.5 physical reads per second, direct path read was only 2.2% of DB time on fast local disk.