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.
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,