[20180409]delete刪除緩慢分析.txt
[20180409]delete刪除緩慢分析.txt
--//連結:http://www.itpub.net/thread-2101054-2-1.html
--//大量產生記錄出現緩慢的情況.可以看出慢的原因是執行計劃走nested loop.
--//按照lz的提示,刪除幾乎是b的全部資料.
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t1 as select * from all_objects;
Table created.
SCOTT@book> create table t2 as select * from all_objects;
Table created.
create unique index pk_t1 on t1(object_id);
alter table t1 add ( constraint pk_t1 primary key (object_id) using index pk_t1 enable validate);
create unique index pk_t2 on t2(object_id);
alter table t2 add ( constraint pk_t2 primary key (object_id) using index pk_t2 enable validate);
--//分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
2.測試1:
select t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS');
Plan hash value: 3761397674
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 676 (100)| | 37356 |00:00:00.07 | 2612 | 2422 | | | |
|* 1 | HASH JOIN RIGHT SEMI| | 1 | 2923 | 311K| 676 (1)| 00:00:09 | 37356 |00:00:00.07 | 2612 | 2422 | 3251K| 2890K| 2997K (0)|
|* 2 | TABLE ACCESS FULL | T1 | 1 | 2923 | 32153 | 338 (1)| 00:00:05 | 37356 |00:00:00.01 | 1213 | 1211 | | | |
| 3 | TABLE ACCESS FULL | T2 | 1 | 84764 | 8112K| 338 (1)| 00:00:05 | 84764 |00:00:00.03 | 1399 | 1211 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T1@SEL$2
3 - SEL$5DA710D3 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OWNER"='SYS')
--//如果slect,執行計劃選擇的是HASH JOIN RIGHT SEMI.
SCOTT@book> explain plan for delete (select t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
Explained.
SCOTT@book> @ &r/dp '' ''
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 3741622905
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2923 | 46768 | 339 (1)| 00:00:05 |
| 1 | DELETE | T2 | | | | |
| 2 | NESTED LOOPS | | 2923 | 46768 | 339 (1)| 00:00:05 |
| 3 | SORT UNIQUE | | 2923 | 32153 | 338 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| T1 | 2923 | 32153 | 338 (1)| 00:00:05 |
|* 5 | INDEX UNIQUE SCAN | PK_T2 | 1 | 5 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AA69DF42
4 - SEL$AA69DF42 / T1@SEL$2
5 - SEL$AA69DF42 / T2@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
SEMI_TO_INNER(@"SEL$AA69DF42" "T1"@"SEL$2")
USE_NL(@"SEL$AA69DF42" "T2"@"SEL$1")
LEADING(@"SEL$AA69DF42" "T1"@"SEL$2" "T2"@"SEL$1")
INDEX(@"SEL$AA69DF42" "T2"@"SEL$1" ("T2"."OBJECT_ID"))
FULL(@"SEL$AA69DF42" "T1"@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"DEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1")
OUTLINE(@"SEL$15E3A8DC")
UNNEST(@"SEL$2")
OUTLINE_LEAF(@"SEL$AA69DF42")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OWNER"='SYS')
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (#keys=0; cmp=3) "T1"."OBJECT_ID"[NUMBER,22],
"T2".ROWID[ROWID,10], "T2"."OBJECT_ID"[NUMBER,22]
3 - (#keys=1) "T1"."OBJECT_ID"[NUMBER,22]
4 - "T1"."OWNER"[VARCHAR2,30], "T1"."OBJECT_ID"[NUMBER,22]
5 - "T2".ROWID[ROWID,10], "T2"."OBJECT_ID"[NUMBER,22]
--//因為delete操作t2表要修改索引,oracle選擇nested loop,走主鍵索引(PK_T2).如果刪除記錄很多自然很慢的,而且T1表如果沒有按照 object_id 聚集,這樣經常會出現
--//讀取的索引葉子不在資料快取的情況.而如果修改如下:
delete (select /*+ use_hash(t2,t1) */ t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
--//也許會快一些.
--//使用set autotrace trace看看.
SCOTT@book> set autotrace trace
SCOTT@book> delete (select t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
37356 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 3741622905
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2923 | 46768 | 339 (1)| 00:00:05 |
| 1 | DELETE | T2 | | | | |
| 2 | NESTED LOOPS | | 2923 | 46768 | 339 (1)| 00:00:05 |
| 3 | SORT UNIQUE | | 2923 | 32153 | 338 (1)| 00:00:05 |
|* 4 | TABLE ACCESS FULL| T1 | 2923 | 32153 | 338 (1)| 00:00:05 |
|* 5 | INDEX UNIQUE SCAN | PK_T2 | 1 | 5 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OWNER"='SYS')
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
17 recursive calls
117329 db block gets
3156 consistent gets
1691 physical reads
22191320 redo size
847 bytes sent via SQL*Net to client
864 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
37356 rows processed
SCOTT@book> rollback ;
Rollback complete.
--//如果使用提示,強迫走HASH JOIN RIGHT SEMI:
SCOTT@book> delete (select /*+ use_hash(t2,t1) */ t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
37356 rows deleted.
Execution Plan
----------------------------------------------------------
Plan hash value: 1142252108
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 2923 | 46768 | 515 (1)| 00:00:07 |
| 1 | DELETE | T2 | | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 2923 | 46768 | 515 (1)| 00:00:07 |
|* 3 | TABLE ACCESS FULL | T1 | 2923 | 32153 | 338 (1)| 00:00:05 |
| 4 | INDEX FULL SCAN | PK_T2 | 84764 | 413K| 177 (0)| 00:00:03 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T1"."OWNER"='SYS')
Statistics
----------------------------------------------------------
10 recursive calls
42276 db block gets
1401 consistent gets
0 physical reads
14730004 redo size
846 bytes sent via SQL*Net to client
887 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
37356 rows processed
SCOTT@book> rollback ;
Rollback complete.
--//有點奇怪的是兩者redo差別很大.前者:22191320 redo size,後者:14730004 redo size.不採用set autotrace trace看看.
3.測試2:
SCOTT@book> @ &r/viewsess 'redo size'
NAME STATISTIC# VALUE SID
---------------------------------- ---------- ---------- ----------
redo size 194 768 41
redo size for lost write detection 196 0 41
redo size for direct writes 197 0 41
SCOTT@book> delete (select t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
37356 rows deleted.
SCOTT@book> @ &r/viewsess 'redo size'
NAME STATISTIC# VALUE SID
---------------------------------- ---------- ---------- ----------
redo size 194 22200940 41
redo size for lost write detection 196 0 41
redo size for direct writes 197 0 41
--//確實與前面set autot trace測試一致.
SCOTT@book> rollback ;
Rollback complete.
--//退出,重複測試:
SCOTT@book> @ &r/viewsess 'redo size'
NAME STATISTIC# VALUE SID
---------------------------------- ---------- ---------- ----------
redo size 194 768 41
redo size for lost write detection 196 0 41
redo size for direct writes 197 0 41
SCOTT@book> delete (select /*+ use_hash(t2,t1) */ t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
37356 rows deleted.
SCOTT@book> @ &r/viewsess 'redo size'
NAME STATISTIC# VALUE SID
---------------------------------- ---------- ---------- ----------
redo size 194 14730816 41
redo size for lost write detection 196 0 41
redo size for direct writes 197 0 41
--//補充兩者的執行計劃:
SCOTT@book> alter session set statistics_level=all;
Session altered.
SCOTT@book> delete (select t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
37356 rows deleted.
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 88jpm2t7jxf7h, child number 2
-------------------------------------
delete (select t2.* from t2 where exists (select 1 from t1 where
t1.object_id=t2.object_id and t1.owner='SYS'))
Plan hash value: 3741622905
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | | | 339 (100)| | 0 |00:00:00.83 | 120K| | | |
| 1 | DELETE | T2 | 1 | | | | | 0 |00:00:00.83 | 120K| | | |
| 2 | NESTED LOOPS | | 1 | 2923 | 46768 | 339 (1)| 00:00:05 | 37356 |00:00:00.13 | 3281 | | | |
| 3 | SORT UNIQUE | | 1 | 2923 | 32153 | 338 (1)| 00:00:05 | 37356 |00:00:00.06 | 1215 | 2108K| 680K| 1873K (0)|
|* 4 | TABLE ACCESS FULL| T1 | 1 | 2923 | 32153 | 338 (1)| 00:00:05 | 37356 |00:00:00.02 | 1215 | | | |
|* 5 | INDEX UNIQUE SCAN | PK_T2 | 37356 | 1 | 5 | 0 (0)| | 37356 |00:00:00.06 | 2066 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AA69DF42
4 - SEL$AA69DF42 / T1@SEL$2
5 - SEL$AA69DF42 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."OWNER"='SYS')
5 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
SCOTT@book> rollback ;
Rollback complete.
SCOTT@book> delete (select /*+ use_hash(t2,t1) */ t2.* from t2 where exists (select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'));
37356 rows deleted.
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 34r8apdn8r0bk, child number 2
-------------------------------------
delete (select /*+ use_hash(t2,t1) */ t2.* from t2 where exists
(select 1 from t1 where t1.object_id=t2.object_id and t1.owner='SYS'))
Plan hash value: 1142252108
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | | | 515 (100)| | 0 |00:00:00.52 | 43677 | | | |
| 1 | DELETE | T2 | 1 | | | | | 0 |00:00:00.52 | 43677 | | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1 | 2923 | 46768 | 515 (1)| 00:00:07 | 37356 |00:00:00.11 | 1392 | 3177K| 2421K| 3047K (0)|
|* 3 | TABLE ACCESS FULL | T1 | 1 | 2923 | 32153 | 338 (1)| 00:00:05 | 37356 |00:00:00.01 | 1215 | | | |
| 4 | INDEX FULL SCAN | PK_T2 | 1 | 84764 | 413K| 177 (0)| 00:00:03 | 84764 |00:00:00.02 | 177 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$AA69DF42
3 - SEL$AA69DF42 / T1@SEL$2
4 - SEL$AA69DF42 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
3 - filter("T1"."OWNER"='SYS')
30 rows selected.
--//實際上還是2者redo存在巨大的不同,而且我的測試僅僅1個索引,如果有多個索引,也許情況更加嚴重.
--//為什麼呢?那位給出解析....
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2152693/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181119]sql語句執行緩慢分析.txtSQL
- [20211109]sqlplus本地登入緩慢分析2.txtSQL
- [20181230]Git Bash啟動緩慢.txtGit
- [20211108]sqlplus 本地登入緩慢.txtSQL
- delete和truncate刪除的區別delete
- [20210804]oracle rac執行命令crs_stat -t -v緩慢的分析.txtOracle
- [20181130]hash衝突導致查詢緩慢.txt
- sql語句執行緩慢分析SQL
- mysql支援跨表delete刪除多表記錄MySqldelete
- Shift + Delete刪除的檔案如何恢復？delete
- [20210518]ssh ip登入緩慢問題解決.txt
- Linux命令curl去操作delete去刪除資源Linuxdelete
- python 使用 delete 方法時報錯，可以正常刪除Pythondelete
- [20201112]tmpwatch 刪除檔案.txt
- MySQL防止delete命令刪除資料的兩種方法MySqldelete
- [20180522]避免刪除共享印表機.txt
- [20210325]如何避免sequence意外刪除.txt
- Linux除錯檢視搜尋工具集-緩慢增加中Linux除錯
- 【LISTENER】Oracle通過監聽連線緩慢分析Oracle
- [20190409]pre_page_sga=true與連線緩慢的問題.txt
- win10 shift delete刪除的檔案如何恢復Win10delete
- 使用delete刪除陣列，其長度會改變嗎？delete陣列
- [20180329]刪除帶斜線的索引.txt索引
- [20190103]設定pre_page_sga=true啟動緩慢的問題.txt
- Mysql資料庫delete刪除後資料恢復報告MySql資料庫delete資料恢復
- 單表的更新UPDATE和刪除記錄DELETE（二十六）delete
- 什麼？還在用delete刪除資料《死磕MySQL系列 九》deleteMySql
- SAP ABAP OData 服務如何支援刪除(Delete)操作試讀版delete
- [20190825]Join View and delete.txtViewdelete
- [20181019]vim小技巧刪除製表符.txt
- [20190130]刪除tab$記錄的恢復.txt
- [20210803]刪除user$的恢復準備.txt
- [20210930]bbed恢復刪除的資料.txt
- 得到txt空白行的行數、將txt檔案的空行刪除和刪除csv檔案中指定的行
- [20200906][轉載]FK on delete.txtdelete
- AWR收集緩慢、掛起的幾種常見情況分析
- [20180612]刪除bootstrap$記錄無法啟動.txtboot
- [20221121]rman刪除歸檔日誌問題.txt