侯体宗的博客
  • 首页
  • Hyperf版
  • beego仿版
  • 人生(杂谈)
  • 技术
  • 关于我
  • 更多分类
    • 文件下载
    • 文字修仙
    • 中国象棋ai
    • 群聊
    • 九宫格抽奖
    • 拼图
    • 消消乐
    • 相册

oracle 数据按主键删除慢问题的解决方法

数据库  /  管理员 发布于 6年前   298

问题描述:

根据表主键id删除一条数据,在PL/SQL上执行commit后执行时间都大于5秒。!!!

问题分析:

需求是删除一个主表A,另有两个附表建有此表的主键ID的外键。删除A表的数据级联删除另两个表的关联数据。增删改查使用hibernate实现。

一开始一直以为是hibernate的内部处理上有关联操作导致的删除和更新数据缓慢。所以将原先使用hibernate的saveOrupdate方法,改查jdbc的
sql语句来处理update和delete数据操作。但是依然没效果!!!
怀疑数据库出问题了!~   

于是拿sql语句在PL/SQL客户端执行,查看执行计划。删除和更新都能使用到索引。但是commit后执行依然很慢! 因此可以判断出是数据库方面的问题。

任何数据库删除一条数据不可能耗费5秒以上的时间啊!那就要查看sql的执行过程了!

网上搜了一堆资料查看。最后确定查看sql执行跟踪文件。 sql执行是一次session,Oracle数据库很好的支持sesion的跟踪,锁表情况等。考虑要操作生

产数据库。不能大量跟踪session。于是选择跟踪指定sesion的方式,只查看自己执行的sql执行计划! 方式如下:

alter session set events='10046 trace name context forever,level 12';  --- 固定语句
delete from t_table1 where id = 23242342;     --- 你要跟踪的sql语句
alter session set events='10046 trace name context off';--- 固定语句

SQL跟踪得到一个trace文件:

通过sql查找存储路径:

select pr.value || '\' || i.instance_name || '_ora_' || to_char(ps.spid) || '.trc' "trace file name" from v$session s, v$process ps, v$parameter pr, v$instance i where s.paddr = ps.addr and s.sid = userenv('sid') and pr.name = 'user_dump_dest';

/home/oracle/DBSoftware/diag/rdbms/ora11g/ora11g/trace\ora11g_ora_42990.trc

然后到服务器上取下trc文件。

打开查看到:

/* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_PORT_REL_AREA"

还有:

4311 /* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_PORT_REL_AREA"4402/*MV_REFRESH (INS) */INSERT /*+ */ INTO "INMS31"."MV_BAND_PORT_REL_AREA"("ID","ACCOUNT_ID","PORT_ID","DEV_IP","PORT_IDEN","AREA_NAME") SELECT "PR"."ID","PR"."ACCOUNT_ID","PR"."PORT_ID","D"."DEV_IP","P"."PORT_IDEN","A"."AREA_NAME" FROM "TB_BAND_USER_PORT_REL" "PR","TB_PORT" "P","TB_DEVICE" "D","TB_AREA" "A" WHERE "PR"."PORT_ID"="P"."ID" AND "P"."DEV_ID"="D"."ID" AND "D"."DEV_MAIN_AREA_ID"="A"."ID" 5309 /* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_FTTH_REL_AREA"5482 /* MV_REFRESH (INS) */INSERT /*+ */ INTO "INMS31"."MV_BAND_FTTH_REL_AREA"("ID","ACCOUNT_ID","ONU_INFO_ID","DEV_IP","ONU_DESC","AREA_NAME") SELECT "PRH"."ID","PRH"."ACCOUNT_ID","PRH"."ONU_INFO_ID","D"."DEV_IP","O"."ONU_DESC","A"."AREA_NAME" FROM "TB_BAND_USER_PORT_REL_FTTH" "PRH","TB_ONU_INFO" "O","TB_DEVICE" "D","TB_AREA" "A" WHERE "PRH"."ONU_INFO_ID"="O"."ID" AND "O"."OLT_ID"="D"."ID" AND "D"."DEV_MAIN_AREA_ID"="A"."ID" 9984 /* MV_REFRESH (DEL) */ delete from "INMS31"."MV_BAND_PORT_REL_AREA"10061 /* MV_REFRESH (INS) */INSERT /*+ */ INTO "INMS31"."MV_BAND_PORT_REL_AREA"("ID","ACCOUNT_ID","PORT_ID","DEV_IP","PORT_IDEN","AREA_NAME") SELECT "PR"."ID","PR"."ACCOUNT_ID","PR"."PORT_ID","D"."DEV_IP","P"."PORT_IDEN","A"."AREA_NAME" FROM "TB_BAND_USER_PORT_REL" "PR","TB_PORT" "P","TB_DEVICE" "D","TB_AREA" "A" WHERE "PR"."PORT_ID"="P"."ID" AND "P"."DEV_ID"="D"."ID" AND "D"."DEV_MAIN_AREA_ID"="A"."ID"

原来在删除之后都有个物化视图的刷新操作!!!

oh. 买噶!  想起在做这个主表的操作时有个物化视图随基表变化而立即刷新的操作!基表有10多万条数据,物化视图关联了多张表。单独刷新也要几秒时间!就是这样原因了!实际现在已经不需要这个物化视图了,所需查询数据已经改成别的方式获取!于是删掉物化视图。执行删除,更新,0.003秒!问题解决!

通过这次问题处理,总结以下教训:

1. 物化视图尽量不要做成立即刷新模式,这样如果基表更新频繁性能问题立马出现。如果确需做物化视图,做成job定时在基表使用闲时执行。

2. 在PL/SQL等客户端执行sql查询基本的数据或删除更新很少数据量而时间超过一秒的就要想法跟踪下sql执行计划了。

3. sql执行计划跟踪采用如下几种方式:

1.首先查看SQL的执行计划,执行计划正常,cost只有4,用到了主键索引

2. 查看等待事件,

3. select * from v$session_wait where sid = 507

4. 查看系统IO,

--------------------------------------

1. 使用 AUTOTRACE 查看执行计划

set autotrace ON | ON EXPLAIN | ON STATISTICS | TRACEONLY | TRACEONLY EXPLAIN
set autotrace OFF

2.  启用 sql_trace 跟踪当前 session

开启会话跟踪:alter session set sql_trace=true;
关闭会话跟踪:alter session set sql_trace=false

3. 启用 10046 事件跟踪当前 session

开启会话跟踪:alter session set events '10046 trace name context forever, level 12';
关闭会话跟踪:alter session set events '10046 trace name context off';
对跟踪文件加标识:alter session set tracefile_identifier='dragon';
SQL> host dir E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\

驱动器 E 中的卷是 DISK1_VOL3

卷的序列号是 609E-62D9

E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP 的目录
2012-07-19  17:58    <DIR>          .
2012-07-19  17:58    <DIR>          ..
2012-07-19  17:58             3,057 byisdb_ora_704.trc
2012-07-19  17:58           169,447 byisdb_ora_704_dragon.trc

   2 个文件        172,504 字节
   2 个目录 22,060,634,112 可用字节

4. 启用 10046 事件跟踪全局 session

这将会对整个系统的性能产生严重的影响,所以一般不建议开启。

开启会话跟踪:alter system set events ‘10046 trace name context forever, level 12';

关闭会话跟踪:alter system set events ‘10046 trace name context off';

获取跟踪文件

SQL> select pr.value || '\' || i.instance_name || '_ora_' || to_char(ps.spid) || '.trc' "trace file name" from v$session s, v$process ps, v$parameter pr, v$instance i where s.paddr = ps.addr and s.sid = userenv('sid') and pr.name = 'user_dump_dest';trace file name--------------------------------------------------------------------------------E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\byisdb_ora_372.trc

5. 使用 Oracle 系统包 DBMS_SYSTEM.SET_EV 跟踪指定 session

PROCEDURE SET_EV

参数名称                       类型                    输入/输出默认值?

------------------------------ ----------------------- ------ --------

 SI                             BINARY_INTEGER          IN

 SE                            BINARY_INTEGER          IN

 EV                            BINARY_INTEGER          IN

 LE                            BINARY_INTEGER          IN

 NM                           VARCHAR2                    IN

参数说明:

SI-指定SESSION的SID;

SE-指定SESSION的SE;

EV-事件ID(如:10046);

LE-表示TRACE的级别;

NM-指定SESSION的username;

SQL> select userenv('sid') sid from dual;    SID----------    143SQL> select sid, serial#, username from v$session where sid=143;    SID  SERIAL# USERNAME---------- ---------- ------------------------------    143    112 UNA_HR

开启会话跟踪:SQL> exec dbms_system.set_ev(143, 112, 10046, 12, '');

关闭会话跟踪:SQL> exec dbms_system.set_ev(143, 112, 10046, 0, '');

6. 使用 TKPROF 工具格式化

tkprof tracefile outputfile [options]

E:\oracle\product\10.2.0\admin\byisdb\udump>tkprof byisdb_ora_704.trc 10046.txt sys=no sort=prsela, exeela, fchela

以上就是小编为大家带来的oracle 数据按主键删除慢问题的解决方法全部内容了,希望大家多多支持~


  • 上一条:
    Oracle触发器trigger详解
    下一条:
    ORACLE实现自定义序列号生成的方法
  • 昵称:

    邮箱:

    0条评论 (评论内容有缓存机制,请悉知!)
    最新最热
    • 分类目录
    • 人生(杂谈)
    • 技术
    • linux
    • Java
    • php
    • 框架(架构)
    • 前端
    • ThinkPHP
    • 数据库
    • 微信(小程序)
    • Laravel
    • Redis
    • Docker
    • Go
    • swoole
    • Windows
    • Python
    • 苹果(mac/ios)
    • 相关文章
    • 分库分表的目的、优缺点及具体实现方式介绍(0个评论)
    • DevDB - 在 VS 代码中直接访问数据库(0个评论)
    • 在ubuntu系统中实现mysql数据存储目录迁移流程步骤(0个评论)
    • 在mysql中使用存储过程批量新增测试数据流程步骤(0个评论)
    • php+mysql数据库批量根据条件快速更新、连表更新sql实现(0个评论)
    • 近期文章
    • 智能合约Solidity学习CryptoZombie第三课:组建僵尸军队(高级Solidity理论)(0个评论)
    • 智能合约Solidity学习CryptoZombie第二课:让你的僵尸猎食(0个评论)
    • 智能合约Solidity学习CryptoZombie第一课:生成一只你的僵尸(0个评论)
    • 在go中实现一个常用的先进先出的缓存淘汰算法示例代码(0个评论)
    • 在go+gin中使用"github.com/skip2/go-qrcode"实现url转二维码功能(0个评论)
    • 在go语言中使用api.geonames.org接口实现根据国际邮政编码获取地址信息功能(1个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf分页文件功能(0个评论)
    • gmail发邮件报错:534 5.7.9 Application-specific password required...解决方案(0个评论)
    • 欧盟关于强迫劳动的规定的官方举报渠道及官方举报网站(0个评论)
    • 在go语言中使用github.com/signintech/gopdf实现生成pdf文件功能(0个评论)
    • 近期评论
    • 122 在

      学历:一种延缓就业设计,生活需求下的权衡之选中评论 工作几年后,报名考研了,到现在还没认真学习备考,迷茫中。作为一名北漂互联网打工人..
    • 123 在

      Clash for Windows作者删库跑路了,github已404中评论 按理说只要你在国内,所有的流量进出都在监控范围内,不管你怎么隐藏也没用,想搞你分..
    • 原梓番博客 在

      在Laravel框架中使用模型Model分表最简单的方法中评论 好久好久都没看友情链接申请了,今天刚看,已经添加。..
    • 博主 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 @1111老铁这个不行了,可以看看近期评论的其他文章..
    • 1111 在

      佛跳墙vpn软件不会用?上不了网?佛跳墙vpn常见问题以及解决办法中评论 网站不能打开,博主百忙中能否发个APP下载链接,佛跳墙或极光..
    • 2017-06
    • 2017-08
    • 2017-09
    • 2017-10
    • 2017-11
    • 2018-01
    • 2018-05
    • 2018-10
    • 2018-11
    • 2020-02
    • 2020-03
    • 2020-04
    • 2020-05
    • 2020-06
    • 2020-07
    • 2020-08
    • 2020-09
    • 2021-02
    • 2021-04
    • 2021-07
    • 2021-08
    • 2021-11
    • 2021-12
    • 2022-02
    • 2022-03
    • 2022-05
    • 2022-06
    • 2022-07
    • 2022-08
    • 2022-09
    • 2022-10
    • 2022-11
    • 2022-12
    • 2023-01
    • 2023-03
    • 2023-04
    • 2023-05
    • 2023-07
    • 2023-08
    • 2023-10
    • 2023-11
    • 2023-12
    • 2024-01
    • 2024-03
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客