2 Replies Latest reply: Sep 27, 2013 2:02 PM by Martin Preiss RSS

    query running for a long time

    991805

      Can some help me please below query has been running for a long time I am unable to fix it

       

      SELECT lmt.tab, pr.tab6, pr.tab7,

                 pr.activity_id, pr.resource_id, lmt.tab2,

                  lmt.tab5, pr.txn_lmt_trans_id, pr.analysis_type,

                  pr.resource_type, pr.resource_category, pr.resource_sub_cat,

                  pr.trans_dt, cd.start_dt, cd.end_dt, pr.currency_cd, pr.rt_type,

                  pr.resource_quantity, pr.resource_amount, pr.foreign_amount,

                  pr.foreign_currency, 2, 'S', pr.resource_id_from,

                  hdr.contract_sign_dt, cd.retainage_id, cd.discount_id, 0, 0,

             cd.limit_bi_cntrl_flg, hdr.ca_rqst_src, 0, hdr.currency_cd,

             hdr.rt_type, 0, 0, pr.bi_distrib_status, pr.gl_distrib_status

           FROM ps_c1 lmt, ps_c2 hdr,  ps_c3 cd,

                  ps_c4 pr

               WHERE lmt.tab = 836626

                 AND hdr.tab2 = lmt.tab2

                 AND cd.tab2 = lmt.tab2

                 AND cd.tab5 = lmt.tab5

                 AND cd.pricing_structure = 'RATE'

                 AND pr.tab6 = cd.tab6_pc

                 AND pr.tab2 = cd.tab2

                 AND pr.tab5 = cd.tab5

                 AND (pr.analysis_type IN ('BIL', 'OLT', 'PMR', 'ROL')

                 AND pr.bi_distrib_status <> 'I'

                 OR  pr.analysis_type = 'REV'

                 AND pr.gl_distrib_status IN ('C', 'N'))

                 AND NOT pr.ca_fee_status IN ('2', '3', '4', '5')

                 AND 0 >= (SELECT count(*)

                          FROM ps_ca_lmt4_tao4 lmt4

                          WHERE lmt4.tab = 836626

                            AND lmt4.tab6 = pr.tab6

                            AND lmt4.tab7 = pr.tab7

                            AND lmt4.activity_id = pr.activity_id

                            AND lmt4.resource_id = pr.resource_id))

       

      Below is the execution plan

       

      ID    PID    Operation    Name    Rows    Bytes    Cost    CPU Cost    IO Cost    Temp space    IN-OUT    PQ Dist    PStart    PStop

       

      0        SELECT STATEMENT        2     598     99885     2G    99823                          

      1    0      NESTED LOOPS                                                       

      2    1        NESTED LOOPS        1     299     99885     2G    99823                          

      3    2          NESTED LOOPS        1     264     99883     2G    99821                          

      4    3            NESTED LOOPS        3     273     8     114779     8                          

      5    4              INDEX RANGE SCAN     ps_c1    6     138     1     8321     1                          

      6    4              TABLE ACCESS BY INDEX ROWID    ps_c3    1     68     2     17743     2                          

      7    6                INDEX UNIQUE SCAN    ps_c3    1          1     9021     1                          

      8    3            TABLE ACCESS BY INDEX ROWID    ps_c4    1     173     33292     671M    33271                          

      9    8              INDEX RANGE SCAN    ps_c4    55808          10139     320M    10129                          

      10    9                INDEX UNIQUE SCAN    PS_CA_LMT4_TAO4    1     46     0     1050     0                          

      11    2          INDEX UNIQUE SCAN    ps_c2    1          1     9021     1                          

      12    1        TABLE ACCESS BY INDEX ROWID    ps_c2    1     35     2     17413     2

       

      Can someone please help its really kind of urgent