Execution path having full scan in a nested loop

  • From: Lok P <loknath.73@xxxxxxxxx>
  • To: Oracle L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 12 Oct 2021 01:19:58 +0530

Hello Listers, We have one database on version 11.2.0.4 of oracle. And
below query is spending quite a lot of time while scanning table 'DETAIL'
in nested loop path i.e. step-9 below. This table does have an index with
the leading column as RID(which is joined column) but still it's going for
a 'TABLE ACCESS STORAGE FULL FIRST ROWS' within a nested loop. Not sure if
it's just because we are reading a lot of rows from that table or if we are
hitting any optimizer restriction. So I want to understand if we can modify
this query to make it go for one time full table scan, maybe with a hash
join kind of operation, so that this can complete in a faster time?

Table DETAIL having ~1.7million rows in it. And column RID having 31K
distinct values in it. And table MASTER having ~34k in it.

SELECT TRIM (rf.fattr3) ,TRIM (rf.fattr) , rf.W_DATE ,rf.CODE ,NVL (SUM
(rf.txn_cnt), 0) AS cnt,NVL (SUM (DECODE (TTYP, 'S', 1, -1) * rf.amt),0)
amt,count(*) over () count1
FROM PBRF rf
WHERE     rf.F_GRP = :b1
AND rf.F_CATG IN ( :b2, :b3)
 AND (rf.fattr3, NVL (rf.fattr, '-')) NOT IN
(SELECT DISTINCT   dtl.FL_NM, NVL (dtl.SID, '-')
  FROM MASTER req, DETAIL dtl
 WHERE     req.RID = dtl.RID
AND req.RSTS IN ('XX', 'YY')
AND req.RTYP = :b4
AND dtl.FL_TYP = 'DP')
AND rf.W_DATE BETWEEN TO_DATE ( :b5,'MM/DD/YYYY')    AND TO_DATE (
:b6,'MM/DD/YYYY')
  GROUP BY rf.CODE,TRIM (rf.fattr),TRIM (rf.fattr3), rf.W_DATE;

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 SQL ID              :  7yc5x3uzx7vzm
 SQL Execution ID    :  16777216
 Execution Started   :  10/11/2021 15:18:40
 First Refresh Time  :  10/11/2021 15:18:40
 Last Refresh Time   :  10/11/2021 15:21:03
 Duration            :  143s
 Module/Action       :  SQL*Plus/-
 Program             :  sqlplus.exe
 Fetch Calls         :  2

Global Stats
===================================================================================================
| Elapsed |   Cpu   |    IO    | Application |  Other   | Fetch | Buffer |
Read | Read  |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Calls |  Gets  |
Reqs | Bytes | Offload |
===================================================================================================
|     144 |     131 |       11 |        1.13 |     0.18 |     2 |    38M |
348K | 293GB |  80.58% |
===================================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3512223473)
==========================================================================================================================================================================================================================
| Id |                  Operation                  |          Name
  |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |
 Cell   |  Mem  | Activity |           Activity Detail            |
|    |                                             |
  | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |
Offload | (Max) |   (%)    |             (# samples)              |
==========================================================================================================================================================================================================================
|  0 | SELECT STATEMENT                            |
  |         |      |         1 |   +143 |     1 |      421 |      |       |
        |       |          |                                      |
|  1 |   WINDOW BUFFER                             |
  |       1 |  125 |         1 |   +143 |     1 |      421 |      |       |
        | 32768 |          |                                      |
|  2 |    HASH GROUP BY                            |
  |       1 |  125 |       142 |     +2 |     1 |      421 |      |       |
        |    1M |          |                                      |
|  3 |     FILTER                                  |
  |         |      |       142 |     +2 |     1 |     6985 |      |       |
        |       |          |                                      |
|  4 |      FILTER                                 |
  |         |      |       142 |     +2 |     1 |     9626 |      |       |
        |       |          |                                      |
|  5 |       TABLE ACCESS BY INDEX ROWID           | PBRF
 |       2 |  119 |       142 |     +2 |     1 |     9626 |      |       |
        |       |          |                                      |
|  6 |        INDEX SKIP SCAN                      | PBRF_IX1
 |       4 |  118 |       142 |     +2 |     1 |     9626 |    1 |  8192 |
        |       |          |                                      |
|  7 |      NESTED LOOPS                           |
  |       3 |    5 |       142 |     +2 |  1712 |      532 |      |       |
        |       |          |                                      |
|  8 |       NESTED LOOPS                          |
  |       3 |    5 |       142 |     +2 |  1712 |     1417 |      |       |
        |       |          |                                      |
|  9 |        TABLE ACCESS STORAGE FULL FIRST ROWS | DETAIL
 |    6436 |    2 |       144 |     +1 |  1712 |     1417 | 348K | 293GB |
 80.58% |   17M |   100.00 | enq: KO - fast object checkpoint (2) |
|    |                                             |
  |         |      |           |        |       |          |      |       |
        |       |          | Cpu (124)                            |
|    |                                             |
  |         |      |           |        |       |          |      |       |
        |       |          | reliable message (4)                 |
|    |                                             |
  |         |      |           |        |       |          |      |       |
        |       |          | cell smart table scan (14)           |
| 10 |        INDEX UNIQUE SCAN                    | MASTER_PK
  |       1 |      |       142 |     +2 |  1417 |     1417 |      |       |
        |       |          |                                      |
| 11 |       TABLE ACCESS BY INDEX ROWID           | MASTER
 |       1 |    1 |       142 |     +2 |  1417 |      532 |      |       |
        |       |          |                                      |
==========================================================================================================================================================================================================================



Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter( NOT EXISTS (SELECT 0 FROM "USER1"."DETAIL"
"DTL","USER1"."MASTER" "REQ" WHERE "REQ"."RID"="DTL"."RID" AND
"REQ"."RTYP"=:B4 AND
              ("REQ"."RSTS"='YY' OR "REQ"."RSTS"='XX') AND
"DTL"."FL_TYP"='DP' AND  LNNVL("DTL"."FL_NM"<>:B1) AND
LNNVL(NVL(:B2,'-')<>NVL("DTL"."SID",'-'))))
   4 - filter(TO_DATE(:B6,'MM/DD/YYYY')>=TO_DATE(:B5,'MM/DD/YYYY'))
   6 - access("RF"."W_DATE">=TO_DATE(:B5,'MM/DD/YYYY') AND "RF"."F_GRP"=:B1
AND "RF"."W_DATE"<=TO_DATE(:B6,'MM/DD/YYYY'))
       filter("RF"."F_GRP"=:B1 AND ("RF"."F_CATG"=:B2 OR "RF"."F_CATG"=:B3))
   9 - storage("DTL"."FL_TYP"='DP')
       filter("DTL"."FL_TYP"='DP' AND LNNVL("DTL"."FL_NM"<>:B1) AND
 LNNVL(NVL(:B2,'-')<>NVL("DTL"."SID",'-')))
  10 - access("REQ"."RID"="DTL"."RID")
  11 - filter("REQ"."RTYP"=:B4 AND ("REQ"."RSTS"='YY' OR "REQ"."RSTS"='XX'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   38469369  consistent gets
   38459185  physical reads
         52  redo size
      14543  bytes sent via SQL*Net to client
        472  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        421  rows processed

Other related posts: