Re: Query Performance Issue

Thank you so much.

On Thu, 4 Nov 2021, 5:27 pm Sayan Malakshinov, <xt.and.r_at_gmail.com> wrote:

> Hi Pap,
>
> > 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)?
>
> Yes, you can try my hinted query - it should be much faster even without
> those suggested indexes. The main problem of that slow plan is accessing
> RFCER many times by low selective CKEY, that produces 1 mln rows. Accessing
> RFCER once even with ISS (index skip scan) by CLKEY(just 8k rows), should
> give much better performance. And obviously it would be much better with an
> index on RFCER with leading CLKEY, but since you wrote:
>
> > it's called thousands of times in a
> process...
>
> I would highly recommend to create both of those indexes as-is, ie with
> all the "optional" columns to avoid extra table lookups.
>
>
> Best regards,
> Sayan Malakshinov
> Oracle performance tuning expert
> Oracle Database Developer Choice Award winner
> Oracle ACE Associate
> http://orasql.org
>
> чт, 4 нояб. 2021 г., 7:19 Pap <oracle.developer35_at_gmail.com>:
>
>> 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