• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    oracle 数据按主键删除慢问题的解决方法

    问题描述:

    根据表主键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/traceora11g_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:\ORACLEPRODUCT10.2.0ADMINBYISDBUDUMP

    驱动器 E 中的卷是 DISK1_VOL3

    卷的序列号是 609E-62D9

    E:\ORACLEPRODUCT10.2.0ADMINBYISDBUDUMP 的目录
    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
    ----------
        143
    
    SQL> 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数据库的删除方法详解
    • Oracle删除数据报ORA 02292错误的巧妙解决方法
    • Oracle删除重复的数据,Oracle数据去重复
    • Oracle数据库中的级联查询、级联删除、级联更新操作教程
    • Oracle误删除表数据后的数据恢复详解
    • 彻底删除Oracle数据库的方法
    • oracle查询重复数据和删除重复记录示例分享
    • oracle数据库添加或删除一列的sql语句
    • oracle 批量删除表数据的几种方法
    上一篇:Linux 自动备份oracle数据库详解
    下一篇:Oracle触发器trigger详解
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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