Re: Query Performance Issue

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 4 Nov 2021 04:04:32 +0300
Message-ID: <CAOVevU6L8xTHsdFzm=YDPsL=BQZ1CWqTpiGuA7zdE9g-LRsBJA_at_mail.gmail.com>

Hi Pap,

I've analyzed your data:
1. "RFCER.CLKEY = 20211001" has a good selectivity, it returns just ~8k rows (compare with 4k rows in your GTT) and only 1240 distinct CKEY values; 2. RFCER.AFRI = 'ZZZZ' is not selective, but adding it into the index below will help to avoid table loookup;
3. RDC.SCD || '/' || RDC.DCC = 'AAAAAA' returns just 1 row, but RDC is a pretty small table, so even FTS(full table scan) is not so hard, but it's better to create FBI on (SCD || '/' || DCC); 4. RDCH.CT_CD = 'XX' (50k rows) and RDCH.CKEY are not selective, but CID should be very selective (4k rows by CID from GTT)

So I would suggest to create a couple of indexes and you can force a better plan even without rewriting the query:
CREATE INDEX RDC_IX_FBI on RDC(SCD || '/' || DCC, CR_KEY); -- last one is optional
CREATE INDEX RFCER_IX_CLKEY_ETC ON RFCER(CLKEY, AFRI, CR_KEY, CKEY); --last 3 are optional, but good to avoid table access and force a plan like this
SELECT --+ leading(rdc RFCER RTNI) use_nl(RFCER) no_merge(rtni) use_hash(rtni) no_adaptive_plan
RDC.SCD, RDC.SCDSC , RDC.DCC, RDC.DCDSC , ERATE   FROM RFCER ,
      ( SELECT/*+ no_merge leading(RTNI RDCH) USE_NL(RDCH) INDEX(RDCH (CT_CD, CID)) */

             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 RFCER.CLKEY = 20211001
       AND RFCER.CR_KEY = RDC.CR_KEY
       AND RDC.SCD || '/' || RDC.DCC = 'AAAAAA';

So you should get something like this:

---

| Id | Operation | Name |

---

| 0 | SELECT STATEMENT | |
| * 1 | HASH JOIN | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | RDC |
| * 4 | INDEX RANGE SCAN | RDC_IX_FBI |
| * 5 | INDEX RANGE SCAN | RFCER_IX_CLKEY_ETC |
| 6 | VIEW | |
| 7 | HASH UNIQUE | |
| 8 | NESTED LOOPS | |
| 9 | NESTED LOOPS | |
| 10 | TABLE ACCESS FULL | RTNI |
| * 11 | INDEX RANGE SCAN | RDCH_IX1 |
| 12 | TABLE ACCESS BY INDEX ROWID | RDCH |

---

Predicate Information (identified by operation id):

---

• 1 - access("RFCER"."CKEY"="RTNI"."CKEY")
• 4 - access("SCD"||'/'||"DCC"='AAAAAA')
• 5 - access("RFCER"."CLKEY"=20211001 AND "RFCER"."AFRI"='ZZZZ' AND "RFCER"."CR_KEY"="RDC"."CR_KEY")
• 11 - access("RDCH"."CT_CD"=TO_NUMBER('XX') AND "RDCH"."CID"=TO_CHAR("RTNI"."MCI"))

On Wed, Nov 3, 2021 at 8:35 PM Pap <oracle.developer35_at_gmail.com> wrote:

> I think you are pointing to the bug below. In our case here it's a SELECT
> query with GTT holding <10K records most of the time. So hopefully we
> won't be much impacted. But anyway , I was trying to see if there is any
> further scope of improvement of this query?
>
> Bug 31031240 - Insert into GTT (Global Temporary Tables) Runs Slow with
> increasing no.of records (Doc ID 31031240.8)
>
> On Wed, Nov 3, 2021 at 2:04 PM Willy Klotz <willyk_at_kbi-gmbh.de> wrote:
>
>> Hi,
>>
>>
>>
>> > Here table RTNI is a global temporary table which is populated during
>> run time
>>
>>
>>
>> We had made some very bad experience with GTT in 12.2 and 19. There were
>> also huge performance-differences between session and transaction-specific
>> GTT, which are going worse with the number of records in the table.
>>
>>
>>
>> Maybe you want to try (for testing purposes) with a regular table, just
>> to see if GTT influences the problem in any way.
>>
>>
>>
>>
>>
>> *Best regards*
>>
>> *Willyk*
>>
>>
>>
>> *Von:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *Im Auftrag von *Pap
>> *Gesendet:* Dienstag, 2. November 2021 20:18
>> *An:* Oracle L <oracle-l_at_freelists.org>
>> *Betreff:* Query Performance Issue
>>
>>
>>
>> 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

Received on Thu Nov 04 2021 - 02:04:32 CET