Re: Query Performance Issue

Thank You So much Sayan for the detailed analysis.

As the new indexes may have some odd consequences for others queries, so need to test those carefully. So do you say there is also the possibility of just tweaking the query(with existing index hints maybe) to make it better(say like I was asking for the modification of the inline distinct query using the exists operator in the initial post)?

Agree that table RDC has total ~39K rows , but if i check the distinct RDC.SCD || '/' || RDC.DCC it's coming ~36K, so function based index should help wherever it is used in such a way.But again, as you said, it's small in size too(~5MB ) so FTS may be okay considering its Exadata machine.

However, table RFCER holds ~4.2million rows, so I am thinking if adding an index just on an individual column CLKEY(which has only ~491 distinct values) will help many scenarios or not? And yes, this part/table/index scan is currently consuming most resources in the query , mostly because of the ~4007 number of times(as many rows there are in the GTT RTNI) it's getting scanned in the current execution path. So maybe if we have to go for an index, we have to go for the composite on all the columns(CLKEY, AFRI, CR_KEY, CKEY) as you mentioned. Correct me if I'm wrong.

On Thu, Nov 4, 2021 at 6:34 AM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> 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