RE: Insert into values generating a lot of logical I/O
Date: Wed, 30 Oct 2013 15:34:39 -0400
Message-ID: <0ac801ced5a7$138633e0$3a929ba0$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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*
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 insertwill help in this situation?
-- Bien Respectueusement Mohamed Houri www.hourim.worpdress.com -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 30 2013 - 20:34:39 CET