Skip to content

Instantly share code, notes, and snippets.

View cs2018ncsa's full-sized avatar

cs2018ncsa

0 followers · 9 following
View GitHub Profile
@cs2018ncsa
cs2018ncsa / pg explain
Created April 20, 2020 14:29
Bitmap Heap Scan on "DiaSource" (cost=541.17..33856.16 rows=7831 width=848) (actual time=1.329..5.238 rows=8524 loops=1)
Recheck Cond: ((("pixelId" >= '10729436479488'::bigint) AND ("pixelId" <= '10729440673791'::bigint)) OR (("pixelId" >= '10729449062400'::bigint) AND ("pixelId" <= '10729499394047'::bigint)) OR (("pixelId" >= '10729532948480'::bi
gint) AND ("pixelId" <= '10729537142783'::bigint)) OR (("pixelId" >= '10729542385664'::bigint) AND ("pixelId" <= '10729542451199'::bigint)) OR (("pixelId" >= '10729542516736'::bigint) AND ("pixelId" <= '10729542647807'::bigint)) OR
(("pixelId" >= '10729546579968'::bigint) AND ("pixelId" <= '10729546711039'::bigint)) OR (("pixelId" >= '10729546776576'::bigint) AND ("pixelId" <= '10729546842111'::bigint)) OR (("pixelId" >= '10729566502912'::bigint) AND ("pixel
Id" <= '10729570697215'::bigint)) OR (("pixelId" >= '10729570959360'::bigint) AND ("pixelId" <= '10729571090431'::bigint)) OR (("pixelId" >= '10729571155968'::bigint) AND ("pixelId" <= '10729571221503'::bi
@cs2018ncsa
cs2018ncsa / gist:b2a4e235dc68f6fe240461f4d2035e96
Created February 10, 2020 22:07
"ADDR" "INDX" "INST_ID" "CON_ID" "COMPONENT" "EVENT" "TIME" "SEQ#" "SID" "SERIAL#" "PID" "FILE_LOC" "FUNCTION" "OPERATION" "SECTION" "DUMP" "DATA"
"00007FB42D07C1A8" "15479" "3" "0" "KRC_CHANGES" "0" "634681606608808" "0" "8" "36700" "0" "krcp.c@750" "krcput" "" "" "" "f= 19 b=788465 c= 1 s=0x 3c7384c7 o=0 b=0 opt=0
@cs2018ncsa
cs2018ncsa / gist:cdf90f5f541a31ad5d9740e9124752bb
Last active February 8, 2020 13:59
Yeah, the "FG wakeup overhead per log file sync" derived metric definitely shows that your foregrounds wait for log file syncs way longer than the corresponding LGWR write duration. That metric divides the "redo synch time overhead (usec)" with "redo synch writes" to get an average extra latency per foreground log file sync. Normally it's just microseconds, but in the output you sent it was close to 900 milliseconds per sync in some cases.
I've slightly edited one of the snapper output sections and removed some less interesting lines:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SID @INST, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES
--------------------------------------------------------------------------------------------------------------------------------------
@cs2018ncsa
cs2018ncsa / snapper_fg.out
Created February 6, 2020 18:53
snapper output for foreground sesssions experiencing unacceptably long LFS waits and lgwr processes.
1710 @3, (LGWR) , STAT, redo write size count ( 512KB) , 1, .08, , , , , ~ per execution
1710 @3, (LGWR) , STAT, redo write size count (1024KB) , 3, .24, , , , , ~ per execution
1710 @3, (LGWR) , STAT, redo write time , 1, .08, , , , , 362.76us per redo write
1710 @3, (LGWR) , STAT, redo write time (usec) , 11619, 918.82, , , , , ~ per execution
1710 @3, (LGWR) , STAT, redo blocks checksummed by FG (exclusive) , 882, 69.75, , , , , ~ per execution
1710 @3, (LGWR) , STAT, redo write gather time