Here I’d like to show performance benefits of Exadata on complex, realistic SQL.

Previous queries are more or less for toy, where the only purpose is to clearly show some feature/concept and what performance benefits you expect from it.

The query I use in this article is known to be problematic on one of production systems, and I’d like to check if Exadata can help.


select /*+ parallel */  
         b.note_type 
,        b.note_num 
,        b.note_date 
,        c.document_num 
,        c.ref_document_id 
,        c.currency_code 
,        b.location_id 
,        loc.phy_loc
,        loc.phy_loc_name
,        loc.cost_center
,        loc.cost_center2
,to_date('01.01.2016' ,'dd.mm.rrrr') 
,to_date(nvl('28.02.2017' ,sysdate),'dd.mm.rrrr') 
,        sum(b.RUC_item)
,        sum(b.RUC_amb)
,        sum(b.RUC)
,        sum(b.nab_vr_item)
,       sum( b.nab_vr_amb)
,        sum(b.nab_vr)
,        sum(b.vp_roba) vp_item
,        sum(b.vp_amb) vp_amb
,        sum(b.vp) vp
,        sum(b.porez_roba) tax_item
,        sum(b.porez_amb) tax_amb
,        sum(b.porez) tax
,        sum(b.vp_s_pdv_roba) vp_
,        sum(b.vp_s_pdv_amb) vp_
,        sum(b.xxx) vp_
,        sum(b.xxx) 
,        sum(b.xxx) 
,        sum(b.xxx) 
,        sum(b.vp_xxx) 
,        sum(b.porez + b.tax) 
,        sum(b.vp_s_pdv + b.vp_s_pdv_usluge) 
,        c.customer_id
,        cmr.customer_name
,        sysdate 
from    (select
                     b.order_id
            ,        b.note_num
            ,        b.location_id
            ,        b.note_date
            ,        b.document_id
            ,        b.note_type
            ,        sum(decode(amb.ambalaza, 'amb', 0, nvl(a.amt_wholesale,0))) - decode(amb.amb, 'amb', 0, nvl(nabvr.nab_vr,0)) 
            ,        sum(decode(amb.ambalaza, 'amb', nvl(a.amt_wholesale,0), 0)) - decode(amb.amb, 'amb', nvl(nabvr.nab_vr,0), 0) 
            ,        sum(nvl(a.amt_wholesale, 0)) - nvl(nabvr.nab_vr, 0) 
            ,        decode(amb.ambalaza, 'amb', 0, nvl(nabvr.nab_vr,0)) 
            ,        decode(amb.ambalaza, 'amb', nvl(nabvr.nab_vr,0), 0) 
            ,        nvl(nabvr.nab_vr, 0) 
            ,        sum(decode(amb.ambalaza, 'amb', 0, nvl(a.amt_wholesale,0))) 
            ,        sum(decode(amb.ambalaza, 'amb', nvl(a.amt_wholesale,0), 0)) 
            ,        sum(nvl(a.amt_wholesale, 0)) 
            ,        sum(decode(amb.ambalaza, 'amb', 0, nvl(a.amt_vat,0))) 
            ,        sum(decode(amb.ambalaza, 'amb', nvl(a.amt_vat,0), 0)) 
            ,        sum(nvl(a.amt_vat,0)) 
            ,        sum(decode(amb.ambalaza, 'amb', 0, nvl(a.amt_wholesale,0) + nvl(a.amt_vat,0))) 
            ,        sum(decode(amb.ambalaza, 'amb', nvl(a.amt_wholesale,0) + nvl(a.amt_vat,0), 0)) 
            ,        sum(nvl(a.amt_wholesale,0) + nvl(a.amt_vat,0) + nvl(a.qty_invoiced,0)*nvl(a.item_fee3,0)) 
            ,        sum(nvl(a.qty_invoiced,0)*nvl(a.item_fee3,0)) 
            ,        sum(0)  
            ,        sum(0) 
            ,        sum(0) 
           from      dnd a
            ,        dn b
            ,        xxx
            ,        (select sum(nab_vr) nab_vr, ref_no_2
                        from   (  select sum(total_cost) nab_vr, ref_no_2
                                  from   tdh
                                  where  tran_code = 82
                                  group by ref_no_2
                                  union all
                                  select sum(total_cost) , ref_no_2
                                  from   th
                                  where  tran_code = 82
                                  group by ref_no_2
                                 )group by ref_no_2) nabvr
             , ord cc
          where      a.id = b.id
            and      a.id = amb.item(+)
            and      nabvr.ref_no_2(+) = cc.tsf_no
            and      b.order_id = cc.id (+)
            and      note_type = 'DN'
            group by  b.order_id
            ,        b.note_num
            ,        b.location_id
            ,        b.note_date
            ,        b.document_id
            ,        b.note_type
            ,        amb.ambalaza
            ,        nabvr.nab_vr
         union
         select
                     b.return_id
            ,        b.note_num
            ,        b.location_id
            ,        b.note_date
            ,        b.document_id
            ,        b.note_type
...

1.
–realistic query execuied from non-Exadata environment
Elapsed: 00:04:26.21

2.
–realistic production query executed on Exadata
Elapsed: 00:00:22.49

Here you can clearly see that Exadata really shines on complex queries that involve parallelism, and not only with toy queries that I0ve used in previous articles. .

You can benefit the most from Exadata when use it for Data Warehouses as you can use the features designed for BI.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.