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

From: Mark W. Farnham <mwf_at_rsiz.com>
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*
    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

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


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 30 2013 - 20:34:39 CET

Original text of this message