Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

performance issue with the Oracle SQL query

Sunny_JMay 9 2014 — edited May 15 2014

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

This post has been answered by Etbin on May 10 2014
Jump to Answer

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2014
Added on May 9 2014
14 comments
5,054 views