How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated? (Doc ID 1398860.1)
Troubleshooting I/O-related waits (Doc ID 223117.1)
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:
Oracle® Database Reference
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.
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:
db_file_multiblock_read_count = min(max I/O size/db_block_size , db_cache_size/(sessions * db_block_size))
Note: Max I/O size is platform-dependent and equal to 1MB for most platforms
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:
Document 473740.1 How To Unset DB_FILE_MULTIBLOCK_READ_COUNT to Default Value
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
=======================================================================
What is the difference between '_db_file_optimizer_read_count' and 'db_file_multiblock_read_count'? (Doc ID 1106073.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]Information in this document applies to any platform.
RDBMS 10.2 and higher
PURPOSE
To define the meaning of _db_file_optimizer_read_count
SCOPE
.
DETAILS
DB_FILE_MULTIBLOCK_READ_COUNT
- 'db_file_multiblock_read_count' affects the runtime performance when the query is executed.
Oracle issue IOs of size = db_file_multiblock_read_count * block size
_DB_FILE_OPTIMIZER_READ_COUNT
- '_db_file_optimizer_read_count' affects the optimizer's decisions when generating an access path.
Oracle costs IOs as being of size = _db_file_optimizer_read_count * block size
The parameter '_db_file_optimizer_read_count' was added in Oracle 10g Release 2 when automatic sizing of IO was introduced.
Since execution IO size is automatic, Oracle needed a way to control the IO size being used by the optimizer.
Since execution IO size is automatic, Oracle needed a way to control the IO size being used by the optimizer.
Relationship to Calculation of Full Table Scan Cost
If MBRC is set, it will be used by the optimizer to calculate the cost of a full table scan.
When MBRC is not set, then _DB_FILE_OPTIMIZER_READ_COUNT will be used instead.
When DB_FILE_MULTIBLOCK_READ_COUNT is 0 or default, then the value of _DB_FILE_OPTIMIZER_READ_COUNT is set to 8.
When DB_FILE_MULTIBLOCK_READ_COUNT is set to any other value, then _DB_FILE_OPTIMIZER_READ_COUNT will derive its value from there.
MBRC is not always set when system statistics are gathered.
If during the interval that the system statistics are gathered, no scattered reads occur (FULL TABLE SCAN reading into the buffer) Oracle will not have enough information to compute the correct
value for MBRC. In this case MBRC and MREADTIM will remain null.As single block reads are more likely to occur, both sreadtim and cpuspeed will still be calculated an the values for these 2 will be set.
When MBRC is not set, then _DB_FILE_OPTIMIZER_READ_COUNT will be used instead.
When DB_FILE_MULTIBLOCK_READ_COUNT is 0 or default, then the value of _DB_FILE_OPTIMIZER_READ_COUNT is set to 8.
When DB_FILE_MULTIBLOCK_READ_COUNT is set to any other value, then _DB_FILE_OPTIMIZER_READ_COUNT will derive its value from there.
MBRC is not always set when system statistics are gathered.
If during the interval that the system statistics are gathered, no scattered reads occur (FULL TABLE SCAN reading into the buffer) Oracle will not have enough information to compute the correct
value for MBRC. In this case MBRC and MREADTIM will remain null.As single block reads are more likely to occur, both sreadtim and cpuspeed will still be calculated an the values for these 2 will be set.
REFERENCES
NOTE:1398860.1 - How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated?NOTE:473740.1 - How To Unset DB_FILE_MULTIBLOCK_READ_COUNT to Default Value?
NOTE:149560.1 - How to Collect and Display System Statistics (CPU and IO) for CBO use
NOTE:470316.1 - Using Actual System Statistics (Collected CPU and IO information)
==================================================================
WHAT IS THE DB_FILE_MULTIBLOCK_READ_COUNT PARAMETER? (Doc ID 1037322.6)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 7.0.16.0 and laterInformation in this document applies to any platform.
Platform: All
Platform versions: All
Products: RDBMS
RDBMS Versions: 7.x - 9.x
Information type: Advisory
Impact: High
Distribution: Public
Subject Area: Performance Tuning
PURPOSE
This document discusses the DB_FILE_MULTIBLOCK_READ_COUNT parameter, and its suggested usage and restrictions.
The information in this note refers to DB_FILE_MULTIBLOCK_READ_COUNT prior to 10.2 and gives some background on the parameter.
As of Oracle Database 10g release 2, the default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent
As of Oracle Database 10g release 2, the default value corresponds to the maximum I/O size that can be efficiently performed and is platform-dependent
For more information on the parameter in 10.2 and above see:
Document 1398860.1 How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated?
Document 1398860.1 How is Parameter DB_FILE_MULTIBLOCK_READ_COUNT Calculated?
QUESTIONS AND ANSWERS
What is DB_FILE_MULTIBLOCK_READ_COUNT?
The db_file_multiblock_read_count initialization parameter determines the maximum number of database blocks read in one I/O operation during a full table scan.
The setting of this parameter can reduce the number of I/O calls required for a full table scan, thus improving performance. While it may be tempting to set a very high value for db_file_multiblock_read_count to improve overall performance, you must
consider several factors before doing so.This is discussed below.
The setting of this parameter can reduce the number of I/O calls required for a full table scan, thus improving performance. While it may be tempting to set a very high value for db_file_multiblock_read_count to improve overall performance, you must
consider several factors before doing so.This is discussed below.
How does it work ?
I/O is a function of the operating system and so there are operating system specific limits imposed on the setting of this parameter. Oracle's ability to read multiple blocks is limited by the operating system's upper limit on the number of bytes which can be read in a single I/O call (MAX_IO_SIZE). If the value of db_file_multiblock_read_count exceeds internal constraints on the setting, it will default to the O/S default: (max_IO_size / db_block_size).
Consult your operating specific documentation for limits and default values of the maximum I/O size.
The setting of db_file_multiblock_read_count dictates how many I/O calls will be required to complete a table scan. For example, if db_file_multiblock_read_count is set to 32, and the Oracle block size = 8k,then a sequential scan of a 256k table can be read in one pass. This improves the speed of the table scan and overall query performance.
I/O is a function of the operating system and so there are operating system specific limits imposed on the setting of this parameter. Oracle's ability to read multiple blocks is limited by the operating system's upper limit on the number of bytes which can be read in a single I/O call (MAX_IO_SIZE). If the value of db_file_multiblock_read_count exceeds internal constraints on the setting, it will default to the O/S default: (max_IO_size / db_block_size).
Consult your operating specific documentation for limits and default values of the maximum I/O size.
The setting of db_file_multiblock_read_count dictates how many I/O calls will be required to complete a table scan. For example, if db_file_multiblock_read_count is set to 32, and the Oracle block size = 8k,then a sequential scan of a 256k table can be read in one pass. This improves the speed of the table scan and overall query performance.
Tuning Analysis
The total number of I/Os actually required to perform a full table scan depends on other factors such as the size of the table and whether parallel query is being used. The cost-based optimizer uses all of these factors,including db_file_multiblock_read_count to determine the cost of full table scans. Cost-based optimizer will favor full table scans when the cost is lower than index scans.
The Explain Plan for a given query will reveal if a full table scan will be performed. The statspack report lists statistics for multi-block reads as "db file scattered read". The term "scattered read" refers to multiple blocks
read into DB block buffers that are "scattered" throughout memory. High values for db file scattered read is often an indicator of many full table scans being performed. Statspack is a useful tool in tuning this and other init.ora parameters.
The goal of setting the db_file_multiblock_read_count parameter is that table scans are performed in fewer, larger I/Os. This is done by evaluating the number of blocks required to complete each table scan over time, then adjusting the parameter so that on average, most scans can be performed in one I/O.
Database tuning is an iterative process which is done through careful analysis of application performance measured by TKPROF and database statistics provided by statspack and dynamic performance views, such as v$sysstat and v$sesstat.
The Explain Plan for a given query will reveal if a full table scan will be performed. The statspack report lists statistics for multi-block reads as "db file scattered read". The term "scattered read" refers to multiple blocks
read into DB block buffers that are "scattered" throughout memory. High values for db file scattered read is often an indicator of many full table scans being performed. Statspack is a useful tool in tuning this and other init.ora parameters.
The goal of setting the db_file_multiblock_read_count parameter is that table scans are performed in fewer, larger I/Os. This is done by evaluating the number of blocks required to complete each table scan over time, then adjusting the parameter so that on average, most scans can be performed in one I/O.
Database tuning is an iterative process which is done through careful analysis of application performance measured by TKPROF and database statistics provided by statspack and dynamic performance views, such as v$sysstat and v$sesstat.
Limitations
The following are some important considerations for setting DB_FILE_MULTIBLOCK_READ_COUNT:
It is extremely important to know your applications in order to determine the best setting for db_file_multiblock_read_count. For example, Online Transaction Processing (OLTP) applications which seldom perform full table scans will not benefit from a high value for db_file_multiblock_read_count, and in fact may suffer performance degradation since the cost-based optimizer may favor a full table scan in some cases when the relative cost is determined to be less due to reading multiple blocks.
If db_file_multiblock_read_count is set too low, additional I/O will be required to perform sequential table scans when the table does not qualify as a short table. A short table is a table consisting of less than 5 Oracle blocks.A long table spans 5 or more Oracle blocks. An in-depth analysis of the database tables and applications is necessary. This parameter is subject to operating system limitations, and changes from the default value should only be made after consulting Oracle Operating Specific documentation.
The cost of setting db_file_multiblock_read_count too high is that the server will consume additional memory and may cause full table scans to be chosen by the Cost-Based Optimizer more frequently.
The maximum value of the overall multiblock read factor is calculated as:
(db_block_size * db_file_multiblock_read_count)
The value of db_file_multiblock_read_count is limited by the multiblock read factor in the operating system level.
Regardless of the MAX_IO_SIZE value allowed by your operating system, db_file_multiblock_read_count cannot exceed:
(db_block_buffers/4)
NOTE: Benchmarks of values higher than 32 have shown little, if any performance gain.
It is extremely important to know your applications in order to determine the best setting for db_file_multiblock_read_count. For example, Online Transaction Processing (OLTP) applications which seldom perform full table scans will not benefit from a high value for db_file_multiblock_read_count, and in fact may suffer performance degradation since the cost-based optimizer may favor a full table scan in some cases when the relative cost is determined to be less due to reading multiple blocks.
If db_file_multiblock_read_count is set too low, additional I/O will be required to perform sequential table scans when the table does not qualify as a short table. A short table is a table consisting of less than 5 Oracle blocks.A long table spans 5 or more Oracle blocks. An in-depth analysis of the database tables and applications is necessary. This parameter is subject to operating system limitations, and changes from the default value should only be made after consulting Oracle Operating Specific documentation.
The cost of setting db_file_multiblock_read_count too high is that the server will consume additional memory and may cause full table scans to be chosen by the Cost-Based Optimizer more frequently.
The maximum value of the overall multiblock read factor is calculated as:
(db_block_size * db_file_multiblock_read_count)
The value of db_file_multiblock_read_count is limited by the multiblock read factor in the operating system level.
Regardless of the MAX_IO_SIZE value allowed by your operating system, db_file_multiblock_read_count cannot exceed:
(db_block_buffers/4)
NOTE: Benchmarks of values higher than 32 have shown little, if any performance gain.
Warnings and Considerations
On HP/UX RDBMS version 7.3.3.0, if the product
(db_block_size * db_file_multiblock_read_count)
exceeds 64K, a core dump and ORA-7445 can occur. This is corrected by reducing the value of db_file_multiblock_read_count and restarting the instance.
The following bugs have been logged for this problem on HP/UX version 7.3.3.0, and are fixed in 7.3.3.3:
505464
509726
516642
Patch 433762 on version 7.3.2 for Solaris allows a setting of db_file_multiblock_read_count = 128 for 8k db_block_size.
(db_block_size * db_file_multiblock_read_count)
exceeds 64K, a core dump and ORA-7445 can occur. This is corrected by reducing the value of db_file_multiblock_read_count and restarting the instance.
The following bugs have been logged for this problem on HP/UX version 7.3.3.0, and are fixed in 7.3.3.3:
505464
509726
516642
Patch 433762 on version 7.3.2 for Solaris allows a setting of db_file_multiblock_read_count = 128 for 8k db_block_size.
Note:30712.1 Init.ora parameter "DB_FILE_MULTIBLOCK_READ_COUNT" Reference note
================================================================================
Clustering Factor (Doc ID 39836.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Information in this document applies to any platform.
***Checked for relevance on 10-Jul-2013***
Information in this document applies to any platform.
***Checked for relevance on 10-Jul-2013***
PURPOSE
This article outlines the meaning of the Clustering Factor Statistic
SCOPE
This article provides developers and DBAs more in depth information regarding the Clustering Factor Statistic
DETAILS
Clustering Factor
The Clustering Factor is a statistic collected for use by the Cost Based Optimizer (CBO) and is a measure of the 'ordered-ness' of an index in comparison to the table that it is based upon. It is used as an indicator for computing the estimated cost of the table lookup following an index access.
The Clustering Factor records the number of data blocks that will be accessed when scanning an index.
Clustering factor values can be seen in the following views:
The Clustering Factor records the number of data blocks that will be accessed when scanning an index.
Clustering factor values can be seen in the following views:
- ALL|DBA|USER_INDEXES.
It can be set also in:
- ALL|DBA|USER_IND_PARTITIONS
- ALL|DBA|USER_IND_SUBPARTITIONS
when the indexes are partitioned.
For further details on Clustering Factor see:
Oracle® Database Concepts
11g Release 2 (11.2)
Part Number E10713-05
Chapter 3 Indexes and Index-Organized Tables
11g Release 2 (11.2)
Part Number E10713-05
Chapter 3 Indexes and Index-Organized Tables
Calculation
It is calculated as follows:
- The index is scanned in order.
- The block portion of the rowid pointed at by the current indexed value is compared with that pointed at by the previous indexed value. This is achieved by comparing adjacent rowids in the index leaf block (the blocks themselves do not need to be visited).
- If these rowids are pointing at different blocks then a counter is incremented.
- This is continued throughout the whole index
- The resultant count is then stored.
From 12c, the SET_GLOBAL_PREFS Procedure allows you to set a value for TABLE_CACHED_BLOCKS. The default is 1 and the max is 255. This parameter is used when gathering the index clustering factor and it tells the gathering engine the average number of blocks cached in the buffer cache for any table. With small tables this could have quite a significant effect, less so with larger objects since 255 will be a less significant proportion of the objects. The recommendation is to leave this parameter at the default. See:
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)
E17602-14
153 DBMS_STATS
SET_GLOBAL_PREFS Procedure
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#ARPLS68664
12c Release 1 (12.1)
E17602-14
153 DBMS_STATS
SET_GLOBAL_PREFS Procedure
http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_stats.htm#ARPLS68664
Interpretation
If the count is close to the number of blocks in the table then the index is well ordered. This is true because the counter only gets incremented when the actual data is found in a different block from the last piece of row data. What this means is that when an index block is read, the table lookups required based on that block are likely to be in the same table blocks. So, the lower the Clustering Factor the less I/O is likely to be used by the statement and so the optimizer is more likely to choose that index.
If the count is close to the number of rows in the table then the index is less well ordered. In this case adjacent index entries do not tend to point to the same block in the table, thus more block reads are likely to be required. An index with a higher clustering factor is more likely to have to revisit Data blocks than with a lower Clustering factor .
The clustering factor can be used by the optimizer to adjust the potential number of blocks that will be accessed by a particular predicate or query. This is useful for determining the number of base table blocks that will be visited when accessed via the index.
The clustering factor is effectively a count of the number of data blocks visited as the result of an index lookup. Multiplying the clustering factor by the selectivity will give the cost of the operation. It is predominately used to calculate costs for index range scans.
If the count is close to the number of rows in the table then the index is less well ordered. In this case adjacent index entries do not tend to point to the same block in the table, thus more block reads are likely to be required. An index with a higher clustering factor is more likely to have to revisit Data blocks than with a lower Clustering factor .
The clustering factor can be used by the optimizer to adjust the potential number of blocks that will be accessed by a particular predicate or query. This is useful for determining the number of base table blocks that will be visited when accessed via the index.
The clustering factor is effectively a count of the number of data blocks visited as the result of an index lookup. Multiplying the clustering factor by the selectivity will give the cost of the operation. It is predominately used to calculate costs for index range scans.
Common Questions
- How can Clustering Factor be Reduced?
The only method to affect the clustering factor is to sort and then store the rows in the table in the same order as in they appear in the index. Exporting rows and putting them back in the same order that they appeared originally will have no affect. Remember that ordering the rows to suit one index may have detrimental effects on the choice of other indexes.
==============================================================================
Comments
Post a Comment
Oracle DBA Information