转 db_file_multiblock_read_count
http://www.laoxiong.net/table_scan_and_buffer_cache.html
全表扫描与buffer cache
https://www.cnblogs.com/Richardzhu/archive/2013/01/23/2872587.html
通过文档知道:
1.理论上,最大db_file_multiblock_read_count和系统IO能力应该有如下关系:
Max(db_file_multiblock_read_count) = MaxOsIOsize/db_block_size
当然这个Max(db_file_multiblock_read_count)还要受Oracle的限制。
2.DB_FILE_MULTIBLOCK_READ_COUNT (数据库方面)
能否提高I/O 通道,由以下渠道的每个extent 大小(DB_FILE_MULTIBLOCK_READ_COUNT 一次不能跨越extent 读),如果是DB_FILE_MULTIBLOCK_READ_COUNT *(block_size)的整数倍,那么就是能有效的减少I/O 读写。
情况1:比如extent 由50个block 组成,DB_FILE_MULTIBLOCK_READ_COUNT =16 ,那么需要读 完整的extent 应该是 3次*16+1次*2 (共4次)共50个blocks。
情况2: 比如extent 由80个block 组成,DB_FILE_MULTIBLOCK_READ_COUNT =16 ,那么需要读 完整的extent 应该是 5次*16 共80个blocks。
如果同样的大小的表(400 blocks),情况1 一共需要 4*8个extent =32 个IO
如果同样的大小的表(400 blocks),情况2一共需要 5*5个extent=25 个IO (更节约资源)
所以结论如下: 如果 单个exent 的block 数目 是DB_FILE_MULTIBLOCK_READ_COUNT的整数倍,更加提升I/O 性能。
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FILE_MULTIBLOCK_READ_COUNT And Extents Management. ----------------------------------------------------- The Parameter 'db_file_multiblock_read_count' specifies the number of Oracle Blocks to be read in Single I/O Operation. DB_FILE_MULTIBLOCK_READ_COUNT should generally be made as large as possible. The value differs between platforms and versions, and usually depends on the settings of DB_BLOCK_SIZE and the maximum OS I/O Size. EXTENT SIZING =============== This parameter is normally used for multiblock reads for Full Table Scans and Fast Full Index Scans and normally can be used with CBO. The Oracle Extent Management can be tuned with this Parameter to get optimal benefit for I/O operation. Normally it is recommended that the entire Segment should be contained in Single Extent so that Full Table Scan or Fast Full Index Scan just have to traverse a single Extent. This will contribute to fast Query Operations.But as such, it is not a must.Even if the Segment is spread over Hundred or Thousands Of Extents and if the extents are of even size with proper ‘db_file_multiblock_read_count’ defined, the Full Table Scan or Fast Full Index Scan still can improve performance. The following Example will illustrate the above-mentioned fact: Suppose a Table X currently occupies eight extents with the HWM (High Water Mark) in the eight extents. Each extent is evenly sized with 50blocks (db_block_size = 8K and Extent size 400k). The db_file_multiblock_read_count is set to 16. ------------------------------------------------------------------------- | | | | | | | | | ------ ------ ------ -------- -------- ---------- -------- <---HWM---> | | | | | | | | | ------------------------------------------------------------------------- E1 E2 E3 E4 E5 E6 E7 E8 <-------------------- 50 blocks per extent ----------------------------> Here E1 - E8 represents Extents each with 50 blocks. The HWM is in 389 Block. So 10 blocks are above the HWM since the first block is the Segment header block and total 400 blocks. The Multiblock I/O never spans Extent Boundaries even if the Extents are contiguous and also if using Locally Managed Tablespaces. Assuming that none of the above blocks are in the Buffer Cache if we do Full Table Scan, then total 31 i/o operations will be required. Since each extent comprises of 50 blocks and 16 blocks read in single I/O operation. This means that 48-blocks/per extents will be read in 3 I/O operations whereas for the last remaining blocks one extra I/O has to be performed, in all making 4 I/O calls per Extent.This amounts to 28 I/O operations up to 7th Extent. The 8th Extent has 10 blocks above the HWM, so there will be only 3 i/o operations in the 8th Extent. (32 blocks in 2 i/o’s and remaining 8 blocks in 1 i/o ) *** The Extents size should always be a Multiple of db_file_multiblock_read_count **** The Extents size should always be a Multiple of db_file_multiblock_read_count to take the advantage of Full Table Scans and Fast Full Index Scans. IF the extents of the Table X are made multiple of db_file_multiblock_read_count (80 blocks per extent), then only 5 Extents are required to manage the entire segment instead of 8 extents. Also since the extents are multiple of db_file_multiblock_read_count, the I/O operations are reduced from 31 to 25. There will only be 5 i/o operations per extent resulting in Total 25 I/O calls. Since each extent comprises of 80 blocks and multiblock_read_count=16, (16 * 5 = 80) each extent will require 5 I/O operations. ------------------------------------------- | | | | | | ------- ------- ------- -------- --------- | | | | | | ------- ------- ------- -------- ---------- | | | | |<--HWM-->| -------------------------------------------- E1 E2 E3 E4 E5 <----------- 80 blocks/Extent ------------> Thus the i/o operations are reduced from 31 to 25 just by proper management of Extents. If the Extents size are a Multiple of db_file_multiblock_read_count, then i/o operations required will be less compared to when the Extents are not Multiple of db_file_multiblock_read_count. Here, reducing the number of Extents have not reduced the I/O Calls but the multiple factor of Blocks per Extent with respect to Multiblock Read, have contributed to lessen the I/O calls. Hence, even if the Segment comprises of Multiple Extents and if the extent size is a multiple of db_file_multiblock_read_count, the FTS will be faster. Remember that even if the Table X was composed of Single Extent of 400 blocks, it would take 25 i/o’s to read up to FTS.
As explained in the Oracle Documentation:
12c Release 1 (12.1)
E17615-18
DB_FILE_MULTIBLOCK_READ_COUNT
http://st-doc.us.oracle.com/12/121/server.121/e17615/refrn10037.htm#REFRN10037
DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.
The default value of this parameter is a value that corresponds to the maximum I/O size that can be performed efficiently. This value is platform-dependent and is 1MB for most platforms.
Because the parameter is expressed in blocks, it will be set to a value that is equal to the maximum I/O size that can be performed efficiently divided by the standard block size. Note that if the number of sessions is extremely large the multiblock read count value is decreased to avoid the buffer cache getting flooded with too many table scan buffers.
Even though the default value may be a large value, the optimizer will not favor large I/Os if you do not set this parameter. It will favor large I/Os only if you explicitly set this parameter to a large value.
Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.
The maximum value is the operating system's maximum I/O size expressed as Oracle blocks ((max I/O size)/DB_BLOCK_SIZE). If you set this parameter to a value greater than the maximum, then Oracle uses the maximum.
From Oracle 10gR2, DB_FILE_MULTIBLOCK_READ_COUNT is automatically set depending on:
- operating system optimal I/O size
- the size of the buffer cache
- the maximum number of sessions
The formula for calculating DB_FILE_MULTIBLOCK_READ_COUNT is calculated by hidden parameter _db_file_optimizer_read_count. db_file_optimizer_read_count equals db_file_multiblock_read_count when the latter is explicitly set, but equals 8 when not. Review following note:
Document 1106073.1 What is the difference between '_db_file_optimizer_read_count' and 'db_file_multiblock_read_count'?
As stated above, even if the value calculated by Oracle is large, the optimizer will not favour large full table scans unless DB_FILE_MULTIBLOCK_READ_COUNT is explicitly set to a large value.
If set, the behavior is consistent to behavior prior to 10gR2.
DB_FILE_MULTIBLOCK_READ_COUNT can be unset by following the information in the following note:
Without WORKLOAD stats, CBO computes multiblock reads as:
io_cost = ceil(blocks * 0.5965/_db_file_optimizer_read_count0.6582)
(The NOWORKLOAD case, the following are used in the formula:
MBRC = _db_file_optimizer_read_count (if system stat MBRC is not gathered)
SREADTIM = IOSEEKTIM + db_block_size/IOTFRSPEED
MREADTIM = IOSEEKTIM + MBRC * db_block_size/IOTFRSPEED))
NOTE: In 10g, CPU costing is always used unless explicitly disabled, so this formula is almost never used. In 9i (or if Optimizer feature enabled is set to 9.x), CPU costing is used only when WORKLOAD stats are gathered.
With WORKLOAD stats, then:
io_cost = max(1, ceil(blocks/MBRC * MREADTIM/SREADTIM))(The workload stats come straight from the WORKLOAD system statistics)
相关文章
- DVWA之File Inclusion(文件包含)
- Cannot read configuration file due to insufficient permissions
- Java File文件操作 创建文件目录,删除文件目录
- 【JAVA】通过File类操作文件夹(目录创建、删除)和文本文件(增/删/改/查)
- BASH: read file to stdout
- 转 UTL_FILE Throws ORA-29284 Or ORA-29283 When Attempting To READ File
- bin/sh^M: bad interpreter: No such file or directory解决
- Csharp: play media file using Windows Media Player
- Java里的IO基础知识笔记:IO流、字节流/字符流、File对象读取、输入流/输出流(使用过后及时关闭、缓冲区)、Filter模式、ZIP操作、读取classpath资源的意义、序列化/反序列化、Reader/Writer、使用Files工具类及其局限性
- 何时会发生db file sequential read等待事件?
- IAR出现错误:get alternative file 解决办法
- iOS开发 - Storyboard file 'MainStoryboard_iPad~ipad.storyboardc' was not found
- Write cv::Mat to a file
- How to Read, Write XLSX File in Java - Apach POI Example---reference
- 【Python】xlrd读取文件操作异常:XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+‘; not supported‘) xlrd.biffh.X
- 版本控制工具 Git SourceTree 报错 fatal: could not read Username for 'https://gitee.com': No such file or directory