Forum Stats

  • 3,854,437 Users
  • 2,264,368 Discussions
  • 7,905,688 Comments

Discussions

LKM Oracle to Oracle performance issue

Joao Rodrigues
Joao Rodrigues Member Posts: 7
edited Feb 22, 2018 9:14AM in Data Integrator

Hello everyone!

I inherited a complex BI loading that involves a also complex ODI environment. As this environment had different databases as sources in different places of the world, the load, as you can see below, uses external tables (file datapump), ftp transfer and then imports the file to the target database.

imagem1.png

Since now all our databases are in the same place i would like to simplify our load to the model you can see below, using just a work table on the target.

pastedImage_1.png

Its working fine but the performance is, to my surprise, worse then the first one. The step "Load Data", responsible for selecting the data from the soruce and inserting in work table on the target, takes a long time to process. The code on source is a simple select and in target is a simple insert as you can see below.

pastedImage_2.png

pastedImage_3.png

What i read untill now is that this problem can be due to a row-by-row access done by ODI, wich would really be a problem since, on my first test i chose a table with over 700k registries (its not a big one).

Would anyone be able to help me figure out what to do? I thought about creating a dblink between the databases but i don't know if would solve the problem nor how to configure the LKM and IKM for this case.

Tagged:
Joao Rodrigues

Answers

  • Adrian_Popescu-Oracle
    Adrian_Popescu-Oracle RomaniaPosts: 1,984 Employee
    edited Feb 8, 2018 4:00PM

    Hi,

    Since source and target are both Oracle DB, then DB Link will provide the best performance.

    Also, on the source table do you use indexes on the fields used in the WHERE clause ?

    Have a look at LKM Oracle to Oracle (DBLINK).

    By default LKM creates and drops the DB link. But I know there is an option allowing to use a DB Link already created in the database.

    Currently in your LKM since you have command on source/target, the binding mechanism takes so long.

    Adrian

    Joao Rodrigues
  • Joao Rodrigues
    Joao Rodrigues Member Posts: 7
    edited Feb 22, 2018 9:14AM

    Hello @Adrian_Popescu-Oracle,

    Thanks for your answer. Here are my answers and comments.

    My example table has around 700k registries. There's no where clause since its a full load, all registries are loaded every day.

    I created a permanent dblink and made some tests (ran 5 times each scenario) and here are the results:

    1) Load selecting from source and then inserting in target (LKM and IKM): around 200 seconds;

    2) Load insert-selecting, running from target using dblink (IKM): around 50 seconds;

    3) Load exporting EXP file from source, transfering through ftp and then inserting from EXP file (LKM and IKM): 15 seconds;

    This is what a can't understand. Scenario 12, in my head, couldn't be slower than scenario 3.

    The original solution is Scenario 3 but, since my databases use different OSs, i'm having trouble on configuring the communication between them.

This discussion has been closed.