Insert into values generating a lot of logical I/O
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Oct 2013 14:46:13 +0100
Dears,
I have the following *Top 5 Timed Foreground Events *for 2 hours (I know
its 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
Other related posts:
