Thanks Karol,

The following are the query plans in sequential order for the SQL statements in the stored procedure:

SELECT P.PHONE_ID,

         P.AREA_CODE,

         F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO

    FROM PER_PHONE PP

    JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID

   WHERE PP.PERSON_ID = :V_PERSON_ID

     AND PP.DEFAULT_PHONE = 1

     AND PP.LOCATION = 'Home'

     AND PP.STATUS_CODE IN ('G','V')

PLAN JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P INDEX (PK_PHONE))

SELECT FIRST 1

             P.PHONE_ID,

             P.AREA_CODE,

             F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO

        FROM PER_PHONE PP

        JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID

       WHERE PP.PERSON_ID = :V_PERSON_ID

         AND PP.LOCATION = 'Home'

         AND PP.STATUS_CODE IN ('G','V')

         ORDER BY PP.STATUS_CODE

PLAN JOIN (PP ORDER IX_PP_STATUS_CODE2,P INDEX (PK_PHONE))

SELECT P.PHONE_ID,

         P.AREA_CODE,

         F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO

    FROM PER_PHONE PP

    JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID

   WHERE PP.PERSON_ID = :V_PERSON_ID

     AND PP.DEFAULT_PHONE = 1

     AND PP.LOCATION = 'Work'

     AND PP.STATUS_CODE IN ('G','V')

PLAN JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P INDEX (PK_PHONE))

SELECT FIRST 1

             P.PHONE_ID,

             P.AREA_CODE,

             F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO

        FROM PER_PHONE PP

        JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID

       WHERE PP.PERSON_ID = :V_PERSON_ID

         AND PP.LOCATION = 'Work'

         AND PP.STATUS_CODE IN ('G','V')

         ORDER BY PP.STATUS_CODE

PLAN JOIN (PP ORDER IX_PP_STATUS_CODE2,P INDEX (PK_PHONE))

SELECT FIRST 10 P.AREA_CODE,

             F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO

        FROM PER_PHONE PP

        JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID

       WHERE PP.PERSON_ID = :V_PERSON_ID

         AND PP.STATUS_CODE IN ('G','V')

         AND P.PHONE_ID NOT IN (:iPhoneID1, :iPhoneID2)

       ORDER BY PP.CREATE_DATE DESC, PP.STATUS_CODE

PLAN SORT (JOIN (PP INDEX (REFPERSON15,IX_PP_STATUS_CODE2,IX_PP_STATUS_CODE2),P INDEX (PK_PHONE)))

SELECT FIRST 10 P.AREA_CODE,

             F_STRIPSTRING(P.PHONE_NO,'-') AS PHONE_NO

        FROM PER_PHONE PP

        JOIN PHONE P ON P.PHONE_ID = PP.PHONE_ID

       WHERE PP.PERSON_ID = :V_PERSON_ID

         AND PP.STATUS_CODE NOT IN ('G','V')

         AND P.PHONE_ID NOT IN (:iPhoneID1, :iPhoneID2)

         ORDER BY PP.CREATE_DATE DESC, PP.STATUS_CODE

PLAN SORT (JOIN (PP INDEX (REFPERSON15),P INDEX (PK_PHONE)))