回收站(recyclebin)引发row cache lock
版本10.2.0.4 RAC 环境
昨天一哥们QQ发来消息,说有个insert ...语句插入了30分钟还没完成,请求帮忙优化,SQL语句如下:
INSERT INTO Temp_CheckSExit101320166 SELECT KEYCOL, CARDNETWORK, CARDID, EXITSTATION, EXITDATE, TOTALTOLL, PAYMETHOD, DEALSTATUS, FREEKIND, SPLITTOLLINFO, ECARDTYPE, OWNERNUM, 0, NULL, NULL, NULL FROMCHECK_SEXIT201108 WHERE PAYMETHOD = 2 AND CARDNETWORK = 3201 AND SUBSTR(EXITNETWORK, 1, 2) = 32 AND ECARDTYPE = 22 AND EXITDATE <= 20110829 AND validflag = 1 AND ENDFLAG = 0
这个SQL很简单,就是从另外一个表抽取数据到另外一个表,执行计划很简单,是全表扫描,询问得知表CHECK_SEXIT201108 只有0.8G,跑30分钟确实不应该
让他监控等待事件,绝大部分等待是row cache lock
处理问题的方法:
1.由于是insert .... select 没有 sequence,所以sequence因素排除了
2.让他检查 alert.log 搜寻十分有 ROW CACHE LOCK关键字,搜索完毕之后没发现有该关键字,所以ORACLE BUG 也基本排除了
3.查看AWR TOP 5 Timed Events,没有发现ROW CACHE LOCK,所以 row cache lock 竞争也排除了
Top 5 Timed Events
Event | Waits | Time(s) | Avg Wait(ms) | % Total Call Time | Wait Class |
---|---|---|---|---|---|
CPU time | 9,610 | 74.7 | |||
db file sequential read | 660,851 | 2,590 | 4 | 20.1 | User I/O |
db file scattered read | 284,346 | 456 | 2 | 3.5 | User I/O |
gc cr multi block request | 448,707 | 112 | 0 | .9 | Cluster |
gc cr disk read | 296,228 | 97 | 0 | .8 | Cluster |
SQL ordered by Elapsed Time
- Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
- % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s) | CPU Time (s) | Executions | Elap per Exec (s) | % Total DB Time | SQL Id | SQL Module | SQL Text |
---|---|---|---|---|---|---|---|
6,083 | 6,079 | 3,035 | 2.00 | 47.24 | 0hhmdwwgxbw0r | select obj#, type#, flags, ... | |
3,510 | 3,491 | 9,879 | 0.36 | 27.25 | b52m6vduutr8j | delete from RecycleBin$ ... | |
3,445 | 3,377 | 1 | 3444.94 | 26.75 | bx1r0wn35qt1p | PL/SQL Developer | begin -- Call the procedure ... |
3,088 | 3,027 | 0 | 23.97 | fxsxz22x0zha3 | JDBC Connect Client | BEGIN DBMID.PRCSPLITALL ( :V1,... | |
3,074 | 3,019 | 0 | 23.87 | 71dgy4pygdq40 | JDBC Connect Client | INSERT INTO Temp_CheckSExit101... | |
2,109 | 2,071 | 0 | 16.37 | gyvwadw7ruh03 | PL/SQL Developer | INSERT INTO Temp_CheckSExit211... | |
1,546 | 1,526 | 0 | 12.01 | 326g4yt56tq4z | toad.exe | INSERT INTO Temp_CheckSExit2... | |
1,247 | 1,222 | 2 | 623.44 | 9.68 | 7wsvh5zkkcxwp | TOAD 9.7.0.51 | begin DBMID.PRCTRANSDATA('DBMI... |
1,231 | 1,214 | 2 | 615.55 | 9.56 | 5gswufgngjg8r | DataTransServer.exe | INSERT INTO DBMID.TRANS_EXIT20... |
848 | 848 | 2 | 424.16 | 6.59 | c6fzw54pm9ra1 | TOAD 9.7.0.51 | select * from v$access where o... |
620 | 602 | 3 | 206.83 | 4.82 | 16514g0b52g4g | TOAD 9.7.0.51 | begin DBMID.PRCTRANSDATA('DBMI... |
613 | 596 | 3 | 204.29 | 4.76 | b771m0v7ndgz2 | TOAD 9.7.0.51 | INSERT INTO DBMID.TRANS_Entry2... |
507 | 329 | 247 | 2.05 | 3.93 | 0pvtkmrrq8usg | select file#, block# from seg... | |
262 | 199 | 48 | 5.46 | 2.03 | 8nsfam0acu8tn | JDBC Thin Client | BEGIN dbmid.PRCCOSMRECORD(:1, ... |
252 | 169 | 15 | 16.79 | 1.96 | 2n67bkp4cv12t | JDBC Thin Client | BEGIN dbmid.PrcGetCardMFlux(:1... |
160 | 151 | 0 | 1.24 | dbgy2zdz33dbv | TOAD 9.7.0.51 | SELECT * FROM DBMTC.RAW_EXIT20... | |
156 | 156 | 4 | 39.07 | 1.21 | cz6qdwvbvf7wm | toad.exe | select * from v$access where o... |
通过查看TOP SQL,我发现recyclebin居然没有关闭,这实在是不应该,recyclebin通常是需要关闭的,于是让该哥们查看表空间使用率
给了他一个脚本,结果跑半天没结果。。。遇到了library cache lock 由于我不能登录他的数据库,所以建议该哥们关闭回收站,因为我怀疑insert的时候由于表空间几乎满了
导致ORACLE去清空回收站,从而引发row cache lock。果然,当那哥们关闭回收站之后,insert 报错 ORA-01653: unable to extend table.... by 128 in tablespace...
现在已经证明了是回收站引发的row cache lock,那个哥们添加了2个数据文件之后insert 很快,最后让那个哥们purge recyclebin
其实还有个方法可以快速定位是回收站引发的问题,这里就不说了