The SP below, slows down to several minutes when the tables become
larger. How would I optimize the query. The query plan is below.

BEGIN
FOR SELECT ENTY_PATIENT.PATIENT_ID PRIMARYKEY,
ENTY_PERSON.PERSON_LASTNAME || ', ' || ENTY_PERSON.PERSON_FIRSTNAME
"SEARCH RESULT"
FROM ENTY_PATIENT INNER JOIN
ENTY_PERSON ON ENTY_PATIENT.ENTITY_ID = ENTY_PERSON.ENTITY_ID
WHERE (ENTY_PERSON.ENTITY_ID
IN(SELECT ENTITY_ID FROM ENTY_PATIENT
WHERE (PATIENT_ID IN (SELECT PATIENT_ID FROM LINK_MEDICALFACILITY_PATIENT
WHERE (MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN)))))
AND (lower(ENTY_PERSON.PERSON_LASTNAME) LIKE lower(:SEARCHSTRING_IN)
|| '%')
INTO
:PRIMARYKEY,
:"SEARCH RESULT"
DO
SUSPEND;
END