Tell us a bit about your indexes, especially what is being used to support the primary key and any other referential constraints. How about the types and value lengths of the column values being inserted? What versions, are you using partitioning (and describe if yes), and is this single instance or RAC (and if RAC, what is the number of instances?) mwf -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mohamed Houri Sent: Wednesday, October 30, 2013 9:46 AM To: ORACLE-L Subject: Insert into values generating a lot of logical I/O Dears, I have the following *Top 5 Timed Foreground Events *for 2 hours (I know it's too long but the job was still running after these 2 hours snapshots): *Event* *Waits* *Time(s)* *Avg wait (ms)* *% DB time* *Wait Class* db file sequential read 1,644,924 9,180 6 14.85 User I/O DB CPU 7,791 12.61 log file sync 106,400 1,947 18 3.15 Commit SQL*Net message from dblink 160,062 474 3 0.77 Network log file switch completion 1,457 409 280 0.66 Configuration When I drill down to the most correlated part in the AWR (*SQL ordered by Gets*) I found kind of following sql at the top: INSERT INTO Table_IN ( *COL1_ID , -- PK_1* * COL2_ID, -- PK_2* COL3 , COL4 , COL5 , COL6 , ) VALUES ( :B11 , :B1 , :B2 , :B3 , :B4 , :B5 , ); Which has been executed 9,556 times generating more than *50 millions* of Buffers Gets. The *segments by Logical Reads* part of the AWR shows, at its TOP, the primary key of the above table with *41 millions* of logical Reads. There is no top wait event related to Buffer Busy waits, however *Segments by ITL Waits* and *Segments by Buffer Busy Waits* both show at their top the above table Table_IN and its primary key. The insert process could be done via 5 to 10 concurrent processes. Each process has its own COL1_ID but two processes can share the same set of COL2_ID. *Questions* : How a simple insert into values could generate such a high number of logical I/O. Do you think that changing the PK to spread the insert will help in this situation? -- Bien Respectueusement Mohamed Houri www.hourim.worpdress.com -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l