考试首页 | 考试用书 | 培训课程 | 模拟考场 | 考试论坛  
  当前位置:编程开发 > ORACLE > 文章内容
  

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

 [ 2017年6月28日 ] 【

  问题描述:

  根据表主键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查找存储路径:

  ?

1
2
3
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"

  还有:

  ?

1
2
3
4
5
6
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

首页 1 2 尾页
本文纠错】【告诉好友】【打印此文】【返回顶部
将考试网添加到收藏夹 | 每次上网自动访问考试网 | 复制本页地址,传给QQ/MSN上的好友 | 申请链接 | 意见留言 TOP
关于本站  网站声明  广告服务  联系方式  站内导航  考试论坛
Copyright © 2007-2013 中华考试网(Examw.com) All Rights Reserved