ORACLE DELETE数据慢的案例

摘要:
我去处理这个问题时,首先怀疑可能是SQL的阻塞、触发器、外键约束、高水位线等因素中的某一个导致DELETE操作慢,于是我打算一个一个排除,我先试着删除一条记录,然后去检查SQL的阻塞情况,结果使用下面SQL语句并没有发现SQL被阻塞。于是SQL的阻塞导致DELETE慢的原因被我排除了。也就是说DELETE操作并不会触发任何触发器。我们可以用跟踪当前会话,查看一下DELETE操作,就会发现它会去处理从表,检查从表有没有对应的记录,而这个外键刚好也没有索引。

今天遇到一个有意思的案例,一开发同事告诉我他删除一个表的记录非常慢,已经快1个多小时了还没有完成。而且删除的记录只有1百多条。真是大跌眼镜的一件事情。最后发现该表与多个表有外键关联关系(这个表即是主表、又是从表),最后我禁用引用该表的外键约束后。一秒内删除了记录。然后启用外键约束关系。下面记录、分析一下解决过程的思路(下面是在测试环境的记录,数据量不一样)。

我去处理这个问题时,首先怀疑可能是SQL的阻塞、触发器、外键约束、高水位线等因素中的某一个导致DELETE操作慢,于是我打算一个一个排除,我先试着删除一条记录,然后去检查SQL的阻塞情况,结果使用下面SQL语句并没有发现SQL被阻塞。于是SQL的阻塞导致DELETE慢的原因被我排除了。

SELECT'节点 ' || A.INST_ID || ' SESSION ' || A.SID || ',' || A_S.SERIAL# ||
 ' 阻塞了 节点 ' || B.INST_ID || ' SESSION ' || B.SID || ',' || B_S.SERIAL# BLOCKINFO,
 A.INST_ID,
 A_S.SID,
 A_S.SCHEMANAME,
 A_S.MODULE,
 A_S.STATUS,
 A.TYPE LOCK_TYPE,
 A.ID1,
 A.ID2,
 DECODE(A.LMODE,
 0,
 'NONE',
 1,
 NULL,
 2,
 'ROW-S (SS)',
 3,
 'ROW-X (SX)',
 4,
 'SHARE (S)',
 5,
 'S/ROW-X (SSX)',
 6,
 'EXCLUSIVE (X)') LOCK_MODE,
 
 '后为被阻塞信息' ,
 B.INST_ID BLOCKED_INST_ID,
 B_S.SID BLOCKED_SID,
 B.TYPE BLOCKED_LOCK_TYPE,
 DECODE(B.REQUEST,
 0,
 'NONE',
 1,
 NULL,
 2,
 'ROW-S (SS)',
 3,
 'ROW-X (SX)',
 4,
 'SHARE (S)',
 5,
 'S/ROW-X (SSX)',
 6,
 'EXCLUSIVE (X)') BLOCKED_LOCK_REQUEST,
 B_S.SCHEMANAME BLOCKED_SCHEMANAME,
 B_S.MODULE BLOCKED_MODULE,
 B_S.STATUS BLOCKED_STATUS,
 B_S.SQL_ID BLOCKED_SQL_ID,
 OBJ.OWNER BLOCKED_OWNER,
 OBJ.OBJECT_NAME BLOCKED_OBJECT_NAME,
 OBJ.OBJECT_TYPE BLOCKED_OBJECT_TYPE,
 CASE
 WHEN B_S.ROW_WAIT_OBJ# <> -1 THEN
 DBMS_ROWID.ROWID_CREATE(1,
 OBJ.DATA_OBJECT_ID,
 B_S.ROW_WAIT_FILE#,
 B_S.ROW_WAIT_BLOCK#,
 B_S.ROW_WAIT_ROW#) 
 ELSE
 '-1'
 END BLOCKED_ROWID, --THE BLOCKED ROWID
 DECODE(OBJ.OBJECT_TYPE,
 'TABLE',
 'SELECT * FROM ' || OBJ.OWNER || '.' || OBJ.OBJECT_NAME ||
 ' WHERE ROWID=''' ||
 DBMS_ROWID.ROWID_CREATE(1,
 OBJ.DATA_OBJECT_ID,
 B_S.ROW_WAIT_FILE#,
 B_S.ROW_WAIT_BLOCK#,
 B_S.ROW_WAIT_ROW#) || '''',
 NULL) BLOCKED_DATA_QUERYSQL
 FROM GV$LOCK A,
 GV$LOCK B,
 GV$SESSION A_S,
 GV$SESSION B_S,
 DBA_OBJECTS OBJ
WHERE A.ID1 = B.ID1
 AND A.ID2 = B.ID2
 AND A.BLOCK > 0 --BLOCK THE OTHER SQL
 AND B.REQUEST > 0
 AND ((A.INST_ID = B.INST_ID AND A.SID <> B.SID) OR
 (A.INST_ID <> B.INST_ID))
 AND A.SID = A_S.SID
 AND A.INST_ID = A_S.INST_ID
 AND B.SID = B_S.SID
 AND B.INST_ID = B_S.INST_ID
 AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)
ORDERBY A.INST_ID,A.SID;

接下来,我检查了该表的的触发器,结果并没有发现DELETE触发器。也就是说DELETE操作并不会触发任何触发器。触发器导致DELETE慢的怀疑也可以排除掉了。

SELECT * FROM DBA_TRIGGERS WHERE TABLE_NAME='INV_LOCATION_PALLETS'

我用show_space检查了一下这个表的高水位线,发现并没有问题,不需要收缩高水位线。高水位线这个因素也可以排除了。只剩下外键约束的影响了。于是检查了一下有哪些表是该表的从表,如下所示

SELECT/*+RULE*/ D.CONSTRAINT_NAME PK_NAME,
 D.TABLE_NAME 
 || '.'
 || D.COLUMN_NAME PK_COLUMN,
 A.CONSTRAINT_TYPE,
 B.CONSTRAINT_NAME FK_NAME,
 B.TABLE_NAME 
 || '.'
 || B.COLUMN_NAME FK_COLUMN
FROM DBA_CONSTRAINTS A 
 JOIN DBA_CONS_COLUMNS B 
 ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 AND A.OWNER = B.OWNER
 JOIN DBA_CONSTRAINTS C 
 ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME
 AND A.R_OWNER = C.OWNER
 JOIN DBA_CONS_COLUMNS D 
 ON C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
 AND C.OWNER = D.OWNER
WHERE D.TABLE_NAME = 'INV_LOCATION_PALLETS'

clip_image001

有时候也可以用下面语句查看引用这个表的外键引用关系

SELECT * FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME='PK_INV_LOCATION_PALLETS'

如上所示,INV_LOCATION_PALLETS这个表有三个从表,而这里面有个表的记录很大,大概2千多万。所以导致DELETE操作很慢。

我们可以用跟踪当前会话,查看一下DELETE操作,就会发现它会去处理从表,检查从表有没有对应的记录,而这个外键刚好也没有索引(下面是使用tkprof命令格式化的内容)。在这篇Delete the data on the table very slow(删除数据慢)博客里面跟深入的介绍、分析了删除表删除数据慢的原因。在此不做过多赘述了。

DELETE INVENTORY.INV_LOCATION_PALLETS 
WHERE
 PALLET_ID =1039928
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 26 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 3 26 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5 
Rows Row Source Operation
------- ---------------------------------------------------
1 DELETE INV_LOCATION_PALLETS (cr=752149 pr=735050 pw=0 time=7550289 us)
 1 INDEXUNIQUE SCAN PK_INV_LOCATION_PALLETS (cr=3 pr=0 pw=0 time=38 us)(object id 59532)
********************************************************************************
select/*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_HD" where "TO_PALLET_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.75 0.74 70540 78205 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.75 0.74 70540 78205 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursivedepth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=78205 pr=70540 pw=0 time=743169 us)
 0 TABLE ACCESS FULL INV_REQ_HD (cr=78205 pr=70540 pw=0 time=743155 us)
********************************************************************************
select/*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_HD" where "FROM_PALLET_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.69 0.67 70528 78205 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.69 0.68 70528 78205 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursivedepth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=78205 pr=70528 pw=0 time=680000 us)
 0 TABLE ACCESS FULL INV_REQ_HD (cr=78205 pr=70528 pw=0 time=679987 us)
********************************************************************************
select/*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_LINES" where "TO_PALLET_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 3.30 3.23 296991 297868 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 3.30 3.23 296991 297868 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursivedepth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=297868 pr=296991 pw=0 time=3232134 us)
 0 TABLE ACCESS FULL INV_REQ_LINES (cr=297868 pr=296991 pw=0 time=3232122 us)
********************************************************************************
select/*+ all_rows */ count(1) 
from
 "INVENTORY"."INV_REQ_LINES" where "FROM_PALLET_ID" = :1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 2.94 2.88 296991 297868 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 2.94 2.88 296991 297868 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursivedepth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=297868 pr=296991 pw=0 time=2885783 us)
 0 TABLE ACCESS FULL INV_REQ_LINES (cr=297868 pr=296991 pw=0 time=2885772 us)
********************************************************************************
begin
 sys.dbms_output.get_line(line => :line, status => :status);
end;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.00 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 4
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.00 0.00 0 0 0 4
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 5 

clip_image002

参考资料

http://www.anbob.com/archives/1962.html/comment-page-1

免责声明:文章转载自《ORACLE DELETE数据慢的案例》仅用于学习参考。如对内容有疑问,请及时联系本站处理。

上篇异步复位和同步释放电路的详细解释Linux shell脚本,按顺序批量启动多个jar包,批量启动spring cloud的jar包下篇

宿迁高防,2C2G15M,22元/月;香港BGP,2C5G5M,25元/月 雨云优惠码:MjYwNzM=

相关文章

oracle的字符集(NLS_LANGUAGE)

数据库服务器字符集select * from nls_database_parameters,其来源于props$,是表示数据库的字符集。 SQL> select * fromnls_database_parameters; PARAMETER VALUE -------------------------...

基于godror 的 oracle prometheus exporter

iamseth/oracledb_exporter 是一个挺不错的 oracle prometheus exporter ,但是因为oracle golang 驱动的问题,不是很方便 (构建,使用),所以基于godror/godror 调整了一个版本(很简单,替换一个依赖包就可以了),以下是参考说明 修改的文件 go.mod module github....

Flink 如何实时分析 Iceberg 数据湖的 CDC 数据

简介: 数据湖的架构中,CDC 数据实时读写的方案和原理 本文由李劲松、胡争分享,社区志愿者杨伟海、李培殿整理。主要介绍在数据湖的架构中,CDC 数据实时读写的方案和原理。文章主要分为 4 个部分内容: 常见的 CDC 分析方案 为何选择 Flink + Iceberg 如何实时写入读取 未来规划 一、常见的 CDC 分析方案 我们先看一下今天的 to...

[转]Oracle 修改或者删除临时表 ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

本文转自:http://blog.csdn.net/treasurelifelhf/article/details/7290729    由于存储过程出现问题,导致前台页面无法显示数据。执行存储过程发现临时表少一字段,无奈无论怎么修改或者删除临时表都报错 ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引。                ...

navicat和 plsql 连接oracle数据库 总结

打开 navicat  -->工具-->选项-->oci   右侧选择oci.dll 的路径 默认 在 navicat的安装目录下有一个 instantclient 的文件夹 直接选择这个里面的 oci.ll 就可以了 我的 navicat 是 11.1.10 64-bit   instantclient 是 instantclient_...

oracle in 的最大值是1000

oracle 的in 最大为1000,如果再多了需要循环查询; IN里面最多200条达到性能瓶颈; 循环查询能提升近8-9倍的效率; 多线程是性能最好的,建议使用。 参考:MySQL之IN的优化 参考:oracle sql查询中用in列表中最大表达式数大于1000的处理...