Re: Query Performance Issue

Hi Pap,

You haven't provided enough info for complete analysis. It would be better if you provide DDL of all those tables with all their indexes, and table statistics.
Also IFFS by RDCH_PK (plan line #11) shows 234k rows estimated, though it returns 702k rows == 3 times more than estimated, so probably you need to actualize your statistics.
Can RDC.SCD or RDC.DCC contain '/' characters? (I understand that you can't show real data, but it's better to provide literals more similar to real - obviously RDC.SCD || '/' || RDC.DCC = 'AAAAAA' can't be true). And show please what do return these queries:

select count(*) from RFCER;

select count(*) c1, count(distinct CKEY) c2

, count(case when CLKEY=20211001 and AFRI='ZZZZ' then 1 end) c3
, count(case when CLKEY=20211001 then 1 end) c4
, count(case when AFRI='ZZZZ'    then 1 end) c5

select count(*) from RDC;

select count(*)
,length(SCD)
,length(DCC)
from RDC
where RDC.SCD || '/' || RDC.DCC = 'AAAAAA' group by length(SCD),length(DCC);

On Tue, Nov 2, 2021 at 10:18 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Hi , It's an oracle 19C database. We have the below query which runs
> ~7-10seconds per execution and we want to see if we can further make the
> improvement in run time someway. it's called thousands of times in a
> process because of certain design limitations of a third party app, so the
> number of executions can't be minimized to once per process. So we are
> trying to see what we can do to minimize the run time per execution for
> this query.
>
> As per ASH the major amount of time spent in plan_line_id 12 and 13 i.e.
> scanning of table RFCER. And in sql monitor that shows ~4K+ executions for
> that line and its matching with the number of rows in table RTNI. Then the
> second highest time is spent on plan_line_id 14 and 15 which shows
> ~1million execution. Here table RTNI is a global temporary table which is
> populated during run time. So want to understand what we can do to improve
> the run time for this query?
>
> SELECT RDC.SCD, RDC.SCDSC , RDC.DCC, RDC.DCDSC , ERATE
> FROM RFCER ,
> ( SELECT DISTINCT RDCH.CKEY
> FROM RTNI , RDCH
> WHERE TO_CHAR (RTNI.MCI) = RDCH.CID
> AND RDCH.CT_CD = 'XX') RTNI,
> RDC
> WHERE RFCER.CKEY = RTNI.CKEY
> AND RFCER.AFRI = 'ZZZZ'
> AND CLKEY = 20211001
> AND RFCER.CR_KEY = RDC.CR_KEY
> AND RDC.SCD || '/' || RDC.DCC = 'AAAAAA';
>
> Thinking if converting the Distinct inline query using below EXISTS
> operator will help anyway?
>
> (SELECT RDCH.CKEY
> FROM RDCH RDCH
> WHERE RDCH.CT_CD = 'XX'
> AND EXISTS
> (SELECT 'X'
> FROM RTNI RTNI
> WHERE TO_CHAR (RTNI.MCI) = RDCH.CID)) RTNI
>
>
> Global Information
> ------------------------------
> Status : DONE (ALL ROWS)
> Instance ID : 2
> SQL ID : 3dfmkcu292v30
> SQL Execution ID : 33555042
> Execution Started : 11/01/2021 22:11:49
> First Refresh Time : 11/01/2021 22:11:55
> Last Refresh Time : 11/01/2021 22:11:56
> Duration : 7s
> Fetch Calls : 1
>
> Global Stats
> =================================================
> | Elapsed | Cpu | Other | Fetch | Buffer |
> | Time(s) | Time(s) | Waits(s) | Calls | Gets |
> =================================================
> | 6.95 | 6.85 | 0.10 | 1 | 3M |
> =================================================
>
> SQL Plan Monitoring Details (Plan Hash Value=3330725745)
>
> ==================================================================================================================================================================================
> | Id | Operation | Name
> | Rows | Cost | Time | Start | Execs | Rows | Mem |
> Activity | Activity Detail |
> | | |
> | (Estim) | | Active(s) | Active | | (Actual) | (Max) |
> (%) | (# samples) |
>
> ==================================================================================================================================================================================
> | 0 | SELECT STATEMENT |
> | | | | | 1 | | . |
> | |
> | 1 | VIEW | VM_NWVW_1
> | 3 | 7616 | | | 1 | | . |
> | |
> | 2 | HASH UNIQUE |
> | 3 | 7616 | | | 1 | | . |
> | |
> | 3 | NESTED LOOPS |
> | 3 | 7615 | 2 | +6 | 1 | 0 | . |
> | |
> | 4 | NESTED LOOPS |
> | 3 | 7615 | 2 | +6 | 1 | 1M | . |
> | |
> | 5 | NESTED LOOPS |
> | 3 | 7612 | 2 | +6 | 1 | 1M | . |
> | |
> | 6 | HASH JOIN |
> | 2080 | 3437 | 2 | +6 | 1 | 4007 | 3MB |
> | |
> | 7 | TABLE ACCESS STORAGE FULL | RTNI
> | 4335 | 30 | 1 | +6 | 1 | 4007 | . |
> | |
> | 8 | VIEW | index$_join$_004
> | 234K | 3406 | 2 | +6 | 1 | 50883 | . |
> | |
> | 9 | HASH JOIN |
> | | | 2 | +6 | 1 | 50883 | 7MB |
> | |
> | 10 | INDEX RANGE SCAN | RDCH_IX1
> | 234K | 857 | 1 | +6 | 1 | 50883 | . |
> | |
> | 11 | INDEX STORAGE FAST FULL SCAN | RDCH_PK
> | 234K | 1779 | 2 | +6 | 1 | 702K | . |
> | |
> | 12 | TABLE ACCESS BY INDEX ROWID BATCHED | RFCER
> | 1 | 6 | 5 | +3 | 4007 | 1M | . |
> | |
> | 13 | INDEX RANGE SCAN | RFCER_IX2
> | 7 | 2 | 4 | +4 | 4007 | 1M | . |
> | |
> | 14 | INDEX UNIQUE SCAN | RDC_PK
> | 1 | | 6 | +2 | 1M | 1M | . |
> | |
> | 15 | TABLE ACCESS BY INDEX ROWID | RDC
> | 1 | 1 | 1 | +1 | 1M | 0 | . |
> | |
>
> ==================================================================================================================================================================================
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
> 6 - access("RDCH"."CID"=TO_CHAR("RTNI"."MCI"))
> 8 - filter("RDCH"."CT_CD"='XX')
> 9 - access(ROWID=ROWID)
> 10 - access("RDCH"."CT_CD"='XX')
> 12 - filter("RFCER"."AFRI"='ZZZZ')
> 13 - access("RFCER"."CKEY"="RDCH"."CKEY" AND "CLKEY"=20211001)
> 14 - access("RFCER"."CR_KEY"="RDC"."CR_KEY")
> 15 - filter("RDC"."SCD"||'/'||"RDC"."DCC"='AAAAAA')
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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