Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Interesting db_file_multiblock_read_count bug

with 2 comments

As of 10gR2, Oracle introduced 2 additional parameters relating to multi block read count.

  • _db_file_optimizer_read_count: mbrc for optimizer
  • _db_file_exec_read_count: mbrc for execution

The purpose is more sophisticated mbrc management.

One thing you must keep in mind. Changing db_file_multiblock_read_count affects both of the above parameters.Well documented here.

Now the real story goes.

One of my customers sent me following request.

“Even when I change the db_file_multiblock_read_count in system level, optimizer ignores it.”

Looks like a simple mistake or a new annoying bug. See following test case.

-- I don't have workload system stats!
SNAME                          PNAME                     PVAL1 PVAL2
------------------------------ -------------------- ---------- -----------------
SYSSTATS_INFO                  STATUS                          COMPLETED
SYSSTATS_INFO                  DSTART                          02-26-2009 18:04
SYSSTATS_INFO                  DSTOP                           02-26-2009 18:04
SYSSTATS_INFO                  FLAGS                         0
SYSSTATS_MAIN                  CPUSPEEDNW              752.298
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

create table t1(c1 int, c2 char(10));

insert into t1
select level, 'x'
from dual connect by level <= 100000
;

exec dbms_stats.gather_table_stats(user, 't1', no_invalidate=>false);

alter system set db_file_multiblock_read_count = 8;

explain plan for select /* 8 */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |    87   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    15 |    87   (3)| 00:00:02 |
--------------------------------------------------------------------------



Ok, the FTS cost with 8 mbrc is 87. With bigger mbrc(16 here), Oracle should decrease the FTS cost. But…

alter system set db_file_multiblock_read_count = 16;

explain plan for select /* 16 */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |    87   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    15 |    87   (3)| 00:00:02 |
--------------------------------------------------------------------------



Oops! Same cost? What’s happening here?

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.

Very stupid, isn’t it? I think there is a logic hole in handling these new parameters(only 2!!!).

Solutions are

  • Session level changes are applied successfully
  • Change another (meaningless) system level optimizer parameter along with db_file_multiblock_read_count

Following test case demonstrates the solution.

alter system set db_file_multiblock_read_count = 8;
alter system set optimizer_mode = all_rows; -- meaningless change

explain plan for select /* 8 */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |    87   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    15 |    87   (3)| 00:00:02 |
--------------------------------------------------------------------------

alter system set db_file_multiblock_read_count = 16;
alter system set optimizer_mode = all_rows; -- meaningless change

explain plan for select /* 16 */ * from t1 where c1 = 1;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |    71   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    15 |    71   (3)| 00:00:01 |
--------------------------------------------------------------------------



This bug(as I believe) is reproducible as of 10gR2 and 11g.

About these ads

Written by Dion Cho

February 27, 2009 at 5:19 am

2 Responses

Subscribe to comments with RSS.

  1. Same results here, 10.2.0.3 and 11.1.0.6 (linux x86_32) are affected.

    Jakub Wartak

    February 27, 2009 at 10:18 pm

  2. hey.. I liked your post.
    You can look at me site for some info about db_file_multiblock_read_count

    Cheers,

    http://www.orainsights.com

    Ido

    Ido

    August 15, 2009 at 7:47 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 59 other followers

%d bloggers like this: