when i am trying to open a report its taking time to open. to that where can i start the performece tunning . wht can do that tell me pls?
nagarjuna2325 (BOB member since 2012-01-04)
Run the same query at database level and see how much time its taking there.
Thanks,
Zaif
zaif235 
(BOB member since 2010-06-15)
Hi,
This can help:
Marek Chladny 
(BOB member since 2003-11-27)
I don’t find any solution to optimize the WEBI report!!! 
My query of my report on BO XI R2 with 61 objects and 83000 lines is executed in:
- 4 seconds in a test software (TOAD).
- 10 secondes in DESKI.
- 2 minutes in WEB INTELLIGENCE.
This query is builded in WEBI, but any object or block or graphic is inserted in the main page, it means that the test report is empty.
To see if the problem is related to the java installation, I tried to run the report with a HTML report panel at place of Java report panel.
And I tried too to play with all the following parameters and I don’t found any SOLUTION:
The actual configuration of WIReportServer server is the following one:
- Maximum Simultanées Connections: 50
- Connexion Time Out: 60 minutes
- Enable Real Time Caching
- Document Cache Duration: 4320 minutes
- Document Cache Size: 1048576 KBytes
- Amount of Cache To Keep When Document Cache Is Full: 70%
- Document Cache Scan Interval: 120 minutes
- Maximum Number Of Downloaded Documents To Cache: 0
- Maximum Binary File Size: 100 MBytes
- Maximum Character File Size: 50 MBytes
The parameters of the universe:
- All the objects of the univers, which I don’t use for the prompts, are not associated to their list of values.
- Array fetch size: 250
- Array bind size: 32767
- Login timeout: 600
- Binary Slice Size: 32000
- The other parameters of the univers are joined to this post.
In “boxir2/bobje/enterprise115/solaris_sparc/dataAccess/RDBMS/connectionServer/oracle/ORACLE.PRM”
N
In “boxir2/bobje/enterprise115/solaris_sparc/dataAccess/RDBMS/connectionServer/CS.CFG”
No
10
In “boxir2/bobje/enterprise115/solaris_sparc/dataAccess/RDBMS/connectionServer/oracle/ORACLE.SBO”
Yes
250
In
“boxir2/bobje/tomcat/webapps/businessobjects/enterprise115/desktoplaunch/WEB-INF/classes/webi.properties”
“boxir2/bobje/tomcat/webapps/jsfadmin/WEB-INF/classes/webi.properties”
“boxir2/bobje/tomcat/webapps/dswsbobje/WEB-INF/classes/webi.properties”
I have:
#WID_FAILOVER_SIZE=10^M
#WID_STORAGE_TOKEN_STACK_SIZE=10^M
The use of the memory in the bo server at an instant t:
load averages: 2.17, 2.69, 2.98; up 269+00:25:03 17:32:02
853 processes: 835 sleeping, 15 zombie, 3 on cpu
CPU states: 92.9% idle, 5.2% user, 1.9% kernel, 0.0% iowait, 0.0% swap
Memory: 64G phys mem, 18G free mem, 56G total swap, 56G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
10522 yaapadm 168 59 0 337M 314M sleep 107.4H 0.20% boe_cmsd
23577 yaapadm 75 29 10 629M 434M sleep 534:29 0.01% java
10575 yaapadm 15 59 0 80M 69M sleep 47:52 0.01% boe_filesd
10767 yaapadm 10 59 0 73M 63M sleep 223:07 0.01% ConnectionServe
20787 yaapadm 30 59 0 705M 670M sleep 136:11 0.00% WIReportServer
10779 yaapadm 32 59 0 120M 90M sleep 157:25 0.00% boe_fcprocd
10685 yaapadm 23 59 0 155M 114M sleep 87:58 0.00% boe_pagesd.bin
10561 yaapadm 22 59 0 48M 34M sleep 85:49 0.00% boe_pagesd.bin
11090 yaapadm 3 59 0 95M 70M sleep 17:39 0.00% boe_fcprocd
10774 yaapadm 17 59 0 111M 83M sleep 59:27 0.00% boe_fccached
10781 yaapadm 21 59 0 225M 167M sleep 31:31 0.00% boe_crystalras
10729 yaapadm 10 59 0 42M 30M sleep 31:06 0.00% boe_eventsd
10617 yaapadm 8 59 0 43M 31M sleep 44:11 0.00% boe_jobsd
10562 yaapadm 18 59 0 71M 56M sleep 60:14 0.00% boe_cachesd
10592 yaapadm 15 59 0 73M 63M sleep 44:50 0.00% boe_filesd
I have 18 Go of free RAM on 64Go in the server.
I tried also to increse the HeapSize in Weblogic.
“%JAVA_HOME%\bin\java” -server – Xms2048m – Xmx2048m
I activated also the traces on WIReportServer but I don’t see anything that it can help me:
I show you some lines that I still suspect:
QT_LocalServer:sParamConnect:VERSION=6;Name=URPT;Shared=4;LoginTimeout=600;Timeout=600;Pool Time=10;Array Fetch Size=250;Array Bind Size=32767;RecommendedLenTransfert=32000;Password_Encryption=0;AliasTable=;MeasureDimension=;Hint=;ConnectInit=;ArrayFetch=1;
I couldn’t modify the second ArrayFetch and I don’t know why its value is “1”.
TraceLog: |Oracle OCI|Oracle 10|JobId:6052548|ENTER OCIParamGet
dvoid * 0x019abe5c
ub4 4
OCIError * 0x015e4e0c
dvoid ** 0x6eb78bfc
ub4 1
I understood that ub4 represents the value of the ArrayFetch and I couldn’t modify it.
Please HELP!!! 
gokou (BOB member since 2011-06-21)
Firstly, is Toad returning all of the rows?
Secondly, what order did you run the different tool tests in, Oracle could be caching the query, as the run times being so different would surprise me.
Mak 1 
(BOB member since 2005-01-06)
TOAD doesn’t give correct elapsed time.
Try using Sql Plus with the following options enabled.
set timing on;
set autotrace trace statistics;
after setting the above run your report query.
But still that doesnt proves that the query is performing badly, it also depends on other factors like load on the db server, etc.
jprasanthram 
(BOB member since 2010-12-10)
That may be correct, however, 4 seconds is not long compared to the other times and could almost be “guessed”. I suspect it is DB caching that is causing this disparity, although, the load on the BO and DB servers could also be playing a part.
Mak 1 (BOB member since 2005-01-06)
Make sure the Oracle database drivers on the SAP BusObjects server and the one on your local workstation (where Deski is running) are the same version and ptach level and configured the same.
Also, check how “far” the SAP BusObjects server is from the Oracle DBMS (network hops, bandwidth and latency).
Andreas 
(BOB member since 2002-06-20)
Toad does not return all the rows and the order is WEBI, Toad, DESKI.
I tried to run the query with SQLPLUS with enabling “set timing on” and “set autotrace trace statistics” on:
- BO Server: it takes the same time of WEBI (2 minutes)
- Oracle Database: it takes 6 seconds.
I verified also that The Oracle database drivers on the SAP BusObjects server and the one on my local workstation have the same versions.
Now I am in checking with the parameters of the Oracle DBMS (network, bandwidth and latency).
If you have more propositions, you are welcome.
Thank you.
gokou (BOB member since 2011-06-21)
You mentioned your fetch size, that’s set in the universe connection - and yes, I have found that to make a lot of difference…
I think I’ve always worked with values in the 500-1500 range for my array fetch, array bind more like 250 - that might be a good place to start. I also export my universe after making connection changes - I can’t guarantee that’s necessary, but I have had issues with passwords not updating until I did that.
Also, are you mixing query times and display times? Your query runs and then Webi has to render your report - you could test that by deleting everything from your report (make a copy and work from that) - how long does it take to open that empty report?
Good luck!
B
bdouglas (BOB member since 2002-08-29)
If you are not returning all the rows in Toad or SQL plus, whats the point of the comparison? You are comparing eggs and apples :? .
BO returns all the rows, so, you need to do the same in your SQL editor.
Mak 1 (BOB member since 2005-01-06)
I tried to play with the value of the ArrayFetch from 2 to 999 with restarting the BO servers ans rexporting the universe. But the result is the same.
My tests and my time results are done with an empty report 
Ok, Toad does not return all the rows, but SQL plus returns all the rows by putting the results in a text file.
gokou (BOB member since 2011-06-21)
As long as you are sure ;). Now, are you sure the database is not caching the results when testing?
Mak 1 (BOB member since 2005-01-06)
Yes, I deleted all the cache and retried to run the queries. I obtained the same times.
gokou (BOB member since 2011-06-21)