昨天一哥们QQ发来消息，说有个insert ...语句插入了30分钟还没完成，请求帮忙优化，SQL语句如下：
1. INSERT INTO Temp_CheckSExit101320166
2. SELECT KEYCOL,
3. CARDNETWORK,
4. CARDID,
5. EXITSTATION,
6. EXITDATE,
7. TOTALTOLL,
8. PAYMETHOD,
9. DEALSTATUS,
10. FREEKIND,
11. SPLITTOLLINFO,
12. ECARDTYPE,
13. OWNERNUM,
14. 0,
15. NULL,
16. NULL,
17. NULL FROMCHECK_SEXIT201108
18. WHERE PAYMETHOD = 2
19. AND CARDNETWORK = 3201
20. AND SUBSTR(EXITNETWORK, 1, 2) = 32
21. AND ECARDTYPE = 22
22. AND EXITDATE <= 20110829
23. AND validflag = 1
24. 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
|
|
|
select obj#, type#, flags, ...
|
|
3,510
|
3,491
|
9,879
|
0.36
|
27.25
|
|
delete from RecycleBin$ ...
|
|
3,445
|
3,377
|
1
|
3444.94
|
26.75
|
|
PL/SQL Developer
|
begin -- Call the procedure ...
|
|
3,088
|
3,027
|
0
|
|
23.97
|
|
JDBC Connect Client
|
BEGIN DBMID.PRCSPLITALL ( :V1,...
|
|
3,074
|
3,019
|
0
|
23.87
|
|
JDBC Connect Client
|
INSERT INTO Temp_CheckSExit101...
|
|
2,109
|
2,071
|
0
|
|
16.37
|
|
PL/SQL Developer
|
INSERT INTO Temp_CheckSExit211...
|
|
1,546
|
1,526
|
0
|
|
12.01
|
|
toad.exe
|
INSERT INTO Temp_CheckSExit2...
|
|
1,247
|
1,222
|
2
|
623.44
|
9.68
|
|
TOAD 9.7.0.51
|
begin DBMID.PRCTRANSDATA('DBMI...
|
|
1,231
|
1,214
|
2
|
615.55
|
9.56
|
|
DataTransServer.exe
|
INSERT INTO DBMID.TRANS_EXIT20...
|
|
848
|
848
|
2
|
424.16
|
6.59
|
|
TOAD 9.7.0.51
|
select * from v$access where o...
|
|
620
|
602
|
3
|
206.83
|
4.82
|
|
TOAD 9.7.0.51
|
begin DBMID.PRCTRANSDATA('DBMI...
|
|
613
|
596
|
3
|
204.29
|
4.76
|
|
TOAD 9.7.0.51
|
INSERT INTO DBMID.TRANS_Entry2...
|
|
507
|
329
|
247
|
2.05
|
3.93
|
|
|
select file#, block# from seg...
|
|
262
|
199
|
48
|
5.46
|
2.03
|
|
JDBC Thin Client
|
BEGIN dbmid.PRCCOSMRECORD(:1, ...
|
|
252
|
169
|
15
|
16.79
|
1.96
|
|
JDBC Thin Client
|
BEGIN dbmid.PrcGetCardMFlux(:1...
|
|
160
|
151
|
0
|
|
1.24
|
|
TOAD 9.7.0.51
|
SELECT * FROM DBMTC.RAW_EXIT20...
|
|
156
|
156
|
4
|
39.07
|
1.21
|
|
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
其实还有个方法可以快速定位是回收站引发的问题，这里就不说了
本文转自 vfast_chenxy 51CTO博客，原文链接：http://blog.51cto.com/chenxy/742905，如需转载请自行联系原作者
