The Performance-button on Oracle Warehouse Builder Design Center

Hereby a somewhat embarrasing story about a performance problem with an Oracle Warehouse Builder – database (11.2.0.3). Embarrasing, while it took too much time to figure out what was going on.

The case: unexpectedly, within a week notice, the performance of an Oracle Warehoude Builder environment decreased drastically: logging in to the user OWBSYS increased from a few seconds to minutes. The nightly schedule took hours more than usual. The administration of the database has been outsourced, so no local DBA around for immediate support, the customer had to submit a service request.

In the meantime the usual investigation started, with the main question first: what has been changed lately in storage, o.s.-level, anti-virus software, database and so on.

Logging in at other database accounts gave no performance issues. Apperently the problem is limited to user OWBSYS. Maybe something had changed in the numerous triggers of OWBSYS?
Indeed, there are a lot of  ‘LOGON’ triggers (names of the triggers have been made invisible, as this is data from customer):

But that could not explain the nightly jobs which took more time, could it? Then, the note 1330370.1, “How To Optimize The OWBSYS Schema in OWB 11g” helped. The performance issue could be caused by bad statistics in the database (should not be a very big surprise for a DBA….).

Oracle Warehouse Builder can run statistics for you in stead of the remote DBA. In the Oracle Warehouse Builder Design Center there’s this ‘button’, which will run DBMS_STATS within the database. It is called Optimize Repository:

This should be done regularly, and as apparently there is no scheduled task at the DBaaS for gathering statistics, this can also be done from the Design Center: –> To enable running the Optimize Repository at start up of OWB Design Client, go to the Preferences Window by clicking menu Tools –> Preferences, selecting OWB->Environment node on the preferences tree, and then choose the option “Allow Optimize Repository Warning on Startup”:

–> If this option is checked, every time OWB Client starts up, it will check whether the Repository needs to be optimized. If OWB Client thinks the repository is stale, it will pop up a dialog window and ask you whether to do the optimization.

These actions must be synchronized with the remote DBA of course. To go back to our problem: the customer pushed this ‘button’, implemented the ‘scheduled task’, performance was back to normal, customer happy. Sometimes things are not that hard as it looks .

Sources: Note 1330370.1, How to optimize the OWBSYS schema in OWB 11g.