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执行计划！方式如下：
altersessionsetevents='10046tracenamecontextforever,level12'; ---固定语句
deletefromt_table1whereid=23242342; ---你要跟踪的sql语句
altersessionsetevents='10046tracenamecontextoff';---固定语句
SQL跟踪得到一个trace文件：
通过sql查找存储路径：
selectpr.value||'\'||i.instance_name||'_ora_'||to_char(ps.spid)
||'.trc'"tracefilename"fromv$sessions,v$processps,v$parameterpr,v$instancei
wheres.paddr=ps.addrands.sid=userenv('sid')andpr.name='user_dump_dest';
/home/oracle/DBSoftware/diag/rdbms/ora11g/ora11g/trace\ora11g_ora_42990.trc
然后到服务器上取下trc文件。
打开查看到：
/*MV_REFRESH(DEL)*/deletefrom"INMS31"."MV_BAND_PORT_REL_AREA"
还有：
4311/*MV_REFRESH(DEL)*/deletefrom"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)*/deletefrom"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)*/deletefrom"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*fromv$session_waitwheresid=507
4.查看系统IO，
--------------------------------------
1.使用AUTOTRACE查看执行计划
setautotraceON|ONEXPLAIN|ONSTATISTICS|TRACEONLY|TRACEONLYEXPLAIN
setautotraceOFF
2. 启用sql_trace跟踪当前session
开启会话跟踪：altersessionsetsql_trace=true;
关闭会话跟踪：altersessionsetsql_trace=false
3.启用10046事件跟踪当前session
开启会话跟踪：altersessionsetevents'10046tracenamecontextforever,level12';
关闭会话跟踪：altersessionsetevents'10046tracenamecontextoff';
对跟踪文件加标识：altersessionsettracefile_identifier='dragon';
SQL>hostdirE:\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,057byisdb_ora_704.trc
2012-07-19 17:58 169,447byisdb_ora_704_dragon.trc
2个文件 172,504字节
2个目录22,060,634,112可用字节
4.启用10046事件跟踪全局session
这将会对整个系统的性能产生严重的影响，所以一般不建议开启。
开启会话跟踪：altersystemsetevents‘10046tracenamecontextforever,level12';
关闭会话跟踪：altersystemsetevents‘10046tracenamecontextoff';
获取跟踪文件
SQL>selectpr.value||'\'||i.instance_name||'_ora_'||to_char(ps.spid)
||'.trc'"tracefilename"fromv$sessions,v$processps,v$parameterpr,v$instancei
wheres.paddr=ps.addrands.sid=userenv('sid')andpr.name='user_dump_dest';
tracefilename
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP\byisdb_ora_372.trc
5.使用Oracle系统包DBMS_SYSTEM.SET_EV跟踪指定session
PROCEDURESET_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>selectuserenv('sid')sidfromdual;
SID
----------
143
SQL>selectsid,serial#,usernamefromv$sessionwheresid=143;
SIDSERIAL#USERNAME
--------------------------------------------------
143112UNA_HR
开启会话跟踪：SQL>execdbms_system.set_ev(143,112,10046,12,'');
关闭会话跟踪：SQL>execdbms_system.set_ev(143,112,10046,0,'');
6.使用TKPROF工具格式化
tkproftracefileoutputfile[options]
E:\oracle\product\10.2.0\admin\byisdb\udump>tkprofbyisdb_ora_704.trc10046.txtsys=nosort=prsela,exeela,fchela
以上就是小编为大家带来的oracle数据按主键删除慢问题的解决方法全部内容了，希望大家多多支持毛票票~