Thanks Tom!
Robert, November 12, 2009 - 12:07 pm UTC
Thanks Tom, for the explanation and action plan.
So by increasing my buffer cache can I anticipate getting my times back to what they were before ASM (10 times faster)? Or is this just an improvement on a 'bad' situation I will just have to live with?
Thanks,
Robert.
November 15, 2009 - 2:17 pm UTC
if you increase the SGA buffer cache to account for what you used to use in the 'secondary sga', you might well find it to be faster than before - as we do not have to search the buffer cache, not find the block, and bother the OS to do the lookup, we would just find the block.
Important Follow up Clarification?......
Robert, November 13, 2009 - 1:05 pm UTC
Tom,
Do you mean increase SGA size of ASM instance or RDBMS instance? ... I had assumed the RDBMS instance until a colleague asked ASM or RDBMS?
Please advise?
Thanks,
Robert.
November 15, 2009 - 3:09 pm UTC
the buffer cache, nothing to do with ASM
the ASM instance doesn't cache blocks, the buffer caches on the individual instances do
Very interesting Revelation.
Dhairyasheel, November 16, 2009 - 7:50 am UTC
"but it would not be a true physical IO because the operating system would find it in ITS cache - the IO would be really fast because the OS never went to disk."
Hi Tom as per your above reply, could you elaborate more on ITS cache. How & where to find it on say Solaris or RHEL.
November 23, 2009 - 11:51 am UTC
you don't need to "find it", it is just there - any memory you are not using for something else, the OS will use to buffer the buffered file systems.
If you run a memory intensive program, the OS file system cache will shrink and maybe virtually disappear.
Stop running things that use lots of physical RAM and it'll grow again.
Since memory is a "use it or lose it" resource, like CPU, they use it when it is there and do not when it isn't.
ITS?
Johnny, November 16, 2009 - 8:19 am UTC
Dhairyasheel,
When Tom mentioned ITS, I don't believe Tom was inventing a new TLA (three letter acronym)! More likely it was uppercase for emphasis. i.e. the operating system found the data in its cache and therefore didn't have to go to disk.
November 23, 2009 - 11:59 am UTC
correct - I can see how I might have been confusing there.
Dhairyasheel and Johnny
Robert, November 16, 2009 - 3:50 pm UTC
Yes... "ITS" as in the 3rd person singular neuter possessive pronoun... correct? (e.g. its own cache; its very own cache, etc.).
November 23, 2009 - 12:30 pm UTC
correct, I capitalized for emphasis, but might have confused instead.
No. of write calls
A reader, November 17, 2009 - 8:21 am UTC
Hi Tom,
Not sure if this fits here.
1) Having two failure groups someone has to "double" the initial write call as the block has to be written to both groups. Who is responsible for this "doubling"? Is it DBWR itself? Does it simply write each block twice?
2) The doubled write calls have to be done as an atomic action. Who ensures atomicity?
3) Doesn't this "doubling" affect performance negatively? Hardware RAID would be somewhat faster?
Cheers,
Markus
November 23, 2009 - 2:16 pm UTC
1) dbwr, yes.
2) they don't have to be atomic, in fact - one of them can fail (that is part of the purpose). But dbwr would typically do async IO to both devices and wait for the OS to notify it of the completed write.
3) we write in parallel, just like the hardware would be doing. dbwr does this all of the time, even in non mirrored setup. dbwr gets a big set of blocks to write and writes them all using async IO and waits for the "disk device" to tell it "all done"
ASM to ASM Migration.
Win, November 17, 2009 - 9:31 pm UTC
Hi Tom, Recently we have performed ASM to ASM migration and post migration couple of batch runs were too slow compare to previous ASM configuration. After couple of runs we increased the size of DB_CACHE and process was much faster this time.
My question is, does it take ASM a couple of process runs to identify hot blocks and arrange them across disks? DB parameters remains the same. the only change was underlying SAN configuration thus there were a need of a new ASM configuration. Please suggest if it is expected to give bad performance follwed by an improved performance when migrating to ASM due to hot block detection and arrangement?
November 23, 2009 - 2:41 pm UTC
... My question is, does it take ASM a couple of process runs to identify hot
blocks and arrange them across disks?...
it doesn't do that at all - unless you do something that causes a rebalance, like adding more storage, removing storage or invoking it directly.
you hit the secondary sga thing entirely - "we increased the size of the db_cache", you are exactly in the case of the secondary sga - read about it and understand what used to happen (OS file system cache) and why that means when you move of a raw, unbuffered IO device from a file system buffered one - you need to account for that.
No. of write calls
A reader, November 27, 2009 - 11:16 am UTC
Hi Tom,
Thanks for answering my questions. You wrote the writes don't have to be atomic. I don't understand that. The data could become stale then. The block scn could become 2 in failgroup 1 while still left on 1 in failgroup 2. Someone (something) has to ensure the data became not stale. Is there a special protocol?
Cheers,
Markus
November 28, 2009 - 1:52 pm UTC
... The data could become stale then. ...
??? No, it would not be. It would be a failure.
This is not any different from what we have been doing with multiplexed redo logs since the very early days.
Yes, we can deal with a parallel write succeeding on one device and not the other and we know what to do when it happens.
That's it! -- second SGA!
Robert, December 07, 2009 - 3:00 pm UTC
Hi Tom,
This was one of the best/simplest, most astonishing solutions I have seen (e.g. fast=true).
When I doubled my buffer cache from 3 to 6G, my batch jobs are running up to 20 times faster.
One follow up question.... Although performance is greatly improved, I still haven't gotten the performance that I had on the 'cooked' file system.
I am planning on increasing SGA another 3G when I get a chance.
Can I (more or less) expect to get same performance on ASM as I had on cooked disks (all things being equal) if I just add the correct amount of additional buffer cache?
Thanks!
Robert.
December 07, 2009 - 3:37 pm UTC
... Can I (more or less) expect to get same performance on ASM as I had on cooked
disks (all things being equal)...
should be faster actually, you won't have the overhead associated with the secondary sga, it'll all just be there already.
How big secondary SGA?
Robert, December 07, 2009 - 4:35 pm UTC
Tom,
Is there any 'reasonable' 'secondary-SGA' size?
I'm sure 'it depends' but just looking for a sanity-check.
I started out with 3G
I upped it to 6G (where it is now)
Would upping it again to 12G seem unreasonable/exsessive to achieve optimal 'secondary-SGA'?
Your thoughts?
Thanks!
Robert.
December 10, 2009 - 7:49 am UTC
you do not size the secondary sga, it is whatever is left over in the OS.
the machine has X units of memory.
You start the machine, the OS takes Y units of memory.
You start Oracle, Oracle takes X units of memory.
Assuming nothing else going on on the machine, you now have (X-Y-Z) units of memory left over, the OS tends to use that all for the secondary SGA.
If your Oracle memory usage goes from X to X++ (a big pga appears all of a sudden), the amount of "secondary sga" goes down.
Now, if you have a database machine - and the machine is all about you - you would assign to the pga what you wanted to assign to the pga and probably the rest to the SGA. In 11g, you might just give us a single memory setting and we'll adjust the pga/sga up and down as we see fit.
Let me ask again....less confused...
Robert, December 11, 2009 - 11:02 am UTC
Tom,
My previews question was not clear...
I was referring to my SGA_TARGET size.
Originally 3g.. upped it to 6g and ASM performance improved drastically but still not as good as it was before ASM.
So I'm thinking of increasing SGA_TARGET to 12G to see if I can get performance as good as it was pre-ASM.
Since you said ASM should perform better than cooked disks... I was just wondering 'ballpark' if this strategy seemed 'excessive' as far as SGA_TARGET size increase goes.
What do you think?
Thanks,
Robert.
December 14, 2009 - 7:42 am UTC
before, when using cooked file systems, all free memory on the machine was used as a secondary sga.
now, using raw/ASM, all free memory is just free memory.
Use it or lose it.
If your performance gains were centered around "physical IO was reduced", and the buffer cache advisor indicates that "you would tend to gain by caching more", then yes, increasing the buffer cache would likely be a positive thing.
Secondary SGA
Mike in Brussels, September 01, 2010 - 9:37 am UTC
Hi Tom,
One thing I'm not fully understanding here...
Given that the non-ASM scenario on the cooked filesystem will use *all* available free memory for its IO cache (the 'secondary SGA'), surely this is likely to make more efficient use of memory than using ASM with a fixed size buffer cache?
I understand that a larger buffer cache used with ASM will reduce the physical reads, but that would be true whether or not you are using ASM, its just that when the physical reads do occur, they are likely be faster without ASM, as they may be found in the o/s IO cache.
In other words, when using ASM there is always likely to be unused free memory whereas without ASM, this memory is put to good use as the IO cache.
Thanks,
Mike.
September 09, 2010 - 6:46 pm UTC
... surely this is
likely to make more efficient use of memory than using ASM with a fixed size
buffer cache?
...
no, not really. The machine is a database machine (typically - you do not use your server for a database machine and a print server and a file server....). The memory is either for the database or the OS.
If you use the OS as a secondary cache, there are two layers of cache, we have to look in OUR cache and the then OS has to look in its cache and then we go to the file system for real.
If you just gave the memory to the SGA in the first place - it would be - look in our cache and find it in there often and then go to disk. The logical IOs to our cache would be much more efficient than the "IO's to the filesystem cache" are.
If you size the SGA correctly given your real memory - you will have the best overall response time.
ASM vs Non-ASm
Mike in Brussels, September 10, 2010 - 9:24 am UTC
Thanks Tom,
"If you size the SGA correctly given your real memory - you will have the best overall response time"
- but sizing the SGA correctly is independent of whether or not you are using ASM isn't it? Having sized the SGA correctly, any physical reads that do occur are likely to be faster without ASM, as there is a chance they will be found in the os cache. Not trying to be pedantic here, but this becomes a real issue when trying to do the best with undersized hardware.
Mike Barratt
September 13, 2010 - 7:46 am UTC
... but sizing the SGA correctly is independent of whether or not you are using
ASM isn't it? ..
no entirely if you have a buffered file system, you could undersize the SGA and accidentally get "decent" performance due to the secondary SGA effect. If you switch to a non-buffered filesystem - you would immediately see that effect (of removing the double buffer) and then by resizing the SGA so that your cache does the work that the secondary SGA used to do - you'll see better then decent results.
... any physical reads that do occur
are likely to be faster without ASM, as there is a chance they will be found in
the os cache. ...
NOT TRUE, since you would have sized the SGA to a size that makes it so that the reads that were in the file system cache would be in the SGA already. The SGA would make the filesystem buffer cache be basically non-existent for database data, it would already be in the SGA and if it was not in the SGA it wouldn't fit in the filesystem cache since the SGA would be using that memory already.
The point would be to grow the SGA to render the filesystem cache useless basically.
A reader, September 13, 2010 - 10:11 am UTC
Hello Sir,
Is it true that the read satisfy by OS cache may be expensive since oracle have to invoke os routine (at low level) where OS have to search in its own cache for the block(s) in question before go for disk read which might be little lengthier and costly rather than we have ASM where SGA have been sized properly since the block liklly to be found in SGA and if not than do disk read rather than going for that context switch to check whether it present in OS cache or not
So what I mean is that context switch between Oracle and OS can be remove in cas of ASM and sizing SGA properly
Please advice
Many Thanks
September 13, 2010 - 2:48 pm UTC
Is it true that the read satisfy by OS cache may be expensive since oracle have
to invoke os routine (at low level) where OS have to search in its own cache
for the block(s) in question before go for disk read
that is the point, yes.
it is not just the system call overhead ( the context switch), it is all of the work that the OS must do that we already do (search a cache)...
Filesystem caching
A reader, September 14, 2010 - 4:00 am UTC
I have found that a filesystem cache can be beneficial in applications that perform full scans of large tables. If they are too big even increasing the SGA can lead to not all of the table being cached even if queried often whereas the filesystem was able to cache this entirely. One way around this if you know your application well and have the memory (and can't fix the frequent full scan) is to alter table ... cache, though you obviously have to be very careful doing this with large tables. Ideally correcting the application or an index should work, but this isn't always possible especially with 3rd party applications.
One other use of a filesystem cache is when you are stuck with a 32 bit database on a machine with extra memory. If you don't want to use the workarounds to increase maximum memory the secondary caching effect of the filesystem can be useful.
So in summary I think the filesystem cache can 'occasionally' provide benefit in poorly performing applications but otherwise I agree with Tom and giving all of that memory to the SGA, and avoiding the filesystem cache, is optimal.
ASM Performance
Mike in Brussels, September 14, 2010 - 10:03 am UTC
"NOT TRUE, since you would have sized the SGA to a size that makes it so that the reads that were in the file system cache would be in the SGA already"
But you can't necessarily do that. Given that in sizing your SGA, you must allow for variable PGA and anything else that MAY require memory, there has to be a percentage unused as the load fluctuates. Surely, without ASM, there is a chance that the free memory can be put to good use as file system cache.
In your explanations above, you have always compared (non-ASM with small SGA) vs (ASM with large SGA). Thats not fair! I would argue that performance with ASM will be <= performance without ASM given the same size SGA.
- Especially for full-table scans, placed at the LRU end of the buffer cache. These will be quickly aged out of the SGA, but may hang around in that free memory used by the file system cache, if not using ASM.
Thanks,
Mike Barratt
September 15, 2010 - 7:21 am UTC
... I would argue that performance with
ASM will be <= performance without ASM given the same size SGA.
...
and I will argue that the performance with ASM (or just raw if you want) will be > performance without ASM given the right size SGA.
Getting a million blocks out of a file system cache for a full scan (looking for them a million times - finding some, not finding others - and remember - they have an LRU too, if the table is truly large enough to care about, it would have aged out as well) is slow - one of the reasons parallel query typically *FLUSHES THE BUFFER CACHE* in hopes of just doing multi-block IO's against the file system without a cache (since the cache actually gets quite in the way in some cases)
ASM vs UFS/ZFS + DIRECT IO
lalu, November 07, 2010 - 11:24 am UTC
Hi Tom,
We are planning for a new database.OS is to be solaris 10 with oracle 10g/11g.
For the database file system which will be a better option from performance point of view:
1:ASM
2:UFS/ZFS with raid 10 with direct IO enabled.
Can we get same performance using UFS with direct IO compared to ASM?
Thanks.
November 08, 2010 - 8:12 am UTC
asm will be just like using raw partitions - without the "messiness" of raw partitions.
If I had the opportunity to pick - I would be looking at ASM overall - allows me to go clustered if and when I want, allows the DBA to control the storage - not the SA (some view this as a con, I view it as a pro).
ASM diskgroup
Reader, December 13, 2010 - 6:23 pm UTC
SAN admin will be allocating disk space from EMC Symmetric.
Is it advisable to create a asm disk group (external redundancy) with ONE lun of size 2TB or with 20 one hundred GB LUNs? Which one would you recommend? Thanks.
December 14, 2010 - 7:17 am UTC
since they are all logical devices and not really devices and you have no control over the layout/mapping - it doesn't really matter. probably a few big luns would be the most manageable.
buffer cache vs filesystem cache
A reader, January 13, 2011 - 5:03 am UTC
The blocks read from a FULL TABLE SCAN are early ejected from the Oracle Buffer Cache, but can be kept more easily in the filesystem cache (because the filesystem cache is not aware that a FTS occurs)
So when using ASM, the database will probably make more true physical I/O than when using a regular filesystem, and adding more DB_CACHE_SIZE wil have probably no effect in this case.
Is it true ?
January 13, 2011 - 10:38 am UTC
Well, think about this - is looking up 1,000,000 blocks in a cache that caches blocks (file the filesystem) and getting a miss on many or most of them (big full table scans) going to be fast? No, it isn't - that is why parallel query generally blows off the buffer cache entirely and uses direct IO - bypassing the cache altogether.
If you are doing a ton of large table full table scans - there is a small chance (if your large tables actually fit entirely into OS memory) in older releases that this would be true - in the exceptional case only.
In 11g, there is in memory full table scans for large tables - if we feel they can fit in the buffer cache.
thtsang, June 22, 2011 - 6:10 am UTC
On a database server with 32GB RAM, we have serveral instance. The sum of sga_target is around 15GB (and sum of pga_aggregate_target another 15G).
I would expect Oracle to occupy most memory. However, the 'free' result is as follows:
$ free -m
total used free shared buffers cached
Mem: 32167 32007 159 0 235 26015
-/+ buffers/cache: 5756 26411
Swap: 32767 700 32067
It seems from the OS's perspective, Oracle took only a little amount of the memory, such that it has 26GB RAM for its disk cache. So we are still using the 'secondary SGA'? Is this normal?
OS: Redhat Enterprise 5.2 x86-64
Oracle: 10.2.0.4 Standard Edition RAC
June 22, 2011 - 10:44 am UTC
PGA's are not allocated up front, they are allocated as needed. If you have few users connected, you would see very little pga being used. PGA will go up and down.
If your SGA's are not entirely "touched" (if we haven't used some of it as yet), the OS might not be counting that against you either.
Filesystem cache - Can duplication of the SGA happen?
Moorthy Rekapalli, August 30, 2011 - 6:25 pm UTC
Tom,
Thanks for your time in answering our questions. Your contribution to the Oracle community is priceless.
In the case of filesystem cache, is there a possibility of caching the same information in both SGA and filesystem cache? Just like Oracle has its algorithms to maintain SGA, O.S./filesystem may have its own algorithms to maintain what should be in cache and what should be flushed out when it can't fit everything in Physical Memory.
My assumption is O.S./filesystem may not know that Oracle (or any other software for that matter) already cached that information and let me cache something else. Based on its algorithms, it may re-cache whatever is already cached in SGA.
For example, let's say that Physical Memory on the Oracle server is 16G. Using 11g feature of AMM, we set the memory_target as 8G and using filesystems instead of ASM or raw devices. Out of the 8G, if db_cache_size happens to be 3G, filesystem may cache the same 3G again. Only when we are searching in the overflow above 3G, then one may see the benefit of that "Secondary SGA".
If this duplication happens, I see filesystems as a drawback. By properly sizing the SGA, in ASM or raw devices or using direct I/O in flesystems itself; one can better utilize the memory by avoiding the duplication, thus getting a bigger bang for the buck.
I am not 100% sure whether this duplication happens or not. Can you please chime in on this?
Thanks,
Moorthy.
August 31, 2011 - 2:01 pm UTC
is there a possibility of caching the same
information in both SGA and filesystem cache?
not only possible but highly probable. The most recently read information would be in the SGA - and in the filesystem cache.
If it were not possible then this "secondary sga effect" would not be possible!
My assumption is O.S./filesystem may not know that Oracle
correct.
Filesystem cache - Can duplication of the SGA happen?
Moorthy Rekapalli, August 31, 2011 - 10:12 am UTC
Tom,
Thanks for your quick response. Probably, you meant to say "not only possible but highly probable" right?
So, bigger the SGA; probability is also higher for the duplication of cache.
When using ASM or using filesystemio_options=directIO in the case of filesystems, one may use the memory much better by allocating that "duplicated secondary sga" portion to Primary Oracle SGA.
Thanks again,
Moorthy.
August 31, 2011 - 2:03 pm UTC
correct on the "not only", I updated that.
So, bigger the SGA; probability is also higher for the duplication of cache.
probably not. The bigger the SGA, the smaller the resulting size of the filesystem cache.
How about this - if the SGA is about the same size as the resulting free memory left on the machine after the database is up and running - and assuming the database is the main driver of all IO - the higher the probability of 'duplication'
The smaller the SGA - the higher the chance of duplication. In order to "duplicate", the block must be in the SGA and in the file system cache. If the SGA > filesystem cache - the duplication chances go down. If the SGA <= filesystem cache - the probability would be higher.
The performance optimization of ASM with 11.2.0.4
yulin, November 17, 2020 - 6:46 pm UTC
"With Oracle Database 11g Release 2 & ASM, all buffers are automatically sized for optimal performance."
Does this statement mean that there is no any effective way to manually adjust & optimize the equivalent ASM disks I/O buffer size or cnts.
November 18, 2020 - 12:00 am UTC
You *can* set the buffers with ASM, its just unlikely you'll see any real difference in performance in most cases.
There may be exceptions but our observation is that if you're having problems, its typically the disk subsystem itself, not the buffer sizes sitting atop it.