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