Re: Query Performance Issue

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')
>

--
http://www.freelists.org/webpage/oracle-l