Oracle有效地使用块(2)
Extents
An extent is a logical unit of database storage space allocation consisting of a number of contiguous data blocks. One or more extents make up a segment. When the existing space in a segment is completely used, the Oracle server allocates a new extent for the segment
Advantages of Large Extents
- Large extents avoid dynamic extent allocation, because segments with larger extents are less likely to need to be extended
-
Larger extents can have a small performance benefit because the Oracle server can read one large extent from disk with fewer multi-block reads than would be required to read many small extents.By matching extent sizes to the I/O and space allocation sizes, the performance cost of having many extents in a segment is minimized. However,for a table that never has a full table scan operation, it makes no difference in terms of query performance whether the table has one extent or multiple extents.
-
Extent maps list all the extents for a certain segment. When MAXEXTENTSis set to UNLIMITED, these maps are in multiple blocks. For best performance, you should be able to read the extent map with a single I/O. Performance degradesif multiple I/Os are necessary for a full table scan to get the extent map. Also, a large number of extents can degrade data dictionary performance, becauseeach extent uses space in the dictionary cache.
Disadvantages of Large Extents
- Large extents require more contiguous blocks, therefore the Oracle server may have difficulty finding enough contiguous space to store them.
- The DBA sizes the segment to allow for growth, so some ofthe space allocated to the segment will not be used initially.
测试 db_file_multiblock_read_coun 与 extent 对IO影响 ：
1.db_file_multiblock_read_coun=16 extent=128(最大值)
1 SQL> create table t as select * from dba_objects; 2 3 Table created. 4 5 SQL> insert into t select * from t; 6 7 128607 rows created. 8 9 SQL> commit; 10 11 Commit complete. 12 13 SQL> 14 15 16 SQL> select file_id,extent_id,block_id,blocks from dba_extents 17 2 where owner='SYS' and segment_name='T'; 18 --file_id文件中extent所包含block的数量 19 FILE_ID EXTENT_ID BLOCK_ID BLOCKS 20 ---------- ---------- ---------- ---------- 21 1 0 135977 8 22 1 1 135985 8 23 1 2 135993 8 24 1 3 136001 8 25 1 4 136009 8 26 1 5 136017 8 27 1 6 136025 8 28 1 7 136033 8 29 1 8 136041 8 30 1 9 136049 8 31 1 10 136057 8 32 33 FILE_ID EXTENT_ID BLOCK_ID BLOCKS 34 ---------- ---------- ---------- ---------- 35 1 11 136065 8 36 1 12 137609 8 37 1 13 137617 8 38 1 14 137625 8 39 1 15 137633 8 40 1 16 137737 128 41 1 17 137865 128 42 1 18 137993 128 43 1 19 138121 128 44 1 20 138249 128 45 1 21 138377 128 46 47 FILE_ID EXTENT_ID BLOCK_ID BLOCKS 48 ---------- ---------- ---------- ---------- 49 1 22 138505 128 50 1 23 138633 128 51 1 24 138761 128 52 1 25 138889 128 53 1 26 139017 128 54 1 27 139145 128 55 1 28 139273 128 56 1 29 139401 128 57 1 30 139529 128 58 1 31 139657 128 59 1 32 139785 128 60 61 FILE_ID EXTENT_ID BLOCK_ID BLOCKS 62 ---------- ---------- ---------- ---------- 63 1 33 139913 128 64 1 34 140041 128 65 1 35 140169 128 66 1 36 140297 128 67 1 37 140425 128 68 1 38 140553 128 69 1 39 140681 128 70 1 40 140809 128 71 1 41 140937 128 72 1 42 141065 128 73 1 43 141193 128 74 75 FILE_ID EXTENT_ID BLOCK_ID BLOCKS 76 ---------- ---------- ---------- ---------- 77 1 44 141321 128 78 1 45 141449 128
注：自己手动创建表空间时，extent的大小最好统一（uniform size）
--手动设置db_file_multiblock_read_count值
1 SQL> alter session set db_file_multiblock_read_count=16; 2 --每次读取16个块 3 --The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). 4 --If you set this parameter to a value greater than the maximum, Oracle uses the maximum.
--查看测试结果
1 SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'; 2 3 Session altered. 4 5 SQL> select count(*) from t; 6 7 COUNT(*) 8 ---------- 9 257214 10 11 SQL> @$ORACLE_HOME/trace.sql 12 13 TRACE_FILE_NAME 14 -------------------------------------------------------------------------------- 15 /opt/oracle/diag/rdbms/nwom/nwom/trace/nwom_ora_3435.trc 16 17 SQL> !more /opt/oracle/diag/rdbms/nwom/nwom/trace/nwom_ora_3435.trc 18 PARSING IN CURSOR #47971673691112 len=22 dep=0 uid=0 oct=3 lid=0 tim=1361937403231126 hv=2763161912 ad='cb85fc18' sqlid='cyzznbykb509s' 19 select count(*) from t 20 END OF STMT 21 PARSE #47971673691112:c=10999,e=17498,p=332,cr=73,cu=0,mis=1,r=0,dep=0,og=1,plh=2966233522,tim=1361937403231125 22 EXEC #47971673691112:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2966233522,tim=1361937403231214 23 WAIT #47971673691112: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361937403231247 24 WAIT #47971673691112: nam='direct path read' ela= 46 file number=1 first dba=135978 block cnt=6 obj#=254332 tim=1361937403231560 25 ....... 26 WAIT #47971673691112: nam='direct path read' ela= 117 file number=1 first dba=136032 block cnt=16 obj#=254332 tim=1361937403232361 27 WAIT #47971673691112: nam='direct path read' ela= 65 file number=1 first dba=136048 block cnt=16 obj#=254332 tim=1361937403232577 28 WAIT #47971673691112: nam='direct path read' ela= 40 file number=1 first dba=136064 block cnt=9 obj#=254332 tim=1361937403232743 29 WAIT #47971673691112: nam='direct path read' ela= 31 file number=1 first dba=137609 block cnt=7 obj#=254332 30 ........ 31 file number：文件号 32 first dba：数据块block_id 33 block cnt: 一次读取数据块的数量 34 35 上述测试 一次读block数最大为16。此时参数db_file_multiblock_read_count=16；一个extent包含block最多为128
2.db_file_multiblock_read_count=512 extent=128(最大值)
--改变db_file_multiblock_read_count大小
1 SQL> alter session set db_file_multiblock_read_count=512; 2 3 Session altered. 4 5 SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER,LEVEL 12'; 6 7 Session altered. 8 9 SQL> select count(*) from t01; 10 11 COUNT(*) 12 ---------- 13 385821 14 15 SQL> @$ORACLE_HOME/trace.sql 16 17 TRACE_FILE_NAME 18 -------------------------------------------------------------------------------- 19 /opt/oracle/diag/rdbms/nwom/nwom/trace/nwom_ora_6783.trc 20 21 SQL>！more /opt/oracle/diag/rdbms/nwom/nwom/trace/nwom_ora_6783.trc 22 23 --查看trace日志 24 PARSING IN CURSOR #47004310211592 len=24 dep=0 uid=0 oct=3 lid=0 tim=1361954062660602 hv=4036868481 ad='cb5e8510' sqlid='f9ny9vbs9vfc1' 25 select count(*) from t01 26 END OF STMT 27 PARSE #47004310211592:c=11997,e=11414,p=192,cr=88,cu=0,mis=1,r=0,dep=0,og=1,plh=705462345,tim=1361954062660600 28 EXEC #47004310211592:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=705462345,tim=1361954062660688 29 WAIT #47004310211592: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1361954062660719 30 WAIT #47004310211592: nam='db file scattered read' ela= 131 file#=1 block#=137642 blocks=7 obj#=254343 tim=1361954062661029 31 WAIT #47004310211592: nam='db file scattered read' ela= 157 file#=1 block#=137649 blocks=8 obj#=254343 tim=1361954062661322 32 ...... 33 WAIT #47004310211592: nam='db file scattered read' ela= 762 file#=1 block#=143197 blocks=44 obj#=254343 tim=1361954062710267 34 WAIT #47004310211592: nam='db file scattered read' ela= 2364 file#=1 block#=143241 blocks=128 obj#=254343 tim=1361954062713521 35 WAIT #47004310211592: nam='db file scattered read' ela= 2130 file#=1 block#=143369 blocks=128 obj#=254343 tim=1361954062717127 36 ...... 37 38 file number：文件号 39 first dba：数据块block_id 40 block cnt: 一次读取数据块的数量 41 42 测试 一次读block数最大为128。此时参数db_file_multiblock_read_count=512；一个extent包含block最多为128。通过两次测试得出ora的物理IO最大取决于db_file_multiblock_read_count与extent中的最小值
推论：在OS最大IO（通常为1M，可以通过dd自行测试）范围内，db的IO最大不会超过extent的值。
