Oracle - filter on analytical function is making the query slow

Question

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |       |       |       | 36060 (100)|          |       |       |
|   1 |  VIEW                          |                             | 56062 |  2846K|       | 36060   (2)| 00:07:13 |       |       |
|   2 |   WINDOW SORT PUSHED RANK      |                             | 56062 |  2189K|  3104K| 36060   (2)| 00:07:13 |       |       |
|   3 |    NESTED LOOPS                |                             | 56062 |  2189K|       | 33561   (2)| 00:06:43 |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| MAR_C                       |     1 |    13 |       |     1   (0)| 00:00:01 |       |       |
|   5 |      INDEX UNIQUE SCAN         | MAR_C_MC_SHNAME_IDX         |     1 |       |       |     0   (0)|          |       |       |
|   6 |     TABLE ACCESS FULL          | AGR_A                       | 56062 |  1478K|       | 33560   (2)| 00:06:43 |       |       |
|   7 |      PARTITION RANGE ALL       |                             |     1 |     8 |       |            |          |1048575|     1 |
|   8 |       SORT AGGREGATE           |                             |     1 |     8 |       |            |          |       |       |
|   9 |        TABLE ACCESS FULL       | RISK_M                      |  1139K|  8901K|       |  1923   (1)| 00:00:24 |1048575|     1 |
--------------------------------------------------------------------------------------------------------------------------------------

SELECT AGG_SID, CLIENT_SID, AUDIT_TM FROM 
                (  SELECT /*+ full(AGR_A)*/ AGR_A.AGG_SID --suppressed index of AGR_A on columns (AGG_SID,AUDIT_TM) purpsoely  
                       ,ROW_NUMBER () OVER (PARTITION BY AGR_A.AGG_SID ORDER BY (AGR_A.AUDIT_TM) DESC) rowN 
                       ,AGR_A.CLIENT_SID 
                       ,AGR_A.AUDIT_TM 
                   FROM AGR_A   
                   JOIN MAR_C ON MAR_C.MC_ID = AGR_A.MARGIN_CENTER_ID AND MAR_C.MC_SHORT_NAME = 'OTC' AND MAR_C.ACTIVE_FLAG = 'Y'                  
                  WHERE AGR_A.AUDIT_TM <TO_TIMESTAMP((SELECT /*+ PUSH_SUBQ FULL ("RISK_M") */ MAX(COB_DT) FROM RISK_M WHERE COB_DT IS NOT NULL) + 1)
                    AND AGR_A.ACTIVE_FLAG = 'Y'                     
                )
               WHERE rowN = 1 ;

THIS FILTER rowN=1ON ANALYTICAL FUNCTION IS TAKING TIME, ANY SOLUTION PLEASE? Note - I am force optimizer to use full table scan for better plan.

It's hard to tell what's going wrong with that query and where the time is being spent, just from the execution plan. It would help to have the *actual* times, not just the estimates. Run your query and get the execution plan like this: `select /*+ gather_plan_statistics */ ...;`, `select * from table(dbms_xplan.display_cursor(format => 'allstats last'));`. Post the entire output here, including the Note section. That will tell us which operation is using the most time. The analytic function itself probably shouldn't take that much time, unless the query returns a huge number of rows. — Jon Heller, Jun 17 '18 at 18:31

score 0 · Answer 1 · answered Jun 17 '18 at 13:47

This is your query (a bit more readable from my perspective):

SELECT AGG_SID, CLIENT_SID, AUDIT_TM
FROM (SELECT /*+ full(AGR_A)*/ a.AGG_SID, --suppressed index of AGR_A on columns (AGG_SID,AUDIT_TM) purpsoely  
            ROW_NUMBER() OVER (PARTITION BY a.AGG_SID ORDER BY a.AUDIT_TM DESC) as rowN ,
            a.CLIENT_SID,
            a.AUDIT_TM 
      FROM AGR_A a JOIN
           MAR_C c
           ON c.MC_ID = a.MARGIN_CENTER_ID 
      WHERE c.MC_SHORT_NAME = 'OTC' AND c.ACTIVE_FLAG = 'Y' AND                
            a.AUDIT_TM < TO_TIMESTAMP((SELECT /*+ PUSH_SUBQ FULL ("RISK_M") */ MAX(m.COB_DT) FROM RISK_M m WHERE m.COB_DT IS NOT NULL) + 1
                                     ) AND
            a.ACTIVE_FLAG = 'Y'                     
      )
WHERE rowN = 1 ;

Not knowing the exact details of the tables (related to sizes and selectivity), I would want indexes on:

RISK_M(COB_DT)
MAR_C(MC_SHORT_NAME, ACTIVE_FLAG)
AGR_A(MARGIN_CENTER_ID, ACTIVE_FLAG, AUDIT_TM, AGG_SID)

I would also get rid of the current compiler hints and move the subquery on RISK_M to the FROM clause. The latter should't affect performance (Oracle has a good optimizer), but it would make the query more readable.

Oracle - filter on analytical function is making the query slow

1 Answers1