Re: Query Performance Issue
From: Pap <oracle.developer35_at_gmail.com>
Date: Wed, 3 Nov 2021 08:35:17 +0530
Message-ID: <CAEjw_fhW_SP7w_L==93hLw1BLv_X=hbrmgX7bDfONxoPajp4fg_at_mail.gmail.com>
> 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
> from RFCER
> where CLKEY=20211001 or AFRI='ZZZZ';
> 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:
> --
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning engineer
> Oracle ACE Associate
> http://orasql.org
>
Date: Wed, 3 Nov 2021 08:35:17 +0530
Message-ID: <CAEjw_fhW_SP7w_L==93hLw1BLv_X=hbrmgX7bDfONxoPajp4fg_at_mail.gmail.com>
RDCH CID 554752 1.80260729118597E-6 0 RDCH CT_CD 3 0.333333333333333 0
On Wed, Nov 3, 2021 at 5:05 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:
> 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
> from RFCER
> where CLKEY=20211001 or AFRI='ZZZZ';
>
> 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-lReceived on Wed Nov 03 2021 - 04:05:17 CET