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'
2.Bug 13520452 - Recursive statement on FILE$ causes a huge workload - superseded (Doc ID 13520452.8)
Recursive statement 'select file# from file$ where ts#=:1' which is executed
inside the tbsfnl() function can cause high workload.
Rediscovery Notes:
High workload due to 'select file# from file$ where ts#=:1'
The select shows a FULL scan of FILE$
3.Bug 13520452 : RECURSIVE STATEMENT ON FILE$ CAUSES A HUGE WORKLOAD
INTERNAL PROBLEM DESCRIPTION:
@The statement 'select file# from file$ where ts#=:1' is executed inside tbsfnl
@ function while performing tablespace alter operations, to fetch the file info
@rmation corresponding to the tablespace. tbsfnl is also called while adding ex
@tent where we choose the best file to allocate space for extent, once per each
@ extent allocation. As the number of files inside tablespace increase, executi
@on of the above statement may impact performance.
<===这段说明了该SQL一般在tbsfnl函数中被执行（执行alter tablespace操作时），在为段分配extent时也会执行以选择适合的文件创建extent
@INTERNAL FIX DESCRIPTION:
@File information per tablespace is maintained by Recovery(rcv) layer in SGA. W
@e are fetching file list per tablespace using the cached information.
<===在11.2.0.3，Oracle提供patch包修复这个BUG，修复之后不需要再执行SQL bsa0wjtftg3uw，而是将数据文件信息直接缓存起来，通过直接查看缓存信息获取数据文件信息，此举避免了上述系统SQL的大量执行。
REDISCOVERY INFORMATION:
High workload due to 'select file# from file$ where ts#=:1'
Q:Which program generated below recursive statements and takes lots of buffer gets
bsa0wjtftg3uw ==> select file# from file$ where ts#=:1
A:
'bsa0wjtftg3uw' is an oracle internal recursive SQL, which is executed during tablespace operations.
The statement 'select file# from file$ where ts#=:1' is executed inside tbsfnl function while performing tablespace alter operations, to fetch the file information corresponding to the tablespace. tbsfnl is also called while adding extent where we choose the best file to allocate space for extent, once per each extent allocation. As the number of files inside tablespace increase, execution of the above statement may impact performance.
Disable autoexetend can do some help to reduce the SQL, but operation like creating table also need to query the tablespace inf