Re: intermittent long "log file sync" waits

Man, I wrote an answer with 4 bullet points and them managed to "select+all" and delete the text just before hitting send.

The short version (possibly with typos):

*1) *Would still like to see the full Snapper output of LGWR *and* LG* processes during the problem time

*2) *The *redo write* metrics in v$sesstat are about LGWR writes, not foreground commit latency experience

*3) *For understanding end to end foreground commit latency, you'd need to look into *redo synch* metrics. 18c has over 70 redo synch metrics, but some years ago (I think when 12c was introduced) I added one metric of interest to Snapper: *redo synch time overhead (usec)*.

I actually divide it by *redo synch writes*, giving a derived metric that I call *FG wakeup overhead per log file sync* in Snapper.

You'd need to run this on some of the foreground sessions experiencing the problem, not LGWR. If the problem is system-wide and lasts for long enough, you may be able to see something in AWR/v$sysstat too, although the various averaging may hide detail there.

*4) *Cursor #0 is shown when the client is making "cursorless" top-level database calls like OCICommit() in OCI or conn.commit() in JDBC, instead of parsing and executing a SQL statement with text "commit" in it. The top_level_call_name in ASH or OPI trace event 10051 would show "COMMIT" as the database call instead of V8 Bundled Exec <https://tanelpoder.com/2011/08/23/v8-bundled-exec-call-and-oracle-program-interface-opi-calls/> .

SQL> _at_tlc commit
Display top-level call names matching %commit% (Oracle 11.2+)

TOP_LEVEL_CALL# TOP_LEVEL_CALL_NAME

--------------- ---------------------------------------------
             12 COMMIT ON
             13 COMMIT OFF
             14 COMMIT
             89 XA XA Commit
            104 Transaction Commit/Rollback
            117 Commit Remote Sites
            127 Commit Remote Sites >= V813

--
Tanel Poder
https://tanelpoder.com/seminar


On Wed, Feb 5, 2020 at 5:32 PM Tanel Poder <tanel_at_tanelpoder.com> wrote:



>

>>>>


--
http://www.freelists.org/webpage/oracle-l