This contains a global temporary table so I was not able to run it isolated
from outside in prod. But if i tried to give the cardinality hint for the
global temp table and run the query with full(RDC) hint the plan looks as
below. It has a bunch of odd things like merge cartesian, buffer sort,
index skip scan etc. So I'm wondering if it will really help or make this
~7second query worse.
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 5220
(1)| 00:00:01 |
| 1 | VIEW | VM_NWVW_1 | 1 | 53 | 5220 (1)|
00:00:01 |
| 2 | HASH UNIQUE | | 1 | 127 | 5220 (1)|
00:00:01 |
| 3 | NESTED LOOPS SEMI | | 1 | 127 | 5219
(1)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 112 | 5217
(1)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 99 | 5215 (1)|
00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL | RDC | 394 | 22852 |
176 (1)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| RFCER | 1 |
41 | 5039 (1)| 00:00:01 |
|* 8 | INDEX SKIP SCAN | RFCER_IX2 | 8612 | | 1242
(0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | 13 | 178 (1)|
00:00:01 |
| 10 | TABLE ACCESS STORAGE FULL | RTNI | 1 | 13 | 2
(0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | RDCH | 234K|
3433K| 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | RDCH_PK | 1 | | 1
(0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
On Wed, Nov 3, 2021 at 1:44 PM Laurentiu Oprea <laurentiu.oprea06@xxxxxxxxx>
wrote:
Hello,
What is the outcome if you add the hint /*+ leading(RDC) */ in your query?
Thanks.
În mie., 3 nov. 2021 la 05:05, Pap <oracle.developer35@xxxxxxxxx> a scris:
Thank you Sayan. Below is the output for the asked queries and the DDL
and statistics information. And yes the column SCD and DCC itself does not
have the '/' character in them but the incoming literal does have that '/'
character to match with the concatenated LHS. And the input literal on the
right hand side varies each time.
I am not sure how line 10 and 11 is operating/getting estimated here in
the plan. But that table has no histogram on column CTD and that is why
perhaps the estimation becomes 702k/3=302K. But the actual number of values
with filter CT_CD= 'XX' is resulting in ~50K rows from that table. So do
you think adding histogram to this column will help in getting us a
better/faster execution path?
select count(*) from RFCER;
--4228120
select count(*) c1, count(distinct CKEY) c2
, count(case when CLKEY=20211001 and AFRI='ZZZZ' then 1 end) c3
, count(case when CLKEY=20211001 then 1 end) c4
, count(case when AFRI='ZZZZ' then 1 end) c5
from RFCER
where CLKEY=20211001 or AFRI='ZZZZ';
C1 C2 C3 C4 C5
3991390 1240 8409 8891 3990908
select count(*) from RDC;
--39093
select count(*),length(SCD),length(DCC)
from RDC
where RDC.SCD || '/' || RDC.DCC = 'AAAAAA'
group by length(SCD),length(DCC);
COUNT(*) LENGTH(SRC_CURRENCY_CD) LENGTH(DEST_CURRENCY_CD)
1 3 3
********** DDL And Statistics******************
DDL:-
CREATE TABLE RFCER
(
CKEY NUMBER,
ERKEY NUMBER NOT NULL,
CLKEY NUMBER NOT NULL,
CR_KEY NUMBER NOT NULL,
AFRI VARCHAR2(38 BYTE) DEFAULT 'NONE' NOT NULL)
CREATE INDEX RFCER_IX2 ON RFCER(CKEY, CLKEY, CR_KEY);
CREATE UNIQUE INDEX RFCER_PK ON RFCER(ERKEY);
ALTER TABLE RFCER ADD ( CONSTRAINT RFCER_PK PRIMARY KEY (ERKEY) USING
INDEX RFCER_PK ENABLE VALIDATE);
Table Num_rows = 4219207
INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
RFCER_IX2 2 3884057 1899024 4164177 168817
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
RFCER CKEY 1240 0.000806451612903226 0
RFCER CLKEY 490 0.00204081632653061 0
RFCER CR_KEY 543 0.00184162062615101 0
RFCER AFRI 235024 4.25488460752944E-6 0
*****************
CREATE TABLE RDC
(
CR_KEY INTEGER CONSTRAINT RDC_C01 NOT NULL,
SCD VARCHAR2(3 BYTE),
DCC VARCHAR2(3 BYTE),
SCDSC VARCHAR2(30 BYTE),
DCDSC VARCHAR2(30 BYTE)
);
CREATE INDEX RDC_IX1 ON RDC(DCC, SCD);
CREATE UNIQUE INDEX RDC_PK ON RDC(CR_KEY);
ALTER TABLE RDC ADD ( CONSTRAINT RDC_PK PRIMARY KEY (CR_KEY) USING INDEX
RDC_PK ENABLE VALIDATE);
Table Num_rows= 39110
INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
RDC_IX1 1 36100 31811 39093
39093
INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
RDC_PK 1 39093 5834 39093 39093
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
RDC SCD 190 0.00526315789473684 0
RDC DCC 190 0.00526315789473684 0
*********************
Table RDCH holds a total ~702K records. It has a composite index -
RDCH_IX1 on column (CT_CD,CID) and another index on column (CID).
INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
RDCH_IX1 2 702344 477946 702344 702344
INDEX_NAME BLEVEL DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
RDCH_IX9 2 554752 557317 702344 702344
TABLE_NAME COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
RDCH CID 554752 1.80260729118597E-6 0
RDCH CT_CD 3 0.333333333333333 0
On Wed, Nov 3, 2021 at 5:05 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
Hi Pap,
You haven't provided enough info for complete analysis. It would be
better if you provide DDL of all those tables with all their indexes, and
table statistics.
Also IFFS by RDCH_PK (plan line #11) shows 234k rows estimated, though
it returns 702k rows == 3 times more than estimated, so probably you need
to actualize your statistics.
Can RDC.SCD or RDC.DCC contain '/' characters? (I understand that you
can't show real data, but it's better to provide literals more similar to
real - obviously RDC.SCD || '/' || RDC.DCC = 'AAAAAA' can't be true).
And show please what do return these queries:
select count(*) from RFCER;
select count(*) c1, count(distinct CKEY) c2
, count(case when CLKEY=20211001 and AFRI='ZZZZ' then 1 end) c3
, count(case when CLKEY=20211001 then 1 end) c4
, count(case when AFRI='ZZZZ' then 1 end) c5
from RFCER
where CLKEY=20211001 or AFRI='ZZZZ';
select count(*) from RDC;
select count(*)
,length(SCD)
,length(DCC)
from RDC
where RDC.SCD || '/' || RDC.DCC = 'AAAAAA'
group by length(SCD),length(DCC);
On Tue, Nov 2, 2021 at 10:18 PM Pap <oracle.developer35@xxxxxxxxx>
wrote:
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
