Yes, but the transaction just wasn't there. I did let it run during the night. Here are FBMonitor diagrams from about 1 hour before slowdown up to now: https://cdn.mise.se/FirebirdDiagrams.png
The vertical line in each diagram marks the time of slowdown (at about 22:05 last night). The upper left diagram shows in red that I accidentally left a transaction open in FlameRobin at about 20:50, which does not correlate to the time of slowdown. I committed it at 7:43 this morning. The I/O activity diagram shows that the cache activity goes up significantly at this time, while disk reads drop slightly. Disk writes drop a bit earlier. Nothing significant seems to happen with these params at the time of slowdown, except that there's a spike of disk reads at that moment. Noting noteworthy in any other diagrams as far as I can see. Looking in the statement history from FBMonitor, for the SQL query of interest, I see a significant change in the statistics and execution plan at the time of slowdown. ===Before slowdown=== Fetches lies around 150 GB Table scan lies around 94-95 % Sequential reads lies around 2.3 million Indexed reads lies around 130 thousand Fragment reads lies around 650 ===After slowdown=== Fetches lies around 1.17 TB (up a factor 8) Table scan lies around 24 (down 70 %) Sequential reads lies around 4.7 million (up a factor 2) Indexed reads lies around 14.7 million (up a factor 113) Fragment reads lies around 2300 (up a factor 3.5) The execution plan (new format, but unfortunately truncated) changes from this: =============== BEFORE SLOWDOWN =============== Select Expression -> Singularity Check -> Aggregate -> Nested Loop Join (inner) -> Filter -> Table "FöretagOmsättningsår" as "UHANT FTGOMSAR2" Access By ID -> Bitmap -> Index "IX_FöretagOmsättningsåDBN" Range Scan (full match) -> Filter -> Table "År" as "UHANT AR2" Access By ID -> Bitmap -> Index "IX_PK_År" Unique Scan Select Expression -> Filter -> Table "Uppgiftsspärr" as "SP2" Access By ID -> Bitmap -> Index "IX_Uppgiftsspärr_Uppgi60J" Range Scan (full match) Select Expression -> Nested Loop Join (inner) -> Filter -> Table "Uppgiftshanterare" as "UHANT2" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Filter -> Table "Uppgiftsspärr" as "SP2" Access By ID -> Bitmap -> Index "IX_Uppgiftsspärr_Uppgi8MK" Range Scan (full match) Select Expression -> Filter -> Table "Uppgiftshållare" as "UH2" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshållare" Unique Scan Select Expression -> Unique Sort (record length: 484, key length: 20) -> Filter -> Nested Loop Join (outer) -> Nested Loop Join (outer) -> Nested Loop Join (inner) -> Nested Loop Join (inner) -> Unique Sort (record length: 322, key length: 112) -> Union -> Nested Loop Join (inner) -> Table "TmpFKExportId" as "UHANT L" Full Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan === DIFF STARTS HERE === -> Nested Loop Join (inner) -> Table "TmpFKExportId" as "UHANT L" Full Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "FöretagOmsättningsår" as "UHANT FTGOMSAR" Access By ID -> Bitmap -> Index "IX_FöretagOmsättningsåDBN" Range Scan (full match) -> Filter -> Table "År" as "UHANT AR" Access By ID -> Bitmap -> Index "IX_PK_År" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Nested Loop Join (inner) -> Table "TmpFKExportId" as "UHANT L" Full Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "ITmiljö" as "UHANT ITMILJ" Access By ID -> Bitmap -> Index "IX_PK_ITmiljö" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Nested Loop Join (inner) -> Table "TmpFKExportId" as "UHANT L" Full Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "Befattningsinnehav" as "UHANT BI" Access By ID -> Bitmap -> Index "IX_Befattningsinnehav_OFT" Range Scan (full match) -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Nested Loop Join (inner) -> Table "Befattningsinnehav" as "UHANT BI" Full Scan -> Filter -> Table "TmpFKExportId" as "UHANT L" Access By ID -> Bitmap -> Index "PK_TmpFKExportId" Unique Scan -> Filter -> Table "Person" as "UHANT P" Access By ID -> Bitmap -> Index "IX_PK_Person" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "Uppgiftshållare" as "UH" Access By ID -> Bitmap -> Index "IX_Uppgiftshållare_HanDS2" Range Scan (full match) -> Filter -> Table "Uppgift" as "UPG" Access By ID -> Bitmap -> Index "IX_Uppgift_Hållare" Range Scan (full match) -> Filter -> Filter -> Table "Uppgiftsspärr" as "SPUHANT" Access By ID -> Bitmap -> Index "IX_Uppgiftsspärr_Uppgi8MK" Range Sc =============== BEFORE SLOWDOWN =============== To this: =============== AFTER SLOWDOWN =============== Select Expression -> Singularity Check -> Aggregate -> Nested Loop Join (inner) -> Filter -> Table "FöretagOmsättningsår" as "UHANT FTGOMSAR2" Access By ID -> Bitmap -> Index "IX_FöretagOmsättningsåDBN" Range Scan (full match) -> Filter -> Table "År" as "UHANT AR2" Access By ID -> Bitmap -> Index "IX_PK_År" Unique Scan Select Expression -> Filter -> Table "Uppgiftsspärr" as "SP2" Access By ID -> Bitmap -> Index "IX_Uppgiftsspärr_Uppgi60J" Range Scan (full match) Select Expression -> Nested Loop Join (inner) -> Filter -> Table "Uppgiftshanterare" as "UHANT2" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Filter -> Table "Uppgiftsspärr" as "SP2" Access By ID -> Bitmap -> Index "IX_Uppgiftsspärr_Uppgi8MK" Range Scan (full match) Select Expression -> Filter -> Table "Uppgiftshållare" as "UH2" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshållare" Unique Scan Select Expression -> Unique Sort (record length: 484, key length: 20) -> Filter -> Nested Loop Join (outer) -> Nested Loop Join (outer) -> Nested Loop Join (inner) -> Nested Loop Join (inner) -> Unique Sort (record length: 322, key length: 112) -> Union -> Nested Loop Join (inner) -> Table "TmpFKExportId" as "UHANT L" Full Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan === DIFF STARTS HERE === -> Filter -> Nested Loop Join (inner) -> Table "År" as "UHANT AR" Full Scan -> Filter -> Table "FöretagOmsättningsår" as "UHANT FTGOMSAR" Access By ID -> Bitmap -> Index "IX_FöretagOmsättningså9OF" Range Scan (full match) -> Filter -> Table "TmpFKExportId" as "UHANT L" Access By ID -> Bitmap -> Index "PK_TmpFKExportId" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Nested Loop Join (inner) -> Table "TmpFKExportId" as "UHANT L" Full Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "ITmiljö" as "UHANT ITMILJ" Access By ID -> Bitmap -> Index "IX_PK_ITmiljö" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Nested Loop Join (inner) -> Table "Befattningsinnehav" as "UHANT BI" Full Scan -> Filter -> Table "TmpFKExportId" as "UHANT L" Access By ID -> Bitmap -> Index "PK_TmpFKExportId" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Nested Loop Join (inner) -> Table "Befattningsinnehav" as "UHANT BI" Full Scan -> Filter -> Table "TmpFKExportId" as "UHANT L" Access By ID -> Bitmap -> Index "PK_TmpFKExportId" Unique Scan -> Filter -> Table "Person" as "UHANT P" Access By ID -> Bitmap -> Index "IX_PK_Person" Unique Scan -> Filter -> Table "Uppgiftshanterare" as "UHANT UHANT" Access By ID -> Bitmap -> Index "IX_PK_Uppgiftshanterare" Unique Scan -> Filter -> Table "Företag" as "UHANT F" Access By ID -> Bitmap -> Index "IX_PK_Företag" Unique Scan -> Filter -> Table "Uppgiftshållare" as "UH" Access By ID -> Bitmap -> Index "IX_Uppgiftshållare_HanDS2" Range Scan (full match) -> Filter -> Table "Uppgift" as "UPG" Access By ID -> Bitmap -> Index "IX_Uppgift_Hållare" Range Scan (full match) -> Filter -> Filter -> Table "Uppgiftsspärr" as "SPUHANT" Access By ID =============== AFTER SLOWDOWN =============== What might cause this sudden and "permanent" change in choise of query plan? Regards, Kjell Den 2020-04-29 kl. 19:12, skrev Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support]: > > Simply run query with join two tables MON$ATATACHEMENTS and > MON$TRANSACTIONS – you can also be interested with joining with > MON$STATEMENTS > > And then you can see which attachement consume this transaction (you > can retrive IP port and proces ID (PID) > > Pozdrawiam, > > Karol Bieniaszewski > > [Non-text portions of this message have been removed]
