Tuning SQL Query which returns REF CURSOR

Question

I am using reference cursor in function to return values. This function is getting called recursively for different input values.

My package Definition is as follows.

create or replace PACKAGE AI_QUERY_EXECUTION_PKG IS 

TYPE AI_STORE_SAMPLE_RECORD
IS
  RECORD
  (
    STORE_ID SM_SAMPLE_STORES.STORE_ID%TYPE,
    STORE_NAME SM_STORES_IDENTIFICATION.STORE_NAME%TYPE,
STORE_ADDRESS SM_STORES_IDENTIFICATION.STORE_ADDRESS%TYPE,
SMS_ID SM_STORES_IDENTIFICATION.SMS_ID%TYPE,
SMS_SERVICE SM_STORES_IDENTIFICATION.SMS_SERVICE%TYPE,
AREA_VALUE_ID SM_ATTRIBUTE_BY_STORE.STORE_ATTRIBUTE_VAL_ID%TYPE,
AREA_NAME SM_STANDARD_ATTRIBUTES_VALUES.ATTRIBUTE_VALUE_DESC%TYPE,
STORE_STATUS SM_SAMPLE_STORES.STORE_STATUS%TYPE );

TYPE AI_STORE_SAMPLE_CURSOR
IS
  REF
  CURSOR
    RETURN AI_STORE_SAMPLE_RECORD;

FUNCTION GET_STORE_SAMPLE_DATA(
  pcountryId      IN NUMBER,
  pstudyProductId IN VARCHAR2,
  pnsoSampleId in number,
  pnsoPeriodKey in NUMBER,
  err_code OUT VARCHAR2,
  err_msg OUT VARCHAR2)
  RETURN AI_QUERY_EXECUTION_PKG.AI_STORE_SAMPLE_MARKET_CURSOR;

 END AI_QUERY_EXECUTION_PKG;

My function is as below.

FUNCTION GET_STORE_SAMPLE_DATA(
pcountryId      IN NUMBER,
pstudyProductId IN VARCHAR2,
pnsoSampleId    IN NUMBER,
pnsoPeriodKey   IN NUMBER,
err_code OUT VARCHAR2,
err_msg OUT VARCHAR2)
  RETURN AI_QUERY_EXECUTION_PKG.AI_STORE_SAMPLE_CURSOR
AS
  REF_AI_STORE_SAMPLE_CURSOR AI_QUERY_EXECUTION_PKG.AI_STORE_SAMPLE_CURSOR;
  x_progress   INTEGER (4) := 0;

  v_err_code   VARCHAR2 (30000);

 v_err_msg    VARCHAR2 (30000);

sms_service1 NUMBER;

sms_service2 NUMBER;

BEGIN

 x_progress := 10;


SELECT MAX(DECODE (sms_service_id, 1, sm_std_attr.productid, 0)) sms_service1,
MAX(DECODE (sms_service_id, 2, sm_std_attr.productid, 0)) sms_service2

INTO sms_service1,
sms_service2

FROM sm_attribute_lookup sm_attr_lookup,
SM_STANDARD_ATTRIBUTES sm_std_attr

WHERE sm_attr_lookup.country_id    = pcountryId
  AND sm_attr_lookup.sms_service_id IN (1, 2)
  AND sm_attr_lookup.country_id      = sm_std_attr.country_id
  AND sm_attr_lookup.attribute_id    = sm_std_attr.attribute_id
  AND sm_attr_lookup.sms_service_id  = sm_std_attr.SMS_SERVICE;

x_progress                        := 20;

OPEN REF_AI_STORE_SAMPLE_CURSOR FOR

SELECT /*+ parallel (4) */ DISTINCT SMPL_STORES.store_id,
stores.STORE_NAME,
STORES.STORE_ADDRESS,
STORES.SMS_ID,
STORES.SMS_service,
sm_attr_by_store.store_attribute_val_id ARea_value_id,
(SELECT attribute_value_desc
FROM sm_standard_attributes_values
WHERE country_id = pcountryId
AND productid    = TO_CHAR ( sm_attr_by_store.store_attribute_val_id)
AND ROWNUM       < 2
) area_name,
smpl_stores.store_status
  FROM CLIENT_STUDIES STDY,
RELATIONSHIP REL,
CLIENT_MARKET_SET CLNT_MKT_SET,
CLIENT_MARKET_LIST CLNT_MKT_LST,
SM_SAMPLE_STORES SMPL_STORES ,
SM_SAMPLE_MARKETS SMPL_MARKETS,
sm_stores_identification stores,
SM_ATTRIBUTE_BY_STORE sm_attr_by_store
  WHERE REL.ACTIVE = 'Y'
  AND REL.TYPE     =
    (SELECT /*+ INDEX (relationshipdefinition XPKRELDEF2)*/ type
FROM relationshipdefinition
WHERE name='STUDY_CONTAINS_CLIENT_MARKET_SET'
AND active='Y'
)
  AND REL.OWNERID                         = getcatalog_id('CLIENT_STUDIES')
  AND REL.PARENTID                        = STDY.CPRODUCTKEYID
      AND REL.PARENTVERSION                   = STDY.CMODVERSION
  AND REL.CHILDID                         = CLNT_MKT_SET.CPRODUCTKEYID
  AND REL.CHILDVERSION                    = CLNT_MKT_SET.CMODVERSION
  AND STDY.COUNTRY_ID                     = pcountryId
  AND STDY.PRODUCTID                      = pstudyProductId
  AND STDY.COUNTRY_ID                     = CLNT_MKT_SET.COUNTRY_ID
  AND STDY.COUNTRY_ID                     = CLNT_MKT_LST.COUNTRY_ID
  AND STDY.COUNTRY_ID                     = SMPL_STORES.COUNTRY_ID
  AND STDY.COUNTRY_ID                     = SMPL_MARKETS.COUNTRY_ID
  AND CLNT_MKT_LST.MARKET_SET_ID          = CLNT_MKT_SET.PRODUCTID
  AND CLNT_MKT_LST.SAMPLE_ID              = SMPL_STORES.SAMPLE_ID
  AND SMPL_STORES.PERIOD_ID               =pnsoPeriodKey
  AND SMPL_STORES.SAMPLE_ID               =pnsoSampleId
  AND SMPL_STORES.STORE_STATUS           IN(1,3)
  AND SMPL_MARKETS.NSO_MARKET_ID          =CLNT_MKT_LST.MARKET_ID
  AND SMPL_STORES.PERIOD_ID               =SMPL_MARKETS.PERIOD_ID
  AND SMPL_STORES.CELL_ID                 =SMPL_MARKETS.CELL_ID
  AND SMPL_MARKETS.SAMPLE_ID              =SMPL_STORES.SAMPLE_ID
  AND SMPL_STORES.store_id                = stores.productid
  AND stores.country_id                   = pcountryId
  AND sm_attr_by_store.STORE_ATTRIBUTE_ID = DECODE (STORES.SMS_service, 1,         sms_service1, sms_service2)
  AND sm_attr_by_store.store_id           = stores.productid
  AND sm_attr_by_store.country_id         = pcountryId;
  err_code := 'SUCCESS';
  err_msg:= x_progress || ' - DONE GET_STORE_SAMPLE_DATA - ' || SQLCODE;
  RETURN REF_AI_STORE_SAMPLE_CURSOR;
CLOSE   REF_AI_STORE_SAMPLE_CURSOR;
  x_progress := 20;
EXCEPTION
WHEN OTHERS THEN
  err_code := x_progress || ' - ' || SQLCODE;
 err_msg  := SUBSTR (SQLERRM, 1, 500);
END GET_STORE_SAMPLE_DATA;

If I dont use parallel hints into select statement of the cursor the query performance is very poor and for multiple recursive calls query almost takes 8 hours to 24 hours depending on data.

with parallel hints query's performance is increased and it takes 1.30 hours to finish the run. but it takes 250GB of TEMPSPACE.

Can you please suggest me the solution for this query to tune.