My Oracle Support Banner

High physical reads and high waits on single block I/O when inserting data (Doc ID 2546382.1)

Last updated on APRIL 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

High physical reads and high waits on single block I/O ('db file sequential read', 'cell single block physical read') when inserting data.


*** Good on a database

INSERT INTO ... SELECT ... FROM ....;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.10       0.24        137       1146          3           0
Execute      1    359.86     376.04     275396    6257444   50172951    26024877
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    359.97     376.28     275533    6258590   50172954    26024877

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        0          0          0  LOAD TABLE CONVENTIONAL  PS_KSEC_FLAT_RULES_LGUO (cr=6257667 pr=275491 pw=271042 time=376074380 us)       <<#####  pr=275491
.....................


*** Bad on another database

INSERT INTO ... SELECT ... FROM ....;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01         79       1121          0           0
Execute      1    639.89    1236.76    2376958   12911855   51983811    25796197
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2    639.91    1236.78    2377037   12912976   51983811    25796197


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
        0          0          0  LOAD TABLE CONVENTIONAL  PS_KSEC_FLAT_RULES_LGUO (cr=12911913 pr=2376969 pw=268618 time=1 us)             <<##### pr=2376969  (9 times)
......................
        
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  cell single block physical read           2108303        1.07        680.77       <<<############

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.