2 Replies Latest reply on Dec 2, 2018 1:27 PM by 3370137

    OBIEE Report Performance Issue

    3370137

      Hi,

      We have just started supporting client with OBIEE 12.2.1.2.0 (Build BIPS-20160923092103 64-bit) with Oracle data base version Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

       

      There is weird performance issue with couple of reports. The reports works perfectly fine in DEV Environment, but they don't return any data in QA and Prod Environment.

       

      Initially we performed gather Stats, and the report returned data in QA & Prod Environment. however couple of hours later same issue was observed.

       

      We suspected one of the Materialized View so we remigrated it from DEV to QA after migration all reports returned data in stipulated time period. but when we re tested them after around 12 hrs we observed that performance was impacted and one report didn't return data at all (Query just keeps on running)

       

      As per design report refers 2 Materialized & 4 to 5 Views in database.

       

      Can anyone advise on

      1) Parameter to be verified in Database or OBIEE.

      2) Recommendations on MV building block

      or what else can be done to get the performance in line. Since the Issue is in Production as well it is very critical.

       

      Thanks

      S sameer

        • 1. Re: OBIEE Report Performance Issue
          Christian Berg

          1.) Are you really talking about BI Publisher *REPORTS*? Or an OBI *ANALYSES*? Two completely different technologies.

           

          2.) What do the DBAs say? Have you looked at the explain plans?

           

          3.) From what you state it looks like 0% OBI issue and 100% database issue.

          • 2. Re: OBIEE Report Performance Issue
            3370137

            Thanks Christian, its an OBI Analysis Report.

            We Copied Physical query from Query log of OBIEE and tried to execute it through OBIEE (using same connection pool as used by report) by Direct Database Request and we observed same behavior i.e query remained in running state.

            Then we replaced the one of the MV with its definition in the physical query and executed the Direct Database request again and this time we got the result.

             

            Original From clause was like

             

            From  View1 left outer join MV2  on  View1.ID=MV2.ID

             

            For Direct Database Request we modified it to

             

            From  View1 left outer join (Select .... from .... Where ....) AS MV2 on  View1.ID=MV2.ID

             

            We have asked DBA team for there recommendation by looking at Explain plan and AWR and unfortunately we didn't receive any inputs.

            So we are not sure on what is causing the issue,