Hi,
I have following SQL and it is considerably slower ,
T_IOT - This is an Oracle Indexed Organized table (about 8.4 Million rows)
- idx\_pk(nr\_time,uid,inst\_id)
- Two secondary indexes T\_IOT\_STATE(st,inst\_id, svnm) , idx2(uid,inst\_id,svnm)
T_BASE - Heap Table - (4.2 million rows)
SQL_ID azxc8vqsbng04, child number 0
-------------------------------------
INSERT INTO T_IOT
SELECT
apar.UID,
'UP',
TO\_DATE('2019-10-24','yyyy-mm-dd'),
TO\_DATE('1969-12-31','yyyy-mm-dd'),
'IDLE',
NULL,
'tkt007.jj.bb.com'
FROM
t\_base apar
WHERE
apar.UID NOT IN (
SELECT
UID
FROM
T\_IOT ob
WHERE
INST\_ID = 'UP'
)
AND apar.GP\_NM = 'UA'
AND ROWNUM \<= 5000;
Plan hash value: 3554622160
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 30926 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | T_IOT | | | | | |
|* 2 | COUNT STOPKEY | | | | | | |
|* 3 | HASH JOIN RIGHT ANTI | | 2697K| 156M| 65M| 30926 (1)| 00:00:02 |
|* 4 | INDEX SKIP SCAN | T_IOT_STATE | 1683K| 46M| | 13872 (1)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | t_base | 4205K| 128M| | 4995 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
4 - SEL$5DA710D3 / OB@SEL$2
5 - SEL$5DA710D3 / APAR@SEL$1
Outline Data
-------------
/*+
BEGIN\_OUTLINE\_DATA
IGNORE\_OPTIM\_EMBEDDED\_HINTS
OPTIMIZER\_FEATURES\_ENABLE('12.2.0.1')
DB\_VERSION('12.2.0.1')
ALL\_ROWS
OUTLINE\_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE\_LEAF(@"INS$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX\_FFS(@"INS$1" "T\_IOT"@"INS$1" ("T\_IOT"."NR\_TIME"
"T\_IOT"."UID" "T\_IOT"."INST\_ID"))
FULL(@"SEL$5DA710D3" "APAR"@"SEL$1")
INDEX\_SS(@"SEL$5DA710D3" "OB"@"SEL$2" ("T\_IOT"."ST" "T\_IOT"."INST\_ID"
"T\_IOT"."SVR\_NM"))
LEADING(@"SEL$5DA710D3" "APAR"@"SEL$1" "OB"@"SEL$2")
USE\_HASH(@"SEL$5DA710D3" "OB"@"SEL$2")
SWAP\_JOIN\_INPUTS(@"SEL$5DA710D3" "OB"@"SEL$2")
END\_OUTLINE\_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=5000)
3 - access("APAR"."UID"="UID")
4 - access("INST_ID"='UP')
filter("INST\_ID"='UP')
5 - filter(NVL("APAR"."GP_NM",'UA')='UA')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200], ROWNUM[8]
3 - (#keys=1) "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200]
4 - "OB".ROWID[ROWID,1249], "NR_TIME"[TIMESTAMP,11], "UID"[VARCHAR2,1024],
"INST\_ID"\[VARCHAR2,200\], "OB".ROWID\[ROWID,1249\]
5 - "APAR"."UID"[VARCHAR2,400], NVL("APAR"."GP_NM",'UA')[200]
From Real time SQL Monitor following was observed ,
There seems to be a massive 5GB Temp for the hash join,
Any idea why this is happening ?
currently the above query takes about 3 - 5 minutes.