Home » RDBMS Server » Performance Tuning » Please advise on configuring memory (Oracle 11.1, Windows Server 2008 R2)
Please advise on configuring memory [message #596176] Wed, 18 September 2013 11:39 Go to next message
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 #596178 is a reply to message #596176] Wed, 18 September 2013 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What make you think the bad performances come from the memory?

Regards
Michel
Re: Please advise on configuring memory [message #596183 is a reply to message #596178] Wed, 18 September 2013 12:19 Go to previous messageGo to next message
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 #596186 is a reply to message #596183] Wed, 18 September 2013 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can only repeat what I said: "What make you think the bad performances come from the memory?".
Why do you care about SGA size?

Quote:
It seem to me it has used up the allocated memory.


It will always use all the memory you give to it (unless you do nothing with your database).
If you have a house with 10 rooms and 15 persons will you use only 2 rooms?

Regards
Michel
Re: Please advise on configuring memory [message #596188 is a reply to message #596183] Wed, 18 September 2013 12:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
jxh461 wrote on Wed, 18 September 2013 22:49
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.


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 Go to previous messageGo to next message
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 #596191 is a reply to message #596188] Wed, 18 September 2013 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

Re: Please advise on configuring memory [message #596192 is a reply to message #596190] Wed, 18 September 2013 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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:00
Quote:
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 Go to previous messageGo to next message
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 #596196 is a reply to message #596194] Wed, 18 September 2013 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for example, WHY the trace file generation privilege is always with the DBAs and not with developers?


It is FIRST, as for all system privileges, a DBA thing for security and availability reasons which does not mean it is not a developer thing, or most likely a DBA developer or developer DBA as most developers are not able to understand a trace file.

Anyway, it is useless to try to optimize memory when the application is badly written. It is just a waste of time. For instance, if an application use literals instead of bind variables you can have as much memory as you want and as many as CPU as you want you will still have bad performances sooner or later.

Re: Please advise on configuring memory [message #596197 is a reply to message #596196] Wed, 18 September 2013 15:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #596201 is a reply to message #596199] Wed, 18 September 2013 15:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then I can rule it out as the cause of the latency.


Wrong reasoning.
Post evidences.

Quote:
maximizing the use of available memory is what I would like to accomplish.


So why you post a question here? increase the SGA as it seems it is the only option you accept.

Re: Please advise on configuring memory [message #596203 is a reply to message #596199] Wed, 18 September 2013 15:42 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
jxh461 wrote on Thu, 19 September 2013 01:58
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 ?


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 #596204 is a reply to message #596201] Wed, 18 September 2013 15:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also 2 remarks:
1/ If you cannot make your application with 1.6G for a single user then you certainly have problems with the application not with Oracle
2/ Apart from consuming your time, what is the purpose of performances test on a system that has not the same number of users, the same memory, the same CPU, the same data, the same workload than the production?
3/ (Yes I'm not good in maths) If the production goes well and the test not, be happy everyone else has the opposite and it is far less comfortable.

Re: Please advise on configuring memory [message #596205 is a reply to message #596199] Wed, 18 September 2013 15:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post FORMATTED results from SGA Advisor from both Test & Prod DBs
Re: Please advise on configuring memory [message #596207 is a reply to message #596203] Wed, 18 September 2013 15:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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".


This is obvious, Oracle runs benchmarks at thousands tps with less than 1G of SGA since about 30 years.

Re: Please advise on configuring memory [message #596208 is a reply to message #596204] Wed, 18 September 2013 16:00 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Quote:
1/ If you cannot make your application with 1.6G for a single user then you certainly have problems with the application not with Oracle

The application is a robust web based reporting tool. With all the procedures and functions running behind the scenes there are multiple sessions invoked. All that is good but why does it take a long time ? The first obvious aspect is the diminished resources compared to production. Hence my inquiry.

Quote:
Apart from consuming your time, what is the purpose of performances test on a system that has not the same number of users, the same memory, the same CPU, the same data, the same workload than the production?

It's purpose if for me as a support analyst with high dba interest to have a work area that does not interupt production. I also allow trainers to do demos on the test server.

Quote:
(Yes I'm not good in maths) If the production goes well and the test not, be happy everyone else has the opposite and it is far less comfortable.

Smile Production has to go well or else lots of people don't get sleep.
Re: Please advise on configuring memory [message #596213 is a reply to message #596208] Wed, 18 September 2013 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The first obvious aspect is the diminished resources compared to production.
OBVIOUS?
1) bad statistics
2) different initialization parameter values.
3) different disk sub-system
4) different number & speed of CPUs

If you don't really know where the application is actually spending its time,
you can only shoot in the dark & make random changes while hoping to get lucky.

SQL_TRACE=TRUE can report where actual time is being consumed.
Until you have some actual facts & details, you continue to pursue Ready, Fire, AIM troubleshooting approach
Re: Please advise on configuring memory [message #596221 is a reply to message #596176] Thu, 19 September 2013 02:32 Go to previous messageGo to next message
John Watson
Messages: 8922
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.
Re: Please advise on configuring memory [message #597377 is a reply to message #596221] Fri, 04 October 2013 02:11 Go to previous messageGo to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
whether remaining RAM size will oracle able to assume after restarting the database,? i mean, from 1648M to 5G size, ?
icon5.gif  Re: Please advise on configuring memory [message #597378 is a reply to message #597377] Fri, 04 October 2013 02:24 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean?

Previous Topic: Need help to tune this query
Next Topic: How to configure SGA_MAX_SIZE parameter
Goto Forum:
  


Current Time: Thu Mar 28 03:46:21 CDT 2024