How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? (Doc ID 1398860.1)
来源:互联网 发布:php动态网站开发 答案 编辑:程序博客网 时间:2024/05/17 22:24
In this Document
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform.
GOAL
This document illustrate how Oracle calculate DB_FILE_MULTIBLOCK_READ_COUNT and the internal formula used.
SOLUTION
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:
Note: Max I/O size is platform-dependent and equal to 1MB for most platforms
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.
It is recommended to leave the value unset so that Oracle will compute the value.
If set, the behaviour is consistent to behaviour 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 = blocks/(1.6765 * db_file_multiblock_read_count 0.6581 )
With WORKLOAD stats, then:
io_cost = blocks/mbrc * mreadtim/sreadtim
REFERENCES
NOTE:1106073.1 - What is the difference between '_db_file_optimizer_read_count' and 'db_file_multiblock_read_count'?
NOTE:473740.1 - How To Unset DB_FILE_MULTIBLOCK_READ_COUNT to Default Value?
- How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? (Doc ID 1398860.1)
- HOW SHIPPING DATES ARE CALCULATED. (文档 ID 1076040.1)
- How to Disable Parallel Transaction Recovery When Parallel Txn Recovery is Active (Doc ID 238507.1)
- How To Find Where The Memory Is Growing For A Process (Doc ID 822527.1)
- How v$recovery_file_dest.space_used is calculated from v$asm_file.bytes in ASM
- How to deinstall OCM (Doc ID 761313.1)
- db_file_multiblock_read_count
- How to configure local_listener parameter with ASMCA [ID 1112993.1]
- How To Setup Password Security? (Doc ID 564125.1)
- How To Manually Remove ORACLE_OCM From Database (Doc ID 859113.1)
- How to Setup Active DataGuard on Exadata (Doc ID 1580796.1)
- How Cost Collector Transfers Cost To Projects (Doc ID 412562.1)
- How Btree Indexes Are Maintained (Doc ID 30405.1)
- How to Relocate and Rename SYSTEM datafiles (Doc ID 191540.1)
- Parameter ID
- HugePages on Linux: What It Is... and What It Is Not... (Doc ID 361323.1)
- 【译】Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1]
- How to Add the JVM Component to an Existing Oracle Database (Doc ID 1461562.1)
- 安全卫士——Day03_2
- 存储过程实现报表数据源的利弊分析
- LeetCode--ZigZag Conversion
- springmvc文件上传
- Newtonsoft.Json 将类转换成json
- How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? (Doc ID 1398860.1)
- Web系统性能调优常用技巧
- 串口 COM口 TTL RS-232 RS-485 区别 释疑
- Java网络编程总结
- gst-ffmpeg重编译出现的error记录
- 机器人最小能量最终结果(调试完成)
- oracle 软件下载地址、文档地址
- 手风琴 html+css+jquery
- Junit单元测试和spring单元测试