cancel
Showing results for 
Search instead for 
Did you mean: 

Webi Performance

Former Member
0 Kudos
68

Is it normal for Webi to take a significant amount of time loading it's micro-cube and rendering to the browser when processing 100,000 records or more?

We've been doing some extensive testing on this recently, and can't seem to find a way to speed this up at all. The more frustrating part is that no one can really explain what the application is doing during those steps in any detail.

Thanks in advance for your input.

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

100K are definitely not a small amount of rows. What do you mean by singificant amount of time? How many rows are displayed? What kind of data source do you access? Are those data saved in the report or are they fetched when the report is run directly from the data source?

Which version of BO do you use?

Regards,

Stratos

Former Member
0 Kudos

We typically display all records on our reports. No charts or graphs, no aggregation. (I don't design the reports BTW).

We're running XI r2 SP 6 against Oracle 11g data sources.

We're on a 2-node Solaris cluster, with 6 Webi Servers on each node.

On both of my BOE servers, I NEVER go above 10% CPU usage and NEVER go above 30% Memory usage. So I know my issues are not infrastructure related.

For 100K+ reports, I'm seeing in the trace logs that it takes 10 - 14 minutes just to fetch the data and load the micro-cube. The Oracle query finishes in less than 2 minutes, so that part is tuned.

Then rendering the report (which can sometimes produce several thousand pages) can take another 6 - 10 minutes.

What I'm really trying to figure out is

1) Is this normal, expected behavior of Webi?

2) Since I'm using so little CPU and Memory, is there a setting I can change to make the Webi Report Server use more?

3) Is there something I can set in the Universe to make it return larger chunks of data? CPU, Memory, and Disk are not issues for us. **Already tried "Array Fetch Size" and it had little effect.

denis_konovalov
Active Contributor
0 Kudos

If you take the SQL used in Webi report and run it in SQLplus on the machine where webi report server is runing it takes 2 minutes , but report running on the same webi machine takes 20 minutes to display ?

I'd say you need to open a support incident to see what's going on, as this shlouldn't be like that... There will always be overhead refreshing and viewing report in Webi vs. Query time in SQLPlus - but it shoudn't be like that.

10-14 minutes just to fetch data might indicate that the orace DB is processing query from webi differently vs. query from sqlplus....

Former Member
0 Kudos

You are correct Dennis.

SQL*Plus takes like 2 minutes to complete the query and display the last record on the screen.

Webi is taking almost 20 minutes from beginning to end. From the trace logs I can tell that it's taking about 10 minutes in the DataFetch step, then about another 10 in the Render step.

I opened a support ticket on this first. They said I needed to restart my servers once a week and manually empty the cache in the /bobje/data directory. I haven't tried that yet, because of my level of skepticism about it.

denis_konovalov
Active Contributor
0 Kudos

well... I can't comment on their suggestions, maybe they know something we don't

You can try it and see if that makes a difference, tho if logs show a call to DB and data comes back 10 minutes later - I'd be looking at DB communications and why it's different vs. SQlPlus.... is the SQL webi sends to DB the same as SQLPlus SQL ?

does DB side has any issues at the time of that refresh.

Because at that point Webi portion stops we're just waiting for DB to process SQL and get data back to us.

Once we jknow what happens in this 10 minutes , we can start deducting why rendering takes another 10 minutes...

What patch level are you on again ?

Also - what happens if you refresh report right after this first refresh ?

I've seen an issue when first refresh against oracle would take 10+ minutes, even in SQLPlus, but all other refreshes were 2~3 minutes, until next morning when sequence repeat itself.

This was fdue to the way tables were prepopulated by oracle and fixed by the DBA....

Please test that scenario as well...

Former Member
0 Kudos

Thanks Denis,

I do plan on testing out the cache clearing soon.

We're on XIr2 SP6 on Solaris.

We're not seeing the query get sent to the DB and Webi just sitting there for 10 minutes waiting. The data starts streaming back to the WI Report Server in chunks (array fetch size I believe), and it takes 10 minutes or so to finish getting all of the data and loading it into the micro-cube before it starts to render the page.

That same query takes much less time to return the data set to the last record in SQL*Plus or SQL Developer (like 2 minutes).

When I ran the query from the native tools, I took the completed query that Webi used from the trace logs, so I'm sure it's the exact same SQL that the Universe generated.

We've actually seen the opposite behavior you describe with regards to refreshes. The first time a report is run seems to be the best it runs. If you don't change any parameters and just hit refresh, it can take twice or three times as long to return. This is a separate issue, though, I've been told. It's reportedly fixed in XI 3.1. There is something that Webi passes in to Oracle 11g that confuses the optimizer and it doesn't pick the same plan it just ran, and ends up generating a bunch of other plans, then takes its sweet time deciding which one to use. Our DBAs disabled something in Oracle to make this go away for now, so this part is okay.

Really just trying to figure out if that data load step can be sped up at all, or if this is what it is and we have to deal with it.

denis_konovalov
Active Contributor
0 Kudos

in the logs you should see normal processing up to the point when SQl is generated and sent to DB, then webi will wait for DB to send results back, once that is done it should start the rendering process and there will be no SQL going anywhere...

SQL will be generated several times if you have prompts/LOV's involved and if LOV's are refreshed - that SQl goes to DB as well....

If webi sits doing nothing before SQL is sent to DB - maybe it's waiting on cache.... but it's weird, you should see those calls in the log...

Ask this case to get escalated to the next level, there's nothing that can be done as far as patches go, as XIR2 is out of patch support, but maybe some of the post SP6 patches have some improvement on this and deeper log review will reveal something we're missing.

You should definately try to play with array fetch size - if you see data come in chunks, maybe you can find the right size to allow you to get it all in one...

I'd still say that 2minutes in native tools and 20 minutes in webi is way too much, something is going wrong here...

Answers (0)