Skip to Main Content
Forums

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!

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

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

Marked as Answer by Sunny_J · Sep 27 2020
Sunny_J

Thanks alot Etbin for your suggestion and the sample query.

i tired to execute the queries that you have mentioned .

2nd Query is not executing it is still executing after 15min also  and 4th query is not giving any result set now.

I request for your suggestion .

Etbin

replacing or condition with union is all that can be suggested.

------ 3rd query

with

base_table as

(select /*+ materialize */

        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

)

select id,dtype,version,type,creation_ts,task_id,color,global_result,main_req_type

  from base_table

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

               )

union

select id,dtype,version,type,creation_ts,task_id,color,global_result,main_req_type

  from base_table

where type is null

------ 4th query

with

base_table as

(select /*+ materialize */

        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

)

select id,dtype,version,resultblock_id,name,value,unit,color,lob_id,creation_ts,sequence,detaillevel,main_req_type

  from base_table

where name is not null

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

               )

union

select id,dtype,version,resultblock_id,name,value,unit,color,lob_id,creation_ts,sequence,detaillevel,main_req_type

  from base_table

where name is null

As you can see I'm just trying to perform the first subquery only once and knowing only the query that's pretty much all that can be suggested (it's more dealing with pattern recognition rather than query tuning)

Regards

Etbin

Sunny_J

Thanks alot Etbin all the queries are working except the 2nd query .

i tried to change but it is giving 0 rows , when i tried with the old query after 1 hr it shows 1000 rows .

i request for your suggest is something i missed out .

Etbin

Based only on code I cannot figure out why the two versions return different results (assuming each id used in joins is not null).

Regards

Etbin

1 - 14
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 12 2014
Added on May 9 2014
#issue, #oracle, #performance, #query, #sql, #with
14 comments
5,074 views