DML 时 db file sequential read 长

• 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

?Index maintenance will be operated automatically during DML operations, and it will take a lot of time when a large number of target rows involved.

SOLUTION

?Please disable constraints and alter indexes unusable when execute DML statements involving a large number of target rows, and rebuild index and enable constraints again after DML execution.