Thanks Paul.

Unfortunately, client has not given the complete DBA privileges on this DB So I can not use oradebug. Even for the trace file I am generating, I need to ask client employee to send me the file, then I am doing tkprof in my laptop.

well, we can not customize DML queries in Informatica. We can customize the queries reading from source but not the queries writing. At the most I can do is take the risk of putting table in nologging.

I am very much pity on myself.

Paul, can you help me to understand the v$segment_statistics better?

Code:
select OBJECT_NAME,SUBOBJECT_NAME,object_type,statistic_name,statistic#,value from v$segment_statistics where obj# in (
select object_id from dba_objects where object_name='A_IDX') order by object_type,value desc;
OBJECT_NAME                    SUBOBJECT_ OBJECT_TYPE        STATISTIC_NAME                 STATISTIC#          VALUE
------------------------------ ---------- ------------------ ------------------------------ ---------- --------------
A_IDX             INDEX              space allocated                        15     3757047808
A_IDX             INDEX              space used                             14     2555428919
A_IDX             INDEX              logical reads                           0      352393280
A_IDX             INDEX              db block changes                        3      119409200
High value means DB is spending that much time to allocate space?

Also, I would like to update that the 12 hours INSERT job for which I started this thread is completed in two and half hours today after dropping all the Indexes associated. But still I am not happy because it has just inserted 600K rows. I am working with ETL guys to find the ETL side waits.


Thanks,