Гуру производительности Oracle,
У меня есть задача настроить зверя sql, который иногда работает более 18 часов в зависимости от количества строк, которые он пытается удалить из глобальной временной таблицы. Для таблицы определен индекс, но вместо этого оптимизатор выполняет хеш-соединение — вот подробности таблицы и результаты плана объяснения таблицы —
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;
Определение таблицы приведено здесь:
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") ;
Результаты плана объяснения:
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
Также важно отметить, что мы также иногда получаем ORA-01652 и ORA-30036 (ошибки расширения табличного пространства Undo и Temp соответственно), когда вышеуказанный запрос выполняется дольше, чем обычно. В течение последних нескольких недель мы расширяли наше временное пространство, чтобы временно устранить ошибки. Я добавляю информацию о табличном пространстве здесь -
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
Мне интересно, как лучше всего настроить запрос, чтобы он выполнялся быстрее - я собираюсь попытаться принудительно удалить индексированную подсказку или подсказку NLJ, чтобы посмотреть, поможет ли это, но если у кого-то из вас есть идеи получше , я был бы очень признателен за это.
Это Oracle 12c, и у нас есть статистика на уровне сеанса для всех наших глобальных временных таблиц. Я все еще изучаю некоторые функции 12c, поэтому не уверен, что делать с большей частью статистики на уровне сеанса в этой таблице.
Спасибо, Брендон.
