We have a major issue with webi reports (4.1 SP7) timing out. There doesn’t seem to be any consistency to this. A report can run in 8 minutes on one day and time out (30 mins+) the next day. There is nothing untoward happening elsewhere on the network that we can see. When you look at the oracle (11g) execution plan, for the slow-run, it picks a completely different path.
Yesterday I compared two almost the same reports - just different date filters. One ran fast, the other slow. Different execution plan again. Ah, we thought - it’s indexes. So we made sure the indexes on the two date filters were the same.
This morning, the fast report is also slow.
We are running out of ideas here. The business is screaming that the service they’re getting is not adequate and we can’t get anything to run consistently.
Doesn’t sound like Webi is the issue to me here but Oracle is, have you taken the report SQL out and run it directly in SQLDeveloper or something and checked the explain plan there ? Have you tried updating the Oracle stats on the days it runs slow and see if it changes the explain plan ? Also date filters depending on what the difference is can change the explain a lot for example you might have the filter in such a way Oracle thinks a full table scan is the most efficient way vs another filter it may think using the index better.
Re-running the SQL in SSMS may help or not (it may be cacheing so giving a false positive). I’d get the DBA involved in the first instance to see if there are any performance benefits.
If they find it’s not an issue, then Webi server and networks admins may be able to help identify anything untowards.
We’re in Oracle territory here - and the DBA retired last summer…
A colleague and I spent a couple of days looking at execution plans and adding more indexes. We’ve cut the SQL, stripped it back, run it in TOAD etc etc …
Got last week’s tranche of reports working a bit better. Main business guy is off this week so we have a break before we start again! Still can’t figure out why the same report performs differently on different days, though.
(BOB member since 2005-03-01)
(BOB member since 2006-09-28)