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/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
----------
    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