I need help on query optimization.

The below-mentioned query is just a sample. Actual query has 50+ scalar subqueries. Base table table_xyz has over 5 million records.

SELECT 
    id,
    seq,
    (
        SELECT
            function_abc(t.id,t.seq,115501)
        FROM
            dual
    ) AS txt_dosage_comments,
    (
        SELECT
            function_abc(t.id,t.seq,115502)
        FROM
            dual
    ) AS fld_txt_total_therapy_duration

FROM
    table_xyz t;

function_abc() reads data from another table which has 100million records. table1 has been indexed on (id, seq, field_id, language) fields. There can be multiple records across the rows for one (id, seq) pair that's why there is a loop to concatenate them and make a string.

This is what function looks like :

FUNCTION function_abc (
        pi_id     int,
        pi_seq    int,
        pi_field_id   int
    ) RETURN CLOB AS
        l_result   CLOB := NULL;
    BEGIN
        FOR rec IN (
            SELECT
                text
            FROM
table_1
            WHERE
                id = pi_id
                AND   seq = pi_seq
                AND   language = '001'
                AND   field_id = pi_field_id
            ORDER BY
                seq
        ) LOOP
            l_result := concat(l_result,rec.text);
        END LOOP;

        RETURN l_result;
    END;

SELECT 
    id,
    seq,
    listagg(case when seq = 115501 then text end) within group ( order by seq ) txt_dosage_comments
    listagg(case when seq = 115502 then text end) within group ( order by seq ) fld_txt_total_therapy_duration
FROM
    table_xyz t,
    ( select *
      from   table_1 
      where  language = '001'
      and    seq in (115501,115502)
    ) t1
where t.id =  t1.id(+)
group by id,seq