Insert performance is very bad

[PAVB]

... I don't have 10046 trace file.

This is a job for oradebug -one of the many Oracle unsong super heroes.

Please try this...

0- Initiate offending process.

1- Figure out the SPID of the offending process.

2- Log into the system as sysdba

3- Start oradebug...
oradebug setospid SPID-from_step_1;

4- Enable 10046...
oradebug event 10046 trace name context forever, level 12

5- Wait... wait... keep waiting... then wait a little more

6- Disable 10046 tracing...
oradebug event 10046 trace name context off

7- tkprof trace file and bingo! you got your 10046 trace on remote session.

[vnktummala]

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?

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,

[vnktummala]

As far as I can see wait events on db_file_sequential_read account for about 20% of the total elapsed time therefore even if you manage to get rid of 100% of this particular wait event - which is not possible - you are looking at a performance gain of about 20%

What are the chances of rewritting this Informatica mapping as a PL/SQL block? - just for testing purposes

I would write the code without using explicit cursors a.k.a. set-based and, I'll eventually use APPEND hint during insert. On the first try I'll commit only once at the end of the job.

So is Oracle waiting another approximate 20% for Informatica to push some work??

Thanks,