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⑹2D9
E:\ORACLE\PRODUCT\10.2.0\ADMIN\BYISDB\UDUMP 的目录
2012-07⑴9 17:58 <DIR> .
2012-07⑴9 17:58 <DIR> ..
2012-07⑴9 17:58 3,057 byisdb_ora_704.trc
2012-07⑴9 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 数据按主键删除慢问题的解决方法
https://www.wanzijz.com/view/61024.html
