Re: Query Performance Issue
From: Pap <oracle.developer35_at_gmail.com>
Date: Wed, 3 Nov 2021 23:00:24 +0530
Message-ID: <CAEjw_fiBBX2f-jnLsmpO5D4JhWwUC+uAHw7wgtaXrbjTTtmGBw_at_mail.gmail.com>
> 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:
Date: Wed, 3 Nov 2021 23:00:24 +0530
Message-ID: <CAEjw_fiBBX2f-jnLsmpO5D4JhWwUC+uAHw7wgtaXrbjTTtmGBw_at_mail.gmail.com>
This contains a global temporary table so I was not able to run it isolated from outside in prod. But if i tried to give the cardinality hint for the global temp table and run the query with full(RDC) hint the plan looks as below. It has a bunch of odd things like merge cartesian, buffer sort, index skip scan etc. So I'm wondering if it will really help or make this ~7second query worse.
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 53 | 5220 (1)| 00:00:01 | | 1 | VIEW | VM_NWVW_1 | 1 | 53 | 5220 (1)| 00:00:01 | | 2 | HASH UNIQUE | | 1 | 127 | 5220 (1)| 00:00:01 | | 3 | NESTED LOOPS SEMI | | 1 | 127 | 5219 (1)| 00:00:01 | | 4 | MERGE JOIN CARTESIAN | | 1 | 112 | 5217 (1)| 00:00:01 | |* 5 | HASH JOIN | | 1 | 99 | 5215 (1)| 00:00:01 | |* 6 | TABLE ACCESS STORAGE FULL | RDC | 394 | 22852 | 176 (1)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| RFCER | 1 | 41 | 5039 (1)| 00:00:01 | |* 8 | INDEX SKIP SCAN | RFCER_IX2 | 8612 | | 1242 (0)| 00:00:01 | | 9 | BUFFER SORT | | 1 | 13 | 178 (1)| 00:00:01 | | 10 | TABLE ACCESS STORAGE FULL | RTNI | 1 | 13 | 2 (0)| 00:00:01 | |* 11 | TABLE ACCESS BY INDEX ROWID | RDCH | 234K| 3433K| 2 (0)| 00:00:01 | |* 12 | INDEX UNIQUE SCAN | RDCH_PK | 1 | | 1(0)| 00:00:01 |
On Wed, Nov 3, 2021 at 1:44 PM Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:
> 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-lReceived on Wed Nov 03 2021 - 18:30:24 CET