Re: Query Performance Issue

Hello,

What is the outcome if you add the hint /*+ leading(RDC) */ in your query?

Thanks.

În mie., 3 nov. 2021 la 05:05, Pap <oracle.developer35_at_gmail.com> a scris:

>
> Thank you Sayan. Below is the output for the asked queries and the DDL and
> statistics information. And yes the column SCD and DCC itself does not have
> the '/' character in them but the incoming literal does have that '/'
> character to match with the concatenated LHS. And the input literal on the
> right hand side varies each time.
>
> I am not sure how line 10 and 11 is operating/getting estimated here in
> the plan. But that table has no histogram on column CTD and that is why
> perhaps the estimation becomes 702k/3=302K. But the actual number of values
> with filter CT_CD= 'XX' is resulting in ~50K rows from that table. So do
> you think adding histogram to this column will help in getting us a
> better/faster execution path?
>
> select count(*) from RFCER;
>
> --4228120
>
> 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';
>
> C1 C2 C3 C4 C5
> 3991390 1240 8409 8891 3990908
>
> select count(*) from RDC;
>
> --39093
>
> select count(*),length(SCD),length(DCC)
> from RDC
> where RDC.SCD || '/' || RDC.DCC = 'AAAAAA'
> group by length(SCD),length(DCC);
>
> COUNT(*) LENGTH(SRC_CURRENCY_CD) LENGTH(DEST_CURRENCY_CD)
> 1 3 3
>
> ********** DDL And Statistics******************
>
> DDL:-
>
> CREATE TABLE RFCER
> (
> CKEY NUMBER,
> ERKEY NUMBER NOT NULL,
> CLKEY NUMBER NOT NULL,
> CR_KEY NUMBER NOT NULL,
> AFRI VARCHAR2(38 BYTE) DEFAULT 'NONE' NOT NULL)
>
> CREATE INDEX RFCER_IX2 ON RFCER(CKEY, CLKEY, CR_KEY);
> CREATE UNIQUE INDEX RFCER_PK ON RFCER(ERKEY);
> ALTER TABLE RFCER ADD ( CONSTRAINT RFCER_PK PRIMARY KEY (ERKEY) USING
> INDEX RFCER_PK ENABLE VALIDATE);
>
> Table Num_rows = 4219207
>
> INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
> RFCER_IX2 2 3884057 1899024 4164177 168817
>
> TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
> RFCER CKEY 1240 0.000806451612903226 0
> RFCER CLKEY 490 0.00204081632653061 0
> RFCER CR_KEY 543 0.00184162062615101 0
> RFCER AFRI 235024 4.25488460752944E-6 0
>
>
> *****************
>
> CREATE TABLE RDC
> (
> CR_KEY INTEGER CONSTRAINT RDC_C01 NOT NULL,
> SCD VARCHAR2(3 BYTE),
> DCC VARCHAR2(3 BYTE),
> SCDSC VARCHAR2(30 BYTE),
> DCDSC VARCHAR2(30 BYTE)
> );
> CREATE INDEX RDC_IX1 ON RDC(DCC, SCD);
> CREATE UNIQUE INDEX RDC_PK ON RDC(CR_KEY);
> ALTER TABLE RDC ADD ( CONSTRAINT RDC_PK PRIMARY KEY (CR_KEY) USING INDEX
> RDC_PK ENABLE VALIDATE);
>
> Table Num_rows= 39110
>
> INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
> RDC_IX1 1 36100 31811 39093
> 39093
>
> INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
> RDC_PK 1 39093 5834 39093 39093
>
> TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
> RDC SCD 190 0.00526315789473684 0
> RDC DCC 190 0.00526315789473684 0
>
> *********************
>
> Table RDCH holds a total ~702K records. It has a composite index -
> RDCH_IX1 on column (CT_CD,CID) and another index on column (CID).
>
> INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
> RDCH_IX1 2 702344 477946 702344 702344
>
> INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
> RDCH_IX9 2 554752 557317 702344 702344
>
> TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
> 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-l