No drawbacks?
Albert Nelson A, April 20, 2011 - 4:43 am UTC
Hi Tom,
Does that mean there is no disadvantage of having a bigger cache?
Suppose the performance with 8 GB cache itself is within acceptable level and if the server is upgraded with additional memory of 60 GB can we just use those additional memory to increase buffer cache size to 60 GB?
Regards,
Albert Nelson A
April 20, 2011 - 8:11 am UTC
I didn't say that - I was saying there might not be a problem, it is not necessarily an issue.
lets take your example, currently 8gb and working fine, go to 60gb...
Maybe you just took that memory from the real memory of the system and in the past that memory was used for the PGA to perform sorts and hashing and the like. Now you don't have that memory there for that purpose anymore and the performance goes down the tank.
OR
Maybe you just took that memory from the real memory of the system and in the past that memory was used for file system caching (like a secondary SGA). Now you are using the memory for the buffer cache directly and performance improves because you are not having to go out to the file system cache to get things, it is already in your SGA
OR
Maybe you just took that memory from the real memory of the system and in the past that memory was not being used for anything. That would indicate that it wouldn't be useful in the buffer cache (no file system caching was being used really) and hence the overall net affect might be "nothing" - you are neither faster nor slower (but your startup times are greatly increased since a big SGA takes longer to setup!)
enq: TX - index contention & buffer busy waits,
A reader, April 20, 2011 - 9:23 am UTC
Hello,
We have a range partitioned table (interval partition on a date column) that is very highly populated (millions of records per day). This table has 4 Indexes two of them local and 2 of them non-partitioned. One of the non-partitioned Index is on an attribute that gets value from a sequence.
We are seeing these two wait events predominantly every time (enq: TX - index contention & buffer busy waits).
In one of your thread (yesterday) you explained about hash-partition the Index on the column that gets value from sequence. We did that. It is now having 128 partitions. So we now have 2 local indexes, 1 hash-partitoned index and 1 non-partitioned index. However, those two wait events is still predominant. In fact, the elapsed time per execution was 0.04 seconds prior to making the Index hash-partitioned and now it is 0.13 seconds.
How to debug this problem?
April 20, 2011 - 12:38 pm UTC
did you first verify that you were getting the waits on the segment you partitioned?
follow up,
A reader, April 20, 2011 - 12:48 pm UTC
No. not sure how to verify that. If you have infomation on that, could you please pass it on?
Thanks,
April 20, 2011 - 12:55 pm UTC
look at v$segment_statistics. Take a snapshot of it (copy) - wait a bit of time, take another snapshot. Then subtract the first copy values from the second copy and you'll know how many of various statistics occurred in that timeframe.
Good one,
A reader, April 20, 2011 - 1:28 pm UTC
That's a good one. The job runs in US night time. I have asked my couterpart to perform that action and send me the report. thanks for your advice.
Pro-Active DB Cache Tuning.
A reader, April 20, 2011 - 2:53 pm UTC
Hi Tom,
How can one calculate the right size for the DB Buffer Cache?
As per your suggestion and my experience I would always prefer to have the logical IO less for an given sql. But how can I come to an conclusion that given a SQL query with low logical IO and inadequate buffer cache(High Physical Reads) and tune that buffer cache or increase the size of buffer cache assuming I have enough physical memory apart from the one used for files system and PGA. Should I look into the physical reads for that SQL flush the cache increase the cache and check again for low physical reads, will this be any feasible test to have efficient performance. Or will it be a worth while to try the recycle and keep pools of the buffer cache for that objects used by SQL.
Thanks.
April 25, 2011 - 7:43 am UTC
use the buffer cache advisor available via a statspack report or AWR report. It will tell you, based on your workload, what the appropriate size would be (it will tell you "if you double the size of your cache, the physical IOs would go from X to Y, if you made your buffer cache 150% of its size, they would go from A to B" and so on.
It is reactive, in order to predict what your IO rates would be - we need to understand your workload. No workload, no way to really estimate
SGA
A reader, April 20, 2011 - 3:58 pm UTC
How would you validate the above options you listed on whether more memory can harm or benefit system performance.
Is not the only way to build two test boxes (one with 8 GB and another with 60 GB) and see how the application performs.
Cant the Oracle Advisors tell you what is the right amount of Buffer cache you need and in most cases i saw they keep asking for more memory. Of course this depends on the size of application and whether the SQL uses Bind variables or not.
April 25, 2011 - 7:47 am UTC
How would you validate the above options you listed on whether more memory can
harm or benefit system performance.
same way I'd validate anything - benchmark it.
Some of it can sort of be validates without benchmarking, by using your knowledge of the system you are working on. Look at what I wrote, can you - should you be able to answer some of those just by knowing?
The advisor would tell you "if you set the buffer cache to X, your physical IOs will become Y".
The advisor will not tell you "but - if you set it to X, the memory we used to use for sorting will not be available anymore". It cannot tell you about the side effects I mentioned above.
Follow up,
A reader, April 21, 2011 - 9:57 am UTC
Hello,
The APJ team ran the SQL query on v$segment_statistics frequently when the INSERT job was running. It was found that a particular Index partition (on a change_date column) constituated the major portion of buffer busy waits. The index which we hash partitioned (a day ago) did not appear in the list.
I think we will try to hash partition this Index as well. Currently this is range partioined. This will probably reduce buffer busy waits.
However, the enq Index contention still appears in the AWR report. How to figure out which Index is causing this enq wait event?
Thanks,
April 25, 2011 - 8:19 am UTC
"I think we will try to hash partition this Index as well. Currently this is
range partioined. This will probably reduce buffer busy waits. "
ummm, that would be a bad idea probably. If it is range partitioned, you probably do range scans on it, you won't be doing those anymore if you hash it.
see
http://forums.oracle.com/forums/thread.jspa?threadID=1048776 and see the writeups by Jonathan Lewis therein.
AWR reports should help
Charlie B., April 21, 2011 - 10:17 am UTC
I'd think that Oracle's AWR report should be able to help you identify the source of the contention issues. In particular, it has sections (in 11.1) with "Segments by Row Lock Waits" and "Segments by ITL Waits".
A reader, April 25, 2011 - 9:40 am UTC
Hi Tom,
Thanks your advice sir, as per your suggestion I see in peak load of awr reports buffer advisor the current size of buffer cache set to 816M estimated physical reads are
3,137 thousands and increasing the buffer cache to 1,600M will result in 2,920 thousands. Do you suggested to increase the Buffer Cache to estimated double the size than the current as the significant reduce in the physical reads is comparatively less and can i assume the current buffer cache size as an optimal size.
Thanks for your suggestions in advance.
April 25, 2011 - 1:25 pm UTC
"it depends". Would a 7% drop in physical IO's make a difference for you timewise? Would a 100% increase in the buffer cache size be taking memory away from something else that could use it?
A reader, April 27, 2011 - 8:22 am UTC
Thanks for the response sir, we have 32 GB real memory and currently have 3GB for SGA and 1GB for pga and the datafiles are placed on filesystem and how can I know the real memory available after allocating (PGA+SGA) and the memory used for filesystem, we dont have any other DB on this box.
Thanks for your suggestions in advance.
April 27, 2011 - 9:09 am UTC
I can only tell you to read the output of the advisors and use your knowledge of the machine and what it supports to determine where to allocate your memory.
If you have 32gb of real memory, and you are only using 4gb of it for the database....
See if the pga advisor says to go "up" and judge for yourself (you have the machine) if a 7% reduction in physical IO would be useful (probably not actually, since you have given the OS some 25gb to use as a file system cache.... your physical IO's are probably not truly physical IO's)
A reader, April 27, 2011 - 12:30 pm UTC
Thanks for the response sir. I would definitely take you suggestion into account and see the what PGA advisors are upto. But is there any way to find out how the remaining 25 gb is used for file systems or not. It's is sun solaris sparc machine. Thanks for time and valuable suggestions
April 27, 2011 - 1:28 pm UTC
If you are not using it for anything else, Solaris will use it to buffer the buffered file systems you have. So, unless you are using directio mount options, it is being used for a file system cache.
A reader, April 27, 2011 - 2:25 pm UTC
Thanks for the response Sir. I don't think the disks are directly mounted on the server we have here SAN and some volume manager for the space management, I will run some tests with the values suggested by the adviser's and see how the performance will effect. Thanks for your valuable suggestions.