Shrink OWB runtime

We were experiencing some slowness (or terrible slowness, depends on when you asked the question) with retrieving data from the Repository Audit browser. It is a development environment so we were not overly interested in what we had been goofing about with mappings and process flows, deployment and execution. When enough waiting was enough, I looked for something that could help. And I found A VERY DANGEROUS solution here. I cannot stress enough that this is NOT something you will do in a production environment. At least not until you have tested it completely and made sure you have all the backups you have to make and then some extra backups of the things that cannot possibly go wrong.

But it proved useful for us, so why can’t it be useful for you?

I copied the necessary code below. It must be run as the design repository owner and the runtime service must be stopped. I presume that if you are prepared to try this, you know how to stop the runtime service. If not, please don’t use this script and do some reading in the manual first or look in google for the term “service_doctor.sql”.

REM @truncate_audit_execution_tables.sql
REM to truncate wb_rt_audit_executions and dependent audit tables
REM in the runtime repository
set echo off
set verify off
rem 'truncate_audit_execution_tables : begin'
alter table wb_rt_feedback disable constraint fk_rtfb_rta;
truncate table wb_rt_feedback;
rem 'wb_rt_feedback truncated'
alter table wb_rt_error_sources disable constraint fk_rts_rta;
truncate table wb_rt_error_sources;
rem 'wb_rt_error_sources truncated'
alter table wb_rt_error_rows disable constraint fk_rtr_rte;
truncate table wb_rt_error_rows;
rem 'wb_rt_error_rows truncated'
alter table wb_rt_errors disable constraint fk_rter_rta;
alter table wb_rt_errors disable constraint fk_rter_rtm;
truncate table wb_rt_errors;
rem 'wb_rt_errors truncated'
alter table wb_rt_audit_struct disable constraint fk_rtt_rtd;
truncate table wb_rt_audit_struct;
rem 'wb_rt_audit_struct truncated'
alter table wb_rt_audit_detail disable constraint fk_rtd_rta;
truncate table wb_rt_audit_detail;
rem 'wb_rt_audit_detail truncated'
alter table wb_rt_audit_amounts disable constraint fk_rtam_rta;
truncate table wb_rt_audit_amounts;
rem 'wb_rt_audit_amounts truncated'
alter table wb_rt_operator disable constraint fk_rto_rta;
truncate table wb_rt_operator;
rem 'wb_rt_operator truncated'
alter table wb_rt_audit disable constraint fk_rta_rte;
truncate table wb_rt_audit;
rem 'wb_rt_audit truncated'
alter table wb_rt_audit_parameters disable constraint ap_fk_ae;
truncate table wb_rt_audit_parameters;
rem 'wb_rt_audit_parameters truncated'
alter table wb_rt_audit_messages disable constraint am_fk_ae;
delete from wb_rt_audit_messages where audit_execution_id is not null;
rem 'wb_rt_audit_messages deleted'
rem 'wb_rt_audit_message_lines cascade deleted'
rem 'wb_rt_audit_message_parameters cascade deleted'
alter table wb_rt_audit_files disable constraint af_fk_ae;
delete from wb_rt_audit_files where audit_execution_id is not null;
rem 'wb_rt_audit_files deleted'
truncate table wb_rt_audit_executions;
rem 'wb_rt_audit_executions truncated'
delete from wb_rt_notify_queue_tab where enq_time < sysdate -7;
rem 'wb_rt_notify_queue_tab deleted older than 7 days'
create table save_wb_rt_not_queue_tab as
select * from wb_rt_notify_queue_tab;
truncate table wb_rt_notify_queue_tab;
insert into wb_rt_notify_queue_tab
select * from save_wb_rt_not_queue_tab;
drop table save_wb_rt_not_queue_tab;
rem 'Table wb_rt_notify_queue_tab saved, truncated and re-inserted'
alter table wb_rt_feedback enable constraint fk_rtfb_rta;
alter table wb_rt_error_sources enable constraint fk_rts_rta;
alter table wb_rt_error_rows enable constraint fk_rtr_rte;
alter table wb_rt_errors enable constraint fk_rter_rta;
alter table wb_rt_errors enable constraint fk_rter_rtm;
alter table wb_rt_audit_struct enable constraint fk_rtt_rtd;
alter table wb_rt_audit_detail enable constraint fk_rtd_rta;
alter table wb_rt_audit_amounts enable constraint fk_rtam_rta;
alter table wb_rt_operator enable constraint fk_rto_rta;
alter table wb_rt_audit enable constraint fk_rta_rte;
alter table wb_rt_audit_parameters enable constraint ap_fk_ae;
alter table wb_rt_audit_messages enable constraint am_fk_ae;
alter table wb_rt_audit_files enable constraint af_fk_ae;
rem 'truncate_audit_execution_tables : end'

4 responses

3 01 2011
2010 in review (the easy way…) « Klein Oracle denkraam

[…] Shrink OWB runtime […]

2 05 2011

When I wrote this, the function you mentioned existed only in its barest form and was not documented very well (if you could find it at all). Hence my hacking around in the repository. There is another advantage to using the ‘hacking’ method: It will reset the high-water mark for all truncated tables and that can potentially speed up the performance of queries on the repository. But even that can be achieved using the tools in the newer versions in OWB by using the ‘Optimize repository’ from the Design Center.
I am glad OWB keeps improving. It was not the first option I tried, using this procedure, but sometimes you have to resort to unconventional methods. At the moment we are indeed using the wb_rt_audit_purge procedure regularly and only use the method described here when working in old development environments.

16 01 2013
Robbert Michel (@RobbertMichel)

Hello Buddel,

Although the option has been there for a long time, I admit that in older version’s documentations it wasn’t easy to find. In fact, I only know about it beacuse I stumbled across it, when manually going through all the views and procedures in the repository.

Still I thought it would be worth mentioning, as this page still ranks quite high on searched for shrinking the OWB repository Audit.

Thanks for the reply. 🙂


2 05 2011

Or you use the inbuild function for this:
WB_RT_AUDIT_PURGE.purge_execution(date ”1900-01-01”,sysdate -7);

This will purge all the runtime data up to 7 days ago. Change the dates for different results.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: