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@xxxxxxxxx> 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@xxxxxxxxxxx> wrote:
Hi,
Here table RTNI is a global temporary table which is populated duringrun 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *Im Auftrag von *Pap
*Gesendet:* Dienstag, 2. November 2021 20:18
*An:* Oracle L <oracle-l@xxxxxxxxxxxxx>
*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')
