sql中如何查看delete的數據

189次閱讀

共計 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 這樣的操作比較多，那么索引可以提高讀取表的速度，這樣可以不使用
全表掃描而使用索引掃描，要快很多。所以還是要區別對待。