4 Replies Latest reply: Jan 10, 2013 3:50 AM by chris227 RSS

    Alternate way to write  the query :

    983563
      Dear Experts I am beginner of sql .With my knowledge i have written this query
      but it is taking very large time in my 11g data base
      Please tell any alternative to write this query in another way to increase the performance

      Please find the explain below
      SELECT DISTINCT *
                 FROM UPGRADATION r1,
                      LOG t2,
                      OMNIACCOUNT m1,
                      (SELECT rid
                         ,  LISTAGG(ACCOUNTNO, ',') WITHIN GROUP (ORDER BY ACCOUNTNO) AS ACCOUNTNO
                                 FROM   OMNIACCOUNT
                                   GROUP  BY rid) mk3,
                      (SELECT mobileno
                            ,  LISTAGG(ACCOUNTNO, ',') WITHIN GROUP (ORDER BY ACCOUNTNO) AS ACCOUNTNO
                                      FROM   OMNIACCOUNT
                                             GROUP  BY mobileno) mk1,
                      (SELECT mobileno
                            ,  LISTAGG(ACCOUNTNO, ',') WITHIN GROUP (ORDER BY accounttype) AS ACCOUNTTYPE
                                      FROM   OMNIACCOUNT
                                             GROUP  BY mobileno) mk2
                WHERE t2.txnauthid = r1.txnauthid
                  AND r1.rid = mk3.rid
                  AND t2.txntype = '54'
                  AND mk1.mobileno = r1.regmobileno
                  AND t2.reqtype = '11'
                  AND t2.dc_id IN (SELECT deliverychannel_id
                                     FROM Channel)
                  AND t2.custbankcode = '32472'
                  AND t2.txndatetime BETWEEN TO_DATE ('28/12/2011 12:00:00 AM',
                                                      'DD/MM/YYYY hh:mi:ss AM'
                                                     )
                                         AND TO_DATE ('28/12/2012 11:59:59 PM',
                                                      'DD/MM/YYYY hh:mi:ss PM'
                                                     )
                  AND r1.rid = m1.rid(+)
                  AND t2.txnstatus IN ('C', 'F', 'T')
                  AND t2.paymentinitiator = 'C'
                  AND mk1.mobileno = mk2.mobileno
      
      
      
      
      Explain Plan For the above statement :
      -----------------------------------
      
      
      
      SELECT STATEMENT  FIRST_ROWSCost: 16  Bytes: 4,270  Cardinality: 1                                      
          25 HASH UNIQUE  Cost: 16  Bytes: 4,270  Cardinality: 1                                  
              24 NESTED LOOPS  Cost: 15  Bytes: 4,270  Cardinality: 1                              
                  19 NESTED LOOPS  Cost: 14  Bytes: 4,270  Cardinality: 1                          
                      13 HASH JOIN  Cost: 13  Bytes: 2,268  Cardinality: 1                      
                          9 NESTED LOOPS OUTER  Cost: 4  Bytes: 252  Cardinality: 1                  
                              7 NESTED LOOPS  Cost: 3  Bytes: 239  Cardinality: 1              
                                  4 NESTED LOOPS  Cost: 2  Bytes: 119  Cardinality: 1          
                                      2 TABLE ACCESS BY INDEX ROWID TABLE LOG Cost: 1  Bytes: 116  Cardinality: 1      
                                          1 INDEX RANGE SCAN INDEX IX_LOG Cost: 1  Cardinality: 8  
                                      3 INDEX UNIQUE SCAN INDEX (UNIQUE) PK_Channel Cost: 1  Bytes: 3  Cardinality: 1      
                                  6 TABLE ACCESS BY INDEX ROWID TABLE UPGRADATION Cost: 1  Bytes: 120  Cardinality: 1          
                                      5 INDEX RANGE SCAN INDEX IDX_REG_AUTH Cost: 1  Cardinality: 1      
                              8 INDEX RANGE SCAN INDEX IDX_MLTN_BNK_ACCT Cost: 1  Bytes: 13  Cardinality: 1              
                          12 VIEW  Cost: 8  Bytes: 1,110,816  Cardinality: 551                  
                              11 SORT GROUP BY  Cost: 8  Bytes: 16,530  Cardinality: 551              
                                  10 TABLE ACCESS FULL TABLE OMNIACCOUNT Cost: 7  Bytes: 21,210  Cardinality: 707          
                      18 VIEW PUSHED PREDICATE  Cost: 1  Bytes: 2,002  Cardinality: 1                      
                          17 FILTER                  
                              16 SORT GROUP BY  Bytes: 33  Cardinality: 1              
                                  15 TABLE ACCESS BY INDEX ROWID TABLE OMNIACCOUNT Cost: 1  Bytes: 33  Cardinality: 1          
                                      14 INDEX RANGE SCAN INDEX IDX_MUL_LNKG Cost: 1  Cardinality: 1      
                  23 VIEW PUSHED PREDICATE  Cost: 1  Cardinality: 1                          
                      22 FILTER                      
                          21 SORT GROUP BY  Bytes: 29  Cardinality: 1                  
                              20 INDEX RANGE SCAN INDEX IDX_MLTN_BNK_ACCT Cost: 1  Bytes: 29  Cardinality: 1              
        • 1. Re: Alternate way to write  the query :
          sb92075
          AND r1.rid = m1.rid(+)
          above results in FTS & you must pay the price of doing it.
          • 2. Re: Alternate way to write  the query :
            riedelme
            980560 wrote:
            Please tell any alternative to write this query in another way to increase the performance
            Scalar subqueries (the subqueries in the SELECT clause) can affect performance. This one is unusally complex, with inline views which can also affect performance. Can you rewrite the SQL to replace the scalar subquery with straighforward joins and see what performance is like then?

            A correlated EXISTS subquery might be more efficient than the IN subquery if you can do a fast indexes lookup. Depends how many rows are being read.

            How long is the SQL taking? How many rows are being read? How many rows are being returned?

            Edited by: riedelme on Jan 8, 2013 8:28 AM
            • 3. Re: Alternate way to write  the query :
              983563
              Can you give an eample for this one which is related to above query
              Scalar subqueries (the subqueries in the SELECT clause) can affect performance. This one is unusally complex, with inline views which can also affect performance. Can you rewrite the SQL to replace the scalar subquery with straighforward joins and see what performance is like then?
              Here i want to retreive data from each and every table yhts why i have designed query like this
              A correlated EXISTS subquery might be more efficient than the IN subquery if you can do a fast indexes lookup. Depends how many rows are being read.
              It is taking approximately 1 Min in production DB Due to this stuck threads are observed in web logic
              approximately 10,000 rows
              How long is the SQL taking? How many rows are being read? How many rows are being returned?
              • 4. Re: Alternate way to write  the query :
                chris227
                Best would be to start from the projection you want.
                Probably you dont really want all columns form this query.
                However these are your join criteria:
                t2.txnauthid = r1.txnauthid
                            AND r1.rid = mk3.rid
                            AND r1.regmobileno = mk1.mobileno
                            AND mk1.mobileno = mk2.mobileno
                            AND r1.rid = m1.rid(+)
                Perhaps you can get rid of AND mk1.mobileno = mk2.mobileno
                with
                (SELECT mobileno
                                      ,  LISTAGG(ACCOUNTNO, ',') WITHIN GROUP (ORDER BY ACCOUNTNO) AS ACCOUNTNO
                                      ,  LISTAGG(ACCOUNTNO, ',') WITHIN GROUP (ORDER BY accounttype) AS ACCOUNTTYPE
                                                FROM   OMNIACCOUNT
                                                       GROUP  BY mobileno)
                Following combination seems to be suspicous:
                AND r1.rid = mk3.rid
                AND r1.rid = m1.rid(+)
                The first restricts the result on all rows with the same id in
                OMNIACCOUNT and UPGRADATION
                so o dont see any gain from the outer join.

                Do you really need the listagg over all OMNIACCOUNT rows.
                If not it would be probably better to do the listagg in the final projection.

                May be one way could be to start with OMNIACCOUNT and outer join UPGRADATION to it.
                Than you can do the listagg at the end.

                Perhaps something like
                select
                *
                from
                OMNIACCOUNT m1
                left outer join
                (Select
                 projection goes here
                from
                log t2 join UPGRADATION r1
                on
                r1.txnauthid = t2.txnauthid
                    AND t2.reqtype = '11'
                    AND t2.dc_id IN (SELECT deliverychannel_id
                                       FROM Channel)
                    AND t2.custbankcode = '32472'
                    AND t2.txndatetime BETWEEN TO_DATE ('28/12/2011 12:00:00 AM',
                                                        'DD/MM/YYYY hh:mi:ss AM'
                                                       )
                                           AND TO_DATE ('28/12/2012 11:59:59 PM',
                                                        'DD/MM/YYYY hh:mi:ss PM'
                                                       )
                    AND t2.txntype = '54'
                    AND t2.txnstatus IN ('C', 'F', 'T')
                    AND t2.paymentinitiator = 'C'
                ) r1
                on
                m1.rid=r1.rid
                AND
                r1.regmobileno = m1.mobileno
                Edited by: chris227 on 10.01.2013 01:45
                corrected