Database Reference
In-Depth Information
Valid values for the
optimizer_features_enable
initialization parameter are database versions such as 10.2.0.5,
11.1.0.7, or 11.2.0.3. Because the documentation (specifically the
Oracle Database Reference
manual) isn't up-to-date for
each patch level for this parameter, it's possible to generate the actual supported values with the following SQL statement:
SQL> SELECT value
2 FROM v$parameter_valid_values
3 WHERE name = 'optimizer_features_enable';
VALUE
----------
8.0.0
8.0.3
8.0.4
...
11.2.0.2
11.2.0.3
11.2.0.3.1
The
optimizer_features_enable
initialization parameter is dynamic and can be changed at the instance and
session levels. In a 12.1 multitenant environment, it can also be set at the PDB level. In addition, it's possible to specify a
value at the statement level with the
optimizer_features_enable
hint. The following two examples show the hint used
to specify the default value and a specific value, respectively (refer to Chapter 11 for more information about hints):
•
optimizer_features_enable(default)
•
optimizer_features_enable('10.2.0.5')
db_file_multiblock_read_count
The maximum disk I/O size used by the database engine during multiblock reads (for example, full table scans or index
fast full scans) is determined by multiplying the values of the
db_block_size
and
db_file_multiblock_read_count
initialization parameters. Thus, the maximum number of blocks read during multiblock reads is determined by
dividing the maximum disk I/O size by the block size of the tablespace being read. In other words, for the default block
size, the
db_file_multiblock_read_count
initialization parameter specifies the maximum number of blocks read.
This is “only” a maximum because there are at least three common situations leading to multiblock reads that are
smaller than the value specified by this initialization parameter:
•
Segment headers and other blocks containing only segment metadata like extent maps are
read with single-block reads.
•
Physical reads—except for a special case related to direct reads performed against a
tablespace using auto segment space management—never span several extents.
•
Blocks already in the buffer cache, except for direct reads, aren't reread from the disk
I/O subsystem.
To illustrate, Figure
9-2
shows the structure of a segment stored in a tablespace using manual segment space
management. Like any segment, it's composed of extents (in this example, 2), and each extent is composed of blocks
(in this example, 16). The first block of the first extent is the segment header. Some blocks (4, 9, 10, 19, and 21) are
cached in the buffer cache. A database engine process executing buffer cache reads of this segment can't perform a
single physical multiblock read, even if the
db_file_multiblock_read_count
initialization parameter is set to a value
greater than or equal to 32.
