performance issue with the Oracle SQL query

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