Hi All,
We are facing problems in lots of insert statement in oracle. Actually the scenario is like that :
1. The application is DW application.
2. We are using Informatica 9 as ETL tool.
3. The Oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
4. There are lot of tables in which we are facing the slow performance of insert statement.
5. We are loading the data by Informatica into Oracle.
6. In Oracle we can see the simple select statement like :
INSERT INTO <<table>>(C1,C2...etc) values (:1,:2, etc);
7. We got the below plan statistics from DBA: This is for one table only. We have approx same plan statistics for all tables.
| Stat Name | Statement Total | Per Execution | % Snap Total |
|---|
| Elapsed Time (ms) | 733,529 | 56.69 | 10.68 |
| CPU Time (ms) | 120,199 | 9.29 | 9.41 |
| Executions | 12,940 | | |
| Buffer Gets | 6,492,400 | 501.73 | 15.38 |
| Disk Reads | 158,734 | 12.27 | 7.17 |
| Parse Calls | 0 | 0.00 | 0.00 |
| Rows | 362,320 | 28.00 | |
| User I/O Wait Time (ms) | 599,986 | | |
| Cluster Wait Time (ms) | 27,783 | | |
| Application Wait Time (ms) | 0 | | |
| Concurrency Wait Time (ms) | 28 | | |
| Invalidations | 0 | | |
| Version Count | 1 | | |
| Sharable Mem(KB) | 56 |
8. All tables are huge tables.
I have verified few things before :
1. There is no CLOB/BLOB columns in tables.
2. We can not disable the indexes on the tables as we don't have permission to do this.
3. Tables are not partition.
Now I have few questions :
1. Can we do the insert statement tuning in Oracle or we need to target in Informatica tuning only?
2. As we can see the huge buffer gets and USER I/O wait time in Plan stats so Can we do something to improve the things in Oracle side?
I am looking for your guidance to come out for this kind of scenario..
Thanks in advance for your help and suggestion...