sql中如何查看delete的数据

63次阅读

共计 4398 个字符，预计需要花费 11 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章主要介绍 sql 中如何查看 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 这样的操作比较多，那么索引可以提高读取表的速度，这样可以不使用
全表扫描而使用索引扫描，要快很多。所以还是要区别对待。