Maybe it's worth noting that if I execute that exact query in FlameRobin, while the batch is running and suffering 50+ second execution time, FlameRobin gets it executed in 2-4 seconds every single time.
Weird. I'll try to rewrite the query using execute block, a temporary table or something like that, to reduce query complexity. Kjell Den 2020-04-30 kl. 13:04, skrev Kjell Rilbe [email protected] [firebird-support]: > > 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 > [email protected] [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] > > [Non-text portions of this message have been removed]
