sql中如何查看delete的数据

这篇文章主要介绍sql中如何查看delete的数据，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

测试数据，在进行删除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这样的操作比较多，那么索引可以提高读取表的速度，这样可以不使用
全表扫描而使用索引扫描，要快很多。所以还是要区别对待。

以上是“sql中如何查看delete的数据”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注编程网精选频道！