Insert into values generating a lot of logical I/O
Date: Wed, 30 Oct 2013 14:46:13 +0100
Message-ID: <CAJu8R6hMEXbAsiNUxU4Y_SOm3ZTfLaTZPUwJcAjh6=3m5KTgvA_at_mail.gmail.com>
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-lReceived on Wed Oct 30 2013 - 14:46:13 CET