Forum Stats

  • 3,874,178 Users
  • 2,266,677 Discussions
  • 7,911,757 Comments

Discussions

performance issue with the Oracle SQL query

Sunny_J
Sunny_J Member Posts: 8
edited May 15, 2014 3:46PM in SQL & PL/SQL

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

Best Answer

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited May 10, 2014 2:12AM Answer ✓

    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

«1

Answers

  • Biju Das
    Biju Das Member Posts: 393
    edited May 9, 2014 3:21PM

    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
    SomeoneElse Member Posts: 14,867 Silver Crown

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

  • Hello Sunny,

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

    Regards,

    Ritesh

  • Sunny_J
    Sunny_J Member Posts: 8
    edited May 9, 2014 5:40PM

    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
    chris227 Member Posts: 3,517 Bronze Crown

    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
    edited May 10, 2014 11:47AM
    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
    jihuyao Member Posts: 462
    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
    Sunny_J Member Posts: 8

    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
    chris227 Member Posts: 3,517 Bronze Crown

    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
    Etbin Member Posts: 8,968 Gold Crown
    edited May 10, 2014 2:12AM Answer ✓

    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

This discussion has been closed.