Oracle Performance tuning of a query in Oracle DB

Question

I have a query which runs for almost 2 hours without producing any result. I have taken the execution plan and it looks like below .

Code

This code basically does pivot operation:

SELECT y.COMPANYNAME as OIPACOMPANYNAME, 
       z.POLICYNUMBER as OIPAPOLICYNUMBER,
       x.STATUSCODE as OIPASTATUSCODE,
       MAX(CASE 
       WHEN w.FIELDTYPECODE='01' AND w.FIELDNAME='PolicyEffDate' THEN w.DATEVALUE
       ELSE NULL
       END ) as OIPAPOLICYEFFDATE,
       x.ISSUESTATECODE as OIPAISSUESTATECODE,
     NULL as OIPACURRENTSTATECODE,
       MAX(CASE 
       WHEN w.FIELDTYPECODE='01' AND w.FIELDNAME='PolicyEndDate' THEN w.DATEVALUE
       ELSE NULL
       END ) as OIPAPOLICYENDDATE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='03' AND w.FIELDNAME='IssueAge' THEN w.INTVALUE
       ELSE NULL
       END) as OIPAISSUEAGE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='03' AND w.FIELDNAME='PolicyYear' THEN w.INTVALUE
       ELSE NULL
       END) as OIPAPOLICYYEAR,
       V.PLANNAME AS OIPAPLANNAME,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='PolicyUniqueIdentifier' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAPOLUPI,
     NULL as OIPAPLANMODALFACTOR,  
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='Participating' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAPARTICIPATING,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='Participating' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAREINSURANCETYPE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='BaseFaceAmount' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPABASEFACEAMOUNT,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='QualType' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAQUALTYPE,
     NULL as OIPATAXQUALSALESMARKET, --- field record not found hence set as null 
     MAX(CASE 
       WHEN w.FIELDTYPECODE='01' AND w.FIELDNAME='BillToDate' THEN w.DATEVALUE
       ELSE NULL
       END) as OIPABILLTODATE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='01' AND w.FIELDNAME='PaidToDate' THEN w.DATEVALUE
       ELSE NULL
       END) as OIPAPAIDTODATE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='PaymentMode' THEN w.TEXTVALUE
       ELSE NULL
      END ) as OIPAPAYMENTMODE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='PaymentMethod' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAPAYMENTMETHOD,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='03' AND w.FIELDNAME='BillingLeadDays' THEN w.INTVALUE
       ELSE NULL
       END) as "OIPABILLING LEAD DAYS",
     MAX(CASE 
       WHEN w.FIELDTYPECODE='03' AND w.FIELDNAME='BankDraftDay' THEN w.INTVALUE
       ELSE NULL
       END) as OIPABANKDRAFTDAY,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='EFTDraftCode' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAEFTDRAFTCODE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='CurrentModalPremiumAmt' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPACURRENTMODALPREMIUMAMT,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='CurrentAnnualPremiumAmt' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPACURRENTANNUALPREMIUMAMT,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='PolicyModalPremiumAdjustment' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPAPOLICYMODALPREMIUMADJ,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='PolicyFee' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPAPOLICYFEE,
     NULL as OIPALOANPRINCIPAL,
     NULL as OIPAOUTSTANDINGLOAN,
     NULL as OIPALOANINTERESTDUE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='LoanInterestRate' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPALOANINTERESTRATE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='LoanInterestType' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPALOANINTERESTTYPE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='APLIndicator' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPAAPLINDICATOR,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='03' AND w.FIELDNAME='LoanBankDraftDay' THEN w.INTVALUE
       ELSE NULL
       END) as OIPALOANBANKDRAFTDAY,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='LoanPaymentMethod' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPALOANPAYMENTMETHOD,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='LoanPaymentMode' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPALOANPAYMENTMODE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='LoanModalPaymentAmount' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPALOANMODALPAYMENTAMOUNT,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='03' AND w.FIELDNAME='LoanBillingLeadDays' THEN w.INTVALUE
       ELSE NULL
       END) as OIPALOANBILLINGLEADDAYS,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='01' AND w.FIELDNAME='LoanBillingDate' THEN w.DATEVALUE
       ELSE NULL
       END) as OIPALOANBILLINGDATE,
     NULL as OIPAPITCASHVALUE,
     NULL as OIPAPITCASHSURRVALUE,
     NULL as OIPANXTMODVARCASHVALUE,
     NULL as OIPAPITDIVONDEP,
     NULL as OIPAPITINTONDEP,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='DividendOptionPrimary' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPADIVIDENDOPTIONPRIMARY,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='DividendOptionSecondary' THEN w.TEXTVALUE
       ELSE NULL
       END) as OIPADIVIDENDOPTIONSECONDARY,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='DividendToApplyToCash' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPADIVIDENDTOAPPLYTOCASH,
     NULL as OIPADIVIDENDTOAPPLYTOACCUDIV,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='DividendToApplyToLoan' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPADIVIDENDTOAPPLYTOLOAN,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='DividendToApplyToOYT' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPADIVIDENDTOAPPLYTOOYT,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='DividendToApplyToPremium' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPADIVIDENDTOAPPLYTOPREMIUM,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='DividendToApplyToPUA' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPADIVIDENDTOAPPLYTOPUA,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='DividendInterestRate' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPADIVIDENDINTERESTRATE,
     MAX(CASE 
       WHEN w.FIELDTYPECODE='01' AND w.FIELDNAME='DividendDeclaredDate' THEN w.DATEVALUE
       ELSE NULL
       END) as OIPADIVIDENDDECLAREDDATE,
     NULL as OIPAPUA,
     NULL as OIPAPUAPURCHASEDLASTANN,----- field record not found hence set as null 
     NULL as OIPAOYT,--field record not found hence set as null
     NULL as OIPAEICTOAPPLYTOOYT,--- field record not found hence set as null 
     MAX(CASE 
       WHEN w.FIELDTYPECODE='04' AND w.FIELDNAME='NetCostBasis' THEN w.FLOATVALUE
       ELSE NULL
       END) as OIPANETCOSTBASIS,


     a.SEGMENTGUID as "OIPASEGMENTGUID",
     MAX(CASE 
       WHEN b.FIELDTYPECODE='02' AND b.FIELDNAME='SegmentFormerSystemPlanCode' THEN b.TEXTVALUE
       ELSE NULL
       END ) as "OIPASEGMENTFORMERSYSTEMPLAN",
     c.SEGMENTNAME as "OIPASEGMENTNAME",
     MAX(CASE 
       WHEN b.FIELDTYPECODE='02' AND b.FIELDNAME='SegmentStatusCode' THEN b.TEXTVALUE
       ELSE NULL
       END ) as "OIPASEGMENTSTATUSCODE",
     MAX(CASE 
       WHEN b.FIELDTYPECODE='01' AND b.FIELDNAME='SegmentIssueDate' THEN b.DATEVALUE
       ELSE NULL
       END ) as "OIPASEGMENTISSUEDATE",
     MAX(CASE 
       WHEN b.FIELDTYPECODE='01' AND b.FIELDNAME='SegmentEndDate' THEN b.DATEVALUE
       ELSE NULL
       END ) as "OIPASEGMENTENDDATE",
     MAX(CASE 
       WHEN b.FIELDTYPECODE='01' AND b.FIELDNAME='SegmentTerminationDate' THEN b.DATEVALUE
       ELSE NULL
       END ) as OIPASEGMENTTERMINATIONDATE,
     MAX(CASE 
       WHEN b.FIELDTYPECODE='03' AND b.FIELDNAME='SegmentIssueAge' THEN b.INTVALUE
       ELSE NULL
       END ) as OIPASEGMENTISSUEAGE,
     MAX(CASE 
       WHEN b.FIELDTYPECODE='03' AND b.FIELDNAME='BaseIssueAge' THEN b.INTVALUE
       ELSE NULL
       END ) as OIPABASEISSUEAGE  
     FROM ASPOLICY x LEFT JOIN ASCOMPANY y ON (x.COMPANYGUID=y.COMPANYGUID)
     INNER JOIN AUDIT_EXT_TEMP_BULK Z ON (z.COMPANYGUID=y.COMPANYGUID and x.POLICYGUID=Z.POLICYGUID )
     LEFT JOIN ASPOLICYFIELD w ON (W.POLICYGUID=z.POLICYGUID)
     LEFT JOIN ASPLAN v ON (v.PLANGUID=x.PLANGUID AND v.COMPANYGUID=z.COMPANYGUID) -- Plan Information
     LEFT JOIN ASSEGMENT a ON (x.POLICYGUID=a.POLICYGUID)  -- Segment 
     LEFT JOIN ASSEGMENTFIELD b ON (b.SEGMENTGUID=a.SEGMENTGUID) -- Segment 
     LEFT JOIN ASSEGMENTNAME c ON (c.SEGMENTNAMEGUID=a.SEGMENTNAMEGUID) 
     GROUP BY y.COMPANYNAME,

             z.POLICYNUMBER,

             x.STATUSCODE,

             x.ISSUESTATECODE,

             v.PLANNAME,

             a.SEGMENTGUID,

             c.SEGMENTNAME;

Here is the tail of the statement exposed for readability:

FROM ASPOLICY x LEFT JOIN ASCOMPANY y ON (x.COMPANYGUID=y.COMPANYGUID)
INNER JOIN AUDIT_EXT_TEMP_BULK Z ON (z.COMPANYGUID=y.COMPANYGUID 
                                      and x.POLICYGUID=Z.POLICYGUID )
LEFT JOIN ASPOLICYFIELD w ON (W.POLICYGUID=z.POLICYGUID)
LEFT JOIN ASPLAN v ON (v.PLANGUID=x.PLANGUID 
                       AND v.COMPANYGUID=z.COMPANYGUID) -- Plan Information
LEFT JOIN ASSEGMENT a ON (x.POLICYGUID=a.POLICYGUID)  -- Segment 
LEFT JOIN ASSEGMENTFIELD b ON (b.SEGMENTGUID=a.SEGMENTGUID) -- Segment 
LEFT JOIN ASSEGMENTNAME c ON (c.SEGMENTNAMEGUID=a.SEGMENTNAMEGUID) 
GROUP BY y.COMPANYNAME,
         z.POLICYNUMBER,
         x.STATUSCODE,
         x.ISSUESTATECODE,
         v.PLANNAME,
         a.SEGMENTGUID,
         c.SEGMENTNAME;

The tables have the following indexes, where left side of the arrow represents the table and right side represents the column which have index on it. Please note: before running the query I had updated stats on the tables by running dbms_stats.gather_table_stats('schema','table name');

AUDIT_EXT_TEMP_BULK-->COMPANYGUID,POLICYGUID);

ASPLAN-->PLANGUID

ASPOLICY-->COMPANYGUID

ASCOMPANY-->COMPANYGUID

ASPOLICYFIELD-->POLICYGUID

ASSEGMENT-->POLICYGUID

ASSEGMENTFIELD-->SEGMENTGUID

ASSEGMENTNAME-->SEGMENTNAMEGUID

Answer 1

"This code basically does pivot operation"

So what it actually does is attempt to create a coherent result set from an Entity-Attribute-Value model. These are notoriously difficult to tune. EAV suits certain use-cases, such as analytics, but re-constructing a row from an atomized set of columns is very expensive. They are also tiresome to query (as you now realise!) and also error-prone, because re-construction is a manual process - this probably isn't what you need to return for OIPAREINSURANCETYPE:

 MAX(CASE 
   WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='Participating' THEN w.TEXTVALUE
   ELSE NULL
   END) as OIPAPARTICIPATING,
 MAX(CASE 
   WHEN w.FIELDTYPECODE='02' AND w.FIELDNAME='Participating' THEN w.TEXTVALUE
   ELSE NULL
   END) as OIPAREINSURANCETYPE,

This demonstrates the unsuitability of EAV for most of the cases where it's used. The schema is still fixed, it's just hard-coded into reads instead of writes.

Of course, EAV is not just hard on those of us who have to query the "model": it also makes it impossible for the database to understand how the data is stored, and so generates bad execution plans.

I admit I'm impressed: I can't remember the last time I saw a G in the Rows column of an explain plan. That is, your explain plan shows your query is wrestling with ~923,000,000,000 rows which the GROUP BY will boil down to a mere ~7,879,000,000 rows.

So, given how much data you're returning how long do you think it should take?

Perhaps that final number surprises you. No doubt you were expecting 7789 rows (one per policy). Instead you have a monstrous Cartesian product. Where do all those extra rows come from?

One of the issues is the join on AUDIT_EXT_TEMP_BULK: that adds more than eight hundred rows per policy because there is no filter on that table. That's an expensive way to retrieve a policy number, which is presumably fixed for every policy. If there isn't a more sensible way to get this one value (say a column on the ASPOLICY table) replacing that table with an in-line view should reduce the numbers quite a bit:

INNER JOIN (select distinct COMPANYGUID, POLICYGUID, POLICYNUMBER 
         from  AUDIT_EXT_TEMP_BULK Z )
 ON (z.COMPANYGUID=y.COMPANYGUID and x.POLICYGUID=Z.POLICYGUID )

Another thing is the GROUP BY: you actually need to aggregate on just these columns:

y.COMPANYNAME as OIPACOMPANYNAME, 
z.POLICYNUMBER as OIPAPOLICYNUMBER,
x.STATUSCODE as OIPASTATUSCODE

Removing the other columns might improve the explain plan quite considerably.