DB_FILE_MULTIBLOCK_READ_COUNT parameter
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,controls the number of blocks pre-fetched into the buffer cache during scan operations, such as full table scan and index fast full scan.
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, Oracle uses the maximum.
Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so 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 With the I/O costing model, the effective multiblock read count value is derived directly from the db_file_multiblock_read_count parameter. However, the CBO doesn’t use the actual db_file_multiblock_read_count value as the CBO knows that most multiblock read operations are unlikely to read the maximum possible number of blocks.
when using CPU costing model, don’t set the db_file_multiblock_read_count parameter, let Oracle determine the maximum optimal size for you and ensure the system statistics are accurate and reflect the actual average MBRC size in your database environment.
Starting in Oracle 10g release 2, Oracle recommends not setting the db_file_multiblock_read_count parameter, allowing Oracle to empirically determine the optimal setting.Oracle Database 10g Release 2 automatically selects the appropriate value for this parameter depending on the operating system optimal I/O size and the size of the buffer cache.
In 10gr2 and beyond, the db_file_multiblock_read_count is not used to estimate the average number of blocks read and a separate metric for the estimated number of actual block
reads. Instead, the optimizer computes two new values, one for optimizer costing and another for the number of I/O requests. multi block read count.
_db_file_optimizer_read_count: mbrc for optimizer
_db_file_exec_read_count: mbrc for execution
SQL> create table test(id int,addre varchar2(2000)); Table created. SQL> insert into test 2 select rownum r,lpad(rownum,2000,'o') nm from dual connect by rownum<=10000; 10000 rows created. exec dbms_stats.gather_table_stats(user,'TEST',null,20); SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and nam.ksppinm like '%db_file_%' order by 1; NAME VALUE -------------------------------------------------- -------------------- _db_file_direct_io_count 1048576 _db_file_exec_read_count 8 _db_file_format_io_buffers 4 _db_file_noncontig_mblock_read_count 11 _db_file_optimizer_read_count 8 db_file_multiblock_read_count 8 db_file_name_convert db_files 200 8 rows selected. SQL> explain plan for select /*testsql*/ * from test where id=10000; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2004 | 745 (1)| 00:00:09 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 2004 | 745 (1)| 00:00:09 | -------------------------------------------------------------------------- SQL> conn / as sysdba Connected. SQL> alter system set db_file_multiblock_read_count=64; System altered. SQL> conn anbob/anbob Connected. SQL> explain plan for select /*testsql*/ * from test where id=10000; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2004 | 745 (1)| 00:00:09 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 2004 | 745 (1)| 00:00:09 | -------------------------------------------------------------------------- Oops!difference MBRC,same Cost This absolutely seems like an optimizer bug. The interesting thing is that Oracle accepts the changed mbrc value when executing multiblock read. This means that Optimizer ignores _db_file_optimizer_read_count change at system level. But query executor uses the changed _db_file_exec_read_count. Solutions are 1,Session level changes are applied successfully 2,"Change another (meaningless) system level optimizer parameter along with db_file_multiblock_read_count" ,I tried,but no effect Following test case demonstrates the solution. SQL> alter session set db_file_multiblock_read_count=64; Session altered. SQL> explain plan for select /*testsql*/ * from test where id=10000; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2004 | 613 (1)| 00:00:08 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 2004 | 613 (1)| 00:00:08 | -------------------------------------------------------------------------- SQL> alter session set db_file_multiblock_read_count=8; Session altered. SQL> explain plan for select /*testsql*/ * from test where id=10000; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------- Plan hash value: 1357081020 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2004 | 922 (1)| 00:00:12 | |* 1 | TABLE ACCESS FULL| TEST | 1 | 2004 | 922 (1)| 00:00:12 | --------------------------------------------------------------------------
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。