sql优化delete处在执行状态无反应

desert_xu · 发表于 2018-4-8 21:32

如果io资源充足的话，直接full scan 更快

lfree · 发表于 2018-4-8 21:49

desert_xu 发表于 2018-4-8 21:31
nest loop 肯定慢了，一条条去删除，当然耗cpu了

好像delete就是趋向nested,我估计oracle在delete时还要delete索引,这样选择索引更佳.

我不知道这样写是否会好一些:

delete (select a.* from a where exists (select 1 from b where a.col=b.col and a.col2=b.col2 and b.col1='1300' and a.col='2018') and a.col='2018');

lfree · 发表于 2018-4-8 21:51

明天写一个例子测试看看.

desert_xu · 发表于 2018-4-9 09:03

lfree 发表于 2018-4-8 21:49
好像delete就是趋向nested,我估计oracle在delete时还要delete索引,这样选择索引更佳.

我不知道这样写 ...

这个如果有索引，索引肯定要维护，但我不知道是不是这样维护的

lfree · 发表于 2018-4-9 09:37

lz改成这样应该快一些:
delete (select /*+ use_hash(a,b) */ a.* from a where exists (select 1 from b where a.col=b.col and a.col2=b.col2 and b.col1='1300' and a.col='2018') and a.col='2018');

wolfop · 发表于 2018-4-9 09:43

直接delete大量数据没法快，改写成CTAS，rename，重建索引吧

lfree · 发表于 2018-4-9 10:06

[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个索引,如果有多个索引,也许情况更加严重.
--//为什么呢?那位给出解析....

shirleylove269 · 发表于 2018-4-9 10:11

A表数据量为1.7亿，B表数据量为90万。也就是说在进行NESTLOOP时，A表会访问90万次。因为每次从B表获取一条数据就需要访问一次A表。在此种情况下访问会慢。
可以从以下几个方面进行考虑：
1.表的统计信息是否UP-TO-DATE. 如果统计信息不是UP-TO-DATE可能导致优化器选择非最佳执行计划。
2.可以考虑使用HINT 改变执行计划，使用并行，对A表和B表使用HASH JOIN，此时对A表的访问可能是全表扫描。注：并行会加大CPU负载，如果系统是CPU BOUND,请慎行。
3. 需要注意TEMPORARY TABLE 的使用情况，排除TEMPORARY TABLE 的大量磁盘操作。
4. 一般情况来说，删除大量数据时会比较慢，因为删除动作会产生REDO LOG 和UNDO LOG ,同时ARCHIVE LOG写也会相对比较频繁。
NOLOGGING可以减少REDO LOG 的产生。如果允话可以考虑。注：NOLOGGING会导致表无法RESTORE如果出现问题。请慎用。

yeats1989523 · 发表于 2018-4-9 16:40

为什么不用create table把数据都放入C表，然后通过C表关联A表去删除数据呢？这样应该会快把？

ZALBB · 发表于 2018-4-9 17:58

yeats1989523 发表于 2018-4-9 16:40
为什么不用create table把数据都放入C表，然后通过C表关联A表去删除数据呢？这样应该会快把？

只删除90万的记录，此时删除比重建省事，

[PL/SQL] sql优化delete处在执行状态无反应

回帖奖励 +1 pub币


3 pub币	回复本帖可获得 1 pub币奖励! 每人限 1 次(中奖概率 10%)