| Please advise on configuring memory [message #596176] |
Wed, 18 September 2013 11:39  |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Hi All,
I am currently experiencing some very slow performance on my application. It is a test environment.
I have requested and obtained additional RAM on the server. I currently have 6.00GB.
When I look at the task manager only 2.64GB is used. Below is some information you may need in order to advise me properly.
SQL> show parameters memory
NAME TYPE VALUE
------------------------------------ ----------- ----------
hi_shared_memory_address integer 0
memory_max_target big integer 1648M
memory_target big integer 1648M
shared_memory_address integer 0
Let me know what other information you need and thank you all in advance for your support.
|
|
|
|
|
|
| Re: Please advise on configuring memory [message #596183 is a reply to message #596178] |
Wed, 18 September 2013 12:19  |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
I am looking at sga_max_size and it is 1648M, which is also the memory_max_target.
It seem to me it has used up the allocated memory. I was thinking of using ALTER statement to increase memory_max_target,
but I am not sure what exactly happens when I do that. Please advise and instruct.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1648M
sga_target big integer 0
|
|
|
|
|
|
| Re: Please advise on configuring memory [message #596188 is a reply to message #596183] |
Wed, 18 September 2013 12:32 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jxh461 wrote on Wed, 18 September 2013 22:49I am looking at sga_max_size and it is 1648M, which is also the memory_max_target.
It seem to me it has used up the allocated memory. I was thinking of using ALTER statement to increase memory_max_target,
but I am not sure what exactly happens when I do that.
Are you 100% sure that the complete SGA is being used by this process? SGA is shared memory. There might be several jobs using up the SGA.
I agree that the best people in this industry follow the bottom-to-top approach, i.e. first thinking about the DBA aspects and then going to the code. However, your question is a bit ambiguous.
|
|
|
|
| Re: Please advise on configuring memory [message #596190 is a reply to message #596176] |
Wed, 18 September 2013 13:24 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Hi Lalit,
I apologize for posing an ambiguous question. My situation is such that I have a test database with full scale production data volume.
Production is using 12G for the SGA and 8G for PGA . I do not expect my test database to perform as in production given that I can't
possible get the same resources, also production is clustered on 4 nodes. However, I would like to increase the current performance
of my application in the test environment. The test environment does not have as many users as production, rarely has more than one person
using the application.
What could be the cause of the performance if not the database memory allocation ? Can you advise on what to look for ?
It looks like I can allocate as much as 3G to the overall memory allocated for the database. Can you advise me on how best I can do this ?
Thanks
|
|
|
|
|
|
| Re: Please advise on configuring memory [message #596192 is a reply to message #596190] |
Wed, 18 September 2013 13:31 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:What could be the cause of the performance if not the database memory allocation ? Can you advise on what to look for ?
The application.
And you say it yourself:
Quote:The test environment does not have as many users as production, rarely has more than one person using the application.
So this can't be a memory problem (assuming the application is tuned).
[Updated on: Wed, 18 September 2013 13:32] Report message to a moderator |
|
|
|
| Re: Please advise on configuring memory [message #596193 is a reply to message #596192] |
Wed, 18 September 2013 13:48 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
The application is what it is in production. The only difference is the amount of resources allocated on the database in test.
I am looking to improve the performance in test, knowing that I can't get optimum performance as in production.
Therefore, I would like some information on how to allocate the additional memory to the database.
I will expect to see improvements when I am actually doing something with the application.
Please advise on how to do this.
Thanks
|
|
|
|
| Re: Please advise on configuring memory [message #596194 is a reply to message #596191] |
Wed, 18 September 2013 14:25 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 19 September 2013 00:00Quote:I agree that the best people in this industry follow the bottom-to-top approach, i.e. first thinking about the DBA aspects and then going to the code.
This is wrong, the best approach is to start from the business rules to end with the OS optimizations.
So you tune the application before tuning the database.
Michel, I disagree on your generic statement. I just commented on the approach, not a convention. I don't want to argue on off-topic things, but, since you disagreed, I have to justify my own statements. Had the business rules of the application been always above DBA aspects, then, for example, WHY the trace file generation privilege is always with the DBAs and not with developers?
|
|
|
|
| Re: Please advise on configuring memory [message #596195 is a reply to message #596176] |
Wed, 18 September 2013 14:30 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jxh461 wrote on Wed, 18 September 2013 22:09
I am currently experiencing some very slow performance on my application. It is a test environment.
Once again, is it happening in production too? Did you or your DBAs for that matter find any bottlenecks(even in test environment)? Why do you THINK that the SGA size is responsible for slow performance. And slow performance of the application not always necessarily be due to the DB. Sometimes, DB is just the victim, and not the culprit.
Knowing just a part or bit of your environment, it is impossible to conclude anything. Digging into the root cause is simply impossible.
|
|
|
|
|
|
| Re: Please advise on configuring memory [message #596197 is a reply to message #596196] |
Wed, 18 September 2013 15:18 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
Have to partially agree with you. When X and Y are inter-dependent on each other, neither could X nor Y be responsible for anything in general. X and Y being the subject of our discussion. They must go hand-in-hand. Let's take it up with a better discussion sometime else. OP must be eagerly waiting for an answer, and won't be satisfied with any of our answers for sure.
|
|
|
|
| Re: Please advise on configuring memory [message #596199 is a reply to message #596195] |
Wed, 18 September 2013 15:28 |
jxh461
Messages: 185
Registered: March 2005
|
Senior Member |
|
|
Quote:Once again, is it happening in production too?
No. Production is doing fine with its immense resources allocated as I mentioned above. The same resources cannot be allocated to test environment including memory resource. All I can hope for is to improve current test environment performance. If I can allocate to available memory to the database, then I can rule it out as the cause of the latency. Can you advise on how to do the underlined correctly ?
Tuning the application is not an option in my case, maximizing the use of available memory is what I would like to accomplish.
Thanks
|
|
|
|
|
|
| Re: Please advise on configuring memory [message #596203 is a reply to message #596199] |
Wed, 18 September 2013 15:42 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
jxh461 wrote on Thu, 19 September 2013 01:58If I can allocate to available memory to the database, then I can rule it out as the cause of the latency. Can you advise on how to do the underlined correctly ?
I have three links from asktom, where the master has himself commented on the SGA and it's share in memory.
SGA size
very large SGA
shared_pool_size
The interesting thing in all the three links is that, T.Kyte says : To have a a very large SGA, "it is just "not necessary".
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Please advise on configuring memory [message #596221 is a reply to message #596176] |
Thu, 19 September 2013 02:32 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I think the core of your question is: "my hypothesis is that poor performance is caused by low memory allocation. How can I test this?" That is easy. You have memory_target set to 1648M, and you have 6G RAM in the machine. So:
alter system set memory_max_target=5g scope=spfile;
alter system set memory_target=5g scope=spfile;
startup force
Set timing on, and run your queries. This will take you a couple of minutes.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
Production has to go well or else lots of people don't get sleep.