Interesting db_file_multiblock_read_count bug

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