[20180409]delete刪除緩慢分析.txt

lfree發表於2018-04-09

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章