 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Oracle / Oracle Server / January 2008ASM 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 > 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. [quoted text clipped - 6 lines] > -- > 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/2007/05/20/system-stats-strategy/ Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc. > 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 [quoted text clipped - 8 lines] > 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.  Signaturehttp://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 [quoted text clipped - 17 lines] > > --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/B28359_01/backup.111/b28270/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/products/secure-backup/pdf/oracle_tuning_backup.pdf Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc. >> > I don't use ASM, but there may be some hints provided in "Cost-Based >> > Oracle Fundamentals" to explain what you are seeing. Paraphrased [quoted text clipped - 27 lines] > 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. Signaturehttp://mgogala.freehostia.com > 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. Signaturehttp://mgogala.freehostia.com >> However, ASM instance parameters are very poorly documented. There are >> no documents explaining how to monitor and change those parameters. [quoted text clipped - 57 lines] > 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. (db block gets + consistent gets - physical reads)/(db block gets + consistent gets) Well, but then again, many "experts" in this group think BCHR does not matter even in regular databases. LMAO. > "Mladen Gogala" <mgog...@yahoo.com> wrote in message > > I wonder whether buffer cache hit ratio would make sense for the ASM [quoted text clipped - 4 lines] > (db block gets + consistent gets - physical reads)/(db 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('db block gets','consistent gets','physical reads'); INST_ID NAME VALUE ---------- ------------------------------ ---------- 1 db block gets 0 1 consistent gets 0 1 physical reads 0 2 db block gets 0 2 consistent gets 0 2 physical reads 0 6 rows selected. SQL> >> "Mladen Gogala" <mgog...@yahoo.com> wrote in message >> > I wonder whether buffer cache hit ratio would make sense for the ASM [quoted text clipped - 31 lines] > > SQL> These stats may not be applicable for ASM then. > 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 >> 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. > >> Well, but then again, many "experts" in this group think BCHR does not > >> matter even in regular databases. LMAO. [quoted text clipped - 4 lines] > > Neither do I. You may be mistaken. I don't recall anyone by that name. Well, google recalls that you should: http://groups.google.com/groups/search?lr=&safe=off&num=10&q=%22bob+jones%22+%22 connor+McDonald%22+bchr+group%3Acomp.databases.oracle.*&safe=off&qt_s=Search jg -- @home.com is bogus http://www.dannorris.com/2008/01/23/acquisition-wednesday-round-2-indian-firm-ro lta-buys-tusc/ On Jan 28, 4:48 pm, "Bob Jones" <em...@me.not> wrote: > "Mladen Gogala" <mgog...@yahoo.com> wrote in message > [quoted text clipped - 10 lines] > > Neither do I. You may be mistaken. I don't recall anyone by that name. > Well, google recalls that you should: > http://groups.google.com/groups/search?lr=&safe=off&num=10&q=%22bob+jones%22+%22 connor+McDonald%22+bchr+group%-- - > - > 3Acomp.databases.oracle.*&safe=off&qt_s=Search > jg Someone quoted his name. I have never talked to this person. What is your concern again? > > Thanks for the clarification. With the inclusion of DB_CACHE_SIZE, > > DB_FILE_MULTIBLOCK_READ_COUNT, and MBRC, I jumped to the conclusion that [quoted text clipped - 53 lines] > > --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/products/database/asm/pdf/take%20the%20guesswor k%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." Please continue to keep the group informed of your progress. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc. > "Take the Guesswork Out of Database Layout and I/O Tuning with Automatic > Storage Management" [quoted text clipped - 5 lines] > 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. Signaturehttp://mgogala.freehostia.com > > "Take the Guesswork Out of Database Layout and I/O Tuning with Automatic > > Storage Management" [quoted text clipped - 44 lines] > > --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 > > "Take the Guesswork Out of Database Layout and I/O Tuning with Automatic > > Storage Management" [quoted text clipped - 44 lines] > > --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 >> > "Take the Guesswork Out of Database Layout and I/O Tuning with >> > Automatic Storage Management" [quoted text clipped - 55 lines] > > 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. Signaturehttp://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 [quoted text clipped - 8 lines] > 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. Signaturehttp://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 [quoted text clipped - 15 lines] > 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. > 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 >> 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. Do you mean they did a good job in being awkward? > 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. [quoted text clipped - 6 lines] > -- > Mladen Gogalahttp://mgogala.freehostia.com 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 ). If you combine changing dfmbrc and setting oracle 10046 traces you can see that at some point increases are ignored ( maybe better wording is no longer applicable ). Whether you are using ASM or doing IO directly from a database instance it probably works the same way would be my guess. > 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. Signaturehttp://mgogala.freehostia.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2010 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.