Oracle系统SQL消耗大量资源(bsa0wjtftg3uw)
top sql第一条是select file# from file$ where ts#=:1
现象:
客户反馈AWR中TOPSQL第一条为系统SQL:select file# from file$ where ts#=:1。
客户的系统是比较繁忙的系统,该AWR报告取样自业务高峰期。
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Release RAC Host
EDI 2695423743 EDI 1 10.2.0.2.0 NO dssdb01
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 32286 20-May-13 07:49:14 205 65.8
End Snap: 32287 20-May-13 08:54:47 210 72.1
Elapsed: 65.55 (mins)
DB Time: 1,375.64 (mins)
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
PX Deq Credit: send blkd 215,620 24,567 114 29.8 Other
CPU time 14,962 18.1
enq: CF - contention 147,525 10,324 70 12.5 Other
db file sequential read 1,472,843 7,988 5 9.7 User I/O
log file sync 119,120 7,308 61 8.9 Commit
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
123,784,076 143,834 860.60 33.58 988.01 1023.40 bsa0wjtftg3uw select file# from file$ where ... <<<<<<<<<<<<<<<< Here!
bsa0wjtftg3uw ==> select file# from file$ where ts#=:1
该SQL在一个小时时间内执行了1023秒。
分析:
在metalink上发现有几个相关的BUG,但版本对应不上,不过根据BUG的说明信息我们可以窥得Oracle处理文件方面的一些内部机制。
1.Bug 14309390 - High CPU usage / Mutex Contention with Recursive statement on FILE$ (Doc ID 14309390.8)
Range: Versions >= 11.2 but BELOW 12.1
Recursive statement SQLID bsa0wjtftg3uw 'select file# from file$ where ts#=:1'
which is executed during tablespace operations can cause a high mutex contention / high CPU usage
2.Bug 13520452 -
top sql第一条是select file# from file$ where ts#=:1
现象:
客户反馈AWR中TOPSQL第一条为系统SQL:select file# from file$ where ts#=:1。
客户的系统是比较繁忙的系统,该AWR报告取样自业务高峰期。
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Release RAC Host
EDI 2695423743 EDI 1 10.2.0.2.0 NO dssdb01
Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 32286 20-May-13 07:49:14 205 65.8
End Snap: 32287 20-May-13 08:54:47 210 72.1
Elapsed: 65.55 (mins)
DB Time: 1,375.64 (mins)
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
PX Deq Credit: send blkd 215,620 24,567 114 29.8 Other
CPU time 14,962 18.1
enq: CF - contention 147,525 10,324 70 12.5 Other
db file sequential read 1,472,843 7,988 5 9.7 User I/O
log file sync 119,120 7,308 61 8.9 Commit
SQL ordered by Gets
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
123,784,076 143,834 860.60 33.58 988.01 1023.40 bsa0wjtftg3uw select file# from file$ where ... <<<<<<<<<<<<<<<< Here!
bsa0wjtftg3uw ==> select file# from file$ where ts#=:1
该SQL在一个小时时间内执行了1023秒。
分析:
在metalink上发现有几个相关的BUG,但版本对应不上,不过根据BUG的说明信息我们可以窥得Oracle处理文件方面的一些内部机制。
1.Bug 14309390 - High CPU usage / Mutex Contention with Recursive statement on FILE$ (Doc ID 14309390.8)
Range: Versions >= 11.2 but BELOW 12.1
Recursive statement SQLID bsa0wjtftg3uw 'select file# from file$ where ts#=:1'
which is executed during tablespace operations can cause a high mutex contention / high CPU usage
2.Bug 13520452 -