CODEWITHSUNDEEP
sqloracle11g
Jaydeep Patel
Jaydeep Patel

Reputation: 2433

Performance of query is very bad

I have below query:

SELECT so1b.hasattachments,
       so1b.sorefitem,
       so1b.sonumber,
       so1b.custponumber,
       so1b.sodate,
       so1b.ostatus,
       so1b.osubstatus,
       so1b.urefitem,
       so1b.username,
       so1b.summary,
       so1b.purefitem
FROM (SELECT DECODE (COUNT (ATT.SOREFITEM), 0, 0, 1) hasattachments,
             so1.SOREFITEM,
             SONUMBER,
             CUSTPONUMBER,
             SODATE,
             so1.OSTATUS,
             so1.OSUBSTATUS,
             so1.urefitem AS urefitem,
             so1.username AS username ,
             ''           AS summary ,
             so1.PUREFITEM
      FROM  ECOrders.SALESORDERS so1,
            ECORDERS.ATTACHMENTS att,
            ecusers.ownership o
      WHERE att.SOREFITEM (+) =so1.SOREFITEM
        AND so1.UREFITEM        = o.urefitem
        AND o.uownerref         = ?
        AND so1.sorefitem      IN
            (SELECT ot.sorefitem
             FROM ECOrders.ORDERITEMS ot,
                  ECUSERS.USERINFO USI
             WHERE ot.sorefitem=so1.sorefitem
               AND OT.OSELLERID  = USI.UREFITEM (+)
               AND UPPER(USI.USERNAME) LIKE UPPER('%Dell Computer%') ESCAPE '~'
            )
      GROUP BY SO1.SOREFITEM,
            SONUMBER,
            CUSTPONUMBER,
            SODATE,
            so1.OSTATUS,
            so1.OSUBSTATUS,
            so1.UREFITEM,
            so1.USERNAME,
            so1.PUREFITEM
      ORDER BY SOREFITEM DESC /* sort is possible on all columns */
      ) so1b
WHERE rownum BETWEEN 1 AND 10

This query performs very bad when data is large. Execution plan is as below:

ID  PA_ID   Execution Plan                                                                      COST    CARD    BYTES
0       SELECT STATEMENT Optimizer=CHOOSE                                                       2906    10      9480
1   0   -COUNT (STOPKEY)             
2   1   --FILTER             
3   2   ---VIEW                                                                                 2906    381     361188
4   3   ----SORT (GROUP BY)                                                                     2906    381     32766
5   4   -----FILTER          
6   5   ------NESTED LOOPS (OUTER)                                                              18      2888    248368
7   6   -------NESTED LOOPS                                                                     17      2888    231040
8   7   --------TABLE ACCESS (BY INDEX ROWID) of 'ECUSERS.OWNERSHIP' (TABLE)                    1       24      240
9   8   ---------INDEX (RANGE SCAN) of 'ECUSERS.OWNERSHIP_UOWNERREF' (INDEX)                    1       24   
10  7   --------TABLE ACCESS (BY INDEX ROWID) of 'ECORDERS.SALESORDERS' (TABLE)                 1       121     8470
11  10  ---------INDEX (RANGE SCAN) of 'ECORDERS.SO_UREFITEM_OSTATUS_OSUB' (INDEX)              1       76   
12  6   -------INDEX (RANGE SCAN) of 'ECORDERS.ATTACHMENTS_SOREFITEM' (INDEX)                   1       1       6
13  5   ------NESTED LOOPS  2   1   49
14  13  -------TABLE ACCESS (BY INDEX ROWID) of 'ECORDERS.ORDERITEMS' (TABLE)                   1       3       33
15  14  --------INDEX (RANGE SCAN) of 'ECORDERS.ORDERITEMS_SOREFITEM02' (INDEX)                 1       3    
16  13  -------INDEX (RANGE SCAN) of 'ECUSERS.USERINFO_I_USERNAME_UREFITEM' (INDEX (UNIQUE))    1       1       38
ID  PA_ID   Execution Plan                                                                      COST    CARD    BYTES

Is there any way I can improve performance of this query?

Upvotes: 3

Views: 125

Answers (2)

user359040
user359040

Reputation:

I suggest replacing the in clause with an exists clause - like so:

        AND exists
            (SELECT null
             FROM ECOrders.ORDERITEMS ot
             JOIN ECUSERS.USERINFO USI 
               on OT.OSELLERID  = USI.UREFITEM
              AND UPPER(USI.USERNAME) LIKE UPPER('%Dell Computer%') ESCAPE '~'
             WHERE ot.sorefitem=so1.sorefitem
            )

The join between ORDERITEMS and USERINFO will be an inner join because you are only interested in items ordered by users from Dell Computer companies.

Upvotes: 1

ninesided
ninesided

Reputation: 23273

I would be looking to do the following:

  1. refactor to use ANSI join syntax, for the sanity of anyone that comes after you
  2. refactor to eliminate the need for the sub-query in the WHERE clause as this will need to be executed one for every row in the table, not just the 10 that you bring back.
  3. if you know you only going to need 10 rows, you could try adding a /*+ FIRST_ROWS(10) */ hint to give the Oracle optimizer a helping hand.
  4. is the sort necessary? Seems like most of your work is done here.

Upvotes: 3

Related Questions