提高在ExaData上运行Oracle查询的速度

Question

我正在处理一个Oracle查询，我迫切需要加快它的速度。如有任何建议，我将不胜感激。

• 数据库是Oracle，运行在ExaData集群上。
• Oracle版本: Oracle数据库11g企业版发行版11.2.0.3.0-64位生产

我有两张桌子。

1)交易:在商店购买- TransactionID

2) TransactionItems:每次购买都有1..many项目-- TransactionID，ItemID

在每个表中，有两个标志：

• FlagA: Y/N
• FlagB: Y/N

查询需要：

1. 为FlagA中的每个记录设置TransactionItem和FlagB的值。
2. 根据FlagA中的标志的值为事务中的每一行设置TransactionItem和FlagB的值

我已经将我的查询分解为4个步骤。

1. TransactionItem标志A的设定值
2. TransactionItem标号B的设定值
3. 事务用标志A的设置值
4. 事务的标号B的设定值

查询运行平稳。然而，这是一个陷阱。有数以十亿计的交易记录，每个交易大约有7个交易项目。

现在它的速度是这么快：

• 总时间: 616秒/ 10.27分钟
• 每秒处理1 218次事务/每分钟处理73 000次事务

我跟踪了每一步的处理时间：

1. TransactionItem标志A的设定值

- 4 minutes 52 seconds

​

1. TransactionItem标号B的设定值

- 3 minutes 26 seconds

​

1. 事务用标志A的设置值

- 1 minute 6 seconds

​

1. 事务的标号B的设定值

- 0 minutes 51 seconds

​

下面是我的完整查询。以下是使用的其他表

产品

• 每个TransactionItem都有一个ProductId，每个产品都有一个ProductCode。
• 一个产品代码有许多产品。

FlagAproductCodes

1. 包含ProductCodes列表的单个列，这些列被归类为FlagA

FlagBproductCodes

1. 包含ProductCodes列表的单个列，这些列被归类为FlagB

TransactionPayment

1. 这是一个包含每个交易的支付细节的事实表。

Payment_Dim

1. 指向TransactionPayment on PaymentID的链接
2. 这是需要的，因为FlagB是基于Payment_Dim.PaymentName设置的

我有以下索引：

事务1. TransactionID

TransactionItems 1. TransactionID 2. ProductID

产品1. ProductID 2. ProductCode

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
;

Answer 1

您需要研究并行执行，这可能是一个过于宽泛的主题，无法在这里进行充分的探讨。(我没有资格多说)。

同时，通过只更新每个表一次，并减少附带查找的次数，您可能会得到一些好处。这是未经测试的，但我认为涉及的逻辑与针对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
/

您可能更喜欢创建一个可更新的视图。但在这么大的数据量上，还需要很长的时间。

这也可能是有用的。

Answer 2

有趣的挑战。我的直接反应是分而治之-编写PLSQL来操作扇区/id范围，并经常提交。然后启动并行作业，在不同的范围内工作，然后调整以找到最佳设置。如果幸运的话，这些表是分区的，那就更好了。

此外，虽然我来自一个一切都是基于集合的时代，在PLSQL还没有出现之前，您可能需要考虑在事务性的基础上重新设计它，而不是当前的基于集合的方法，即以批量收集的形式获取主行更新，然后使用大容量集合来驱动详细表更新。我已经发现，这可以更快，当然它给了你更多的控制，在批处理这一行动。这也将为您提供重新启动的选项，如果应该失败，例如快照太旧，归档日志填充等。如果这是不希望重新开始。

Answer 3

我真的很感激你的指导--这真的很有帮助！

启用并行产生了巨大的变化！

ALTER启用并行DML；

再次感谢你的帮助