Hello,
I have 1 CREATE TABLE Dynamic Query in procedure. When I run that Query in SQL editor , it takes 10-15 mins.
But same Query running from procedure taking lot of time , giving 'timeout error'
Query having join of multiple tables like :
FROM IS_LOCAL_MART_DM VDM,
DISCREPANCY_MANAGEMENT DM,
CLINICAL_STUDIES CS,
OCL_STUDY_SITES ST,
OCL_SITES OS,
PROCEDURES P,
PROCEDURE_DETAILS PD,
PROCEDURE_QUESTIONS PQ,
PROCEDURE_QUESTION_GROUPS PQG,
PROCEDURE_VARIABLES PV,
PROC_DET_VAR_USAGE PDVU,
RXC.DISCREPANCY_ENTRY_REVIEW_HIST DERH
WHERE VDM.STUDY = CS.STUDY
AND DM.DISCREPANCY_ENTRY_ID = DERH.DISCREPANCY_ENTRY_ID
AND DM.CLINICAL_STUDY_ID = CS.CLINICAL_STUDY_ID
AND DM.CLINICAL_STUDY_ID = ST.CLINICAL_STUDY_ID
AND DM.STUDY_SITE = ST.STUDY_SITE
AND ST.SITE_ID = OS.SITE_ID
AND DM.CLINICAL_STUDY_ID = P.CLINICAL_STUDY_ID
AND DM.PROCEDURE_ID = P.PROCEDURE_ID
AND DM.PROCEDURE_VER_SN = P.PROCEDURE_VER_SN
AND DM.CLINICAL_STUDY_ID = PD.CLINICAL_STUDY_ID
AND DM.PROCEDURE_DETAIL_ID = PD.PROCEDURE_DETAIL_ID
AND DM.PROCEDURE_ID = PD.PROCEDURE_ID
AND PQ.PROCEDURE_QUESTION_GROUP_ID = PQG.PROC_QUE_GRP_ID
AND PQ.PROC_QUES_PROC_VER_SN = PQG.PROC_QUE_GRP_PROC_VER_SN
AND PQ.PROC_QUES_PROC_VER_SN = PV.PROC_VARIABLE_PROC_VER_SN
AND PV.PROCEDURE_QUESTION_ID = PQ.PROC_QUES_ID
AND PQG.PRIMARY_REFERENCE_FLAG = 'Y'
AND PV.PROC_VARIABLE_ID = PDVU.PROC_VARIABLE_ID
AND PV.PROC_VARIABLE_PROC_VER_SN =
PDVU.PROC_DET_VAR_USG_PROC_VER_SN
AND PV.PROCEDURE_VARIABLE_PROC_ID = PDVU.PROCEDURE_ID
AND PDVU.PROC_DETAIL_ID = PD.PROCEDURE_DETAIL_ID
AND PDVU.PROC_DET_VAR_USG_PROC_VER_SN =PD.PROCEDURE_DETAIL_PROC_VER_SN;
Please suggest if any one face same type of issue earlier.
Thanks in Advance.