删除主表 子表外键没有索引的性能优化

这篇具有很好参考价值的文章主要介绍了删除主表 子表外键没有索引的性能优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

整个表147M,执行时一个CPU耗尽, buffer gets 超过1个G, 启用并行也没有用

删除主表 子表外键没有索引的性能优化,ffmpeg,oracle

 

今天开发的同事问有个表上的数据为什么删不掉?我看了一下,也就不到100000条数据,表上有外键,等了5分钟hang在那里,时间原因,我对表上的外键禁用后,瞬间删除。

现在来还原这个问题。

sys@ANBOB>select count(*) from bjhr.doctor_exam_member;
COUNT(*)
——————–
92102

sys@ANBOB>delete bjhr.doctor_exam_member;
–hang

–等待10分钟都未执行完,检查表的外键信息

bjhr@ANBOB>
    SELECT /*+RULE*/
          D.CONSTRAINT_NAME pk_name,-- d.table_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 user_constraints a
           JOIN user_cons_columns b
              ON a.constraint_name = b.constraint_name AND a.owner = b.owner
          JOIN user_constraints c
             ON A.R_CONSTRAINT_NAME = C.CONSTRAINT_NAME AND A.R_OWNER = c.owner
          JOIN user_cons_columns d
             ON c.constraint_name = d.constraint_name AND c.owner = d.owner
         WHERE D.table_name = 'DOCTOR_EXAM_MEMBER'
bjhr@ANBOB>/

PK_NAME              PK_COLUMN                                C FK_NAME                        FK_COLUMN
-------------------- ---------------------------------------- - ------------------------------ -------------------------------------------------------
PK_DOCTOR_EXAM_MEMBE DOCTOR_EXAM_MEMBER.DOCTOR_EXAM_MEMBER_ID R FK_RESULT_N_REFERENCE_DOCTOR   RESULT_NOTIFICATION_RECORD.DOCTOR_EXAM_MEMBER_ID
PK_DOCTOR_EXAM_MEMBE DOCTOR_EXAM_MEMBER.DOCTOR_EXAM_MEMBER_ID R RESULT_RE_DOCTOR_MEMBER        DOCTOR_EXAM_RESULT.DOCTOR_EXAM_MEMBER_ID

–有外键,之前已对子表进行过删除,否则会报错ORA-02266

delete RESULT_NOTIFICATION_RECORD;
delete DOCTOR_EXAM_RESULT;
commit;

–下面开始分析,创建新的session

sys@ANBOB>select xidsqn,xidusn,object_id,session_id,locked_mode from v$locked_object;

              XIDSQN               XIDUSN            OBJECT_ID           SESSION_ID          LOCKED_MODE
-------------------- -------------------- -------------------- -------------------- --------------------
                2102                  203              1639631                 2290                    3
                2102                  203              1639572                 2290                    3

sys@ANBOB>select object_name,object_type from dba_objects where object_id in(1639631,1639572);

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
DOCTOR_EXAM_MEMBER             TABLE
DOCTOR_EXAM_RESULT             TABLE

sys@ANBOB>select event,p1,p2,p1text,p2text,seconds_in_wait,state from v$session_wait where sid=2290;
EVENT                                   P1    P2 P1TEXT               P2TEXT                    SECONDS_IN_WAIT STATE
------------------------------ ----------- ----- -------------------- -------------------- -------------------- -------------------
latch: shared pool              1611704464   307 address              number                                213 WAITED SHORT TIME

--trace hanganalyze and systemstate

alter session set events 'immediate trace name systemstate level 266';
alter session set events 'immediate trace name hanganalyze level 3';

--hanganalyze trace 
===============================================================================

Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 
     Chain 1 Signature Hash: 0x673a0128
 [b] Chain 2 Signature: 'Streams AQ: waiting for messages in the queue'
     Chain 2 Signature Hash: 0xa00e2e87

===============================================================================
Sessions in an involuntary wait or not in a wait:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (ANBOB.ANBOB)
                   os id: 27158
              process id: 94, oracle@dev-db (TNS V1-V3)
              session id: 2290
        session serial #: 7981
    }
    is not in a wait:
    {
               last wait: 11 min 0 sec ago
                blocking: 0 sessions
            wait history:
              1.       event: 'latch: shared pool'
                 time waited: 0.000114 sec
                     wait id: 183             p1: 'address'=0x6010a890
                                              p2: 'number'=0x133
                                              p3: 'tries'=0x0
              * time between wait #1 and #2: 1.586255 sec
              2.       event: 'latch: shared pool'
                 time waited: 0.000032 sec
                     wait id: 182             p1: 'address'=0x6010a890
                                              p2: 'number'=0x133
                                              p3: 'tries'=0x0
              * time between wait #2 and #3: 0.133830 sec
              3.       event: 'latch: shared pool'
                 time waited: 0.000114 sec
                     wait id: 181             p1: 'address'=0x6010a890
                                              p2: 'number'=0x133
                                              p3: 'tries'=0x0
    }

Chain 1 Signature: 
Chain 1 Signature Hash: 0x673a0128

–对systemstate 没发现可疑信息
[oracle@dev-db ~]$ awk -f ass109.awk /oracle/diag/rdbms/ANBOB/ANBOB/trace/ANBOB_ora_23020.trc

— 奇怪为什么会发生在latch:shared pool上? 应该是sql解析和shared pool相关的事件,随后结束delete,做10046 观察究竟

sys@ANBOB>oradebug setmypid;
Statement processed.

sys@ANBOB>oradebug event 10046 trace name context forever,level 12
Statement processed.

sys@ANBOB>delete bjhr.doctor_exam_member;

92102 rows deleted.

sys@ANBOB>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_7784.trc
sys@ANBOB>oradebug event 10046 trace name context off;
Statement processed.

— 格式化trace,终于发现了答案.

delete bjhr.doctor_exam_member
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1     47.30      48.39        201        222     657611       92102
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     47.31      48.39        201        222     657611       92102

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        2        0.00          0.00
  db file scattered read                         26        0.00          0.00
  db file sequential read                        24        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************

-- check deferred objects

select pctfree_stg, pctused_stg, size_stg,initial_stg, next_stg, minext_stg, 
  maxext_stg, maxsiz_stg, lobret_stg,mintim_stg, pctinc_stg, initra_stg, 
  maxtra_stg, optimal_stg, maxins_stg,frlins_stg, flags_stg, bfp_stg, enc_stg,
   cmpflag_stg, cmplvl_stg
from
 deferred_stg$  where obj# =:1

********************************************************************************
select /*+ all_rows */ count(1)
from
 "BJHR"."RESULT_NOTIFICATION_RECORD" where "DOCTOR_EXAM_MEMBER_ID" = :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  92102     11.31      11.34          0          0          0           0
Fetch    92102      0.63       0.64          0          0          0       92102
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   184205     11.95      11.99          0          0          0       92102

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=0 pr=0 pw=0 time=43 us)
         0          0          0   TABLE ACCESS FULL RESULT_NOTIFICATION_RECORD (cr=0 pr=0 pw=0 time=12 us cost=3 size=5 card=1)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  latch: shared pool                              2        0.00          0.00

********************************************************************************
select /*+ all_rows */ count(1)
from
 "BJHR"."DOCTOR_EXAM_RESULT" where "DOCTOR_EXAM_MEMBER_ID" = :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  92102      6.97       7.11          0          0          0           0
Fetch    92102   1012.96    1016.14          0  566243096      92102       92102
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   184205   1019.93    1023.25          0  566243096      92102       92102

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=6148 pr=0 pw=0 time=30196 us)
         0          0          0   TABLE ACCESS FULL DOCTOR_EXAM_RESULT (cr=6148 pr=0 pw=0 time=30184 us cost=1647 size=5 card=1)

********************************************************************************
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       39      0.00       0.01          0          0          0           0
Execute 184248     18.29      18.46          0          0          0           0
Fetch   184276   1013.60    1016.79          0  566243218      92102      184238
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   368563   1031.90    1035.27          0  566243218      92102      184238

 1129  elapsed seconds in trace file.

TIP:
在删除doctor_exam_member表时,检查了他的所有参照表(子表),然后对doctor_exam_member表的每次记录都要去参照表查询是否存在,此时刚好参考表的外键列上并无索引,导致每一行记录都会导致FTS(full table scan),这也是查询v$session_event时偶尔出现latch: CBC (hot block)的原因。

你可能疑问子表数据都delete了为什么还查询这么久?我做个小测试

sys@ORA10GR2>select count(*) from bjhr_dev.DOCTOR_EXAM_RESULT;
COUNT(*)
——————–
0
sys@ORA10GR2>select bytes,blocks from dba_segments where segment_name=’DOCTOR_EXAM_RESULT’ and owner=’BJHR_DEV’;
BYTES BLOCKS
——————– ——————–
50331648 6144

sys@ORA10GR2>set autot trace stat
sys@ORA10GR2>select count(*) from bjhr_dev.DOCTOR_EXAM_RESULT where DOCTOR_EXAM_MEMBER_ID=1;

Statistics
———————————————————-
0 recursive calls
0 db block gets
6040 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

sys@ORA10GR2>alter table bjhr_dev.doctor_exam_result enable row movement;
Table altered.
sys@ORA10GR2>alter table bjhr_dev.DOCTOR_EXAM_RESULT shrink space;
Table altered.
sys@ORA10GR2>alter table bjhr_dev.doctor_exam_result disable row movement;
Table altered.

sys@ORA10GR2>select bytes,blocks from dba_segments where segment_name=’DOCTOR_EXAM_RESULT’ and owner=’BJHR_DEV’;
BYTES BLOCKS
——————– ——————–
196608 24
sys@ORA10GR2>select count(*) from bjhr_dev.DOCTOR_EXAM_RESULT where DOCTOR_EXAM_MEMBER_ID=1;
Statistics
———————————————————-
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

TIP:
FTS查询会遍历表segment 已格式化过所有data block.

Summary:
在建有外键约束的子表列上需要创建索引,对子表全表删除时可以采用truncate 或delete(有外键不能truncate时)后对表进行shrink space操作,或删除父表前对子表的外键约束做Disable.文章来源地址https://www.toymoban.com/news/detail-606323.html

到了这里,关于删除主表 子表外键没有索引的性能优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 优化索引粒度参数提升ClickHouse查询性能

    当对高基数列进行过滤查询时,总是希望尽可能跳过更多的行。否则需要处理更多数据、需要更多资源。ClickHouse缺省在MergeTree表读取8192行数据块,但我们可以在创建表时调整该 index_granularity 参数。本文通过示例说明如何调整该参数优化查询性能。 下面示例,创建表并插入

    2024年02月11日
    浏览(45)
  • PostgreSQL性能调优:优化查询和索引设计

    随着数据量的增长和业务需求的变化,数据库性能成为了许多企业关注的焦点之一。在众多的数据库管理系统中,PostgreSQL因其稳定性和可靠性而备受青睐。然而,即使是最强大的系统也需要合适的调优,以确保其能够高效地处理大规模数据和复杂查询。 本文将介绍如何在P

    2024年02月07日
    浏览(57)
  • MySQL 数据存储和优化------MySQL索引原理和优化 ---- (架构---索引---事务---锁---集群---性能---分库分表---实战---运维)持续更新

    Mysql架构体系全系列文章主目录(进不去说明还没写完) https://blog.csdn.net/grd_java/article/details/123033016 本文只是整个系列笔记的第二章:MySQL索引原理和优化,只解释索引相关概念。 索引可以提高查询效率,影响where查询和order by排序,它可以从多方面进行分类,但是实际创建时

    2024年02月02日
    浏览(54)
  • “更新查询超时时间“——优化ES索引更新性能的方法

    “更新查询超时时间”——优化ES索引更新性能的方法 在实际运用中,Elasticsearch (ES) 索引上的数据不可避免的需要进行更新操作。而update_by_query API 是一个十分强大的ES 更新功能工具,可以应对各种复杂的更新需求。然而,在进行高负载的大数据量操作时,update_by_query 会产生

    2024年02月03日
    浏览(47)
  • 主键、外键、建表范式、MySQL索引、用户管理

    1.1 问题 完成如下练习: 练习主键的创建、查看、删除、添加、验证主键 练习复合主键的使用 练习与auto_increment连用的效果 1.2 方案 主键使用规则: 表头值不允许重复,不允许赋NULL值 一个表中只能有一个primary key 表头 多个表头做主键,称为复合主键,必须一起创建和删除

    2024年01月16日
    浏览(49)
  • 一文彻底搞清楚MySQL的主键、外键、约束和各种索引

    主键用于唯一标识表中每一行数据,外键用于建立表与表之间关联关系,约束用于限制表中数据的规则,索引用于加速查询。 主键是一种用于唯一标识表中每一行数据的标识符。在Mysql中,主键可以是一个或多个列的组合,但是必须满足以下条件: 主键列的值必须唯一,不能

    2024年02月08日
    浏览(51)
  • E往无前|腾讯云大数据ES索引原理剖析及写入性能优化最佳实践

    导读 本文经过大量案例总结和踩坑复盘,归纳整理了Elastisearch集群在写入性能优化方面一些常用的优化技巧和避坑指南。 在我们服务腾讯云ES的客户过程中,经常会收到一些客户对云上ES集群读写性能未能达到预期的反馈,并希望我们能够配合做一些性能压测及调优的工作。

    2024年02月09日
    浏览(51)
  • three.js 场景中如何彻底删除模型和性能优化

    在three.js场景中,要彻底删除外部模型,需要执行以下几个步骤: 从场景中移除模型 你可以使用 scene.remove(model) 或者 scene.remove(model.children[0]) 将模型从场景中移除。如果是多个模型,可以用循环来处理。 移除所有材质和纹理 模型通常会包含材质和纹理,即使你把它们从场景

    2024年02月10日
    浏览(45)
  • MySQL--删除表的外键约束,简单易懂。

    删除表的外键约束 我们曾在第一章中讲过外键的作用,以及如何创建一个表的外键。建立了外键我们就建立起了两张表的关联关系,那如果我想删除主表呢?为了确保数据库的正确性,我们必须先解除两个表之间的关联关系,那就是删除外键约束啦!让我们先来看看删除外键

    2024年04月17日
    浏览(45)
  • 【数据库】如何利用Python中的petl将PostgreSQL中所有表的外键删除,迁移数据,再重建外键

    在数据库管理中,外键是一种重要的约束,用于确保数据的一致性和完整性。然而,在某些情况下,我们可能需要删除或修改外键。本文将介绍如何使用Python中的petl库将PostgreSQL中所有表的外键删除,迁移数据,并重新建立外键。 首先,我们需要安装petl和psycopg2库。在命令行

    2024年02月10日
    浏览(49)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包