AnsweredAssumed Answered

Query Performance - v13.2

Question asked by clarity2689 on Nov 11, 2013
Latest reply on Nov 16, 2013 by Michael.Jain
Hello, Below SQL is consuming high CPU memory in v13.2 which was caught in our Oracle DB analysis. Can anyone let me know what is this SQL about. Is it related to Daramart Extraction Job? *********************************************************************************************************************************************** select odf_q.* , (select investment_type_name from ( SELECT o.code investment_type_code, c.name investment_type_name FROM odf_objects o, cmn_captions_nls c, odf_object_extensions oe WHERE c.pk_id = o.ID AND 1= :v0 and 1=1 AND c.table_name = 'ODF_OBJECTS' AND language_code = :v1 AND oe.extension_code = 'inv' AND oe.object_code = o.code) q_investment_type where rownum = 1 and q_investment_type.investment_type_code = odf_q.investment_type) investment_type_caption , (select name from ( SELECT INV.ID ID , INV.CODE CODE , INV.NAME NAME , CA.ODF_OBJECT_CODE OBJECT_CODE , CLS.NAME TYPE_NAME FROM INV_INVESTMENTS INV LEFT OUTER JOIN INV_PROJECTS PRJ ON INV.ID = PRJ.PRID, ODF_CA_INV CA, ODF_CLASS_V CLS WHERE 1=1 AND NVL(PRJ.IS_TEMPLATE, 0)=0 AND (INV.PURGE_FLAG=0 OR INV.PURGE_FLAG IS NULL) AND CA.ID = INV.ID AND CLS.CODE = CA.ODF_OBJECT_CODE AND CLS.LANGUAGE_CODE = :v2 AND 1= :v3 and 1=1) q_prprojectid where rownum = 1 and q_prprojectid.id = odf_q.prprojectid) prprojectid_caption , (select full_name from ( SELECT SRM_RESOURCES.ID ID, SRM_RESOURCES.LAST_NAME LAST_NAME, SRM_RESOURCES.FIRST_NAME FIRST_NAME, SRM_RESOURCES.FULL_NAME FULL_NAME, SRM_RESOURCES.UNIQUE_NAME UNIQUE_NAME, SRM_RESOURCES.UNIQUE_NAME UNIQUE_CODE FROM SRM_RESOURCES, PRJ_RESOURCES WHERE 1=1 AND 1= :v4 and 1=1 AND SRM_RESOURCES.ID=PRJ_RESOURCES.PRID) q_prresourceid where rownum = 1 and q_prresourceid.id = odf_q.prresourceid) prresourceid_caption , (select prname from ( SELECT PRJ_RES.PRID PRID, PRJ_RES.PRUID UNIQUE_CODE, SRM_RES.FULL_NAME PRNAME FROM PRJ_RESOURCES PRJ_RES , SRM_RESOURCES SRM_RES WHERE PRJ_RES.PRISROLE != 0 AND PRJ_RES.PRID = SRM_RES.ID AND 1=1 AND 1= :v5 and 1=1) q_prroleid where rownum = 1 and q_prroleid.prid = odf_q.prroleid) prroleid_caption , (select b.name from cmn_partitions a, cmn_captions_nls b where a.id = b.pk_id and b.table_name = 'CMN_PARTITIONS' and b.language_code = :v6 and a.code = odf_q.partition_code union select name from cmn_captions_nls where pk_id = -1 and table_name = 'CMN_PARTITIONS' and language_code = :v7 and 'NIKU.ROOT' = odf_q.partition_code) partition_code_caption , (select NAME from cmn_lookups_v where language_code = :v8 and lookup_type = :v9 and rownum = 1 and LOOKUP_ENUM = odf_q.prbooking) prbooking_caption , (select NAME from cmn_lookups_v where language_code = :v10 and lookup_type = :v11 and rownum = 1 and LOOKUP_ENUM = odf_q.restype) restype_caption from ( select row_number() over ( order by odf_pk) odf_row_num, count(*) over (partition by pmd_analytical_partition_by) odf_num_rows , odf_cols.* from ( select 'x' pmd_analytical_partition_by, prteam.prid odf_pk , INV_INVESTMENTS.ODF_OBJECT_CODE investment_type , prteam.prAvailStart pravailstart , prteam.prProjectID prprojectid , PRJ_RESOURCES.PRISROLE isrole , prteam.prResourceID prresourceid , srm_resources.DATE_OF_HIRE prdoh , inv_investments.NAME project_name , prteam.prRoleID prroleid , srm_resources.DATE_OF_TERMINATION prdot , prteam.prAvailFinish pravailfinish , prteam.HARD_FINISH hard_finish , odf_ca_team.partition_code partition_code , prteam.prBooking prbooking , CASE WHEN SRM_RESOURCES.RESOURCE_TYPE < 2 THEN PRTEAM.PRALLOCSUM / 3600 / PRJ_HPD_FACTOR_FCT() ELSE PRTEAM.PRALLOCSUM END prallocsum , srm_resources.EMAIL email , NVL((select 1 from dual where exists ( select rq.id requisition_id from rsm_req_requisitions rq where rq.requirement_id = prteam.prid and rq.status_code = (select lookup_code from cmn_lookups where lookup_type = 'REQUEST_STATUS_LIST' and lookup_enum = prteam.prstatus))), 0) has_requisitions , inv_investments.SCHEDULE_FINISH projectfinish , inv_investments.SCHEDULE_START projectstart , prteam.HARD_START hard_start , srm_resources.FULL_NAME full_name , srm_resources.RESOURCE_TYPE restype , NVL(prteam.pravailstart, inv_investments.schedule_start) pravailstart_sort, NVL(prteam.pravailfinish, inv_investments.schedule_finish) pravailfinish_sort from prteam prteam LEFT OUTER JOIN prj_baseline_details B ASEREC ON BASEREC.OBJECT_ID = PRTeam.PRID AND BASEREC.OBJECT_TYPE='TEAM' and BASEREC.IS_CURRENT = 1, srm_resources CREATED, srm_resources UPDATED, srm_resources srm_resources LEFT OUTER JOIN srm_resources RES_MANAGER ON SRM_RESOURCES.MANAGER_ID=RES_MANAGER.USER_ID, prj_resources prj_resources, inv_investments inv_investments LEFT OUTER JOIN inv_projects inv_projects ON INV_INVESTMENTS.ID = INV_PROJECTS.PRID, odf_ca_team odf_ca_team , (select 'x' pmd_analytical_partition_by, inv_investments.id odf_pk, odf_ca_inv.odf_period_type odf_period_type, inv_investments.entity_code entity, odf_ca_inv.odf_period_start odf_period_start, odf_ca_inv.odf_period_end odf_period_end from inv_investments inv_investments, odf_ca_inv odf_ca_inv where 1 = 1 and inv_investments.id = odf_ca_inv.id) prprojectid where PRTeam.prProjectID = prprojectid.odf_pk AND ( PRTEAM.PRRESOURCEID IN (SELECT PV.OBJECT_INSTANCE_ID FROM CMN_SEC_CHK_USER_V0 PV, SRM_RESOURCES RES WHERE PV.OBJECT_ID = 664 AND PV.PERMISSION_CODE = 'ResourceViewBook' AND PV.USER_ID = :v12 AND RES.ID = PV.OBJECT_INSTANCE_ID AND RES.IS_ACTIVE = 1) OR PRTEAM.PRPROJECTID IN ( -- GET IDS FOR ANY GLOBAL RIGHTS INVESTMENTS SELECT ID FROM INV_INVESTMENTS WHERE ODF_OBJECT_CODE IN ( SELECT OE.OBJECT_CODE FROM ODF_OBJECT_EXTENSIONS OE, ODF_OBJECTS O, CMN_SEC_CHK_USER_R_V S WHERE S.USER_ID = :v13 AND O.CODE = OE.OBJECT_CODE AND OE.EXTENSION_CODE = 'inv' AND S.OBJECT_TYPE = 'RECORD' AND S.PERMISSION_CODE = 'READ' AND S.COMPONENT_CODE = 'INV' AND O.IS_CUSTOM = 0 AND S.OBJECT_CODE = O.RIGHT_CODE ) AND INV_INVESTMENTS.IS_ACTIVE = 1 UNION -- GET THE INSTANCE IDS SELECT OBJECT_INSTANCE_ID FROM CMN_SEC_CHK_USER_V0 PERM, INV_INVESTMENTS INV WHERE PERM.USER_ID = :v14 AND PERM.PERMISSION_CODE = 'READ' AND PERM.OBJECT_ID IN (SELECT S.ID FROM ODF_OBJECT_EXTENSIONS OE, ODF_OBJECTS O, CMN_SEC_OBJECTS S WHERE O.CODE = OE.OBJECT_CODE AND OE.EXTENSION_CODE = 'inv' AND S.OBJECT_TYPE_CODE = 'RECORD' AND S.COMPONENT_CODE = 'INV' AND O.IS_CUSTOM = 0 AND S.OBJECT_CODE = O.RIGHT_CODE ) AND INV.ID = PERM.OBJECT_INSTANCE_ID AND INV.IS_ACTIVE = 1 ) ) and prteam.created_by = CREATED.user_id and prteam.last_updated_by = UPDATED.user_id and prteam.prresourceid = srm_resources.id and prteam.prresourceid = prj_resources.prid and prteam.prprojectid = inv_investments.id and prteam.prid = odf_ca_team.id and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 ) odf_cols ) odf_q where odf_q.odf_row_num between :v15 and :v16 order by odf_q.odf_row_num ***********************************************************************************************************************************************

Outcomes