Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Tom Kyte

Thanks for the question, Debaditya.

Asked: October 18, 2007 - 5:11 pm UTC

Answered by: Tom Kyte - Last updated: March 07, 2011 - 3:18 pm UTC

Category: Database - Version: 10.2.0

Viewed 10K+ times! This question is

Whilst you are here, check out some content from the AskTom team: Running external programs from the scheduler

You Asked

Hi Tom,

I have received a recommendation from a fellow analyst that with 10g it is better not to set the parameter db_file_multiblock_read_count and let oracle determine the best value for the number of blocks read in multi-block I/O operations.

We have not set this parameter on our 4 node (RAC) CRM data warehouse and we find that the value of db_file_multiblock_readcount is different in the 4 instances e.g. 22, 45, 46 and 42 respectively.

I am arguing that based on the above numbers the cost of full table scan will vary from one instance to another and hence the optimizer can choose a different execution plan (good or bad) for the same query when executed in different instances.

I am confused so I wanted your opinion about setting (hardcoding or dynamic determination) the db_file_multi_block_read_count in a RAC environment.

Thanks

and we said...

Your conclusion is correct - that due to the different settings - you might see different plans.

But - to that - I say "so". The fact that you have different values would indicate to me that you have different workloads on your nodes. This value is determined by the database based on your historical actual multi-block read count.

You see - when you set this manually (say to 64), you are telling Oracle "hey, you will read 64 blocks at a time, that is pretty good". But in reality, you don't really ever read 64 (on three of your nodes, you read about 40-45, on the other only about 22) when you do a multiblock IO - you do less than 64. So, while the query was costed with a read count of 64, it is really only doing 22 - which is not nearly as efficient as 64 (but it cannot do 64 - it can only do 22 - some of the 64 blocks you would have read are already in the buffer cache, we cannot read them from disk again - so the big read never actually happens)

I recommend letting everything that you can default.

and you rated our response

  (18 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

More Queries

October 19, 2007 - 7:16 pm UTC

Reviewer: Debaditya Chatterjee from New York

From the 10g Release 2 documentation (Performance guide - section 14.4.1.2) I found the optimizer actually uses mbrc= 8 for costing when db_file_multiblock_read_count is not set. So the cost of the query doesn't change from one instance to another although the db_file_multiblock_read_counts are different.

I have tried a test case by logging into all 4 instances separately and the cost of the query and the plan was exactly similar. (This actually contradicts my initial understanding !!)

However now I am more confused, even though each RAC instance had 4 different values for db_file_multiblock_read_count still the cost of the query has remained the same (since optimizer has assumed mbrc=8). So in other words the optimizer is telling us it will perform the same amount work (expressed in terms of cost) even though the work load characteristic of each instance is different. I hope I am able to express myself properly.

My database version is 10.2.0.2 and it runs on linux.

MBRC

October 22, 2007 - 2:54 am UTC

Reviewer: Connor from Best place on earth

The value of db_file_... (whether set or not) should not have an impact on costing assuming system stats are present.

hth
connor

October 22, 2007 - 11:22 am UTC

Reviewer: A reader

Tom - Is the above statement for Connors true in 9i/10g/11g ?
Tom Kyte

Followup  

October 22, 2007 - 1:39 pm UTC

it depends on the amount of system stats in place as well, if you just gather no workload stats, it could well still count (the multi-block read count) for costing.

easy way for you to find out:

create an empty table
using set table stats, tell us it has 100,000 blocks
set autotrace traceonly explain
select * from it
change multiblock read count in session
select * from it

Until I set system stats (or gathered them for a real work load) the gathered system stats by themselves was insufficient.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_system_stats;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1
------------------------------ ------------------------------ ----------
PVAL2
-------------------------------------------------------------------------------
SYSSTATS_INFO                  STATUS
COMPLETED

SYSSTATS_INFO                  DSTART
10-22-2007 13:36

SYSSTATS_INFO                  DSTOP
10-22-2007 13:36

SYSSTATS_INFO                  FLAGS                                   1


SYSSTATS_MAIN                  CPUSPEEDNW                         663.81


SYSSTATS_MAIN                  IOSEEKTIM                          28.486


SYSSTATS_MAIN                  IOTFRSPEED                       5753.295


SYSSTATS_MAIN                  SREADTIM


SYSSTATS_MAIN                  MREADTIM


SYSSTATS_MAIN                  CPUSPEED


SYSSTATS_MAIN                  MBRC


SYSSTATS_MAIN                  MAXTHR


SYSSTATS_MAIN                  SLAVETHR



13 rows selected.

ops$tkyte%ORA10GR2> select name, value, isdefault from v$parameter where name like '%multiblock_read%';

NAME
------------------------------
VALUE
-------------------------------------------------------------------------------
ISDEFAULT
---------
db_file_multiblock_read_count
46
TRUE


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 1;

Session altered.

ops$tkyte%ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K| 10007   (1)| 00:05:00
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K| 10007   (1)| 00:05:00
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 64;

Session altered.

ops$tkyte%ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K|   632   (1)| 00:00:19
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K|   632   (1)| 00:00:19
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> exec dbms_stats.set_system_stats( 'sreadtim', 1.932 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.set_system_stats( 'mreadtim', .554 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.set_system_stats( 'cpuspeed', 340 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_stats.set_system_stats( 'mbrc', 49 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 1;

Session altered.

ops$tkyte%ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K|   840  (20)| 00:00:02
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K|   840  (20)| 00:00:02
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 64;

Session altered.

ops$tkyte%ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K|   840  (20)| 00:00:02
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K|   840  (20)| 00:00:02
------------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off

October 22, 2007 - 2:08 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

For the reader interested in the inner details of the CBO - the NOWORKLOAD case is covered in detail in "Cost Based Oracle", page 20. In essence, the CBO assumes that MBRC = db_file_multiblock_read_count.

October 22, 2007 - 2:21 pm UTC

Reviewer: Aman Sharma

Sir you have shown in Effective Oracle that from 9i onwards, Oracle picks the maximum IO size from operating system and than decides the value for DBMRC.If there is an odd value given its changed to 8 implicitly.Is this correct in 10g too?
2)Oracle has proposed that one shouldn't set any value for this parameter in 10g.And also this limits the fetch size limited to 10% of data buffer cache.Also there is is some thing called prefetch limi of 10kb from 10g.What is this prefetch limit?

October 22, 2007 - 2:21 pm UTC

Reviewer: Aman Sharma

Sir you have shown in Effective Oracle that from 9i onwards, Oracle picks the maximum IO size from operating system and than decides the value for DBMRC.If there is an odd value given its changed to 8 implicitly.Is this correct in 10g too?
2)Oracle has proposed that one shouldn't set any value for this parameter in 10g.And also this limits the fetch size limited to 10% of data buffer cache.Also there is is some thing called prefetch limi of 10kb from 10g.What is this prefetch limit?

Tom Kyte

Followup  

October 23, 2007 - 11:21 am UTC

I don't understand your first comment...

2) correct, you should not set it.

I don't understand this 10% value you just spit out - what is that?

or the 10kb ????

you'll want to point us to where you see this written and we can comment then

Thanks

October 24, 2007 - 1:35 pm UTC

Reviewer: Debaditya Chatterjee

Thanks Tom, for explaining the effect of systems stats on cost calculation.

I can't replicate your example

April 23, 2009 - 11:57 pm UTC

Reviewer: Willie Doyle from Indianapolis USA

We are on 10gR2. We are also on a 5 node RAC system.
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ---------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    04-22-2009 10:52
SYSSTATS_INFO                  DSTOP                                     04-22-2009 10:54
SYSSTATS_INFO                  FLAGS                                   0
SYSSTATS_MAIN                  CPUSPEEDNW                       1559.026
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            7.363
SYSSTATS_MAIN                  MREADTIM                            8.086
SYSSTATS_MAIN                  CPUSPEED                             1559
SYSSTATS_MAIN                  MBRC                                   16
SYSSTATS_MAIN                  MAXTHR                             288768
SYSSTATS_MAIN                  SLAVETHR

SQL> drop table t;

Table dropped.

SQL> create table t as select * from all_objects where 1=0;

Table created.

SQL> exec dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000 );

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select * from t;

Explained.

SQL> select *
  2  from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K|  2195   (1)| 00:00:27 |
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K|  2195   (1)| 00:00:27 |
--------------------------------------------------------------------------

8 rows selected.

SQL> alter session set DB_FILE_MULTIBLOCK_READ_COUNT = 1;

Session altered.

SQL> explain plan for
  2  select * from t;

Explained.

SQL> select *
  2  from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K| 10007   (1)| 00:02:01 |
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K| 10007   (1)| 00:02:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> alter session set DB_FILE_MULTIBLOCK_READ_COUNT = 16;

Session altered.

SQL> explain plan for
  2  select * from t;

Explained.

SQL> select *
  2  from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|  9765K|  2195   (1)| 00:00:27 |
|   1 |  TABLE ACCESS FULL| T    |   100K|  9765K|  2195   (1)| 00:00:27 |
--------------------------------------------------------------------------

8 rows selected.

Tom Kyte

Followup  

April 27, 2009 - 11:33 am UTC

select name || '=' || value from v$parameter where isdefault = 'FALSE';

what have you set?

Here you go!

April 28, 2009 - 4:33 pm UTC

Reviewer: Willie Doyle from Indianapolis, IN

First, thanks for responding!

Second, The DB version we are on is 10.2.0.4. Two things that stand out here are "optimizer_features_enable" and "compatible". The optimizer_features_enable parameter is only temporarily set to 10.2.0.3 because we had a couple queries parse really badly after the upgrade to 10.2.0.4. Those queries are being re-worked as we speak. The parameter was temporarily changed to alleviate the problem queries on our system in the mean time. I have replicated the test I posted after running "alter session set optimizer_features_enable = '10.2.0.4';" and everything comes out the same. I am not sure why the "compatible" parameter is set to 10.2.0.3.
processes=750
sga_max_size=6442450944
spfile=+WEBSPROD_DATA/websprod/spfilewebsprod.ora
gcs_server_processes=4
sga_target=6442450944
control_files=+FLASH/websprod/controlfile/current.273.641346893, /oracle/oradata/redo/WEBSPROD/controlfile/control02.ctl
db_block_size=8192
compatible=10.2.0.3.0
log_archive_dest_1=LOCATION=+FLASH
log_archive_dest_2=LOCATION=/oracle/oradata/arch/WEBSPROD
log_archive_dest_state_1=DEFER
log_archive_format=%t_%s_%r.arc
archive_lag_target=1800
db_files=1024
db_file_multiblock_read_count=16
cluster_database=TRUE
cluster_database_instances=3
db_create_file_dest=+WEBSPROD_DATA
db_create_online_log_dest_1=+FLASH
db_create_online_log_dest_2=/oracle/oradata/redo
db_recovery_file_dest=+FLASH
db_recovery_file_dest_size=96636764160
thread=3
fast_start_mttr_target=900
db_flashback_retention_target=4320
instance_number=3
undo_management=AUTO
undo_tablespace=UNDOTBS3
undo_retention=604800
remote_login_passwordfile=EXCLUSIVE
audit_sys_operations=TRUE
db_domain=
service_names=WEBSPROD
dispatchers=(PROTOCOL=TCP) (SERVICE=WEBSPRODXDB)
local_listener=LISTENER_DS5
remote_listener=LISTENERS_WEBSPROD
utl_file_dir=/oracle/oradata/oraoutput/websprod
smtp_out_server=XXXX.walkerinfo.com
job_queue_processes=10
parallel_max_servers=80
background_dump_dest=/app/oracle/product/admin/WEBSPROD/bdump
user_dump_dest=/app/oracle/product/admin/WEBSPROD/udump
core_dump_dest=/app/oracle/product/admin/WEBSPROD/cdump
audit_file_dest=/app/oracle/product/admin/WEBSPROD/adump
optimizer_features_enable=10.2.0.3
audit_trail=DB
db_name=WEBSPROD
open_cursors=300
_optimizer_cost_based_transformation=ON
pga_aggregate_target=3221225472

Tom Kyte

Followup  

April 28, 2009 - 5:04 pm UTC

can you

set autotrace traceonly
alter session set events '10053 trace name context forever, level 1';
select * from t;
exit

in sqlplus at the very end of your script - send me the trace file (thomas.kyte @ oracle.com )

Done

April 29, 2009 - 1:32 pm UTC

Reviewer: Willie Doyle from Indianapolis, IN

I just sent you the trace file.
Tom Kyte

Followup  

April 29, 2009 - 3:19 pm UTC

looks like an explain plan oddity on your site. If you look in the 10053 file, your cost is 698 - not what explain plan was putting out.

Let's try this a bit differently, looking at the ACTUAL (not explain) plan. I've added "object_id = 0" for you since your table T has data (not needed if you use my "where 1=0" create).

You only need run the bit in bold, I re-ran the entire test case:


set echo on
column pval2 format a30
column value format a20
column PLAN_TABLE_OUTPUT format a72 truncate
set linesize 1000

drop table t;
create table t as select * from all_objects where 1=0;
exec dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000  );

exec dbms_stats.delete_system_stats;
select * from sys.aux_stats$;
set serveroutput OFF
alter session set db_file_multiblock_read_count = 1;
select * from t T1 where object_id = 0;
select * from table(dbms_xplan.display_cursor);

alter session set db_file_multiblock_read_count = 64;
select * from t T2 where object_id = 0;
select * from table(dbms_xplan.display_cursor);


begin
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1559.026 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 7.363 );
dbms_stats.set_system_stats( 'MREADTIM', 8.086 );
dbms_stats.set_system_stats( 'CPUSPEED', 1559 );
dbms_stats.set_system_stats( 'MBRC', 16 );
dbms_stats.set_system_stats( 'MAXTHR', 288768 );
commit;
end;
/
<b>
select * from sys.aux_stats$;

set serveroutput OFF
alter session set db_file_multiblock_read_count = 1;
select * from t T3 where object_id = 0;
select * from table(dbms_xplan.display_cursor);

alter session set db_file_multiblock_read_count = 64;
select * from t T4 where object_id = 0;
select * from table(dbms_xplan.display_cursor);
</b>



my output is what I expected - without workload stats - cost fluctuates, with them - it is constant.

ops$tkyte%ORA10GR2> set echo on
ops$tkyte%ORA10GR2> column pval2 format a30
ops$tkyte%ORA10GR2> column value format a20
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set linesize 1000
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000  );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.delete_system_stats;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    04-29-2009 14:59
SYSSTATS_INFO                  DSTOP                                     04-29-2009 14:59
SYSSTATS_INFO                  FLAGS                                   0
SYSSTATS_MAIN                  CPUSPEEDNW                        599.833
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

ops$tkyte%ORA10GR2> set serveroutput OFF
ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 1;

Session altered.

ops$tkyte%ORA10GR2> select * from t T1 where object_id = 0;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  0ar2zcdn01kq1, child number 0
-------------------------------------
select * from t T1 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       | 10015 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K| 10015   (1)| 00:02:01
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=0)


18 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 64;

Session altered.

ops$tkyte%ORA10GR2> select * from t T2 where object_id = 0;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  f83ma65zsx0h7, child number 0
-------------------------------------
select * from t T2 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |  1813 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K|  1813   (1)| 00:00:22
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=0)


18 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2  dbms_stats.set_system_stats( 'CPUSPEEDNW', 1559.026 );
  3  dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
  4  dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
  5  dbms_stats.set_system_stats( 'SREADTIM', 7.363 );
  6  dbms_stats.set_system_stats( 'MREADTIM', 8.086 );
  7  dbms_stats.set_system_stats( 'CPUSPEED', 1559 );
  8  dbms_stats.set_system_stats( 'MBRC', 16 );
  9  dbms_stats.set_system_stats( 'MAXTHR', 288768 );
 10  commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from sys.aux_stats$;

SNAME                          PNAME                               PVAL1 PVAL2
------------------------------ ------------------------------ ---------- ------------------------------
SYSSTATS_INFO                  STATUS                                    COMPLETED
SYSSTATS_INFO                  DSTART                                    04-29-2009 14:59
SYSSTATS_INFO                  DSTOP                                     04-29-2009 14:59
SYSSTATS_INFO                  FLAGS                                   1
SYSSTATS_MAIN                  CPUSPEEDNW                       1559.026
SYSSTATS_MAIN                  IOSEEKTIM                              10
SYSSTATS_MAIN                  IOTFRSPEED                           4096
SYSSTATS_MAIN                  SREADTIM                            7.363
SYSSTATS_MAIN                  MREADTIM                            8.086
SYSSTATS_MAIN                  CPUSPEED                             1559
SYSSTATS_MAIN                  MBRC                                   16
SYSSTATS_MAIN                  MAXTHR                             288768
SYSSTATS_MAIN                  SLAVETHR

13 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set serveroutput OFF
ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 1;

Session altered.

ops$tkyte%ORA10GR2> select * from t T3 where object_id = 0;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  gk1yfsrdfjtcz, child number 0
-------------------------------------
select * from t T3 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   696 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K|   696   (2)| 00:00:06
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=0)


18 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set db_file_multiblock_read_count = 64;

Session altered.

ops$tkyte%ORA10GR2> select * from t T4 where object_id = 0;

no rows selected

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  7x8b13dvnmng6, child number 0
-------------------------------------
select * from t T4 where object_id = 0

Plan hash value: 1601196873

------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   696 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  1000 |    97K|   696   (2)| 00:00:06
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=0)


18 rows selected.

ops$tkyte%ORA10GR2> edit test


Now I am really confused

April 29, 2009 - 4:27 pm UTC

Reviewer: Willie Doyle from Indianapolis, IN

Some followup:
1) I created table "t" exactly as you see in the test script. It has no rows, I double checked. Why do you think it has rows?

2) I do see in the trace file where the cost is actually 696. This is frustrating. I have tried your code in bold. I have tried SQL Developer and SQL Plus. I cannot get any of these tools to tell me that cost. So Now I feel like I am completely wasting my time (and yours) if I can't even see the real cost. Is there somewhere you can point me to get explain plans to show correctly, with the actual cost? Getting trace files off the server isn't practical.

3) Lets get to the heart of the issue and assume there is some explain plan problem on my side. I think you can clear up the entire issue here.

3.a.) IF I have collected WORKLOAD statisitics on the database, What value will be used for multiblock read count as far as CBO is concerned? Is it 8, (like I see above in "More Queries" October 19, 2007 - 7pm US/Eastern), or is it the value in MBRC (from the system statistics), or something else?

3.b.) Is it a dynamic, always changing, type of value, or does it only change when you gather WORKLOAD statistics?

3.c.) On a RAC system, how to you tell what the value is on each node, what command?

3.d.) This weekend, we are planning to reset the db_file_multiblock_read_count parameter by running "alter system reset db_file_multiblock_read_count scope=spfile sid='*';" . I think that would be your recommendation. Should we recollect the WORKLOAD statistics after we unset this parameter, or does this parameter have no affect on how workload statics are gathered.
Tom Kyte

Followup  

April 29, 2009 - 9:35 pm UTC

1) sorry, that was a misread on my part

2) I think we have a problem with explain plan (explain plan has lots of problems
http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html
) here. Did you run the bit in bold - it *does NOT use explain plan at all*

3.a) 10053 trace file shows that - 16 was used
3.b) workload stats, we use 16 which is what was OBSERVED to be the average read count to COST the query, but we'll use the maximum IO size at run time to process.
3.c) you'd have to query the aux_stats$ table on the node.
3.d) you'd want to collect the workload statistics during a "typical period of activity" - not right after a database bounce. The workload statistics are based on actual observed values - since you have them (10053 shows this) we are using 16 right now to COST queries but the max IO size on your system to PROCESS queries.

MBRC in cost calculation

May 04, 2009 - 12:10 am UTC

Reviewer: iamanoracledba from HongKong

Hi Tom,

1. In Jonathan Lewis's book, he mentioned there is "adjusted mbrc", which is not the exact value in system statistics. My quesiont is how did optimizer "adjust" the mbrc? Is there any formula to calculate it?

2. I found there is a article to process the formula derivation. It's interesting. The process and result seems ok. Is there any other factor involved the formula besides those listed in the formula?
http://www.hellodba.com/Doc/FTS_Cost_formula_crack_eng_1.htm

rgds,
iamanoracledba

Have the same problem

May 05, 2009 - 4:57 am UTC

Reviewer: Kay

Hi all,

I have the same problem with Willie, after upgrade to 10.2.0.4, the query parse really bad.
And I still have no clue about what cause it.
Tom Kyte

Followup  

May 11, 2009 - 8:22 am UTC

Willie isn't having a "parse really bad" whatever that means. We are diagnosing why he cannot replicate my example (which is not leading to a "really bad parse", just an interesting side effect of statistics in RAC - see below)

You'd need to be a bit more "clear" as to the issue if you would like some feedback.

db_file_multiblock_read_count and RMAN?

May 06, 2009 - 3:10 pm UTC

Reviewer: a big fan

Tom,

Does the setting of the db_file_multiblock_read_count parameter have any effect on RMAN?

We've noticed that when RMAN starts running against one of our DBs with a 32k blocksize and db_file_multiblock_read_count set to 16 that we see a much larger amount of data getting read per second from the EMC symm compared to when another DB with an 8k blocksize and db_file_multiblock_read_count set to 8 runs.

Would a larger db_file_multiblock_read_count value cause RMAN to read a larger number of blocks from disk per second?

Thanks.
Tom Kyte

Followup  

May 11, 2009 - 10:21 am UTC

this describes the read phase of an RMAN backup:

http://docs.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmtunin.htm#sthref1864


It does not use db_fmbrc to my knowledge (and in 10g and above, you should DEFINITELY let this parameter DEFAULT - we'll use the max IO size for multiblock IO but the actual observed multiblock read count for costing full scans, that is perfect)

I figured out what was happening with my system

May 06, 2009 - 6:38 pm UTC

Reviewer: Willie Doyle from Indianapolis, IN

I went back to run my test case for the 5th time to make sure I wasn't seeing things. When I ran it, it came out like Tom said it would. I was floored, so I wondered how that happened and started trying different things on differnt nodes. Bottom line: On a RAC system, the system statistics ONLY take affect on the node they were gathered until the DB is bounced. This is how I was getting all of these crazy results! Each time I would connect, I would get a different node. If I got the node where the system stats were collected, then tom's case (above) works, If I get on one of the other nodes, his case didn't work.

Here is what I now know to be true (hopefully tom will agree):
1) There are WORKLOAD system stats and NOWORKLOAD system stats. By default, 10gR2 has NOWORKLOAD stats.
2) With only NOWORKLOAD stats, the init param "db_file_multiblock_read_count" has a BIG effect on CBO's decision to use indexes or do full table scans. Not only does this parameter affect CBO's perception of how costly a full table scan is, but it ALSO effects how many blocks are actually read from I/O during a multi-block read.
3) After the "improved" WORKLOAD system stats are gathered, the init param "db_file_multiblock_read_count" has NO EFFECT on anything anymore. It doesn't affect CBO, and oracle uses other things to figure out how many blocks to read during an I/O. We actually unset the parameter on our system.
4) System stats ARE global to the DB. All nodes in a RAC cluster share the same WORKLOAD (and NOWORKLOAD) system stats.
5) Even though number 4 is true, ONLY the instance that you were connected to when you gathered system stats has the stats actually IN USE. You can connect to other nodes and query sys.aux_stats$, and it will show that the stats are there, but they ARE NOT being used yet. To remedy this, you either need to bounce the DB, or actually connect to each node separately and set the stats using dbms_stats.set_system_stats.

BTW: To the poster above who was talking about a 10.2.0.4 upgrade changing how queries are parsed, See this link: http://www.dba-oracle.com/t_slow_performance_after_upgrade.htm I wont even pretend to say that Don Burleson is a good source of information, but this page is a good laundry list of things you should independently investigate and verify on your own system. This thread is not about "all the performance things that go wrong after a 10gR2 upgrade".

Here is the script I use to test/prove. You must have access to connect to 2 different nodes in a RAC system at the same time. I setup seperate aliases in my tnsnames.ora and used the "INSTANCE" directive to make sure I get connected to the node I want to get connected to.

Side note: To make all of this testing work, YOU MUST make ALL of the test SQL statements different so it forces a hard parse.

--connect to ANY Node of RAC
--Setup our test case (one time only)
drop table t;

create table t as select * from all_objects where 1=0;

BEGIN
dbms_stats.set_table_stats( user, 'T', numrows=>100000, numblks => 10000 );
END;

--Now connect to NODE2 of RAC
--MAke sure you are connected to NODE2
select sys_context('USERENV', 'INSTANCE') from dual;

BEGIN
dbms_stats.delete_system_stats();
commit;
END;

-- You should see no system stats
select * from sys.aux_stats$

--Now connect to NODE1 of RAC
--MAke sure you are connected to NODE1
select sys_context('USERENV', 'INSTANCE') from dual;

-- Now start the experiment
BEGIN
dbms_stats.delete_system_stats();
commit;
END;

--You should see just the NOWORKLOAD statics
select * from sys.aux_stats$

--Try to make db_file_multiblock_read_count have an effect

alter session set db_file_multiblock_read_count = 1;
commit;

select 'test1node1' TestVar1 from t;

select * from table(dbms_xplan.display_cursor());

alter session set db_file_multiblock_read_count = 64;
commit;

select 'test1node1' TestVar64 from t;

select * from table(dbms_xplan.display_cursor());

-- AFTER running the 6 statements, you should see that db_file_multiblock_read_count has a big effect on the COST shown in the explain plan.

-- Now gather workload stats
BEGIN
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1015.714 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 4 );
dbms_stats.set_system_stats( 'MREADTIM', 8 );
dbms_stats.set_system_stats( 'CPUSPEED', 1016 );
dbms_stats.set_system_stats( 'MBRC', 13 );
dbms_stats.set_system_stats( 'MAXTHR', 2048 );
commit;
END;

-- Confirm they are there
select * from sys.aux_stats$;

--Try to make db_file_multiblock_read_count have an effect

alter session set db_file_multiblock_read_count = 1;
commit;

select 'test2node1' TestVar1 from t;

select * from table(dbms_xplan.display_cursor());

alter session set db_file_multiblock_read_count = 64;
commit;

select 'test2node1' TestVar64 from t;

select * from table(dbms_xplan.display_cursor());

-- AFTER running the 6 statements, you should see that db_file_multiblock_read_count has NO EFFECT on the COST shown in the explain plan.

--Now connect to NODE2 of RAC
--MAke sure you are connected to NODE2
select sys_context('USERENV', 'INSTANCE') from dual;

-- The stats should still be there from collecting them on NODE1 a minute ago.
select * from sys.aux_stats$;

-- But run the test again and you see, they arent actually IN USE on this node (i.e. db_file_multiblock_read_count has an effect on this node)

alter session set db_file_multiblock_read_count = 1;
commit;

select 'test3node2' TestVar1 from t;

select * from table(dbms_xplan.display_cursor());

alter session set db_file_multiblock_read_count = 64;
commit;

select 'test3node2' TestVar64 from t;

select * from table(dbms_xplan.display_cursor());

-- Now gather workload stats on NODE2
BEGIN
dbms_stats.set_system_stats( 'CPUSPEEDNW', 1015.714 );
dbms_stats.set_system_stats( 'IOSEEKTIM', 10 );
dbms_stats.set_system_stats( 'IOTFRSPEED', 4096 );
dbms_stats.set_system_stats( 'SREADTIM', 4 );
dbms_stats.set_system_stats( 'MREADTIM', 8 );
dbms_stats.set_system_stats( 'CPUSPEED', 1016 );
dbms_stats.set_system_stats( 'MBRC', 13 );
dbms_stats.set_system_stats( 'MAXTHR', 2048 );
commit;
END;

--verify (for giggles)
select * from sys.aux_stats$;

-- Now the system stats are IN USE (i.e. db_file_multiblock_read_count HAS NO effect)
alter session set db_file_multiblock_read_count = 1;
commit;

select 'test4node2' TestVar1 from t;

select * from table(dbms_xplan.display_cursor());

alter session set db_file_multiblock_read_count = 64;
commit;

select 'test4node2' TestVar64 from t;

select * from table(dbms_xplan.display_cursor());

Tom Kyte

Followup  

May 11, 2009 - 11:02 am UTC

Thanks for the comprehensive feedback - really appreciate that. Now to try to figure out if this is the expected behaviour :)

And was it the default / expected behaviour?

November 04, 2010 - 11:01 pm UTC

Reviewer: Neil

Hi Tom,

Sorry to drag up a relatively old thread, but I found it very interesting and informative.

However, you never followed up as to whether the behaviour on RAC was expected, or, if it has changed in 11G?

Many thanks.
Tom Kyte

Followup  

November 05, 2010 - 9:13 am UTC

big thread, lots of discussions - how about you distill out what you want me to comment on. *which* RAC behavior exactly?

db_file_multiblock_read_count

February 10, 2011 - 5:56 am UTC

Reviewer: A reader

Hi Tom,

I have a basic query:
1) Is DB_FILE_MULTIBLOCK_READ_COUNT parameter value related to I/O read from buffer or disk?
2) Why would we not set DB_FILE_MULTIBLOCK_READ_COUNT to higher value? How does it affect the table scans? I thought keeping higher value for this parameter would enable a single I/O to have large number of blocks to read from physical files. Then what is the problem?
Tom Kyte

Followup  

February 10, 2011 - 5:00 pm UTC

1) it controls the IO size we use to perform physical reads from disk

2) see our other conversation:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:957829973821#2977330600346538746


DB_file_multiblock_read_count

March 07, 2011 - 2:21 pm UTC

Reviewer: vipul from Mumbai

Hi tom,
Thanks for your valuable inputs on db_file_multiblock_read_count.We have oracle 10.2.0.4 on solaris with 4CPUs in production environment and db_file_multiblock_read_count is set to 128.As I am not getting how this large number is being set by our team,can you throw some light on setting proper value for db_file_multiblock_read_count.

Thanks in advance
Vipul

Tom Kyte

Followup  

March 07, 2011 - 3:18 pm UTC

in 10g the correct setting is:

<this space left intentionally blank>




see this conversation
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:957829973821#2977330600346538746