Увеличьте скорость выполнения запросов Oracle на ExaData
Я работаю над запросом Oracle, и мне очень нужно, чтобы он работал быстрее. Буду очень признателен за любые советы.
- База данных - Oracle, работающая в кластере ExaData.
- Версия Oracle: Oracle Database 11g Enterprise Edition, выпуск 11.2.0.3.0 - 64-разрядная версия
У меня есть две таблицы.
1) Транзакции: покупка в магазине - TransactionID
2) TransactionItems: каждая покупка имеет 1..many предметов - TransactionID, ItemID
В каждой таблице есть два флага:
- Флаг A: Да / Нет
- Флаг B: Да / Нет
Запрос должен:
- Установите значение FlagA и FlagB для каждой записи в TransactionItem.
- Установите значения FlagA и FlagB для каждой строки в транзакции, основываясь на значениях флагов в TransactionItem
Я разбил свой запрос на 4 шага.
- Установить значение флага A для TransactionItem
- Установить значение флага B для TransactionItem
- Установите значение флага A для транзакции
- Установите значение флага B для транзакции
Запрос работает без сбоев. Тем не менее, это подвох. Есть миллиарды записей транзакций, и каждая транзакция имеет около 7 позиций транзакций.
Вот как быстро это идет сейчас:
- Общее время: 616 секунд / 10,27 минут
- Обрабатывает 1218 транзакций в секунду / 73 000 транзакций в минуту
Я отслеживал время процесса для каждого шага:
Установить значение флага A для TransactionItem
- 4 минуты 52 секунды
Установить значение флага B для TransactionItem
- 3 минуты 26 секунд
Установите значение флага A для транзакции
- 1 минута 6 секунд
Установите значение флага B для транзакции
- 0 минут 51 секунда
Ниже мой полный запрос. Вот другие используемые таблицы
Товар
- Каждый TransactionItem имеет ProductId. Каждый продукт имеет ProductCode.
- Один код продукта имеет много продуктов
FlagAproductCodes
- Один столбец со списком ProductCodes, которые классифицированы как FlagA
FlagBproductCodes
- Один столбец со списком кодов продуктов, которые классифицированы как FlagB
TransactionPayment
- Это таблица фактов, содержащая реквизиты платежа для каждой транзакции
Payment_Dim
- Ссылки на TransactionPayment на PaymentID
- Это необходимо, потому что FlagB установлен на основе Payment_Dim.PaymentName
У меня есть эти индексы:
Транзакции 1. TransactionID
TransactionItems 1. TransactionID 2. ProductID
Продукт 1. ProductID 2. Код продукта
FlagAproductCodes 1. ProductCode
FlagBproductCodes 1. ProductCode
Оплата 1. PaymentID 2. PaymentCode 3. Payment_Name
Я очень ценю помощь, спасибо
-- 1. Set value of FlagA for TransactionItem
Update
TransactionItems Item
Set FlagA =
(
Select
Case
When
Item.FlagA_Qty = 0 Then 'N' -- this is the quantity of items purchased that fall into the FlagA category
When
FlagA.ProductCode Is Null Then 'N'
Else
'Y'
End
From
Product Prod
Left Join
FlagAproductCodes FlagA
On Product.ProductCode = FlagA.ProductCode
Where
Product.Prod_Id = Item.Prod_Id
)
;
-- 2. Set value of FlagB for TransactionItem
Update TransactionItems
Set FlagB = 'Y'
Where ItemID In
(
Select
Trans_Items.ItemID
From
TransactionItems Trans_Items
Inner Join Product Prod
On Trans_Items.Prod_Id = Product.Prod_Id
Inner Join FlagBproductCodes FlagB
On Product.ProductCode = FlagB.ProductCode
Where
(
Trans_Items.Gov_FlagA_Qty < Trans_Items.Item_Qty
)
AND
(
Exists
(Select Transaction_Payment_Fid
From TransactionPayment Trans_Pay
Inner Join Warehouse.Payment_Dim Pay_Dim
On Trans_Pay.Payment_Id = Pay_Dim.Payment_Id
Where
Transaction_Fid = Trans_Items.Transaction_Fid
And Upper(Pay_Dim.Payment_Name) Like '%ABC%'
)
)
)
;
Update TransactionItems
Set FlagB = 'N'
Where FlagB Is Null;
-- 3: Set FlagA for Transactions
Update
Transactions
Set
Gov_FlagA_Flag =
Case When Exists
(Select ItemID
From TransactionItems Item
Where Item.Transaction_Fid = Transactions.Transaction_Fid
and gov_FlagA_flag = 'Y')
Then 'Y'
Else 'N'
End
;
-- 4: Set FlagB for Transactions
Update
Transactions
Set
FlagB =
Case When Exists
(Select ItemID
From TransactionItems Item
Where Item.Transaction_Fid = Transactions.Transaction_Fid
And FlagB = 'Y')
Then 'Y'
Else 'N'
End
;
3 ответа
Вам нужно изучить параллельное выполнение, которое, вероятно, слишком обширная тема, чтобы полностью ее здесь изучить. (И я не вправе много говорить об этом).
В то же время вы можете получить некоторую выгоду, обновляя каждую таблицу только один раз и уменьшая количество случайных поисков. Это не проверено, но я думаю, что это та же логика, что и ваши три обновления для TransactionItems, например:
merge into TransactionItems TI
using (
select P.Prod_ID,
case when FAPC.ProductCode is null then 'N' else 'Y' end as FlagA,
case when FBPC.ProductCode is null then 'N' else 'Y' end as FlagB
from Product P
left join FlagAproductCodes FAPC on FAPC.ProductCode = P.ProductCode
left join FlagAproductCodes FBPC on FBPC.ProductCode = P.ProductCode
) temp
on (temp.Prod_id = TI.Prod_ID)
when matched then
update set TI.FlagA = case when temp.FlagA = 'Y' and TI.FlagA_Qty != 0
then 'Y' else 'N' end,
TI.FlagB = case when TI.FlagA_Qty < TI.Item_Qty
and exists (
select Transaction_Payment_Fid
from TransactionPayment TP
join Payment_Dim PD on TP.Payment_Id = PD.Payment_Id
where TP.Transaction_Fid = TI.Transaction_Fid
and upper(PD.Payment_Name) Like '%ABC%'
) then 'Y' else 'N' end
/
Вы можете предпочесть создать обновляемый вид. Но на этот объем данных это все еще займет много времени.
Я действительно ценю руководство - это действительно помогло!
Включение параллели имело огромное значение!
ALTER SESSION ENABLE PARALLEL DML;
Еще раз спасибо всем за помощь
Интересный вызов. Моя немедленная реакция - разделить и завоевать - написать PLSQL для работы с секторами / диапазонами идентификаторов и выполнять частые коммиты. Затем запустите параллельные задания для работы на разных диапазонах, затем настройтесь на поиск оптимальных настроек. Если по счастливой случайности таблицы разбиты на части, тем лучше.
Кроме того, хотя я и приветствую эпоху, когда все было сделано на основе множеств, еще до того, как о PLSQL даже и мечтали, вы можете подумать о повторном проектировании на транзакционной основе, а не о своем нынешнем подходе, основанном на множествах. для обновления основных строк как массового сбора, а затем используйте массовый сбор для запуска обновлений таблицы сведений. Я обнаружил, что это может быть быстрее, и это, безусловно, дает вам гораздо больший контроль при выполнении этой операции. Это также даст вам возможность перезагрузить компьютер в случае сбоя, например, слишком старый снимок, заполненные архивные журналы и т. Д. Вы не захотите начинать все сначала, если это не удастся.