RE: Insert into values generating a lot of logical I/O

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_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 insert will help in this situation?

--
Bien Respectueusement
Mohamed Houri
www.hourim.worpdress.com

--
http://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.

--
http://www.freelists.org/webpage/oracle-l