how can i optimize this query taking 30 seconds for 1746 rows

Question

query:

SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID)
                 FROM USER_ROLE A, USER B, ROLE C
                WHERE A.ROLE_ID = C.ROLE_ID
                  AND C.GROUP_ID = 3
                  AND A.USER_ID = B.USER_ID
                  AND B.TEMPLATE_IND = 'N'
                  AND B.ONAP_PARTCODE IS NULL
                  AND A.PARTCODE ='005'
                GROUP BY A.PARTCODE,
                         A.USER_ID,
                         A.ROLE_ID,
                         C.SUBGROUP;

Explain plan:

--------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |    74 |
|   1 |  HASH GROUP BY                 |                    |     1 |    74 |
|   2 |   NESTED LOOPS                 |                    |     1 |    74 |
|   3 |    NESTED LOOPS                |                    |    56 |  3024 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROLE               |     8 |   240 |
|*  5 |      INDEX RANGE SCAN          | N_ROLE_IDX2        |     8 |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| USER_ROLE          |     7 |   168 |
|*  7 |      INDEX RANGE SCAN          | N_USER_ROLE_IDX6   |     7 |       |
|   8 |    REMOTE                      | MV_PT_USER         |     1 |    20 |
--------------------------------------------------------------------------------

Answer 1

Without knowing details about your table schema and indices, it would be hard to give decent advice.

It's probably won't help much, but I do see that the A.PARTCODE in your GROUP BY clause isn't needed.

Answer 2

I re-wrote your query to use ANSI-92 syntax:

  SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID)
    FROM USER_ROLE a
    JOIN USER b ON b.user_id = a.user_id
               AND b.template_ind = 'N'
               AND b.onap_partcode IS NULL
    JOIN ROLE c ON c.role_id = a.role_id
               AND c.group_id = 3
   WHERE a.PARTCODE ='005'
GROUP BY a.USER_ID, a.ROLE_ID, c.SUBGROUP, a.PARTCODE;

It's not any faster, just more clear for me to suggest the following covering indexes:

CREATE INDEX ur_idx ON USER_ROLE (user_id, role_id, partcode) COMPUTE STATISTICS;
CREATE INDEX u_idx ON USER (user_id, template_ind) COMPUTE STATISTICS;
CREATE INDEX r_idx ON ROLE (role_id, group_id) COMPUTE STATISTICS;

Answer 3

The ROWS estimate in the plan ends with 1, not anywhere near 1700. The 'remote' operation on MV_PT_USER is interesting. It appears that the optimizer is assuming one (or maybe zero) rows will be returned by that operation, even though there is no filtering.

Gathering some stats on that object may tell oracle how many rows it is likely to return, and suggest a different plan to the optimizer.

Answer 4

Your query takes 30 seconds. Now we need to know where those 30 seconds are spent on. The provided information gives close to nothing, so it's time to trace a run of the query.

Here is some quick information on how to gather the needed information: http://forums.oracle.com/forums/thread.jspa?messageID=1812597

Regards, Rob.