Insert or Update or Delete A Large Number of Data Rows Running Slow With Excessive 'db file sequential read' Waiting (Doc ID 2741163.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

• Insert or Update or Delete a large number of data rows was running very slow.
  
  
• The tkprof formated 10046 event trace shows that only a little time spent on TABLE ACCESS FULL <TABLENAME> and most time spent on DML operations and waiting count and waiting time of 'db file sequential read' are significantly high.
  
  
  DELETE FROM <TABLENAME>
  WHERE
  <COLUMN_NAME1> = 4 AND
  <COLUMN_NAME2> = '606' AND
  SUBSTR(<COLUMN_NAME3>,1,6) = '202011'
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.03       0.05          1          1          1           0
  Execute      1     46.54    1854.79     406728     176647   13348346      592102
  Fetch        0      0.00       0.00          0          0          0           0
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        2     46.57    1854.84     406729     176648   13348347      592102
  
  Misses in library cache during parse: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 222
  Number of plan statistics captured: 1
  
  Rows (1st) Rows (avg) Rows (max)  Row Source Operation
  ---------- ---------- ----------  ---------------------------------------------------
           0          0          0  DELETE  <TABLENAME> (cr=176647 pr=406728 pw=0 time=1854790092 us)  *<<--More than 1800 seconds on DELETE operation
      592102     592102     592102   TABLE ACCESS FULL <TABLENAME> (cr=176524 pr=176271 pw=0 time=22160321 us cost=48796 size=46004280 card=657204)  *<<--Only 22 seconds
  
  
  Elapsed times include waiting on following events:
    Event waited on                             Times   Max. Wait  Total Waited
    ----------------------------------------   Waited  ----------  ------------
    Disk file operations I/O                        2        0.00          0.00
    db file sequential read                    230462        0.30       1792.61 *<<--High Waited Times, low average waiting time.
    db file scattered read                       1615        0.27         18.78
• The raw information of 10046 event trace shows most of 'db file sequential read' was used to access the related indexes of <TABLENAME> according to the obj#.
  
  
  WAIT #380492080: nam='db file sequential read' ela= 632 file#=179 block#=181922 blocks=1 obj#=217032 tim=29193073324384
  WAIT #380492080: nam='db file sequential read' ela= 5905 file#=181 block#=813644 blocks=1 obj#=217032 tim=29193073330342
  WAIT #380492080: nam='db file sequential read' ela= 567 file#=179 block#=249359 blocks=1 obj#=217032 tim=29193073330928
  WAIT #380492080: nam='db file sequential read' ela= 2699 file#=181 block#=1486242 blocks=1 obj#=217033 tim=29193073333657
  WAIT #380492080: nam='db file sequential read' ela= 3857 file#=179 block#=260406 blocks=1 obj#=217033 tim=29193073337562
  WAIT #380492080: nam='db file sequential read' ela= 807 file#=179 block#=209570 blocks=1 obj#=217034 tim=29193073338439
  WAIT #380492080: nam='db file sequential read' ela= 631 file#=179 block#=284203 blocks=1 obj#=217034 tim=29193073339115
  WAIT #380492080: nam='db file sequential read' ela= 657 file#=179 block#=284322 blocks=1 obj#=217250 tim=29193073339841
  WAIT #380492080: nam='db file sequential read' ela= 687 file#=179 block#=294976 blocks=1 obj#=217250 tim=29193073340573
  WAIT #380492080: nam='db file sequential read' ela= 492 file#=179 block#=173730 blocks=1 obj#=217251 tim=29193073341130
  WAIT #380492080: nam='db file sequential read' ela= 10668 file#=181 block#=1578947 blocks=1 obj#=217251 tim=29193073351842
  WAIT #380492080: nam='db file sequential read' ela= 670 file#=181 block#=107079 blocks=1 obj#=217251 tim=29193073352557
  WAIT #380492080: nam='db file sequential read' ela= 551 file#=179 block#=162466 blocks=1 obj#=217252 tim=29193073353136
  WAIT #380492080: nam='db file sequential read' ela= 521 file#=181 block#=1460788 blocks=1 obj#=217252 tim=29193073353679
  WAIT #380492080: nam='db file sequential read' ela= 549 file#=179 block#=234552 blocks=1 obj#=217252 tim=29193073354273
  ... ...

Cause

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