Introducing Optimize Repository

    Have you ever had the experience that Oracle Warehouse Builder (OWB) Client becoming extremely slow after you create a bulk of objects or import a huge project into the repository? If the answer happens to be yes, maybe next time you can try one of the tools OWB Design Client comes with, Optimize Repository.

What is the Optimize Repository?

  OWB uses the Oracle database for its metadata repository. While OWB is running, it frequently accesses its metadata repository for querying or managing metadata. So OWB will perform better if database access and operations are fast and efficient. Optimize Repository is a tool in OWB that improves database related operation on the metadata repository.

    Oracle utilizes execution plans when performing Database operation. Basically, Optimize Repository uses the Oracle package DBMS_STATS to gather OWB related objects’ statistics, then provide the statistics gathered by DBMS_STATS to the database query optimizer to help it form the best execution plan for each SQL statement.

How can I launch Optimize Repository?

    You can launch it manually via UI by using menu Tools -> Optimize Repository.

clip_image002[18]

Or, you can use OMB Command to launch it.

OMB+>OMBOPTIMIZE

    OWB also provides a mechanism to do the Optimize Repository automatically. To enable running the Optimize Repository at start up of OWB Design Client, go to the Preferences Window by clicking menu Tools and then select Preferences, selecting OWB->Environment node on the preferences tree, and then choose the option “Allow Optimize Repository Warning on Startup”.

clip_image004

    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 the following dialog and ask you whether to do the optimization.

clip_image006

A Simple Example:

Here, I will give a demo about how to use Optimize Repository.

First, create a new repository, and then import a large project. Here I use a project that contains 47507 objects; you can find any other large project instead.

I provide a script (demo.tcl) here. What the script does is Copy an Oracle Module to the same project and count the time operation takes to complete the task.

set oracle_module_path “STUDENT_V1”

set oracle_module_name “CURR”

OMBCONN owb_owner/owb_owner@localhost:1521:orcl

OMBCC '$ oracle_module_path '

set start_time [clock seconds]

puts "Copy Start at [clock format $start_time -format {%H:%M:%S}]"

OMBCOPY ORACLE_MODULE '$oracle_module_name ' TO 'COPY_OF_$ oracle_module_name'

set end_time [clock seconds]

puts "Copy Finish at [clock format $end_time -format {%H:%M:%S}]"

set delta [expr $end_time-$start_time]

set min [expr int($delta/60)]

set sec [expr $delta % 60]

puts "Elapsed time: MM:SS - $min:$sec"

OMBREVERT

OMBDISC

Change oracle_module_path and oracle_module_name according to your project, then go to <ORACLE_HOME>/owb/bin/unix, run the script.

./OMBPlus.sh /tmp/demo.tcl

I try to copy an Oracle module named CURR that contains 40 tables. Below is the result I get.

Copy Start at 23:58:36

Copy Finish at 00:03:23

Elapsed time: MM:SS – 4:47

Apparently, it’s very slow! It takes me nearly 5 minutes to complete copy. All right, this time, let’s do Optimize Repository instead to see whether the operation processing time will be shorter. After optimizing the repository, rerun the script.

Copy Start at 00:17:28

Copy Finish at 00:18:26

Elapsed time: MM:SS – 0:58

See the difference? What a miracle! It only takes me 58 seconds!

Some Notes:

1. Running the Optimize Repository for the first time or for the first time since a long while may take a long time (hours/days!!). There’s definitely no need to be worried, just be patient, leave it out there and it will get things done.

2. If the database has enabled automatic optimizer statistics collection that runs as part of the automated maintenance tasks infrastructure (Auto Task) OWB will rely on it to gather statistics, Optimize Repository will do nothing. You can use following SQL statement to check whether the Automatic optimizer is enabled.

Select enabled from dba_scheduler_jobs where job_name= 'GATHER_STATS_JOB'

3. Time should be another consideration when you make decision whether to do optimize repository. Even optimize repository help a lot in improving OWB performance, it is not recommended to do it quite often. Because normally it will cost you hours to run Optimize Repository. Here give you some recommendations. For large repositories, which contain more than 50000 objects, optimizer only needed when 10% objects have been changed. For medium repositories, which contain more than 10000 objects and less than 50000 objects, optimizer only needed when 20% objects have been changes. For small repositories, which less than 10000 objects, optimizer only needed when 40% objects have been changed.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today