performance issue with the Oracle SQL query

Sunny_J

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

Etbin

Hints might not be necessary (necessity proportional to cardinalities)

select pc.id,pc.dtype,pc.version,pc.external_ref,pc.creation_ts,rq.type

  from pas_case pc,

       as_request rq,

       pas_context cn

where rq.case_id = pc.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 cn.creation_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

   and cn.creation_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

------ 2nd query

with

main_request as

(select /*+ materialize */

        cn.id as context_id,rq.type as main_req_type

   from pas_request rq

         join

        pas_context cn

     on rq.id = cn.request_id

    and rq.dtype = 'MAINREQUEST'

    and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.chc.polling',

                    'bgc.tbf.repair.vap','bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution',

                    'bgc.cbm','bgc.dar.e2etest.preparation'

                   )

    and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

    and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

    and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

    and cn.id between 20141800000000000 and 20141999999999999

    and rq.id between 20141800000000000 and 20141999999999999

)

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

               main_request 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

       main_request main_req

    on ra.request_id = main_req.context_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

                       join

                       pas_context cn

                    on 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 cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

                   and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

                   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

where type is null

    or (type is not null

   and  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'

                )

       )

------ 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

                               join

                               pas_context cn

                            on 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 cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

                           and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

                           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

   and ri.id between 20141800000000000 and 20141999999999999

where ri.name is null

    or (ri.name is not null

   and  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'

                   )

       )

------ 5th query

with

main_request as

(select /*+ materialize */

        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

        join

        pas_context cn

      on 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 cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

    and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

    and cn.id between 20141800000000000 and 20141999999999999

    and mreq.id  between 20141800000000000 and 20141999999999999

)

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

       main_request main_req

    on tsk.parent_context_id = main_req.id

   and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')

   and tsk.id between $$low_id1 and $$high_id1

union

select id,version,dtype,case_id,type,correlation_id,initiator,executor,

       category,parent_context_id,creation_ts,type

  from main_request

Regards

Etbin

Biju Das

Oracle hint should be the last option to use.What is the data type of the column where you are using CAST eg, CN.CREATION_TS?  Is there any index on this column? Use of function over this column will ignore index if any on this column.

Information you provided is not sufficient.

Regards

Biju

SomeoneElse

> i request from this forum for an urgent help

Keep in mind this is a forum of volunteers.  All posts have the same priority: none.

RiteshSinha

Hello Sunny,

Could you please attach the explain plan of these queries it available.

Regards,

Ritesh

Sunny_J

Hi Biju

Thanks for your reply .

 

CREATION_TS :  is TIMESTAMP(6) data type

END_TS: is TIMESTAMP(6) data type

There is an index defined o nthe creation_ts column

chris227

If i was the DB or the QS i would prefer fixing the more "basic" issues with your queries rather than suggesting the usage of hints.

1. Handle dates as dates and give the optimizer the possibility to do the same: CAST(CN.CREATION_TS AS DATE)

2. Dont mix ansi-join syntax with oracle join syntax

Unknown

Each table in the query contains 1.2 million records and DBA suggested to use the 'Oracle - hints'

1. A DBA should NOT be recommending ANY hints unless that DBA knows that the hint is required to fix whatever (currently unknown) problem you are having.

2. Hints are generally only used to help determine what the problem is. There are exceptions but hints are NOT normally used to actually 'fix' the problem.

jihuyao

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')

It is weird to think about the business meaning of excluding 1 sec for the time range.  Is not it simple to do something like trunc(end_ts) = :inputdate and create a function index to access the one day data?  The function index is probably good enough for the first query and also helpful to the others (add other columns in if needed).

Sunny_J

Thank you for your reply

Where as the field is having date time in the mentioned format .

the field   CREATION_TS = 06-MAY-14 12.30.07.205000 PM

will it be fine still if we use the TO_TIMESTAMP .  would you please suggest

chris227

What is the data type of the column CREATION_TS?

If it is timestamp than the "format" you see is just a "display" format.

If it is varchar2 your on the wrong road.

Etbin

Hints might not be necessary (necessity proportional to cardinalities)

select pc.id,pc.dtype,pc.version,pc.external_ref,pc.creation_ts,rq.type

  from pas_case pc,

       as_request rq,

       pas_context cn

where rq.case_id = pc.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 cn.creation_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

   and cn.creation_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

------ 2nd query

with

main_request as

(select /*+ materialize */

        cn.id as context_id,rq.type as main_req_type

   from pas_request rq

         join

        pas_context cn

     on rq.id = cn.request_id

    and rq.dtype = 'MAINREQUEST'

    and rq.type in ('bgc.dar.vap.resolution.advice','bgc.dar.e2etest.execution','bgc.chc.polling',

                    'bgc.tbf.repair.vap','bgc.dar.e2etest','bgc.dar.e2etest.intermediate.execution',

                    'bgc.cbm','bgc.dar.e2etest.preparation'

                   )

    and cn.status in ('FINISHED','CANCELLED','TIMEOUT','ERROR')

    and cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

    and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

    and cn.id between 20141800000000000 and 20141999999999999

    and rq.id between 20141800000000000 and 20141999999999999

)

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

               main_request 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

       main_request main_req

    on ra.request_id = main_req.context_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

                       join

                       pas_context cn

                    on 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 cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

                   and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

                   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

where type is null

    or (type is not null

   and  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'

                )

       )

------ 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

                               join

                               pas_context cn

                            on 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 cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

                           and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

                           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

   and ri.id between 20141800000000000 and 20141999999999999

where ri.name is null

    or (ri.name is not null

   and  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'

                   )

       )

------ 5th query

with

main_request as

(select /*+ materialize */

        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

        join

        pas_context cn

      on 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 cn.end_ts >= to_timestamp('2014-05-06 00:00:00','yyyy-mm-dd hh24:mi:ss')

    and cn.end_ts <= to_timestamp('2014-05-06 23:59:59.999999','yyyy-mm-dd hh24:mi:ss.ff6')

    and cn.id between 20141800000000000 and 20141999999999999

    and mreq.id  between 20141800000000000 and 20141999999999999

)

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

       main_request main_req

    on tsk.parent_context_id = main_req.id

   and tsk.dtype in ('ANALYSIS_TASK','DECISION_TASK')

   and tsk.id between $$low_id1 and $$high_id1

union

select id,version,dtype,case_id,type,correlation_id,initiator,executor,

       category,parent_context_id,creation_ts,type

  from main_request

Regards

Etbin