We had this problem, the BI report was not returning any data, while the tables had data. We took query from the BI session log, and fired it in the TOAD client, it did return expected values?
This what we found after much head scratching:
There was a materialized view, which TOAD query was not using, while same query from BI was using. Same query had different explain plans. Reason behind this was following startup query hiding in BI connection pools:
alter session set query_rewrite_integrity=STALE_TOLERATED
Toad was not using the stale materialized view, and that explained the difference. I hope this blog saves lot of people from losing their hair (and sleep)!!
No comments:
Post a Comment