Re: ASM parameters

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: 26 Jan 2008 15:18:09 GMT
Message-ID: <479b4f30$0$1344$834e42db@reader.greatnowhere.com>


On Fri, 25 Jan 2008 14:10:52 -0800, Charles Hooper wrote:

> On Jan 25, 2:28 pm, Mladen Gogala <mgog..._at_yahoo.com> wrote:

>> On Fri, 25 Jan 2008 03:43:42 -0800, Charles Hooper wrote:
>> > I don't use ASM, but there may be some hints provided in "Cost-Based
>> > Oracle Fundamentals" to explain what you are seeing.  Paraphrased
>> > from that book:
>> > Oracle uses MBRC from the SYS.AUX_STATS$ for cost calculations, but
>> > the runtime engine tries to use DB_FILE_MULTIBLOCK_READ_COUNT when
>> > performing physical reads.  On Start up, Oracle determines the
>> > operating system's largest physical read size and silently uses that
>> > to limit whatever value is set for DB_FILE_MULTIBLOCK_READ_COUNT.
>>
>> > You might also want to take a look at the following regarding the
>> > DB_FILE_MULTIBLOCK_READ_COUNT parameter:
>> >http://jonathanlewis.wordpress.com/2007/05/20/system-stats-strategy/
>>
>> Charles, ASM is a physical layer, volume manager in user space. I am
>> just trying to increase the  read-ahead to speed up backups. ASM cache
>> is very much like file system cache, not like the DB cache. I am afraid
>> that CBO doesn't have much to do with it. This would be analogous to
>> increasing the block size of the underlying file system.
>>
>> --http://mgogala.freehostia.com

>
> Thanks for the clarification. With the inclusion of DB_CACHE_SIZE,
> DB_FILE_MULTIBLOCK_READ_COUNT, and MBRC, I jumped to the conclusion that
> you were performance tuning data retrieval, rather than RMAN
> performance.
>
> A quick Google search found a couple interesting articles that might be
> helpful:
> While the following is specific to Oracle 11g (most will likely apply to
> 10g R2), it does discuss how ASM requires slighly different parameters
> for optimal performance. You have probably already found this article,

Oracle10 can work with Oracle11 ASM. There is a good recipe for performance improvement on large databases in the ML note 368055.1. My management agreed to let me try Oracle10g with Oracle11 ASM, but I am still waiting for the hardware.
However, ASM instance parameters are very poorly documented. There are no documents explaining how to monitor and change those parameters. Performance can be, as is the case with OCFS, abysmal if everything is left on default. ASM instances have classic structure:

oracle   13376     1  0 Jan25 ?        00:00:00 asm_pmon_+ASM
oracle   13378     1  0 Jan25 ?        00:00:00 asm_psp0_+ASM
oracle   13380     1  0 Jan25 ?        00:00:00 asm_mman_+ASM
oracle   13382     1  0 Jan25 ?        00:00:00 asm_dbw0_+ASM
oracle   13384     1  0 Jan25 ?        00:00:00 asm_lgwr_+ASM
oracle   13386     1  0 Jan25 ?        00:00:00 asm_ckpt_+ASM
oracle   13388     1  0 Jan25 ?        00:00:00 asm_smon_+ASM
oracle   13390     1  0 Jan25 ?        00:00:00 asm_rbal_+ASM
oracle   13392     1  0 Jan25 ?        00:00:09 asm_gmon_+ASM
oracle   13760     1  0 Jan25 ?        00:00:00 oracle+ASM (DESCRIPTION=
(LOCAL=Y
oracle   32739 32738  0 10:07 ?        00:00:00 oracle+ASM (DESCRIPTION=
(LOCAL=Y They also have similar parameters as the ordinary instances:
NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
instance_type                        string      asm
SQL> show parameter db_cache_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
db_cache_size                        big integer 128M
SQL> show parameter read_count
NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
db_file_multiblock_read_count        integer     256

I noticed rather significant differences in RMAN transfer rates when these parameters are modified. You are one of the most experienced DBA's on this group and I am grateful for your suggestions.

-- 
http://mgogala.freehostia.com
Received on Sat Jan 26 2008 - 09:18:09 CST

Original text of this message