导航：首页 > 服务器 >

sql中如何查看delete的数据

发表于：2024-09-22 作者：千家信息网编辑

千家信息网最后更新 2024年09月22日，这篇文章主要介绍sql中如何查看delete的数据，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！2、测试数据，在进行删除delete的时候有索引和没有索引的对象的统计信息，会话

千家信息网最后更新 2024年09月22日sql中如何查看delete的数据

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