Dears
Good evening
I'm new to start using Oracle SQL , I have a sql query which is taking more time to execute in the production .
Each table in the query contains 1.2 million records and DBA suggested to use the 'Oracle - hints' . i dont have proper knowledge on this topic and facing problems to implement this hints to imrpovise the performance . In the product the Informatica jobs are failed and stuck with this query performance issue .
i request from this forum for an urgent help for me to resolve this issue by using 'hints' . kindly help me .
SELECT
CASE.ID,
CASE.DTYPE,
CASE.VERSION,
CASE.EXTERNAL_REF,
CASE.CREATION_TS,
RQ.TYPE
FROM
PAS_CASE CASE,
AS_REQUEST RQ,
PAS_CONTEXT CN
where rq.case_id = case.id
AND rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and CN.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
AND CAST(CN.CREATION_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND
CAST(CN.CREATION_TS AS DATE) < TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
------ 2nd query
SELECT
RA.ID,
RA.VERSION,
RA.REQUEST_ID,
RA.NAME,
RA.VALUE,
RA.LOB_ID,
RA.DTYPE,
RA.CREATION_TS,
TASK.MAIN_REQ_TYPE
from PAS_REQUESTATTRIBUTE RA
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on RA.REQUEST_ID = task.ID
and RA.ID between $$LOW_ID1 AND $$HIGH_ID1
UNION
SELECT
RA.ID,
RA.VERSION,
RA.REQUEST_ID,
RA.NAME,
RA.VALUE,
RA.LOB_ID,
RA.DTYPE,
RA.CREATION_TS,
MAIN_REQ.TYPE
from PAS_REQUESTATTRIBUTE RA
join
(
select rq.id,rq.type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on RA.REQUEST_ID = main_req.ID
--- 3rd query
SELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT ,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is not null
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
UNION
SELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is nullSELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT ,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is not null
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
UNION
SELECT
RB.ID,
RB.DTYPE,
RB.VERSION,
RB.TYPE,
RB.CREATION_TS,
RB.TASK_ID,
RB.COLOR,
RB.GLOBAL_RESULT,
TASK.MAIN_REQ_TYPE
FROM
PAS_RESULTBLOCK RB
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
) task
on rb.task_id = task.ID
and rb.ID between $$LOW_ID1 AND $$HIGH_ID1
and RB.TYPE is null
--- 4th query
SELECT
RI.ID,
RI.DTYPE,
RI.VERSION,
RI.RESULTBLOCK_ID,
RI.NAME,
RI.VALUE,
RI.UNIT,
RI.COLOR,
RI.LOB_ID,
RI.CREATION_TS,
RI.SEQUENCE,
RI.DETAILLEVEL,
RES_BLK.MAIN_REQ_TYPE
FROM
PAS_RESULTITEM RI
join
(
select
rb.ID, rb.TYPE as rb_type , task.TYPE as task_type, task. main_req_type from pas_resultblock rb
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between 20141800000000000 AND 20141999999999999
) task
on rb.task_id = task.ID
and rb.ID between 20141800000000000 AND 20141999999999999
and RB.TYPE IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
) res_blk
on ri.resultblock_id = res_blk.ID
where RI.NAME IN
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and RI.ID between 20141800000000000 AND 20141999999999999
and RI.NAME is not null
UNION
select
RI.ID,
RI.DTYPE,
RI.VERSION,
RI.RESULTBLOCK_ID,
RI.NAME,
RI.VALUE,
RI.UNIT,
RI.COLOR,
RI.LOB_ID,
RI.CREATION_TS,
RI.SEQUENCE,
RI.DETAILLEVEL ,
RES_BLK.MAIN_REQ_TYPE
from pas_resultitem ri
join
(
select
rb.ID, rb.TYPE as rb_type , task.TYPE as task_type, task. main_req_type from pas_resultblock rb
join
(
select tsk.ID , tsk.TYPE, main_req. main_req_type
from PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between 20141800000000000 AND 20141999999999999
) task
on rb.task_id = task.ID
and rb.ID between 20141800000000000 AND 20141999999999999
) res_blk
on ri.resultblock_id = res_blk.ID
where RI.ID between 20141800000000000 AND 20141999999999999
and RI.NAME is null
-- 5HT QUERY
SELECT
TSK.ID,
TSK.VERSION,
TSK.DTYPE,
TSK.CASE_ID,
TSK.TYPE,
TSK.CORRELATION_ID,
TSK.INITIATOR,
TSK.EXECUTOR,
TSK.CATEGORY,
TSK.PARENT_CONTEXT_ID,
TSK.CREATION_TS,
MAIN_REQ.MAIN_REQ_TYPE
FROM
PAS_REQUEST tsk
join
(
select cn.id as context_id, rq.TYPE as main_req_type
from PAS_REQUEST rq
, PAS_CONTEXT cn
where rq.id = cn.request_id
and rq.DTYPE = 'MAINREQUEST'
and rq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and rq.ID between 20141800000000000 AND 20141999999999999
) main_req
on tsk.parent_context_id = main_req.context_id
and tsk.DTYPE in ('ANALYSIS_TASK','DECISION_TASK')
and tsk.ID between $$LOW_ID1 AND $$HIGH_ID1
UNION
select
MREQ.ID,
MREQ.VERSION,
MREQ.DTYPE,
MREQ.CASE_ID,
MREQ.TYPE,
MREQ.CORRELATION_ID,
MREQ.INITIATOR,
MREQ.EXECUTOR,
MREQ.CATEGORY,
MREQ.PARENT_CONTEXT_ID,
MREQ.CREATION_TS,
MREQ.TYPE
from PAS_REQUEST mreq
, PAS_CONTEXT cn
where mreq.id = cn.request_id
and mreq.DTYPE = 'MAINREQUEST'
and mreq.TYPE in
(
'bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.tbf.repair.vap','bgc.dar.e2etest',
'bgc.dar.e2etest.intermediate.execution','bgc.cbm','bgc.dar.e2etest.preparation','bgc.chc.polling'
)
and cn.STATUS in ('FINISHED','CANCELLED','TIMEOUT','ERROR')
and CAST(cn.END_TS AS DATE) >= TO_DATE('2014-05-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
and CAST(cn.END_TS AS DATE) <= TO_DATE('2014-05-06 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
and cn.ID between 20141800000000000 AND 20141999999999999
and mreq.ID between 20141800000000000 AND 20141999999999999