High physical reads and high waits on single block I/O when inserting data
(Doc ID 2546382.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 11.1.0.6 and laterInformation 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 <<<############
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 |