Insert into values generating a lot of logical I/O

From: Mohamed Houri <mohamed.houri_at_gmail.com>
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*

    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

Received on Wed Oct 30 2013 - 14:46:13 CET