[20180409]delete删除缓慢分析.txt

最新推荐文章于 2018-10-17 21:58:33 发布
最新推荐文章于 2018-10-17 21:58:33 发布
阅读量95 收藏
点赞数

[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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2152693/

cuixun7780
关注
MySQL Delete From 删除数据.docx
09-26
MySQL Delete From 删除数据 MySQL Delete From 语句用于删除数据表中的记录，DELETE 语句的基本语法为：`DELETE FROM tb_name WHERE definition`，其中 `tb_name` 是要删除记录的表名，`definition` 是删除记录的...
index dive 导致同类sql执行计划不一致
渔夫数据库笔记
01-15 479
1.版本 1)操作系统 cat /proc/version Linux version 3.10.0-1127.18.2.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) 2)mysql数据库版本 mysql> select version(); +-----------+ | version() | +-----------+ | 8.
数据的删除很慢，怎么办
winson的专栏
11-07 3530
工作中遇到这样一个问题，删除（delete）某张表的数据时，发现很慢。使用的数据库是oracle。差不多两分钟删除了10条。 分析了一下，主要是一下问题引起的。     1删除时有子查询     2有索引     3外键约束 在不能truncate的情况下怎么删除比较快呢？     1优化查询条件     2每删除一定数据进行提交，commit。     3删除索引，把数据删除后重
Mysql删除数据特别慢及解决方法
热门推荐
说文科技，做有态度的研究。
10-17 2万+
Mysql删除数据特别慢及其解决方法
一次delete速度异常慢的处理过程
cuizhanzhun6201的博客
07-31 470
一次小数据量删除，但花费2个小时还没完成的问题 delete from TOPBOX_COURSEWARE where id like '760%';花费非常长的时间，topbox_courseware表大概2w数据，要删除的...
insert&delete注入.txt
06-10
insert&delete注入
delete fast full scan 优化方案.txt
11-01
现在发现在12c版本的数据库中，有很多业务SQL执行计划应该选择index range scan，但是选择了index fast full scan，消耗了多余的IO。
Windows大文件删除脚本delete.bat
03-16
`delete.bat` 脚本通过调用内置的系统命令来实现文件和文件夹的无提示删除。通常，这样的脚本会包含`del` 或 `rd` 命令，`del` 用于删除文件，而`rd` 用于删除空的文件夹。 在使用`delete.bat` 脚本时，有几点需要...
java删除本地文件.txt
06-05
本文详细介绍了如何使用Java程序删除本地文件，包括创建`File`对象、使用`delete()`方法删除文件以及处理可能出现的异常情况。此外，还提供了一个递归删除目录及其包含的所有文件的方法。通过这些知识，开发者可以更...
ORACLE中的delete语句
csnewdn的专栏
11-16 7214
ORACLE中的delete语句 一、语法 DELETE FROM 表名称 WHERE 列名称 = 值 二、删除某行 "Fred Wilson" 会被删除： DELETE FROM Person WHERE LastName = 'Wilson' 三、删除所有行 可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的： DELETE FROM
数据库基础测验20241113.doc
11-13
数据库基础测验20241113.doc
微信小程序下拉选择组件
11-13
微信小程序下拉选择组件
DICOM文件+DX放射平片-数字X射线图像DICOM测试文件
11-13
DICOM文件+DX放射平片—数字X射线图像DICOM测试文件，文件为.dcm类型DICOM图像文件文件,仅供需要了解DICOM或相关DICOM开发的技术人员当作测试数据或研究使用，请勿用于非法用途。
Jupyter Notebook《基于双流 Faster R-CNN 网络的 图像篡改检测》+项目源码+文档说明+代码注释
11-13
<项目介绍> - 基于双流 Faster R-CNN 网络的 图像篡改检测 - 不懂运行，下载完可以私聊问，可远程教学 1、该资源内项目代码都经过测试运行成功，功能ok的情况下才上传的，请放心下载使用！ 2、本项目适合计算机相关专业(如计科、人工智能、通信工程、自动化、电子信息等)的在校学生、老师或者企业员工下载学习，也适合小白学习进阶，当然也可作为毕设项目、课程设计、作业、项目初期立项演示等。 3、如果基础还行，也可在此代码基础上进行修改，以实现其他功能，也可用于毕设、课设、作业等。 下载后请首先打开README.md文件（如有），仅供学习参考, 切勿用于商业用途。 --------
使用epf捕获没有CA证书的SSLTLS明文（LinuxAndroid内核支持amd64arm64）.zip
11-13
c语言
(源码)基于Arduino的天文数据库管理系统.zip
11-13
# 基于Arduino的天文数据库管理系统 ## 项目简介 本项目是一个基于Arduino的天文数据库管理系统，旨在为Arduino设备提供一个完整的天文数据库，包括星星、星系、星团等天体数据。项目支持多种语言的星座名称，并提供了详细的天体信息，如赤道坐标、视星等。 ## 项目的主要特性和功能 星座目录包含88个星座，提供拉丁语、英语和法语的缩写和全名。 恒星目录包含494颗亮度达到4等的恒星。 梅西耶目录包含110个梅西耶天体。 NGC目录包含3993个NGC天体，亮度达到14等。 IC目录包含401个IC天体，亮度达到14等。 天体信息每个天体（不包括星座）提供名称、命名、相关星座、赤道坐标（J2000）和视星等信息。 恒星额外信息对于恒星，还提供每年在赤经和赤纬上的漂移以及视差。 ## 安装使用步骤 1. 安装库使用Arduino IDE的库管理器安装本项目的库。 2. 解压数据库将db.zip解压到SD卡中。
(源码)基于JSP和SQL Server的维修管理系统.zip
最新发布
11-13
# 基于JSP和SQL Server的维修管理系统 ## 项目简介 本项目是一个基于JSP和SQL Server的维修管理系统，旨在提供一个高效、便捷的维修管理解决方案。系统涵盖了从维修订单的创建、管理到配件的录入、更新等多个功能模块，适用于各类维修服务行业。 ## 项目的主要特性和功能 1. 用户管理 管理员和客户的注册与登录。 管理员信息的管理与更新。 客户信息的创建、查询与更新。 2. 维修订单管理 维修订单的创建、查询与更新。 维修回执单的创建与管理。 3. 配件管理 配件信息的录入与更新。 配件库存的管理与查询。 4. 评价与反馈 客户对维修服务的评价记录。 系统反馈信息的收集与管理。 5. 数据加密与安全 使用MD5加密算法对用户密码进行加密存储。 通过过滤器实现登录验证，确保系统安全。 ## 安装使用步骤
CONFLICT (modify/delete): 123.txt deleted in HEAD and modified in b1. Version b1 of 123.txt left in tree.
05-18
这个冲突通常发生在Git合并分支时，意味着在分支b1中，文件123.txt被修改了，而在主分支HEAD中，文件123.txt被删除了。因此，Git无法自动解决这个冲突。 要解决这个冲突，你需要手动决定该如何处理这个文件。你可以...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值