sql中如何查看delete的數(shù)據(jù)

這篇文章主要介紹sql中如何查看delete的數(shù)據(jù)，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！

站在用戶的角度思考問題，與客戶深入溝通，找到新華網(wǎng)站設(shè)計與新華網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設(shè)計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：網(wǎng)站設(shè)計制作、成都做網(wǎng)站、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、主機(jī)域名、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋新華地區(qū)。

2、測試數(shù)據(jù)，在進(jìn)行刪除delete的時候有索引和沒有索引的對象的統(tǒng)計信息，會話的統(tǒng)計信息，會話的等待事件

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 ---實際行數(shù)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 --表塊的數(shù)量
       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   --邏輯讀是實際行數(shù)的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秒（索引的讀?。?br/>       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語句實際上多在對表和索引的讀取環(huán)節(jié)，LOG生成量差不多，UNDO delete更多，
但是基本上多一個索引時間會*2，所以對于大量的數(shù)據(jù)刪除，和平凡的INSERT不適合建立索引，
但是如果只是DELETE TABLE WHERE ID=1這樣的操作比較多，那么索引可以提高讀取表的速度，這樣可以不使用
全表掃描而使用索引掃描，要快很多。所以還是要區(qū)別對待。

以上是“sql中如何查看delete的數(shù)據(jù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

本文名稱：sql中如何查看delete的數(shù)據(jù)
本文網(wǎng)址：http://muchs.cn/article6/pdpgig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、全網(wǎng)營銷推廣、微信公眾號、建站公司、網(wǎng)站維護(hù)、用戶體驗

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容