OWB Performance Tips
September 20, 2004
Mark Rittman

There was an interesting thread on the OTN OWB Forum the other week, where a poster asked if anyone had any tips on improving the performance of Oracle Warehouse Builder. Nikolai Rochnik from the OWB product team came back with some useful guidance:

"There can be three factors affecting the performance of OWB Client working with Design Repository. Here is what you can do about them now as well as what OWB development has done (a lot!) to improve this in the next release (November 2004). They are in the order of greatest impact.

1. Design Repository management.

Problem: CHANGELOG table in the OWB design repository is typically several orders of magnitude smaller in row count than pctree, intralink, or interlink, so the nested loop plan is best. The optimizer's row estimate in the absence of gathered statistics does not serve us very well here.

Solution in the next release:

* Automated gathering of schema statistics (and refresh statistics on a regular basis)
* Increased use of SQL bind variables
* Improved SQL hints
* Ensured definition of all necessary indexes
* Used COMPRESS for indexes

Workaround for now: Try analyzing OWB design repository with the following statements. This is only worthwhile if the size of the repository is large.

exec dbms_stats.gather_table_stats(null, 'PCTREE', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

exec dbms_stats.gather_table_stats(null, 'INTERLINK', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

exec dbms_stats.gather_table_stats(null, 'INTRALINK', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

exec dbms_stats.gather_table_stats(null, 'CHANGELOG', null, DBMS_STATS.AUTO_SAMPLE_SIZE, false, 'FOR ALL COLUMNS SIZE AUTO');

exec dbms_stats.gather_index_stats(null, 'IDX_PCTREE_PARENTID', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'IDX_PCTREE_CHILDID', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'IDX_PCTREE_PARENTID_CHILDID', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'IDX_PCTREE_CHILDID_PARENTID', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'IDX_INTERLINK_LINKFROM', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'IDX_INTERLINK_LINKTO', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'IDX_INTRALINK_LINKFROM', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_index_stats(null, 'CHANGELOG_PK', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

2. Memory management.

Solution in the next release: A lot of improvements to prevent memory leaks and unnecessary flushing of needed objects.

Workaround for now: Try a higher Java Virtual Machine (JVM) limit for you OWB Client: Edit -Xmx value in [OWB Home]\owb\bin\win32\owbclient.bat from default 384M. This is only worthwhile if the client machine has enough real memory to support the VM sizes of all actively running applications and if Windows Task Manager shows the owbclient java session Memory Usage and VM Size values close to the Xmx value, then OWB starts spending a larger portion of its effort managing memory.

3. Network latency.

Problem: Network latency is why having OWB Client installed local to the Oracle database containing the design repository will always be faster, as you observe "I found OWB much faster when Oracle and OWB running on same machine having 400 MB only." Obviously this does not solve the problem in multiple developer environment, but it is not as big of the factors above. We are confident that OWB Client 'chatting' over the network is already optimized."

Adding to this, it's been my experience that the two issues that can have the biggest impact on OWB performance are first, a client machine with less than 512MB of memory, and secondly, contention on the disk unit that houses the OWB design repository. There really is no point using the OWB client on a machine with 256MB or less of memory (it'll run extremely slow, and often crash or hang and lose your uncommitted work), and more than once we've found that sluggish OWB performance was down to the disks holding our design repository database also being used, without us knowing, to hold the database for a large and active OLTP application.