Hi Nando,

> what's the PLAN? Does it include a SORT clause?

I have attached the plans at the end of this message.
And yes, they contain a SORT clause.

> If so, the server is
> probably using that much RAM to sort your sets. You can have it
> fallback to disk by tweaking the SortMemSize and SortMemUpperLimit
> (IIRC) firebird.conf parameters.

SortMemUpperLimit was set to 64 MB in my firebird.conf.
AFAIU, the server should have consumed at most 64 MB of RAM for
sorting for my connection. I have even changed the value to 4 MB,
but in both cases the server process consumed 1.25 GB of RAM.

> Doesn't look to be IBO-related.

This is why I had posted this issue to the firebird-support list,
but people seem to have different opinions on this.

Thanks for your answer,


Christian




PREPARE STATEMENT
TR_HANDLE = 16161792
STMT_HANDLE = 16208896

SELECT DISTINCT
V.Policennummer,
K.O_Name1 || ' ' || K.O_Name2 AS Name,
VT.Vertragsname,
V.Laufzeit,
V.Beitragszahldauer,
PE.PID,
PP.ID,
PM.Z_Personal_ID,
PM.Z_PA_ID
FROM T_Pa_Pos_TMP PP
LEFT OUTER JOIN T_Vertraege V ON (PP.Z_Vertrags_ID = V.ID)
LEFT OUTER JOIN T_Pa_Pos_Makler_TMP PM ON (PM.Z_Pa_Pos_Id = PP.ID)
LEFT OUTER JOIN P_Hole_Kunden_Name(V.Z_Kid) K ON (V.Z_KID = O_KID)
LEFT OUTER JOIN T_Vertragstypen VT ON (V.Z_Vertragstyp = VT.ID)
LEFT OUTER JOIN T_Personal PE ON (PM.Z_Personal_ID = PE.ID)
WHERE T_PA_Pos_Makler_TMP.Z_Personal_ID =? /* "MLNK_Z_PERSONAL_ID _0" */
AND (PM.Z_Ausbezahlt_Mit_PA = ? /* PA_ID */ )
AND (PM.Betrag <> 0)
ORDER BY K.O_Name1 ASC

PLAN SORT (SORT (JOIN (JOIN (JOIN (JOIN (JOIN (PP NATURAL,V INDEX (PK_T_VERTRAEGE)),PM INDEX (FK_PA_POS_M_T_PA_POS_ID)),T_KUNDEN INDEX (UNQ_T_KUNDEN_KID)),VT INDEX (PK_T_VERTRAGSTYPEN)),PE INDEX (RDB$PRIMARY1))))

----*/
/*---
PREPARE STATEMENT
TR_HANDLE = 16161792
STMT_HANDLE = 16254104

SELECT M.Prozentsatz,
M.Praemie,
M.Betrag,
M.Bemerkung,
M.Anz_Monatsteile,
PT.Typ_Name,
M.Ist_Storno
FROM T_Pa_Pos_Makler_TMP M
LEFT OUTER JOIN T_Provisionstypen PT ON (Z_Provisionstyp_ID=PT.ID)
WHERE T_PA_Pos_Makler_Tmp.Z_Personal_ID =? /* "MLNK_Z_PERSONAL_ID _0" */
AND T_PA_Pos_Makler_Tmp.Z_Pa_Pos_ID =? /* "MLNK_Z_PA_POS_ID _1" */

PLAN JOIN (M INDEX (FK_PA_POS_M_T_PERSONAL_ID,FK_PA_POS_M_T_PA_POS_ID),PT INDEX (PK_T_PROVISIONSTYPEN))