RE: Insert into values generating a lot of logical I/O
- From: "Iotzov, Iordan" <IIotzov@xxxxxxxxxxxxxxx>
- To: "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Oct 2013 10:20:13 -0400
Hi,
I understand that the statement was executed 9,556 times (sourced from
executions_delta in dba_hist_sqlstat, I assume).
Can you see how many records were actually inserted?
If there is a significant difference, you should check how BULK inserts are
counted by AWR.
HTH,
Iordan Iotzov
Check out SmartSource Xpress, our new iPad app!
Follow us on Twitter | Like us on Facebook
-----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
This message and its attachments may contain legally privileged or confidential
information. It is intended solely for the named addressee. If you are not the
addressee indicated in this message (or responsible for delivery of the message
to the addressee), you may not copy or deliver this message or its attachments
to anyone. Rather, you should permanently delete this message and its
attachments and kindly notify the sender by reply e-mail. Any content of this
message and its attachments that does not relate to the official business of
News America Incorporated or its subsidiaries must be taken not to have been
sent or endorsed by any of them. No warranty is made that the e-mail or
attachment(s) are free from computer virus or other defect.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
