updation of table taking more time through informatica

Posted on February 16, 2016 by viswamitra 4 comments

Today I want share a performance issue I faced recently….

I got a call from a application team guy saying he is updating a table in DESTINATION database using informatica based on the existence of rows in SOURCE. I asked him to explain in detail what he is doing. He explained as below.

We have two databases “SOURCE” and “DESTINATION” , there is no direct connection between the two databases, so using informatica to update each row in destination table if it exists in the source database. We have two tables in those databases “SOURCE_TABLE” with some 40,000 rows , “DESTINATION_TABLE” with 10 lac rows, these two tables are same(in structure and indexes). We want to update DESTINATION_TABLE by using SOURCE_TABLE based on a key.

1) First informatica select data from SOURCE database using primary key.

2) Update the table in the DESTINATION database using the data selected from source.

This process took more than a day and finally he canceled the processes.

The main reason for this is , when it try to perform the update of destination table , it is not using the index.

REASON :-

The update operation is performing on varchar columns , when we try to update using informatica it will try to use the nvarchar2 data type even though the source type it selected is varchar2.

I do not have informatica here , I am demonstrating same using a simple example.

SQL> create table source_table as select * from dba_objects;

Table created.

SQL> create table destination_table as select * from dba_objects;

Table created.

SQL> alter table source_table modify object_name nvarchar2(100);

Table altered. ===> For simulating the behavior of informatica I am changing the column data type to nvarchar2

SQL> create index source_idx on source(OBJECT_NAME);

Index created.

SQL> create index destination_idx on source(OBJECT_NAME);

Index created.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’source_table’);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(‘SCOTT’,’destination_table’);

PL/SQL procedure successfully completed.

Now I am updating the destination table using the source table.

SQL> explain plan for select object_name from destination where OBJECT_NAME=(select object_name from source where OBJECT_NAME=’ICOL$’);

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 304743110

———————————————————————————
| Id | Operation         | Name        | Rows | Bytes | Cost (%CPU)| Time     |
———————————————————————————
|   0 | SELECT STATEMENT |             |   726 | 18150 |   190   (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| DESTINATION |   726 | 18150 |   187   (1)| 00:00:03 |
|* 2 |   INDEX RANGE SCAN| SOURCE_IDX |     1 |    48 |     3   (0)| 00:00:01 |
———————————————————————————

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
—————————————————————————————————-
—————————————————

   1 – filter(SYS_OP_C2C(“OBJECT_NAME”)= (SELECT “OBJECT_NAME” FROM
              “SOURCE” “SOURCE” WHERE “OBJECT_NAME”=U’ICOL$’))
   2 – access(“OBJECT_NAME”=U’ICOL$’)

16 rows selected.

It is using the source_idx for selecting from source table and it is not able to use the destination_idx because “SYS_OP_C2C” function has been applied on the object_name column.

NOTE : If any function applied on the indexed column , the query will not use the index.

To overcome this

1) Change the settings in informatica.

2) Create functional index in oracle.

SQL> create index c2c_fun on DESTINATION_TABLE(SYS_OP_C2C(“OBJECT_NAME”));

Index created.

SQL> explain plan for select object_name from destination_table where OBJECT_NAME=(select object_name from source_table where OBJECT_NAME=’ICOL$’);

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
—————————————————————————————————-
Plan hash value: 2074950781

————————————————————————————————-
| Id | Operation                   | Name              | Rows | Bytes | Cost (%CPU)| Time     |
————————————————————————————————-
|   0 | SELECT STATEMENT            |                   |   726 | 70422 |   154   (0)| 00:00:02 |
|   1 | TABLE ACCESS BY INDEX ROWID| DESTINATION_TABLE |   726 | 70422 |   151   (0)| 00:00:02 |
|* 2 |   INDEX RANGE SCAN          | C2C_FUN           |   291 |       |     3   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN         | SOURCE_IDX        |     1 |    48 |     3   (0)| 00:00:01 |
————————————————————————————————-

PLAN_TABLE_OUTPUT
—————————————————————————————————-
Predicate Information (identified by operation id):
—————————————————

   2 – access(SYS_OP_C2C(“OBJECT_NAME”)= (SELECT “OBJECT_NAME” FROM “SOURCE_TABLE”
              “SOURCE_TABLE” WHERE “OBJECT_NAME”=U’ICOL$’))
   3 – access(“OBJECT_NAME”=U’ICOL$’)

17 rows selected.

References :-

http://prasannagee.blogspot.co.uk/2011/04/nvarchar2-varchar2.html

https://jonathanlewis.wordpress.com/2007/07/29/nls/

http://joze-senegacnik.blogspot.co.uk/2009/12/what-is-purpose-of-sysopc2c-internal.html

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=438198087445286&parent=DOCUMENT&sourceId=732666.1&id=276914.1&_afrWindowMode=0&_adf.ctrl-state=nzi7t1mey_148