There's a problem on the oracle-l listserver at present about an insert taking far too much time (and CPU). It's a known issue and there are 47 statistics in v$sysstat (19.11) with names like 'ASSM%' to help diagnose it. How many do you think are described in the database reference manual? None.

Yet another adaptive lgwr optimization: on Exadata X10+, pipelined log writes may defer redo writes until a suitably sized write batch has accumulated in the log buffer. The deferral can involve spinning in a tight loop up to 25 times (maximum hard-coded in kcrfw_defer_write).

Nested loops, baby 😜

In den nächsten Tagen veröffentlichen wir nicht nur die Agenda, sondern am Tag nach dem SOUG Day planen wir noch ein Special für Euch! Schaut rein und meldet Euch an unter soug.ch.

So glad that all new features are documented so well... NOT 😜 Manually enabling and disabling adaptive lgwr evaluation trace for pipelined / overlapped redo writes:

POUG journey started… not even at the airport and lufthansa’s delay notification leaves no hope of making the connecting flight in MUC 🙈

So 23ai replaces ksesecl0(func, loc, err) with kseseclv(err, func, loc, ...) ... Why is it always just a few days before POUG that this kind of low-level discoveries surface? 🙄

A new tool in 0x.tools family: xtop - Top for Wall-Clock Time. It uses eBPF/xcapture v3 and gives you "x-ray vision" into Linux system activity. It will be available on next Tuesday 19 Aug at 1pm EDT when I also run a live demo webinar! tanelpoder.com/posts/xtop-t...

"Slide n of 142".. this is getting out of control... 🙈

Today's discovery (19.26): Oracle derives different log parallelism defaults, depending on platform. Max number of public redo strands is: Exadata: CPU_COUNT <= 256: 16 CPU_COUNT> 256: CPU_COUNT/16 Non-Exadata: CPU_COUNT <= 32: 2 CPU_COUNT > 32: CPU_COUNT/16 Max limit: 256

Oh my, looks like the "ms" in "kso_sched_delay_avg_ms" actually means "microseconds" ... 🤷‍♂️

1/6 The "redo synch time overhead" in Oracle is the difference between a FG's log file sync (LFS) wait end time and LGWR's redo write completion time. LGWR and LG workers track the redo write completion times in the "write info array" in the SGA.

lgwr: You have the control gdb: I have the control

Over the past few weeks, my understanding of undocumented LGWR mechanics has improved quite a bit, and I'm now tempted to write a talk titled "LGWR beyond the docs - what Oracle didn't write to disk" 😉

1/4 In Adaptive Scalable mode, lgwr can dynamically assign multiple lg workers to process a redo write in parallel when these conditions are met: - redo write size > KSFD_MAXIO (usually 1 MB) - _max_log_write_parallelism > 1 - active public redo strands > 1

The trace below shows lg00 submitting a log write at T4 (lwn scn 0x70f14b55773) while lg04's earlier write submitted at T2 (lwn 0x70f14b55768) is still in flight, suggesting Pipelined Log Writes. But the trace is from an X8, not X10, where the feature is documented ...🤔 (I/Os throttled for testing)

1/7 On Exadata with pmemlog, the Fast Log File Sync dynamically tunes the log file sync sleep duration to balance responsiveness vs cpu ovherhead (spinning after wakeup). Oracle tracks three wait variants via different session stat counters: 1. Sleep 2. Spin 3. Backoff Sleeps

If you've ever felt the need to manually control adaptive lgwr features, this gdb script's got you covered: t.ly/lJIW1 😎 It lets you enable and disable adaptive scalable lgwr, fast log file sync, and log parallelism. Highly experimental, of course! Example 👇

Geeky Sunday mission accomplished… 💥✌️😎 The Fast Sync sleep duration used during log file sync waits is an adaptive moving average calculated every 3 sec by ckpt. For the curious, more details in this python script: tinyurl.com/mrycy7zh More explanations will follow some other time.

Week end plan: figure out how Fast Sync calculates the adaptive sleep duration used during log file sync waits. Answer is in the numbers below ... 🤔

Life before dynamic tracing: sleep. Life after: desperately trying not to lose your mind while chasing one more elusive oracle code path … 🐇🕳️

Haven't used good old perf for a while and forgot how cool it actually is. Wondering what process and code paths on your system modify a memory address? perf's got you covered: perf record -a -g -e mem:<addr>:w

Tired: Trust the abstraction Wired: Trace the abstraction

1/10 The log parallelism feature has been part of Oracle since 9i (20+ years or so), but surprisingly, there's not much information available on how it really works. Time for a closer look ... 👇

Went through to Texas, yeah, Texas… and we had some fun 🤘

Been digging into lgwr mechanics lately, and whew, have they become dynamic ... log parallelism, adaptive log file sync, fast sync, adaptive scalable lgwr, pipelined log writes, ... Wondering if anybody actually knows how all these features work and interact with each other 🤔

🚨 Hot off the Press! 🚨 Excited about the powerful new #Oracle Database Snapshot and Cloning features now available with #Exadata #Exascale? Don’t miss out—get the latest #MAA Best Practices Guide and start cloning with confidence! A huge thank you to the incredible MAA and Engineering teams!

1/9 Interesting redo buffer allocation mechanics in Oracle, showing how much care and thought have gone into the rdbms design.

So much fabric, so little bag … this compression ratio would make HCC weep, and it decompresses in a flash … 😉 Blue skies!

1/10 Redo copy latch internals - Part 2: lgwr mechanics This explains what happens during a "LGWR wait for redo copy" event.

1/11 A thread on Oracle redo copy latch internals. Part 1 - FG latch acquisition

Interesting data, indeed. 😉 (gdb) call (void) kcrfw_dump_interesting_data()

Don‘t miss the all new SOUG DEV Day in Zurich and learn about the great but unfortunately not well known feature „property graphs“ presented by the product manager Hans Viehmann 👍 soug.ch/event/sougde...

0x.tools xCapture v3: Linux Performance Analysis with Modern eBPF and DuckDB 👀👀 tanelpoder.com/posts/xcaptu...

Intel PT is pretty cool technology. Give it a try if it‘s available on one of your machines 😉 2024.bsidesmunich.org/talks/002-07...

JAS-MIN just got integration with OpenAI ant - to be honest - we're a bit impressed :D github.com/ora600pl/jas...

For geeks: two bpftrace scripts to map UTS event and component names to their internal ids. t.ly/bGj0M t.ly/ypKuv Didn’t plan on it, but sometimes the rabbit hole chooses you... Turns out "oradebug doc" doesn't expose all trace events.

How come I only found out about the bpf based tpcdump implementation "ptcpdump" today? This enables packet filtering by process, user and container. Now waiting for the next network issue 😉 t.ly/jbrJH

New blog: When eBPF stack->pt_regs reads return garbage on the latest Linux kernels, blame Fred! tanelpoder.com/posts/ebpf-p...

1/5 Oracle can restart DMLs for different reasons, each behaving differently: Write consistency: on write conflict, handled in updThreePhaseExe Internal errors: on ORA-14403 (and possibly others), handled in opiexe A stmt can even incur both restart types in a single execution.

A Single Disk Replacement can cause Performance Issues on entire Exadata System (Doc ID 3059053.1)

So, mostly layer 8 problem then? 😄 In any case, great write-up, really enjoyed the read!

New blog & tool: TracepointArgs: List Any Linux Tracepoint with Their Arguments, Datatypes and Related Structs tanelpoder.com/posts/tpargs... This should be useful for all the Linux kernel explorers and eBPF nerds!

@connormcd.bsky.social Unfortunately, the link to your recent blog post on fetch size autotuning in jdbc 23ai is dead ... Any idea what happened or did you remove it? connor-mcdonald.com/2025/01/17/j...

It continues to amaze me time and again what bpf based tracing makes possible … been looking into Oracle write consistency and update restart mechanics recently and tinkered with a bpftrace script to detect restarts system-wide and in real-time: t.ly/MgyQ- 🤓

1/4 Interesting UPDATE restart variation: Oracle may try to update a row during the ALL LOCKED phase that wasn't returned and locked in the prior LOCK phase (row 0 in the example).