Re: Execution path having full scan in a nested loop

The shape of the plan you're describing is one where the subquery is unnested to a non-mergeable aggregate view. To see if this is legal as far as the optimizer is concerned you could add the hints /*+ unnest no_merge */ to the subquery.

Regards
Jonathan Lewis

On Mon, 11 Oct 2021 at 20:50, Lok P <loknath.73_at_gmail.com> wrote:

> 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
>

--
http://www.freelists.org/webpage/oracle-l