COGS Revenue Matching Report poor performance with EXADATA (Doc ID 1629939.1)

Last updated on MARCH 19, 2019

Applies to:

Symptoms

In Exadata, COGS Revenue Matching Report exhibits poor performance on following sql:

ERROR
-----------------------
--Bug 12409271(Added following hint to start execution with Z and use Index join) SELECT /*+ Leading(Z) INDEX_JOIN(lines RA_CUSTOMER_TRX_LINES_U1 RA_CUSTOMER_TRX_LINES_N1)*/ :i_all_lines all_lines, Z.order_number order_number, Z.order_date order_date, Z.customer_name customer, Decode(trx.TRX_NUMBER, Min(lines1.TRX_NUMBER), Decode(Z.INVOICE_LINE, Min(lines1.MIN_INV_LINE), Z.currency, NULL), NULL) currency, Z.sales_order_line order_line, Z.sales_order_line_id order_line_id, trx.trx_number invoice_number, Z.invoice_line invoice_line, Z.item item_number, ROUND(DECODE(Z.item_type_code, 'CONFIG', lines2.total_earned_revenue, SUM (DECODE(dist.account_class, 'UNEARN', 0, 'UNBILL', 0, dist.acctd_amount ) ) ) *DECODE(Z.earned_cogs, Z.total_earned_cogs, 1, Z.earned_cogs/DECODE(Z.total_earned_cogs, 0, 1, Z.total_earned_cogs)), 2) earned_revenue, ROUND(DECOD



STEPS
-----------------------
1. run COGS Revenue Matching Report

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.