Oracle Performance Gurus,
I have a task of tuning a beast of a sql that runs for more than 18 hours at times depending on the number of rows it tries to delete from a global temp table. There is an index defined on the table but the optimizer is doing a hash join instead - here are the details of the table and the explain plan results of the table -
DELETE FROM
T1
WHERE ROWID IN
(
SELECT ROWID FROM
(
SELECT
ROWID,
ROW_NUMBER() OVER (PARTITION BY A,B,C,D ORDER BY C,D) DUP
FROM T1
WHERE FLAG1 = 0
)
WHERE DUP > 1
);
COMMIT;
The table definition is as given here -
CREATE GLOBAL TEMPORARY TABLE "T1"
(
A VARCHAR2(50 BYTE),
B NUMBER(10,0),
C VARCHAR2(20 BYTE),
D NUMBER,
A1 FLOAT(126),
B1 FLOAT(126),
C1 FLOAT(126),
D1 FLOAT(126),
A2 NUMBER,
B2 NUMBER,
C2 FLOAT(126),
D2 FLOAT(126),
A3 FLOAT(126),
B3 FLOAT(126),
C3 FLOAT(126),
D3 FLOAT(126),
A4 FLOAT(126),
B4 FLOAT(126),
FLAG1 NUMBER
) ON COMMIT PRESERVE ROWS ;
CREATE INDEX T1IDX ON T1 ("A", "B", "C", "D") ;
The explain plan results are -
Query Plan Rows Rowsource Time
DELETE STATEMENT Cost = 3936614
DELETE T1 1109
NESTED LOOPS 1 1
VIEW VW_NSO_1 220M 0
SORT UNIQUE 1 163
VIEW 220M 2
WINDOW SORT 220M 355
TABLE ACCESS FULL T1 220M 94
TABLE ACCESS BY USER ROWID T1 1 313
It is also important to mention that we're also getting occassional ORA-01652 and ORA-30036 (Undo and Temp tablespace extension errors respectively) when the above query runs longer than usual. We have been adding to our temp space for the past few weeks to mitigate the errors temporarily. I am adding the tablespace information here -
Tablespace Name SizeinMB FreeMB
---------------- --------- --------
T1_Sp1_DATA_TS 3712 180.88
T1_PE1_INDEX_TS 1 0.94
SYSAUX 1160 60.06
T1_SYS_BLOB_TS 525 81.13
T1_SIF_EXPORT_TS 5 4
T1_SIF_TS 1 0.69
T1_FL1_INDEX_TS 3590 173.06
Staging_DATA_TS 1436 165.63
T1_FLR_pf1_TS 2219 238
T1_Sp1_dv1_TS 1004 2.75
T1_Sp1_pf1_TS 5868 8.75
T1_SYS_DATA_TS 34 3.63
T1_SYS_el1_TS 159 11.88
T1_Sp1_INDEX_TS 5785 309.69
T1_e1_INDEX_TS 5 4
USERS 66740 21538.06
T1_FL1_DATA_TS 1932 95.38
T1_BLOB_TS 12415 591.44
T1_Sp1_Fx1_TS 3249 215.75
T1_ST1_INDEX_TS 2 0.94
T1_SIF_INDEX_TS 2 0.38
SYSTEM 405 7.19
T1_FL1_Fx1_TS 6475 351.63
T1_ST1_DATA_TS 1 0.13
T1_SA_INDEX_TS 5 4
T1_NET_DATA_TS 13 0.19
T1_Staging_DATA_TS 872404.9375 176406.69
T1_FL1_sc1_TS 4071 254.63
T1_SA_DATA_TS 5 4
T1_NET_BLOB_TS 26757 1291.38
T1_NET_INDEX_TS 57 3.63
T1_SYS_INDEX_TS 33 4.88
T1_Sp1_ps1_TS 2129 103.75
T1_e1_DATA_TS 5 4
T1_SA_BLOB_TS 5 4
T1_SI1_BLOB_TS 2 0.25
T1_PE1_DATA_TS 1 0.94
TEMP 196605.96875
I'm wondering what is the best way to tune the query to have it run faster - I am going to try forcing an indexed hint or a NLJ hint to the delete to see if it helps, but if any of you has any better ideas, I'd much appreciate that.
This is Oracle 12c and we have session-level stats for all our global temp tables. I am still learning some of the 12c features so not sure what to make out much of the session-level stats on this table.
Thanks, Brendon
I would change the approach. Rather than delete the rows you don't want, write the query to keep the rows that you want. Write them into a new table. Then drop the old table and rename. So basically you have something logically like
insert into T1_new select .... where DUP = 1
drop table T1
rename T1_new to T1
This also opens up the opportunity to use direct path insert ( via /*+APPEND */ hint). If you have resources available, you can also employ parallelism.