ASM parameters

10.2.0.3 on RH Linux 4.0, update 4, 2 node RAC. I am experimenting with
ASM parameters, DB_CACHE_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT.
Default cache size is 25M, MBRC is 110. Default block size is 4k.
I have set cache size to 128M, MBRC to 256 (1M reads). I tried going above
1M, but the parameter was ignored. RMAN seems to work much faster,
benchmark is in progress. Does anybody have any recommendations or
experiences to share?

--
Mladen Gogala
http://mgogala.freehostia.com

On Jan 25, 3:46*am, Mladen Gogala wrote:
> 10.2.0.3 on RH Linux 4.0, update 4, 2 node RAC. I am experimenting with
> ASM parameters, DB_CACHE_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT.
> Default cache size is 25M, MBRC is 110. Default block size is 4k.
> I have set cache size to 128M, MBRC to 256 (1M reads). I tried going above
> 1M, but the parameter was ignored. RMAN seems to work much faster,
> benchmark is in progress. Does anybody have any recommendations or
> experiences to share?
>
> --
> Mladen Gogalahttp://mgogala.freehostia.com

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/2...tats-strategy/

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

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/2...tats-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 Database 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

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/2...tats-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 Database 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

On Jan 25, 2:28*pm, Mladen Gogala 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/2...tats-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 Database 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, but I thought that I would post it any way:
http://download.oracle.com/docs/cd/B...0/rcmtunin.htm

This document discusses the significance of some of the hidden Oracle
parameters that control memory available to RMAN:
http://www.oracle.com/technology/pro...ing_backup.pdf

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

"Mladen Gogala" wrote in message
news:479a386e$0$1341$834e42db-at-reader.greatnowhere. com...
> 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/2...tats-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 Database 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.
>

Well, that's what you get when turning a RDBMS into a volume manager -
awkwardness.
Oracle likes to think that database can be a all-purpose software.

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

> On Jan 25, 2:28Â*pm, Mladen Gogala 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/2...tats-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 Database 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

On Sat, 26 Jan 2008 15:18:09 +0000, Mladen Gogala wrote:

> 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 also have wait interface. All of the classic wait
event tables have their ASM counterparts. I looked into V$SYSTEM_EVENT
table:

SQL> select event,time_waited from v$system_event order by 2 desc;

EVENT
TIME_WAITED
----------------------------------------------------------------
-----------
rdbms ipc message
226141091
DIAG idle wait
20574722
pmon timer
20571058
gcs remote message
20568154
ges remote message
20523925
SQL*Net message from client
10515309
DFS lock handle
190351
PX Idle Wait
73904
kfk: async disk IO
27319
log write(even)
25253
log write(odd)
24735

EVENT
TIME_WAITED
----------------------------------------------------------------
-----------
DBFG waiting for reply
9752
enq: AD - deallocate AU
6938
enq: AD - allocate AU
6467
enq: HD - contention
4561
ASM mount : wait for heartbeat
438
rdbms ipc reply
269
GCS lock open S
231
SQL*Net message to client
213

I wonder whether buffer cache hit ratio would make sense for the ASM
instances and how to calculate it. I will let you know of my findings.
--
http://mgogala.freehostia.com

"Mladen Gogala" wrote in message
news:479b5213$0$1344$834e42db-at-reader.greatnowhere. com...
> On Sat, 26 Jan 2008 15:18:09 +0000, Mladen Gogala wrote:
>
>> 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 also have wait interface. All of the classic wait
> event tables have their ASM counterparts. I looked into V$SYSTEM_EVENT
> table:
>
> SQL> select event,time_waited from v$system_event order by 2 desc;
>
> EVENT
> TIME_WAITED
> ----------------------------------------------------------------
> -----------
> rdbms ipc message
> 226141091
> DIAG idle wait
> 20574722
> pmon timer
> 20571058
> gcs remote message
> 20568154
> ges remote message
> 20523925
> SQL*Net message from client
> 10515309
> DFS lock handle
> 190351
> PX Idle Wait
> 73904
> kfk: async disk IO
> 27319
> log write(even)
> 25253
> log write(odd)
> 24735
>
> EVENT
> TIME_WAITED
> ----------------------------------------------------------------
> -----------
> DBFG waiting for reply
> 9752
> enq: AD - deallocate AU
> 6938
> enq: AD - allocate AU
> 6467
> enq: HD - contention
> 4561
> ASM mount : wait for heartbeat
> 438
> rdbms ipc reply
> 269
> GCS lock open S
> 231
> SQL*Net message to client
> 213
>
> I wonder whether buffer cache hit ratio would make sense for the ASM
> instances and how to calculate it. I will let you know of my findings.
> --
> http://mgogala.freehostia.com

(Database block gets + consistent gets - physical reads)/(Database block gets +
consistent gets)

Well, but then again, many "experts" in this group think BCHR does not
matter even in regular databases. LMAO.

On Jan 26, 2:12 pm, "Bob Jones" wrote:
> "Mladen Gogala" wrote in message
> > I wonder whether buffer cache hit ratio would make sense for the ASM
> > instances and how to calculate it. I will let you know of my findings.
> > --
> >http://mgogala.freehostia.com
>
> (Database block gets + consistent gets - physical reads)/(Database block gets +
> consistent gets)
>

Sorry, no can do...

SQL> select instance_name,startup_time from gv$instance;

INSTANCE_NAME STARTUP_TIME
---------------- -------------------
+ASM1 2008_01_24 20:02:58
+ASM2 2008_01_25 11:42:44

SQL> select inst_id,name,value
2 from gv$sysstat
3* where name in('Database block gets','consistent gets','physical
reads');

INST_ID NAME VALUE
---------- ------------------------------ ----------
1 Database block gets 0
1 consistent gets 0
1 physical reads 0
2 Database block gets 0
2 consistent gets 0
2 physical reads 0

6 rows selected.

SQL>