Forum Stats

  • 3,838,840 Users
  • 2,262,405 Discussions
  • 7,900,767 Comments

Discussions

performance issue with the Oracle SQL query

2»

Answers

  • Sunny_J
    Sunny_J Member Posts: 8

    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
    Etbin Member Posts: 8,968 Gold Crown

    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
    Sunny_J Member Posts: 8
    edited May 14, 2014 3:30AM

    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
    Etbin Member Posts: 8,968 Gold Crown

    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

This discussion has been closed.