Hi DBAs,
We have a query below is running slow (about 15 min). If without
dbms_lob.substr on the LOB, it run about 10 seconds. All tables are very
small. Any suggestion to improve the speed? Thanks!
SELECT DISTINCT program_directive.pd_id,
dbms_lob.substr(program_directive_version.purpose_text, 500, 1) as
purpose_text
FROM program_directive, program_directive_version, version_xref,
program_directive_type_code, app_user nasa_int, app_user resp_focal,
app_user initiator, app_user resp_board_sig, app_user apprv_board_sig,
app_user ip_sig_user, app_user cntr_sig_user, organizations
initiating_org, program_directive_status_code, pd_ip_signature,
pd_contractor_signature, pd_concurrence, pd_csb, pd_csb_board,
pd_evaluation_version, pd_evaluator, agency, contractor_code,
concurrence_status_code, code_value eval_status, code_value
eval_response, evaluator_code, version_xref eval_vxref
WHERE program_directive.pd_id = program_directive_version.pd_id
AND program_directive_version.pd_version_id = version_xref.version_id
AND trim(program_directive_version.product_type_cd) =
trim(version_xref.product_type_cd)
AND trim(program_directive.type_cd) =
trim(program_directive_type_code.code(+))
AND trim(program_directive_version.status_cd) =
trim(program_directive_status_code.code(+))
AND trim(program_directive_version.resp_focal_userid) =
trim(resp_focal.userid(+))
AND trim(program_directive_version.nasa_integrator_userid) =
trim(nasa_int.userid(+))
AND trim(program_directive_version.initiator_userid) =
trim(initiator.userid(+))
AND trim(program_directive_version.resp_board_sig_userid) =
trim(resp_board_sig.userid (+))
AND trim(program_directive_version.apprv_board_sig_userid) =
trim(apprv_board_sig.userid (+))
AND trim(pd_ip_signature.sig_userid) = trim(ip_sig_user.userid (+))
AND trim(pd_contractor_signature.sig_userid) = trim(cntr_sig_user.userid
(+))
AND program_directive_version.initiating_org_id =
initiating_org.organization_id(+)
AND program_directive_version.pd_id = pd_ip_signature.pd_id (+)
AND program_directive_version.pd_version_id =
pd_ip_signature.pd_version_id (+)
AND program_directive_version.pd_id = pd_contractor_signature.pd_id (+)
AND program_directive_version.pd_version_id =
pd_contractor_signature.pd_version_id (+)
AND program_directive_version.pd_id = pd_concurrence.pd_id (+)
AND program_directive_version.pd_version_id =
pd_concurrence.pd_version_id (+)
AND program_directive_version.pd_id = pd_csb.pd_id (+)
AND program_directive_version.pd_version_id = pd_csb.pd_version_id (+)
AND pd_csb.pd_id = pd_evaluation_version.pd_id (+)
AND pd_csb.pd_version_id = pd_evaluation_version.pd_version_id (+)
AND pd_csb.csb_id = pd_evaluation_version.csb_id (+)
AND pd_csb.pd_id = pd_csb_board.pd_id (+)
AND pd_csb.pd_version_id = pd_csb_board.pd_version_id (+)
AND pd_csb.csb_id = pd_csb_board.csb_id (+)
AND pd_evaluation_version.pd_id = pd_evaluator.pd_id (+)
AND pd_evaluation_version.pd_version_id = pd_evaluator.pd_version_id (+)
AND pd_evaluation_version.csb_id = pd_evaluator.csb_id (+)
AND pd_evaluation_version.evaluation_id = pd_evaluator.evaluation_id (+)
AND pd_evaluation_version.evaluation_version_id =
pd_evaluator.evaluation_version_id (+)
AND pd_evaluator.evaluator_code_id = evaluator_code.evaluator_code_id
(+)
AND pd_evaluation_version.evaluation_status_code_id =
eval_status.code_value_id (+)
AND pd_evaluator.eval_response_code_id = eval_response.code_value_id (+)
AND trim(pd_concurrence.concurrence_status_cd) =
trim(concurrence_status_code.code (+))
AND pd_ip_signature.agency_id = agency.agency_id (+)
AND pd_contractor_signature.contractor_id =
contractor_code.contractor_id (+)
AND pd_evaluation_version.evaluation_version_id = eval_vxref.version_id
(+)
AND trim(pd_evaluation_version.product_type_cd) =
trim(eval_vxref.product_type_cd (+))
/
Oracle LazyDBA home page