Performance Issue in Oracle
Hello Experts,
My oracle database version is 12.1.0.2.0.
I have SQL which takes 40 sec to execute and get the result .
My SQL looks something like this :
from the above code if I remove the below condition, this query executes in less than 1 sec but this condition is required but we cannot just remove it else we need to optimize it in writing it in a better way..:
My oracle database version is 12.1.0.2.0.
I have SQL which takes 40 sec to execute and get the result .
My SQL looks something like this :
select distinct d.risk_assessment_id,
CASE WHEN d.re_assessment_flag ='R' THEN 'Reassessment'
ELSE 'New' END
as Type_of_Assessment,
p.supplier_code as Third_Party_Code,
c.object_name as Third_Party_Name,D.ENGAGEMENT_ID,
Q.SIT_SERVICE_NAME as Product_Service_Name
...
...
..
..
..
..
from
MS_SPI_SUPPLIER p,
ms_spi_supplier_sit q,
MS_GRC_CORE_OBJECT c,
MS_SPI_DTCC_SCOPE d
where
p.object_id=q.object_id
and not exists ((select 1 from ms_spi_supplier_v de where de.risk_assessment_id=d.risk_assessment_id and
de.risk_assessment_id in (select ed.immediate_parent_ra_id from ms_spi_supplier_v ed where ed.immediate_parent_ra_id is not null) )
)
and p.object_id=c.object_id
and p.dd_process_instance_id=d.dd_process_instance_id
and q.sit_engagement_id=d.engagement_id
and d.risk_assessment_id is not null
and (d.RA_STATUS=2 or d.ra_status=4) and d.qualification_status=4
and D.RE_ASMT_DUE_DATE is not null and p.QUALITY_STATUS='Active'
and p.object_id is not null;from the above code if I remove the below condition, this query executes in less than 1 sec but this condition is required but we cannot just remove it else we need to optimize it in writing it in a better way..:
and not exists ((select 1 from ms_spi_supplier_v de where de.risk_assessment_id=d.risk_assessment_id and
de.risk_assessment_id in (select ed.immediate_parent_ra_id from ms_spi_supplier_v ed where ed.immediate_parent_ra_id is not null) )
)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks the issue is solved... The code changes provided is working fine, the main issues was with another record which was creating the locks. and releasing time was creating the execution process was delayed . The indexes created on other tables and the old query was still taking 9 sec and later after creating the index on the other tables the query is not executing in less than 2 sec.
Thanks a lot for your help.
Thanks a lot for your help.
ASKER