delete的統計資料檢視和比較

2、測試資料，在進行刪除delete的時候有索引和沒有索引的物件的統計資訊，會話的統計資訊，會話的等待事件

delete test_a
刪除耗費491秒
select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
   where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like 'physical%' or b.name like '%read%' or B.NAME like   '%undo%' or b.name like '%redo%') and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       318 session logical reads                                               4202712 ---實際行數400W
       318 physical read total IO requests                                        1142
       318 physical read total multi block requests                               1117
       318 physical read total bytes                                         145031168
       318 physical reads                                                        17704 --表塊的數量
       318 physical reads cache                                                  17704
       318 physical read IO requests                                              1142
       318 physical read bytes                                               145031168 ---delete這裡會物理讀取實際的大小140M，而後面的等待事件說明在這裡小號了43秒
       318 redo synch writes                                                         1
       318 physical reads cache prefetch                                         16562
       318 redo entries                                                        4017828
       318 redo size                                                        1141494072 --任然的REDO生成了1G，和INSERT差不多
       318 redo buffer allocation retries                                           49
       318 redo log space requests                                                  49
       318 redo log space wait time                                                296
       318 redo ordering marks                                                   73945
       318 redo subscn max counts                                                74118
       318 undo change vector size                                           589435236 --undo生成了580M，比INSERT更多
       318 no work - consistent read gets                                        17906
       318 IMU undo allocation size                                              63992

  select *
          from v$session_event a
        where a.SID = 318
      order by TIME_WAITED desc;
 
       SID EVENT                             TIME_WAITED_MICRO
---------- ---------------------------------------------------
       318 SQL*Net message from client               206690838
       318 db file scattered read                     43843361 --43秒
       318 log file switch completion                  2952901
       318 db file sequential read                      188312
       318 log buffer space                              28325
       318 SQL*Net message to client                        89
 
 
SQL> select * from v$sess_time_model where sid=318 order by  value desc;
 
       SID    STAT_ID STAT_NAME                                                             VALUE
---------- ---------- ---------------------------------------------------------------- ----------
       318 3649082374 DB time                                                           491842941
       318 2821698184 sql execute elapsed time                                          491750449
       318 2748282437 DB CPU                                                            436421084 --等待比INSERT相比增加了
       318 1431595225 parse time elapsed                                                   405367
       318  372226525 hard parse elapsed time                                              396096
       318 1990024365 connection management call elapsed time                               28152
       318 1311180441 PL/SQL compilation elapsed time                                        3400
       318 2643905994 PL/SQL execution elapsed time                                           646
       318 1159091985 repeated bind elapsed time                                              319

會話315
delete test_b;

耗時1100多秒
SQL> select c.sid,b.NAME,a.VALUE from v$sesstat a ,v$statname b ,(select SID from v$mystat where rownum<=1) c
  2     where a.SID=c.sid and a.STATISTIC#=b.STATISTIC# and (b.NAME like 'physical%' or b.name like '%read%' or B.NAME like   '%undo%' or b.name like '%redo%') and a.VALUE<>0 ;
 
       SID NAME                                                                  VALUE
---------- ---------------------------------------------------------------- ----------
       315 session logical reads                                              16309976   --邏輯讀是實際行數的4倍，包含表本生的讀取，還有索引讀取
       315 physical read total IO requests                                        9501
       315 physical read total multi block requests                               1101
       315 physical read total bytes                                         211697664
       315 physical reads                                                        25842 --物理塊 索引+表
       315 physical reads cache                                                  25842
       315 physical read IO requests                                              9501
       315 physical read bytes                                               211697664 --- 這裡基本是表144M+72m索引的大小
       315 redo synch writes                                                         1
       315 physical reads cache prefetch                                         16341
       315 redo entries                                                        8036150
       315 redo size                                                        1959456476 ---2G
       315 redo buffer allocation retries                                           85
       315 redo log space requests                                                  87
       315 redo log space wait time                                                595
       315 redo ordering marks                                                  116447
       315 redo subscn max counts                                               125117
       315 undo change vector size                                           922274632 --900M
       315 no work - consistent read gets                                        18761
      
 SQL>  select *
  2            from v$session_event a
  3          where a.SID = 315
  4        order by TIME_WAITED desc;
 
       SID EVENT                          TIME_WAITED_MICRO
---------- ------------------------------------------------ ---------------------------------------------
       315 SQL*Net message from client            317688133
       315 db file sequential read                131743590  ---131秒（索引的讀取）
       315 db file scattered read                  34265605  --34秒
       315 log file switch completion               6006692
       315 events in waitclass Other                  58204
       315 SQL*Net message to client                     65
 
SQL> select * from v$sess_time_model where sid=315 order by  value desc;
 
       SID    STAT_ID STAT_NAME                                                             VALUE
---------- ---------- ---------------------------------------------------------------- ----------
       315 3649082374 DB time                                                          1068676495
       315 2821698184 sql execute elapsed time                                         1068593627
       315 2748282437 DB CPU                                                            886348292
       315 1431595225 parse time elapsed                                                   129178
       315  372226525 hard parse elapsed time                                              112666
       315 1311180441 PL/SQL compilation elapsed time                                       25308
       315 1990024365 connection management call elapsed time                               21365
       315 2643905994 PL/SQL execution elapsed time                                          3872
       315 1159091985 repeated bind elapsed time                                              518
      
實際上對比INSERT，DELETE語句實際上多在對錶和索引的讀取環節，LOG生成量差不多，UNDO delete更多，
但是基本上多一個索引時間會*2，所以對於大量的資料刪除，和平凡的INSERT不適合建立索引，
但是如果只是DELETE TABLE WHERE ID=1這樣的操作比較多，那麼索引可以提高讀取表的速度，這樣可以不使用
全表掃描而使用索引掃描，要快很多。所以還是要區別對待。