某日，在一个数据库中，发现大量buffer busy wait的进程，经检查，是监控等查询表空间使用率的进程处于buffer busy wait。而其blocking session wnnnn进程。
(1)看到监控程序被1013进程阻塞，blocking session是1013
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> select x.BLOCKING_SESSION,x.event,x.program,x.* from v$session x 2 where event like '%buffer%' 3 / BLOCKING_SESSION EVENT PROGRAM ---------------- ---------------------------------------------------------------- --------------------------------- 1013 buffer busy waits JDBC Thin Client 1013 buffer busy waits JDBC Thin Client 1013 buffer busy waits oracle@cnsh281019 (J000) 1013 buffer busy waits oracle@cnsz081014 (TNS V1-V3) SQL> |
看到这个等待的file id是5 block id是2
|
1 2 3 4 5 6 |
P1TEXT P1 P1RAW P2TEXT P2 ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- file# 5 0000000000000005 block# 2 file# 5 0000000000000005 block# 2 file# 5 0000000000000005 block# 2 file# 5 0000000000000005 block# 2 |
但是这个file id和block id上是没有对象的：
|
1 2 3 4 5 6 7 |
SQL> select * from dba_extents y where y.relative_fno=5 and 2 between block_id and block_id+blocks-1 2 / OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------ SQL> |
File id为5 的文件为：
|
1 2 3 4 5 |
SQL> select fs.name,ts.name,(fs.bytes)/1024/1024/1024 size_gb,blocks from v$datafile fs,v$tablespace ts where file#=5 and ts.ts#=fs.ts#; NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14895.5078 976192000 |
注意这里已经是一个很大的文件了，有15T。
我们进一步发现blocking session 1013是ora_w005进程，该进程是有SMCO进程spawn出来的子进程，SMCO进程是11g的新进程，用于自动扩展或回收空间。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
* SMCO (space management coordinator) process coordinates the execution of various space management related tasks, such as proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task. What is the function of SMCO background process SMCO coordinates the following space management tasks. It performs proactive space allocation and space reclamation. It dynamically spawns slave processes (Wnnn) to implement the task. ● Tablespace-level space (Extent) pre-allocation. Pre-allocation here refers to datafile extention, Datafile extension happens when a space request (extent allocation) operation generally triggered via Insert / loading to a segment does not find contiguous space in the tablespace, the session will extend the file by the next increment set for the datafile and will continue with the space request or Extent allocation. For SMCO to autoextend a datafile, the AUTOEXTEND should be set to ON for the datafile. SMCO decides to expand the tablespace based on history, extension is split evenly across all datafiles in the tablespace which have not reached their maxsize and are still limited to 10% of the full tablespace size in one hourly SMCO wakeup. (Full tablespace size = Sum of datafile sizes at any given instance of time.) Apart from the above mentioned task, the SMCO process is also responsible for performing the following tasks. ● updating block and extents counts in SEG$ for locally managed tablespaces after adding an extent (from unpublished Bug 12940620) ● Securefile lob segment pre-extension. ● Securefile lob segment in-memory dispenser space pre-allocation. ● Securefile lob segment space reclamation (moving free chunks from uncommitted free space area to committed free space area). ● Temporary segment space reclamation. |
发现此进程当前还在不断的工作，不断的增加表空间：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SQL> select fs.name,ts.name,(fs.bytes)/1024/1024/1024 size_gb,blocks from v$datafile fs,v$tablespace ts where file#=5 and ts.ts#=fs.ts#; NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14895.5078 976192000 SQL> / NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14895.6054 976198400 SQL> / NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14895.7031 976204800 SQL> / NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14895.8007 976211200 SQL> / NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14895.8007 976211200 SQL> / NAME NAME SIZE_GB BLOCKS -------------------------------------------------------------------------------- ------------------------------ ---------- ---------- +DATA_myfavodb_DG/myfavodb/datafile/nolapdata.258.818069485 NOLAPDATA 14900.3906 976512000 SQL> |
因此在空间还没分配完成之前，监控程序查询dba free space会挂住，因此当期还无法统计该表空间的free的大小。
解决办法：
建议可以disable SMCO的自动扩展空间功能，不自动预先分配extent，恢复成和10g，9i一样的功能。
|
1 2 3 |
SMCO (Space Management Coordinator) For Autoextend On Datafiles And How To Disable/Enable (Doc ID 743773.1) It can be turned off by setting "_enable_space_preallocation"=0 as below: ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0; |
该语句可以不重启数据库动态操作。
另外，对于一个表空间15T的一个数据文件，是在太大，建议还是用一般的表空间，分散到多个数据文件上。之前在另外一个客户exadata项目上遇到过使用bigfile的temp tablespace的性能问题，打散后解决。