ASM parameters

"Steve Howard" wrote in message
news:0b2436f4-d9d6-4e7c-b3e4-a8c3540ddd9e-at-e4g2000hsg.googlegroups.com...
> 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>

These stats may not be applicable for ASM then.

On Sat, 26 Jan 2008 10:38:53 -0800, hpuxrac wrote:

> Increasing dfmbrc only works up to a point. Tom Kyte has written a
> bunch about it and has some test scripts and methodology that shows (
> varies by os if I remember correctly ) bumping it up only works to a
> point. ( I think the book is Expert One on One ... can't check my work
> bookshelf right now ).

John, this was about ASM, not the RDBMS layer.



--
http://mgogala.freehostia.com

On Jan 26, 10:18 am, Mladen Gogala wrote:
> On Fri, 25 Jan 2008 14:10:52 -0800, Charles Hooper wrote:
> > 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

Mladen, thanks for the complement. However, there have been several
cases where I might take an hour or longer researching an answer, when
some of the other contributors to the group take 5 minutes or less to
provide an answer that is more accurate and better described than the
answer I would have posted. On a day to day basis, in addition to
(fighting with) Oracle, I deal with a lot of other things that have
nothing to do with databases (Windows, Linux, firewalls, vpn
equipment, MS Exchange, computer hardware, phone equipment, network
equipment, Fanuc CNC controls, training, etc.). The goal is to learn
more than one forgets each day. There are a number of very
knowledgeable DBAs (and developers) contributing to this group, but
unfortunately not all of the answers provided in 5 minutes or less
convey meaningful content.

A couple other references that might be helpful:
Metalink Note:5576584.8, Bug 5576584 - Poor ASM parallel read
performance, affects ASM on 10.2.0.3 and below, fixed in 10.2.0.4. It
looks like a patch is available for 10.2.0.3 on Linux, which is dated
March 2007. However, the last update to the bug report is January 16,
2008 (there is an update to the bug report in November that might make
some DBAs uneasy). I am not sure if this would affect RMAN
performance, but you might want to take a look at the bug report.

"Take the Guesswork Out of Database Layout and I/O Tuning with
Automatic Storage Management"
http://www.oracle.com/technology/pro...ng%2001-06.pdf
"The Kernel Sequential File I/O (ksfq) provides support for sequential
disk/tape access and buffer management. Ksfq allocates 4 sequential
buffers by default. The size of the buffers is determined by
dbfile_direct_io_count parameter set to 1MB by default. Some of the
ksfq clients are Datafile, Redolog file, RMAN, Archive log file,
Datapump, Data Guard and the File Transfer Package."

Please continue to keep the group informed of your progress.

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

On Sat, 26 Jan 2008 19:14:14 -0800, Charles Hooper wrote:

> "Take the Guesswork Out of Database Layout and I/O Tuning with Automatic
> Storage Management"
> http://www.oracle.com/technology/pro...df/take%20the%
20guesswork%20out%20of%20db%20tuning%2001-06.pdf
> "The Kernel Sequential File I/O (ksfq) provides support for sequential
> disk/tape access and buffer management. Ksfq allocates 4 sequential
> buffers by default. The size of the buffers is determined by
> dbfile_direct_io_count parameter set to 1MB by default. Some of the
> ksfq clients are Datafile, Redolog file, RMAN, Archive log file,
> Datapump, Data Guard and the File Transfer Package."

Charles, this is a great document. Here is my brief understanding how
ASM works. ASM runs in user space, not in kernel space, which means it
isn't a driver. It only provides database processes (s00x, dbwr, lgwr,
ckpt) with locations where to read from or write to. The IO calls
themselves are still performed by the corresponding database processes
and are targeted to the underlying raw devices.
In other words, ASM handles what is known as "file system metadata" -
directories, files, extent maps and alike. If you take a look at IBM
JFS, the open source implementation, you will spot the terms "transaction"
and "file system metadata" in the include headers. JFS is, of course, a
full grown file system with various options. ASM is not, but Oracle still
needs to know where exactly on the disk block 20A3F in the file 133 is.
ASM takes care of that. DBA puts a bunch of raw devices into an ASM disk
group and ASM creates extent map and "directories" for him.

On the operating system level, one can control caching of file blocks and
prefetch. I was trying to do the same with ASM, but to no avail. One very
nice thing about general purpose cluster file system like JFS is that one
can open files using the O_DIRECT flag and without it. If the file is
opened without the O_DIRECT flag, it will be buffered and the file system
prefetch will be applied to it. That will be of great help for utilities
like tar, cpio, cp, scp or gzip and performance of these utilities will be
as expected. If, on the other hand, ASM or OCFS is all you have, better be
prepared for a shock. Simple tar or cp operations will take hours,
literally. Not even the oracle version of those utilities will help much.
Buffering and prefetch are the only cure. That is why I moved
log_archive_dest outside of ASM, wherever possible. Still, the performance
of RMAN backup is abysmal. I am using RMAN with the MML library for
NetBackup. With the log_archive_dest (ext3 cross-mounted using NFS3) I am
getting 30 MB/sec transfer rate. With ASM - only 5MB sec. I am alleviating
the problem by using "BACKUP AS COMPRESSED BACKUPSET", but that, too, is
slow. I was hoping for an under the hood file system cache and prefetch
implementation.



--
http://mgogala.freehostia.com

On Jan 27, 4:17 pm, Mladen Gogala wrote:
> On Sat, 26 Jan 2008 19:14:14 -0800, Charles Hooper wrote:
> > "Take the Guesswork Out of Database Layout and I/O Tuning with Automatic
> > Storage Management"
> >http://www.oracle.com/technology/pro...df/take%20the%
>
> 20guesswork%20out%20of%20db%20tuning%2001-06.pdf
>
> > "The Kernel Sequential File I/O (ksfq) provides support for sequential
> > disk/tape access and buffer management. Ksfq allocates 4 sequential
> > buffers by default. The size of the buffers is determined by
> > dbfile_direct_io_count parameter set to 1MB by default. Some of the
> > ksfq clients are Datafile, Redolog file, RMAN, Archive log file,
> > Datapump, Data Guard and the File Transfer Package."
>
> Charles, this is a great document. Here is my brief understanding how
> ASM works. ASM runs in user space, not in kernel space, which means it
> isn't a driver. It only provides database processes (s00x, dbwr, lgwr,
> ckpt) with locations where to read from or write to. The IO calls
> themselves are still performed by the corresponding database processes
> and are targeted to the underlying raw devices.
> In other words, ASM handles what is known as "file system metadata" -
> directories, files, extent maps and alike. If you take a look at IBM
> JFS, the open source implementation, you will spot the terms "transaction"
> and "file system metadata" in the include headers. JFS is, of course, a
> full grown file system with various options. ASM is not, but Oracle still
> needs to know where exactly on the disk block 20A3F in the file 133 is.
> ASM takes care of that. DBA puts a bunch of raw devices into an ASM disk
> group and ASM creates extent map and "directories" for him.
>
> On the operating system level, one can control caching of file blocks and
> prefetch. I was trying to do the same with ASM, but to no avail. One very
> nice thing about general purpose cluster file system like JFS is that one
> can open files using the O_DIRECT flag and without it. If the file is
> opened without the O_DIRECT flag, it will be buffered and the file system
> prefetch will be applied to it. That will be of great help for utilities
> like tar, cpio, cp, scp or gzip and performance of these utilities will be
> as expected. If, on the other hand, ASM or OCFS is all you have, better be
> prepared for a shock. Simple tar or cp operations will take hours,
> literally. Not even the oracle version of those utilities will help much.
> Buffering and prefetch are the only cure. That is why I moved
> log_archive_dest outside of ASM, wherever possible. Still, the performance
> of RMAN backup is abysmal. I am using RMAN with the MML library for
> NetBackup. With the log_archive_dest (ext3 cross-mounted using NFS3) I am
> getting 30 MB/sec transfer rate. With ASM - only 5MB sec. I am alleviating
> the problem by using "BACKUP AS COMPRESSED BACKUPSET", but that, too, is
> slow. I was hoping for an under the hood file system cache and prefetch
> implementation.
>
> --http://mgogala.freehostia.com

Hi Mladen,

I appreciate this analysis, as it is difficult to get this information
from Oracle in a straight forward, plain ole' English manner
sometimes. I will say that I have seen really good throughput moving
ASM files with dbms_file_transfer, using both the copy_file (local)
and put_file (remote) procedures.

Regards,

Steve

On Fri, 25 Jan 2008 20:23:30 -0600, Bob Jones wrote:

> 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.

And they're doing surprisingly good job with it. Unfortunately, they're
doing less then stellar job documenting it.

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

On Sat, 26 Jan 2008 13:12:04 -0600, Bob Jones wrote:

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

Bob, I followed your quarrel with other people on this group, most
notably Connor McDonald. Please, note that not every topic on this
group is about BCHR. I don't want to discuss that all over again.

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

On Jan 27, 1:17*pm, Mladen Gogala wrote:
> On Sat, 26 Jan 2008 19:14:14 -0800, Charles Hooper wrote:
> > "Take the Guesswork Out of Database Layout and I/O Tuning with Automatic
> > Storage Management"
> >http://www.oracle.com/technology/pro...df/take%20the%
>
> 20guesswork%20out%20of%20db%20tuning%2001-06.pdf
>
> > "The Kernel Sequential File I/O (ksfq) provides support for sequential
> > disk/tape access and buffer management. *Ksfq allocates 4 sequential
> > buffers by default. *The size of the buffers is determined by
> > dbfile_direct_io_count parameter set to 1MB by default. *Some of the
> > ksfq clients are Datafile, Redolog file, RMAN, Archive log file,
> > Datapump, Data Guard and the File Transfer Package."
>
> Charles, this is a great document. Here is my brief understanding how
> ASM works. ASM runs in user space, not in kernel space, which means it
> isn't a driver. It only provides database processes (s00x, dbwr, lgwr,
> ckpt) with locations where to read from or write to. The IO calls
> themselves are still performed by the corresponding database processes
> and are targeted to the underlying raw devices.
> In other words, ASM handles what is known as "file system metadata" -
> directories, files, extent maps and alike. If you take a look at IBM
> JFS, the open source implementation, you will spot the terms "transaction"
> and "file system metadata" in the include headers. JFS is, of course, a
> full grown file system with various options. ASM is not, but Oracle still
> needs to know where exactly on the disk block 20A3F in the file 133 is.
> ASM takes care of that. DBA puts a bunch of raw devices into an ASM disk
> group and ASM creates extent map and "directories" for him.
>
> On the operating system level, one can control caching of file blocks and
> prefetch. I was trying to do the same with ASM, but to no avail. One very
> nice thing about general purpose cluster file system like JFS is that one
> can open files using the O_DIRECT flag and without it. If the file is
> opened without the O_DIRECT flag, it will be buffered and the file system
> prefetch will be applied to it. That will be of great help for *utilities
> like tar, cpio, cp, scp or gzip and performance of these utilities will be
> as expected. If, on the other hand, ASM or OCFS is all you have, better be
> prepared for a shock. Simple tar or cp operations will take hours,
> literally. Not even the oracle version of those utilities will help much.
> Buffering and prefetch are the only cure. That is why I moved
> log_archive_dest outside of ASM, wherever possible. Still, the performance
> of RMAN backup is abysmal. I am using RMAN with the MML library for
> NetBackup. With the log_archive_dest (ext3 cross-mounted using NFS3) I am
> getting 30 MB/sec transfer rate. With ASM - only 5MB sec. I am alleviating
> the problem by using "BACKUP AS COMPRESSED BACKUPSET", but that, too, is
> slow. I was hoping for an under the hood file system cache and prefetch
> implementation.
>
> --http://mgogala.freehostia.com

Have you tried increasing the channel count? Do the PX waits increase
if you do?

I'm wondering where the real problem lies, whether it really is with
ASM or something that is a consequence of the MML. Or RAC issues as
implied by those gcs and ges waits. Maybe ASM just isn't always smart
enough to not have to figure out which node has which appropriate
block and has to ask and wait around to find out. Have you tried it
without RAC?

jg
--
@home.com is bogus.
"You, you, and you. Panic. The rest of you come with me." - U.S.
Marine Gunnery Sgt

On Mon, 28 Jan 2008 11:01:21 -0800, joel garry wrote:

> On Jan 27, 1:17Â*pm, Mladen Gogala wrote:
>> On Sat, 26 Jan 2008 19:14:14 -0800, Charles Hooper wrote:
>> > "Take the Guesswork Out of Database Layout and I/O Tuning with
>> > Automatic Storage Management"
>> >http://www.oracle.com/technology/pro...df/take%20the%
>>
>> 20guesswork%20out%20of%20db%20tuning%2001-06.pdf
>>
>> > "The Kernel Sequential File I/O (ksfq) provides support for
>> > sequential disk/tape access and buffer management. Â*Ksfq allocates 4
>> > sequential buffers by default. Â*The size of the buffers is determined
>> > by dbfile_direct_io_count parameter set to 1MB by default. Â*Some of
>> > the ksfq clients are Datafile, Redolog file, RMAN, Archive log file,
>> > Datapump, Data Guard and the File Transfer Package."
>>
>> Charles, this is a great document. Here is my brief understanding how
>> ASM works. ASM runs in user space, not in kernel space, which means it
>> isn't a driver. It only provides database processes (s00x, dbwr, lgwr,
>> ckpt) with locations where to read from or write to. The IO calls
>> themselves are still performed by the corresponding database processes
>> and are targeted to the underlying raw devices. In other words, ASM
>> handles what is known as "file system metadata" - directories, files,
>> extent maps and alike. If you take a look at IBM JFS, the open source
>> implementation, you will spot the terms "transaction" and "file system
>> metadata" in the include headers. JFS is, of course, a full grown file
>> system with various options. ASM is not, but Oracle still needs to know
>> where exactly on the disk block 20A3F in the file 133 is. ASM takes
>> care of that. DBA puts a bunch of raw devices into an ASM disk group
>> and ASM creates extent map and "directories" for him.
>>
>> On the operating system level, one can control caching of file blocks
>> and prefetch. I was trying to do the same with ASM, but to no avail.
>> One very nice thing about general purpose cluster file system like JFS
>> is that one can open files using the O_DIRECT flag and without it. If
>> the file is opened without the O_DIRECT flag, it will be buffered and
>> the file system prefetch will be applied to it. That will be of great
>> help for Â*utilities like tar, cpio, cp, scp or gzip and performance of
>> these utilities will be as expected. If, on the other hand, ASM or OCFS
>> is all you have, better be prepared for a shock. Simple tar or cp
>> operations will take hours, literally. Not even the oracle version of
>> those utilities will help much. Buffering and prefetch are the only
>> cure. That is why I moved log_archive_dest outside of ASM, wherever
>> possible. Still, the performance of RMAN backup is abysmal. I am using
>> RMAN with the MML library for NetBackup. With the log_archive_dest
>> (ext3 cross-mounted using NFS3) I am getting 30 MB/sec transfer rate.
>> With ASM - only 5MB sec. I am alleviating the problem by using "BACKUP
>> AS COMPRESSED BACKUPSET", but that, too, is slow. I was hoping for an
>> under the hood file system cache and prefetch implementation.
>>
>> --http://mgogala.freehostia.com
>
> Have you tried increasing the channel count? Do the PX waits increase
> if you do?
>
> I'm wondering where the real problem lies, whether it really is with ASM
> or something that is a consequence of the MML. Or RAC issues as implied
> by those gcs and ges waits. Maybe ASM just isn't always smart enough to
> not have to figure out which node has which appropriate block and has to
> ask and wait around to find out. Have you tried it without RAC?
>
> jg

Joel, this is actually a suggestion made by a colleague of mine. I
will definitely try it out. When two smart guys suggest the same thing,
then it is likely to be the solution. I'll post the results.



--
http://mgogala.freehostia.com

"Mladen Gogala" wrote in message
news:479d8ff9$0$1344$834e42db-at-reader.greatnowhere. com...
> On Sat, 26 Jan 2008 13:12:04 -0600, Bob Jones wrote:
>
>> Well, but then again, many "experts" in this group think BCHR does not
>> matter even in regular databases. LMAO.
>
> Bob, I followed your quarrel with other people on this group, most
> notably Connor McDonald. Please, note that not every topic on this
> group is about BCHR. I don't want to discuss that all over again.
>

Neither do I. You may be mistaken. I don't recall anyone by that name.