Home » RDBMS Server » Performance Tuning » Impact of RAM to oracle (11gR2 on HP-UX IA64)
Impact of RAM to oracle [message #594262] Tue, 27 August 2013 01:12 Go to next message
yipchunyu
Messages: 5
Registered: August 2013
Junior Member
We have two Oracle 11g R2 running on HP-UX IA64 (with 192 GB ram).
One for production and the other running for test (2 instances on it).
I want to have a concrete picture to see the impact of the RAM.
For example, will it runs faster enough on the UAT machines (as if we double the RAM).
Or whether it helps the production machines too.

It's because we already ask Oracle's support for advice and they suggest that adding more ram won't help.
But I saw some other people say that adding RAM will greatly help the performance.

Can I simply keep running below SQL to monitor the V$DB_CACHE_ADVICE? (in daily operation / running batch job) to check?
Or it has a better way to predict the performance gain with more RAM?

SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')
AND advice_status = 'ON';


* I am not a DBA and know very little on these issues. I just want to know more on personal interest.
Re: Impact of RAM to oracle [message #594263 is a reply to message #594262] Tue, 27 August 2013 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Adding RAM will help if you have a lack of RAM otherwise it could even decrease the performances.

Quote:
Can I simply keep running below SQL to monitor the V$DB_CACHE_ADVICE? (in daily operation / running batch job) to check?


You can FIRST do that and see what it tells you.

The following is useless in your query:
Quote:
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')


Regards
Michel
Re: Impact of RAM to oracle [message #594295 is a reply to message #594263] Tue, 27 August 2013 04:23 Go to previous messageGo to next message
yipchunyu
Messages: 5
Registered: August 2013
Junior Member
Dear Michel,
Thx for your quick advice. I don't understand why adding more RAM will even decrease the performance. Would u pls share some information for my study? Many thx in advance.
Re: Impact of RAM to oracle [message #594301 is a reply to message #594295] Tue, 27 August 2013 04:28 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The real question to my mind is why you trust google and forum posters (no disrespect Michel Wink ) more than the vendors actual support team...
Re: Impact of RAM to oracle [message #594310 is a reply to message #594295] Tue, 27 August 2013 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't say it will, I said it could.
More memory -> less io -> more cpu -> more concurrency on latch -> more waits -> less performances
As I said, it is not mandatory it works like this but it could and this is not the only way.
It is not harmless to add more something (memory, cpu...).

Regards
Michel
Re: Impact of RAM to oracle [message #594317 is a reply to message #594310] Tue, 27 August 2013 06:54 Go to previous messageGo to next message
roblco
Messages: 1
Registered: March 2012
Junior Member
I'm not saying that Michel's conjecture is true, but if it is. I can see that Oracle might just to an inappropriate nested loop since the whole table is in memory instead of a merge (from memory). Remember that humans makes these programs and they go in with certain assumptions.
Re: Impact of RAM to oracle [message #594362 is a reply to message #594317] Tue, 27 August 2013 21:25 Go to previous messageGo to next message
yipchunyu
Messages: 5
Registered: August 2013
Junior Member

I did some checking on last night's situation and want to share with you (and seek for advice again).
* sorry I don't know how to format the table properly.

SGA Target = 90GB

Time Cache (MB) Buffers Estd Phys|Read Factor Estd Phys| Reads
Before Batch 1664 196586 3.1424 53064410
Before Batch 17536 2071714 1 16886540
Before Batch 33280 3931720 0.8495 14344901
Restarted Instance 7936 937657 1 90315
Restarted Instance 80384 9497558 1 90315
Restarted Instance 158720 18753140 1 90315
Just started Batch 7936 937657 1 1272264
Just started Batch 80384 9497558 1 1272264
Just started Batch 158720 18753140 1 1272264
Batch 7936 937657 1.6097 30662437
Batch 80384 9497558 1 19048083
Batch 158720 18753140 0.9913 18882797
Batch 7936 937657 1.9738 76564474
Batch 80384 9497558 1 38790310
Batch 158720 18753140 0.9238 35833626
After Batch 7936 937657 1.8039 113862332
After Batch 80384 9497558 1 63121122
After Batch 158720 18753140 0.8951 56502070

Does it mean adding RAM is no use to improve the batch? Or simply because we restart the instance (before batch) and so the adviser won't reflect it fast enough (which if we double the DB cahce, it should helps the batch jobs, just like the advisor suggest during after the batch?)
Re: Impact of RAM to oracle [message #594363 is a reply to message #594362] Tue, 27 August 2013 21:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

above contains directions how to maintain table format!
Re: Impact of RAM to oracle [message #594429 is a reply to message #594362] Wed, 28 August 2013 05:59 Go to previous messageGo to next message
yipchunyu
Messages: 5
Registered: August 2013
Junior Member
Re-posted with proper format:

I did some checking on last night's situation and want to share with you (and seek for advice again).


SGA Target = 90GB

Time	           Cache (MB)	Buffers	Estd     Phys|Read Factor     Estd Phys| Reads
Before Batch	        1664    196586	         3.1424	              53064410
Before Batch	       17536	2071714	         1	              16886540
Before Batch	       33280	3931720	         0.8495	              14344901
Restarted Instance	7936	937657	         1	              90315
Restarted Instance     80384	9497558	         1	              90315
Restarted Instance    158720	18753140	 1	              90315
Just started Batch	7936	937657	         1	              1272264
Just started Batch     80384	9497558	         1	              1272264
Just started Batch    158720	18753140	 1	              1272264
Batch	                7936	937657	         1.6097	              30662437
Batch	               80384	9497558	         1	              19048083
Batch	              158720	18753140	 0.9913	              18882797
Batch	                7936	937657	         1.9738	              76564474
Batch	               80384	9497558	         1	              38790310
Batch	              158720	18753140	 0.9238	              35833626
After Batch	        7936	937657	         1.8039	              113862332
After Batch	       80384	9497558	         1	              63121122
After Batch	      158720	18753140	 0.8951	              56502070


Does it mean adding RAM is no use to improve the batch? Or simply because we restart the instance (before batch) and so the adviser won't reflect it fast enough (which if we double the DB cahce, it should helps the batch jobs, just like the advisor suggest after the batch?)
Re: Impact of RAM to oracle [message #594453 is a reply to message #594301] Wed, 28 August 2013 10:13 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
In my experience the vendor is amongst the last person I'd trust for advice. They generally seem come from the "throw more hardware at it" stable.
Re: Impact of RAM to oracle [message #594496 is a reply to message #594453] Thu, 29 August 2013 03:43 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Well, yes. But in this case they're saying not to Wink
Re: Impact of RAM to oracle [message #594531 is a reply to message #594496] Thu, 29 August 2013 10:06 Go to previous message
yipchunyu
Messages: 5
Registered: August 2013
Junior Member
Roachcoach wrote on Thu, 29 August 2013 16:43
Well, yes. But in this case they're saying not to Wink


it's simply because the hardware is from HP (instead of Oracle itself). LOL

anyway, so, my assumption seems correct? Which it simply because the instance has been restarted and so the db cache advisor won't update fast enough?
How should I quantify the performance improvement? For example,
If I double the db cache (to 150GB), the physical IO will be lower. But how will it translate to actual performance gain? Need to run the batch with extra RAM for a real figure?

After Batch	        7936	937657	         1.8039	              113862332
After Batch	       80384	9497558	         1	              63121122
After Batch	      158720	18753140	 0.8951	              56502070

Previous Topic: NOWAIT lock requests could hang (like Parallel Queries may hang "enq: TS - contention")
Next Topic: Please help on this
Goto Forum:
  


Current Time: Thu Mar 28 07:53:48 CDT 2024