Re: db_file_multiblock_read_count and 10g

From: (wrong string) 조동욱 <ukja.dion_at_gmail.com>
Date: Sat, 24 Jan 2009 23:44:19 +0900
Message-ID: <43c2e3d60901240644y2a51a94du4fd25bcacdf5df3c_at_mail.gmail.com>

This is called autotuned MBRC - literally, Oracle autotunes the value of MBRC. This is a good stuffer, but there is something you must be warned before you use it.

As of Oracle 10gR2, MBRC is exposed as 2 hidden parameters.

• _db_file_exec_read_count : MBRC used by execution engine
• _db_file_optimizer_read_count : MBRC used by optimizer

If you use autotuned MBRC, Oracle would set _db_file_exec_read_count to the maximum value high enough to perform efficiently and _db_file_optimizer_read_count to the reasonable value for optimizer.

This means that your SQL execution plans would change violently.

You said that you're using 64 MBRC. This means that your execution plans are built on this value. But once you use autotuned MBRC, Oracle would rebuild the execution plans based on the _db_file_optimizer_read_count value.

This would not be what you want.

The best practice is

• Set system stat's MRBC to your current db_file_multiblock_read_count value, like dbms_stats.set_system_stats('mbrc', 64). This would gurantee that your execution plans would not be changed unexpectedly by changed MBRC value.
• Then use autotuned MBRC

  ---

Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)

---

2009/1/24 Lou Avrami <avramil_at_concentric.net>

> Hello all,
>
> We have 6 databases that could be classified as smaller data warehouses -
> each of them is 2-7 terabytes in size. These were running RDBMS 9.2.0.8 and
> are now being upgraded by the vendor who created them to 10.2.0.4.
>
> In 9i these databases had the parameter db_file_multiblock_read_count set
> to 64. I believe that in 10g the general recommendation is to NOT set
> db_file_multiblock_read_count, that the database will dynamically determine
> to appropriate value for each unique transaction.
>
> Does db_file_multiblock_read_count get set automagically in 10gR2? Does
> anyone have any experiences they can relate in 10gR2 with it being defined
> dynamically, as opposed to explicitly setting it?
>
> The answer for us probably will be "test it", but I thought I would see if
> anyone out there has any experiences that could also be taken into account.
>
> Thanks,
> Lou Avrami
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jan 24 2009 - 08:44:19 CST