Other databases? How much resources can this particular database use? How Much IO can your system sustain? Check that out. Good luck! Oh, one more thought Most folks do not just 'happen' to have that. Why does the box have 10GB memory? Didn't someone estimate the needs and configure accordingly? Be sure to check back with the initial sizing requirements for the box and you are not unlikely to find an initial oracle sizing 'stake in the ground'.
Once the system is in production, you can use statspack to see whether the initial estimate needs to be adjusted based on actual usage and data patterns.
Now our sever has RAM 10G. Oracle SGA is about 1. There is only one Oracle database on this box. In other words, there is no other application on it. The server doesn't use any swap space currently. However, my boss doubt if we enlarge SGA to 2G, the performance should be better. How do I judge the suitable SGA size if we don't use any swap? Esteemed Contributor. Hi, based on my experience and if you are agin in the test phase this means that you can gete rrors without impacting production my suggetsion is to start with 2 GB of SGA, monitor it and in case enlarge it.
A good way to monitor it is using the attached sql script. HTH, Art. Exalted Contributor. Shalom, It totally depends on what the database is going to do.
If NO the please help me in understanding the real purpose of this file. October 19, - am UTC. It is not for locking the sga. It is an internal undocumented file that you should not delete or overwrite ;. I tried to increase SGA size greater than 1. Can you tell me how to increase the size of SGA greater than 1.
November 22, - am UTC. Need a bit of "detail", like - what you did, what os, etc. Nice to see differnet scenario.
But in ideal case, what should be the size of differed parts of SGA , how to calculate? January 28, - pm UTC.
If you don't really have a good idea - use the automatic SGA feature of 10g. Else, as stated - lots of times - you need slightly more information about what, how, whatever you will be doing and how much data you'll be doing it to and how many users will be doing it and so on.
A reader, February 01, - am UTC. I know, if I oversize my shared pool, it will degrade the performance due to shared pool latch contention and was of the opinion that though increasing buffer cache might not improve the performance but will atleast not degrade the performance of a database. Want to know your opinion on this.
Thanks and Regards Vivek. February 15, - am UTC. I do not agree with the diagnoses so much as far as 'increasing the shared pool will increase latch contention', or 'due to buffer cache contention' it is true that oversizing your SGA can lead to a better performance b the same performance c incredibly bad performance but, can you point us to something you have read so we can see the point being made in context? Hi Tom, Thanks for your reply.
In my question above , I have raised a doubt over an statement passed by one of the DBA that "an oversized SGA might degrade the performance due to buffer cache contention".
The DBA has specifically said about the buffer cache contention. Hence, my question was that will an oversized buffer cache degrade the performance? I feel that as I gradually increase the buffer cache, at one point of time, I will not see any performance improvement but will also not observe any degradation. Am I correct?
Regards Vivek. February 15, - pm UTC. The more you got, the more you got to manage. Bigger is not always better. Not everything is positively impacted by larger buffer caches.
Hi Tom. I have these questions: 1. Is there a query to determine that? Should both parameters be set to the same value initially and if not, why? Thanks for any advice. May 25, - pm UTC. There is no such thing as a "recommended gap". You are simply telling us a how large the sga should be right now b how big you would like to have the flexibility to grow it to without shutting down. Can you elaborate more A reader, May 26, - am UTC. Can you elaborate more on my first question? I am using AWR through Enterprise manager.
May 26, - am UTC. Right after you give me the indisputable logic, I'll show you how to email it. No, I cannot say that, you cannot say that. Tell me, what magical number would that be? And what if you have no more physical memory to give to the SGA? No more monitoring, no more thinking about how it could be, should be, would be used.
Set the PGA aggregate target and the SGA target to be a tad less than physical memory on the machine and you are done. We are adding another 4GB of physical memory to our database server and allocating 3GB of this to the recycle pool.
Oracle Server 9. OLAP application. Under consideration is to also lock the SGA into memory. Could you explain or direct to appropriate reference why or why not we would want to lock the SGA into memory? This is a concept I am not familiar with - and your guidance would be appreciated here.
September 20, - pm UTC. There was lots of memory, but it the OS was greedily keeping it in its filesystem cache. Rather than trying to teach Linux to be less "swappy", we pinned the SGA into memory by enabling hugepages. We now have 0 bytes of swap used--even after a subsequent SGA size increase--and are loving life again.
Tom's your-SGA-is-too-big-if-you-swap should be taken as doctrine, not dogma. Tom, I'd like to post this as a new topic, but since you're "Too Busy" for me story of my life ; I'll post a comment here. What are your thoughts about AMM? Since I've changed companies, I now have had a chance to look at it and I'm a little maybe more than a little suprised about a couple of things. First the stats of the box: Win Server bit 4 3.
With Oracle automatically reducing the buffer cache and increasing the Shared Pool, this seems counter intuitive to my tuning and reading your books experience. Isn't a 5. We have several legacy apps on this db that were recently upgraded and they don't use bind variables which I think may be causing Oracle to keep upsizing the shared pool. Your thoughts?
October 09, - pm UTC. Ahh, I see that now, you do. Yes, that is what auto memory management will tend to do over time, your shared pool is TRASHED, it says "instead of , we'll steal from the cache" You can set minimums for the various caches if you like - things that start with A work for many to most people, but not in every single case. When you have a disaster of a system - as you do no binds, ugh , you might have to do somethings "yourself".
As if I'd be happy letting any tool determine how my db runs. I much prefer controlling as many options as I can and using tools to point me in a sometimes not even the right direction. I have one of the database. Why is this showing like this? December 28, - am UTC.
Considering that we have set ASMM for our instance. Please help me in understanding the concepts. September 05, - pm UTC. Tom, I have over a dozen production databases I am kind of puzzeled.
Could you please share your thought in this regard? Thanks, David. September 11, - am UTC. It is impossible to give you a single answer - I can tell you 14gb is correct and give you a 'for example', I can tell you 14gb is very wrong and give you a for example , I can tell you it matters not whether you use 14gb or some other value and Ours is an OLTP database.
We have set Auto SGA. As per metalink this wait event happens when Oracle tries to pull memory out of some pool and allocates it to something else. Can you please describe how to reduce this wait event? Should we go for manual SGA? September 15, - pm UTC. No matter what. Now, is your major wait event actually causing a performance related problem - have you determined that your applications are suffering from this.
I ask, because I have had more than one time where people say "always we see X and Y as our top two wait events, how can we fix that", only to discover they were waiting for like 20 seconds in a 15 minute window across dozens of users eg: so what. My first thought is "you have a hard parse problem" it always comes back to binding. So, do you? Since the number of latches is limited then the bigger SGA you have the longer the chains of buffers you get, the longer it takes to scan it, the longer your process is holding the dedicated latch.
I think what you have to do is dedicating the amount of memory you think you need, and then monitor your system. At the same time you should not forget that your disk probably cost much more than your memory so why not take advantage of them A reader, October 01, - pm UTC.
Tom I remember an old post of yours which I couldn't find where you described a problem with a shared pool that was too big. Every night, staff was paid to flush the pool at 3am. You had them drop the pool to 70MB and everything was fine. I have another situation where the pool seems awfully large to me, 1. This doesn't "magically" tell you what the pool should be does it?
October 03, - pm UTC. Recently I was given the responsibility to manage one 10g database which someone else configured. It is running in manual memory management mode so shared pool, java pool, large pool is set manually. What is this part of sga and what is it used for? Thank you for all the wonderful tips that you share on this website. Hi Tom, We are runnning oracle Thanks in advance. Hi Tom, Do you mean that the parameters were set manually. February 17, - am UTC.
Goto your init. Overview of the parameters in the init. Suppose my database size is 6. I have set some parameters in the init. Could you please check and tell whether the following parameters are correct in the init.
Hi, Here's what I do to size up the sga. We set the sga to approx mb. We run the apps a few times. Also as a rule of thumb, re-sizing sga or other pools is the last resort for us.
Usually dramatic improvements are seen by tuning the app code. Am I right Tom? March 31, - am UTC. Except for: "Usually dramatic improvements are seen by tuning the app code.
The size of the SGA and PGA has to be a function of the amount of real memory installed - in addition to other things. Additional Technical info on previous comment. Is that whats happening here? April 01, - am UTC. If I had 4gb of ram, mb would not have been my starting point, probably closer to 2gb at least. HI Tom, Thanks for the answer. Also I can understand the auto tuning of the memory pools, but what I want to know is what data inside oracle forms the basis from which Oracle derives the intelligence of howmuch to scale up or scale down the pools.
Can I set this by bouncing one instance at a time instead of re-starting entire RAC-database? This is for 10gRel2 on Solaris Thank you.
May 02, - am UTC. Hi Tom, Thanks for you reply. Appreciate it! So we do load balancing via services we created and using resource manager where we do heavy lifting on 2 powerful nodes versus quick queries and transactions on other 3. Now 3 nodes are maxed out with 32G of physical memory so we cannot add more memory, but we would like to make use other 2 nodes where we 60G and 80G of memory resp.
Can we set that at SID level as well or is it set globally same for all instances in cluster? We would like to do this without downtime if possible. I'm asking lot of questions here as I couldn't find clear answer on having imbalancing in terms of SGA between RAC nodes in any oracle docs.
Appreciate your help and thanks for taking some time out from your busy schedule. May 02, - pm UTC. Hi Tom, Thank you for your quick and prompt reply. Thanks again! August 06, - am UTC. I do not understand. Would you use words in the future, we are not talking over our mobile phones.
Why are you setting an underscore parameter. Unfortunately, they can only be used once a system is operating and running under a normal load; so for our discussion of installation, they are useless.
If you have no idea whatsoever, make the buffer area at least 60 to MB or so you will usually outgrow the Oracle default rather quickly for a database that is near 1 gigabyte in total physical size, and up to MB for one that is around 20 gigabytes in size. For databases smaller than 1 gigabyte physical size, the Oracle defaults may be usable. Make the shared pool at least 50 to MB. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy. Already have an account?
Sign in. By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account. We'll send a magic link to your inbox. Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic.
Hie Sapna, Before creating an Oracle database you need to calculate the total size of the physical database structures , the size of the various pools etc. All the best bye Vinay. Tablespaces and other physical structures i m ready with regards Sapna.
0コメント