Discuss DB_FILE_MULTIBLOCK_READ_COUNT parameters and interval size settings
Author: Lunar
We know that Oracle gets data from the table in two ways:
· When using RowID (usually using index scan)
· Scan through full table
If you read the data through the RowID, the number of intervals in the table is not a factor of reading performance (if you use a parallel query, there is a large number of intervals in a table to significantly increase the performance of I / O), Oracle will pass through RowID. Find directly to the need and get the corresponding data.
If it is a full table scan, the size of the interval may result in performance issues. Oracle will read multiple blocks at a time when the full menu is scanned. The number of pieces read each time will be subject to the initialization parameter db_file_multiblock_read_count and the I / O buffer size of the operating system. For example, if the size of the Oracle Block is 4KB, the operating system I / O buffer size is 64KB, then you can read up to 16 each block at a full table scan, so the value of DB_FILE_MULTIBLOCK_READ_COUNT is set. It has changed the performance of the full table scan for more than 16.
Typically, setting the db_file_multiblock_read_count parameter is considered as follows:
(1) Create a new table space using a separate data file
(2) Creating a separate unregulated table in this table space
(3) Query V $ fileStat to verify the initial statistics of the test
(4) Perform full surface scan on the table
(5) Query V $ fileStat to determine the termination statistics of the test and subtract the start statistics from the medium. Eliminate the phyblkrds value with Phyrds to determine a valid multiple read count.
(6) Delete this table space used to test
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.1.0 - Production ON Saturday 28 10:11:22 2003
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
The idle routine is connected.
SQL> Startup
Oracle routines have been started.
Total System Global Area 93395628 Bytes
Fixed Size 453292 bytes
Variable size 67108864 bytes
Database buffers 25165824 BYTES
Redo buffers 667648 bytes
The database is loaded.
The database has been opened.
SQL> Show Parameter DB_BLOCK_SIZE;
Name Type Value
----------------------------------- --- ------------------
DB_BLOCK_SIZE INTEGER 8192
SQL> Show parameter db_file_multiblock_read_count;
Name Type Value --------------------------------- ------------
DB_FILE_MULTIBLOCK_READ_COUNT INTEGER 16
SQL>
Create a new table space using a separate data file:
SQL> CREATE TABLESPACE LUNAR
2 DataFile 'd: /lunar.dbf' size 10m
3 Default Storage (Initial 1M next 1m pctincrease 0);
The table space has been created.
Create a separate unrequited table in this table space:
SQL> CREATE TABLE LUNAR
2 TableSpace Lunar
3 AS SELECT * FROM DBA_OBJECTS;
The table has been created.
SQL> SELECT RELATIVE_FNO from DBA_DATA_FILES
2 where tablespace_name = 'lunar';
Relative_fno
----------------
15
Query V $ FileStat to verify the initial statistics of the test:
SQL> SELECT PHYRDS, PHYBLKRD FROM V $ fileStat
2 WHERE FILE # = 15;
Phyrds phyblkrd
---------- ----------
0 0
Perform full mete scan on the table:
SQL> SELECT Count (*) from lunar;
Count (*)
------------
27547
Query V $ fileStat to determine the termination statistics of the test:
SQL> SELECT PHYRDS, PHYBLKRD FROM V $ fileStat
2 WHERE FILE # = 15;
Phyrds phyblkrd
---------- ----------
24 376
SQL>
The value of the initial statistics of Phyrds and Phyblkrd is 0;
The values of the termination statistics of Phyrds and Phyblkrd are 24 and 376, respectively;
PHYRDS termination statistics of the value - the value of the initial statistics of Phyrds = 24;
PHYBLKRD termination statistics - the value of the initial statistics of phyblkrd = 376;
PHYBLKRD's difference / PHYRDS difference = 15.67
Therefore, effective multi-piece read count is 16
It should be noted that if it is not tested with new tablespace, then test the difference in step 3 and 5, change the DB_FILE_MULTIBLOCK_READ_COUNT parameter in the session stage, and then get the difference in step 3 and 5, And repeat the test.
Remember, do not set the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter to the value.
Setting the interval size Size Consideration should be reasonable to use Oracle's capabilities to perform multi-block access when scanning in full menu, while reading operations cannot be across intervals. For example, it is assumed that the operating system I / O buffer size is 64KB, checks to read a 640KB size table. If set to 64KB per interval, a total of 10 intervals, if the full table scan is performed, Oracle needs 10 times. Operation (equivalent to reading a range); if set to a 640KB interval, Oracle still requires 10 readings (because the operating system I / O buffer size is 64KB), the visible compression interval does not improve performance; if set For each interval 80kb, a total of 8 intervals, each interval Oracle needs to read twice, first read 64KB, the second reading of the remaining 16KB (read operation cannot be cross-section), so a total of 16 times Operation (equivalent to reading a range). The impact of interval size settings to performance is obvious. In summary, set the problem to consider the following problems in the setup interval.
· Creating a range that is significantly greater than or equal to the operating system I / O buffer size (preferably an integer multiple of the operating system I / O buffer size). In this way, if the interval is very large, even if the interval size is not an integer number of operating system I / O buffers, there is only a small additional read operation (if the above 640KB and 80KB differences) are required.
• Set DB_FILE_MULTIBLOCK_READ_COUNT to take full advantage of the operating system I / O buffer. The size of the DB_FILE_MULTIBLOCK_READ_COUNT <= Operation System I / O buffer / Oracle Block should be considered. If DB_FILE_MULTIBLOCK_READ_COUNT is set too large, the optimizer considers full table scan more efficient and changing the execution plan, and then the actual situation is not.
· If you must create a small range, create its size is an integer multiple of the operating system I / O buffer size.
